Mysql
引擎狀態輸出中缺少 InnoDB 緩衝池命中率
我想檢查 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]至於你的第二個問題，show engine innodb 輸出中確實沒有足夠的資訊來做出決定。您需要考慮伺服器的 RAM 量，無論它是在做其他事情還是它是專用伺服器，是否混合在 MyISAM 表中（這樣您就不會餓死 OS 記憶體，從而扼殺 MyISAM 性能）。
當我不確定我的設置時，我是MySQL 的 Percona 配置嚮導的粉絲。它不會給你“最好的”性能，但它應該給你一個相當不錯的地方來開始你的調整。