Is the buffer pool big enough: hit ratio, wait_free and online resize

Command

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%';

What it does

Shows buffer pool size, cache hit ratio, free / dirty page counts and the free-page wait (wait_free) counter; resizes it without a restart and tracks progress.

When to use it

When read IO is high, after adding / removing RAM, or during capacity planning.

Caution

Hit ratio alone is misleading; even 99% can mean thousands of disk reads per second. Look at the per-second growth of Innodb_buffer_pool_reads; wait_free > 0 means the buffer pool is too small or the page cleaners cannot keep up. The new size is rounded to a multiple of innodb_buffer_pool_chunk_size × instances. Brief stalls are possible during a resize, more so when shrinking; avoid peak hours. On a dedicated server start at 60–75% of RAM; leave room for per-connection memory, OS cache and other processes, since swapping is always worse than a smaller buffer pool.