msyql数据库性能监控整理

msyql数据库性能监控整理

1、监控thread_cache命中率
admin@localhost : (none) 07:51:20> show variables like '%thread%';
+---------------------------------------+---------------------------+
| Variable_name | Value |
+---------------------------------------+---------------------------+
| innodb_read_io_threads | 1 |
| innodb_thread_concurrency | 0 |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_purge_thread | 8 |
| innodb_write_io_threads | 16 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| pseudo_thread_id | 2919973 |
| thread_cache_size | 512 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
| thread_statistics | OFF |
+---------------------------------------+---------------------------+
14 rows in set (0.00 sec)
说明：可以看出thread_cahce池中最大可以放512个连接线程，每个线程分配262144/512=512K内存空间

admin@localhost : (none) 07:50:50> show status like '%connection%';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| Connections | 2920267 |
| Max_used_connections | 1008 |
+----------------------+---------+
2 rows in set (0.01 sec)

admin@localhost : (none) 07:51:08> show status like '%thread%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Com_show_thread_statistics | 0 |
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 219 |
| Threads_connected | 790 |
| Threads_created | 2821 |
| Threads_running | 4 |
+----------------------------+-------+
7 rows in set (0.00 sec)
从上可以看出到目前为止服务器共有2920267 次连接，最大并发数为1008，当前thread_cahce中连接有219个，连接数为790个，共创建了2821次连接，当前活跃的有4个。
利用公式可以计算出thread_chace的命中率 ：（Connections - Threads_created）/Connections *100=99.9%

2、监控innodb cache
admin@localhost : (none) 08:08:24> show status like '%innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 1007126 |
| Innodb_buffer_pool_read_ahead_evicted | 938110 |
| Innodb_buffer_pool_read_requests | 643795018139 |
| Innodb_buffer_pool_reads | 24749326 |
+---------------------------------------+--------------+
5 rows in set (0.00 sec)
admin@localhost : (none) 08:08:32> show status like '%innodb_buffer_pool_wait%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 27 |
+------------------------------+-------+
1 row in set (0.00 sec)
admin@localhost : (none) 08:09:06> show variables like '%innodb_buffer%';
+---------------------------------+-------------+
| Variable_name | Value |
+---------------------------------+-------------+
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 23622320128 |
+---------------------------------+-------------+
3 rows in set (0.00 sec)

说明：1 - （Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)=1 - 24749326/643795018139 =3.84 说明innodb cache的命中率并不怎么高。

 

原文地址：http://blog.sina.com.cn/s/blog_4e46604d01016obc.html