Innodb存储引擎的缓存命中率计算

最新推荐文章于 2025-02-24 22:34:27 发布

weixin_34289454

最新推荐文章于 2025-02-24 22:34:27 发布

阅读量185

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/FengGeBlog/p/10283095.html

本文详细介绍了如何通过分析InnoDB缓存命中率来优化MySQL数据库性能，重点关注Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads两个指标，以此判断是否需要调整buffer_pool的大小。

数据库的慢查询是我们在生产环境中必须经常检测的，如果慢查询语句过多，说明我们应该增加buffer_pool的大小了。常常检查的指标就是查看缓存命中率是否过低。

mysql> show status like 'innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 190107  7:19:16 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 31732                                            |
| Innodb_buffer_pool_bytes_data         | 519897088                                        |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 1298499                                          |
| Innodb_buffer_pool_pages_free         | 1024                                             |
| Innodb_buffer_pool_pages_misc         | 8                                                |
| Innodb_buffer_pool_pages_total        | 32764                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 438699486                                        |
| Innodb_buffer_pool_reads              | 536971                                           |
| Innodb_buffer_pool_wait_free          | 42342                                            |
| Innodb_buffer_pool_write_requests     | 237826577                                        |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.04 sec)