LLI Wiki — My Digital Knowledge Base

Calculating the InnoDB buffer pool hit ratio

Calculate the InnoDB buffer pool hit ratio from the status counters, and set the buffer pool size.

The buffer pool hit ratio is the share of InnoDB page reads that are served from memory instead of disk. A low ratio can mean that the buffer pool is too small for the working set. Look at the trend over time rather than at a single value.

Read the counters

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Sample output:

Variable_name Value
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 0
Innodb_buffer_pool_read_ahead_evicted 91
Innodb_buffer_pool_read_requests 141420021
Innodb_buffer_pool_reads 57486155

Innodb_buffer_pool_read_requests counts logical reads. Innodb_buffer_pool_reads counts the reads that had to go to disk.

Compute the hit ratio

hit ratio (%) = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100

With the sample values: (1 - 57486155 / 141420021) * 100, which is about 59.4 %. The ratio Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests (about 40.6 % here) is the miss ratio, not the hit ratio.

Negative or huge values

A value such as 18446744073709549183 is 2^64 minus a small number. It is a negative number shown as an unsigned 64-bit integer, which means a subtraction between two counters gave a negative result. Check the formula and the two samples it uses.

Check and change the buffer pool size

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

On MySQL 5.7 and earlier, the same value is in information_schema.GLOBAL_VARIABLES. On MySQL 8.0, use performance_schema.global_variables.

Change the size at runtime. The value is in bytes, so 3221225472 is 3 GB:

SET GLOBAL innodb_buffer_pool_size = 3221225472;

Make the change permanent in the configuration file:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

To dump or load the buffer pool immediately, set innodb_buffer_pool_dump_now or innodb_buffer_pool_load_now to ON with SET GLOBAL.

Notes

Mastodon LLIMastodon SocialFosstodon