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
- The key cache is the equivalent for MyISAM tables, and its hit ratio is misleading for many workloads. Percona explains why in Why you should ignore MySQL's key cache hit ratio.
- Reference: InnoDB buffer pool, MySQL 5.6 reference manual.