我想检查MySQL上的InnoDB的缓冲池命中率，但是当我查询数据库时，命中率似乎从结果中消失了：
使用查询： SHOW ENGINE INNODB STATUS\G
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 169799966; in additional pool allocated 6386688 Dictionary memory allocated 1122462 Buffer pool size 8192 Free buffers 1 Database pages 8163 Old database pages 2993 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 610, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 3483, created 6047, written 184508 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8163, unzip_LRU len: 0 I/O sum[41]:cur[0], unzip sum[0]:cur[0]
我期待Buffer pool hit rate 999 / 1000 ，哪里去了？
顺便说一句：上面输出的任何注释？ 我应该增加游泳池的大小吗？
这是正确的地方，在“页面预读”开始的行之上。 如果在后续的SHOW ENGINE INNODB STATUS \ G之前，没有任何活动碰到缓冲池，那么“没有缓冲池页获取”。
在一个innodb表上运行一个查询，你应该再次看到它。
mysql> SHOW ENGINE INNODB STATUS \ G SELECT COUNT（id）FROM table; SHOW ENGINE INNODB STATUS \ G
没有缓冲池页面得到：
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 26461863936; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 657921776 (407996024 + 249925752) Page hash 25500536 (buffer pool 0 only) Dictionary cache 102125423 (102000592 + 124831) File system 88288 (82672 + 5616) Lock system 63750528 (63750152 + 376) Recovery system 0 (0 + 0) Dictionary memory allocated 124831 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 0 Database pages 1557609 Old database pages 574956 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 464944064, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 470241613, created 2454749, written 331978152 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1557609, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
询问innodb：
+-----------+ | COUNT(id) | +-----------+ | 291 | +-----------+ 1 row in set (0.01 sec)
现在你看到'缓冲池命中率'：
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 26461863936; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 657921776 (407996024 + 249925752) Page hash 25500536 (buffer pool 0 only) Dictionary cache 102125423 (102000592 + 124831) File system 88288 (82672 + 5616) Lock system 63750528 (63750152 + 376) Recovery system 0 (0 + 0) Dictionary memory allocated 124831 Buffer pool size 1572863 Buffer pool size, bytes 25769787392 Free buffers 0 Database pages 1557609 Old database pages 574956 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 464944064, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 470241613, created 2454749, written 331978152 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1557609, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
至于你的第二个问题，显示引擎innodb输出中没有足够的信息来做出这个决定。 您需要考虑服务器的RAM数量，无论是做其他事情，还是专用服务器，无论您是否在MyISAM表中混合（因此您不要使操作系统caching挨饿，导致MyISAM性能下降）。
当我不确定我的设置时，我是MySQL的Perconaconfiguration向导的粉丝。 它不会给你“最好”的performance，但它应该给你一个相当不错的地方开始你的调整。
SELECT FLOOR(NUMBER_PAGES_GET/(NUMBER_PAGES_GET+NUMBER_PAGES_READ) * 100) as InnoDB_Buffer_Pool_Hit_Rate FROM information_schema.INNODB_BUFFER_POOL_STATS;