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的命中率并不怎么高。