Diagnosing MariaDB Performance Loss from an Undersized InnoDB Buffer Pool

Recognizable condition

Queries that were previously fast become consistently slower under normal load, with increased read latency and more queries appearing in the slow query log. The server shows high disk I/O while CPU remains moderate. The useful takeaway is that the InnoDB buffer pool is too small for the working set, so most reads miss cache and go to disk.

The buffer pool is InnoDB's in-memory cache for data and index pages. When the working set of hot data exceeds the pool, evictions force frequent physical reads and flushes.

Cause / diagnostic table

Limitations

Metrics can be affected by replication lag or read replicas. Inspect the primary node that serves writes. Buffer pool statistics are cumulative since start; compare deltas over a consistent interval. The pool size is a restart-only variable in MariaDB.

Ordered checks on the primary

Run checks in MariaDB client as a user with privileges to view global status and variables. Do not run on a replica for sizing decisions.

1. Confirm current allocation vs data size

Run:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Note the bytes value. Compare to the approximate size of InnoDB data files on disk and the expected hot working set. If pool size is significantly smaller than the working set, undersizing is plausible.

2. Measure buffer pool hit ratio

Run:

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_reads';

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_read_requests';

Compute hit ratio as 1 - reads / requests over a 5 to 15 minute interval. A sustained ratio below ~0.90 suggests many reads miss cache. Record both values before and after the interval to avoid startup bias.

3. Check pages in use and dirty pages

Run:

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_data';

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_dirty';

High pages_data close to total pages indicates the pool is full. Persistently high pages_dirty with frequent flushes points to write pressure from a small pool.

4. Quantify I/O waits

Query performance_schema for InnoDB I/O waits:

SELECT EVENT_NAME, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/io/file/innodb%';

Increased wait time correlates with cache misses. Cross-check with server metrics for innodb_io_r and innodb_io_w growth.

5. Correlate with slow queries

Review slow query log timestamps against periods of high read_requests and low hit ratio. If slow queries rise when buffer pool is saturated, the pool is a contributing factor.

Fixes tied to findings

Hit ratio low, pool smaller than working set

Increase innodb_buffer_pool_size in my.cnf under [mysqld]. Keep allocation at roughly 50 to 70 percent of available RAM to avoid swapping. This change requires a server restart.

Example configuration change:

[mysqld]
innodb_buffer_pool_size = 8G

Risk: restart causes downtime. Insufficient RAM leads to swapping and worse performance.

High dirty pages and flush pressure

Verify innodb_io_capacity and innodb_flush_method are appropriate for storage. If the pool is already sized reasonably, tune flush behavior rather than only increasing pool size.

Pool sized correctly but hit ratio still low

Investigate non-buffer-pool causes: inefficient queries, missing indexes, or a working set that is simply larger than RAM can hold. Consider query optimization or archival.

Escalation criteria

• Hit ratio remains below 0.90 after increasing pool to safe RAM limits.
• I/O waits remain high with low buffer pool activity, indicating storage or hardware limits.
• Performance degrades after restart with increased pool size, suggesting memory pressure or swapping.

Escalate to storage and capacity planning. Collect delta metrics over a full business cycle before further changes.

Verification after change

After a restart with a new pool size, re-run the hit ratio measurement over the same interval and load pattern. Confirm pages_data stabilizes below total pages and dirty pages flush without spikes. Monitor slow query log for reduction in I/O-bound queries.

Rollback: revert innodb_buffer_pool_size to the previous value in my.cnf and restart. Keep the previous value documented before change.