☰

引擎状态输出中缺少 InnoDB 缓冲池命中率

tag-icon • tag-icon mysql innodb database-administration database-performance
引擎状态输出中缺少 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：它去哪儿了？

顺便问一下：上面的输出有什么注释吗？我应该增加池大小吗？

答案1

它就在它应该在的位置，在以“预读页面”开头的行上方。如果在后续的 SHOW ENGINE INNODB STATUS\G 之前没有任何活动触及缓冲池，则会出现“无缓冲池页面获取”的情况。

在 innodb 表上运行查询你应该再次看到它。

mysql> 显示引擎 INNODB 状态\G 从表中选择 COUNT(id)；显示引擎 INNODB 状态\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 配置向导当我不确定我的设置时。它不会给你“最佳”性能，但它应该给你一个相当不错的地方来开始你的调整。

答案2

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;

相关内容