buffer pool 是否够大：命中率、wait_free 与在线调整大小

命令

SELECT ROUND(@@innodb_buffer_pool_size / 1024 / 1024) AS bp_mb,
       @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS chunk_mb,
       @@innodb_buffer_pool_instances AS instances;
SELECT
  ROUND(100 * (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
                 / NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)), 3) AS hit_pct,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free') AS pages_free,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS pages_dirty,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_wait_free') AS wait_free;

-- Online resize and status tracking
SET PERSIST innodb_buffer_pool_size = 8589934592;
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool_resize_status%';

作用

显示 buffer pool 大小、缓存命中率、空闲页/脏页数量以及空闲页等待（wait_free）计数器；无需重启即可调整其大小并跟踪进度。

适用场景

读 IO 很高、增减内存后或做容量规划时。

注意

单看命中率会产生误导；即使 99% 也可能意味着每秒数千次磁盘读。应关注 Innodb_buffer_pool_reads 每秒的增量；wait_free > 0 表示 buffer pool 太小或 page cleaner 跟不上。新大小会向上取整为 innodb_buffer_pool_chunk_size × 实例数的倍数。调整大小期间可能出现短暂停顿，缩小时更明显；避开高峰时段。在专用服务器上从内存的 60–75% 起步；为每连接内存、操作系统缓存和其他进程留出空间，因为发生 swap 总比 buffer pool 小一些更糟。