MySQL InnoDB Buffer Pool Tuning and Monitoring

The InnoDB buffer pool is MySQL’s most critical memory structure — it caches data pages, index pages, and change buffer pages in RAM, so most reads never touch the disk. Setting it correctly is the single highest-impact MySQL tuning step for WordPress.

Problem: The default MySQL InnoDB buffer pool size is 128 MB — far too small for a production WordPress database — causing excessive disk I/O as frequently-accessed pages are evicted from memory on every request.

Solution: Set innodb_buffer_pool_size to 70–80% of available RAM in my.cnf. Monitor hit ratio with SHOW STATUS LIKE 'Innodb_buffer_pool_reads' — a miss rate above 1% indicates the pool is undersized. Enable innodb_buffer_pool_dump_at_shutdown = ON to warm the pool on restart without a cold-start penalty.

The examples below set the correct buffer pool size, configure multiple buffer pool instances for parallel access, monitor the hit ratio and evictions in real time, and flush the pool safely during maintenance.

# Rule of thumb: buffer pool = 70-80% of available RAM for a dedicated MySQL server
# For a shared server (MySQL + PHP-FPM + Nginx): 40-50% of RAM
#
# Example: 8 GB server dedicated to MySQL → 6 GB buffer pool
# /etc/mysql/mysql.conf.d/mysqld.cnf  (or /etc/my.cnf)

[mysqld]
# ── BUFFER POOL SIZE ──
innodb_buffer_pool_size = 6G

# ── BUFFER POOL INSTANCES ──
# Reduces contention for pools > 1 GB: 1 instance per GB, max 64
# Each instance locks independently, improving parallel throughput
innodb_buffer_pool_instances = 6

# ── BUFFER POOL DUMP/LOAD ──
# After restart, MySQL reloads the last-used pages — warms up the pool instantly
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_pct         = 25   # dump only the hottest 25% of pages

# ── PAGE SIZE ──
# Default 16K is good for WordPress; keep unless migrating from an existing instance
innodb_page_size = 16384

# Apply changes:
systemctl restart mysql
# (innodb_buffer_pool_size can also be changed online in MySQL 5.7+)
SET GLOBAL innodb_buffer_pool_size = 6 * 1024 * 1024 * 1024;

Monitor the buffer pool hit ratio and detect eviction pressure:

-- Buffer pool hit ratio (should be > 99% in production)
SELECT
    ROUND(
        ( 1 - (
            ( SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads' ) /
            ( SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests' )
        ) ) * 100, 3
    ) AS hit_ratio_pct;

-- Buffer pool usage breakdown (pages)
SELECT
    pool_id,
    pool_size           AS total_pages,
    free_buffers        AS free_pages,
    database_pages      AS data_pages,
    modified_database_pages AS dirty_pages,
    pages_made_young    AS pages_promoted_to_hot,
    pages_not_made_young AS pages_stayed_cold
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- Pages being evicted before being accessed (bad sign)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
-- Non-zero means MySQL is waiting for free pages — buffer pool too small

-- How full is the buffer pool?
SELECT
    ROUND( database_pages / pool_size * 100, 1 ) AS fill_pct
FROM information_schema.INNODB_BUFFER_POOL_STATS
LIMIT 1;

-- Which tables/indexes occupy the most buffer pool pages?
SELECT table_name, index_name, COUNT(*) AS pages_cached
FROM information_schema.INNODB_BUFFER_PAGE
WHERE table_name LIKE '%ynYYAa7bFT%'
GROUP BY table_name, index_name
ORDER BY pages_cached DESC
LIMIT 20;

NOTE: If the hit ratio drops below 95%, the buffer pool is too small for the working dataset — add RAM or archive old data. If Innodb_buffer_pool_wait_free is non-zero, MySQL is running out of free pages and stalling writes — reduce innodb_max_dirty_pages_pct to flush dirty pages more aggressively, or increase the buffer pool size.