Improve MySQL Database Performance with the InnoDB Buffer Pool configuration

The database is often the source of performance problems in PHP applications, but there are many different reasons why this is the case.

The most straightforward is that individual queries that the application issues are slow, due to their inefficient structure, by not using indexes or other coding mistakes.

But for MySQL databases, a common problem is also the misconfiguration of the server itself. And MySQL has a single setting that is overwhelmingly responsible for the performance, called InnoDB Buffer Pool Size.

The setting declares how much of the database tables and indexes can be cached in memory (RAM). MySQL performance experts recommend keeping all data and indexes in memory.

Because databases are usually growing over time, this setting is not a “once and forever” setting, but it should be re-evaluated from time to time.

So if you are experiencing slow database queries, before blaming the individual query, you should always keep in mind to check if the database is too big for the InnoDB Buffer Pool.

Because if the database is too big, then depending on the application usage patterns, InnoDB keeps changing what it stores in memory constantly, always purging data again from the cache, and re-loading it from disk at high expense when its needed again.

Determine current setting of InnoDB Buffer Pool

To find out what the current size of the InnoDB buffer pool is, you can issue the following queries against your MySQL database:

In this case its 8 MB of memory. An extremely low value! The default value according to the MySQL docs is 128MB, however different distribution packages might already change that to something different.

Determine InnoDB Buffer Pool Cache Hit Rate

There are several ways to determine the current buffer pool cache hit rate. On the internet you often find instructions run the following command, looking at a huge blog of text of unstructured data.

The output is rather large so that sometimes it makes sense to set the pager tool to less before:

Search for the section “BUFFER POOL AND MEMORY” it will look like this:

You find the note “Buffer pool hit rate” at the bottom and it shows a 1000 / 1000, which is the best value you can get.

There is a better way to get this information from MySQLs internal data tables:

This can also be calculated through two global status variables, and you can run the following SQL query to get the same value:

Outputs:

This gives a hit rate of 99,999%, or a 1000 / 1000 as calculated in the InnoDB buffer pool status.

This is a very good value, but when its worse than 90%, then this usually means that too many reads are not hitting the buffer pool as mentioned before.

InnoDB status seems to multiply the percentage by 1000 instead of 100 to get “human readable” values, 971.975 essentially means 97,1975%.

But what time frame are those numbers relating to? Reading the MySQL docs very carefully it seems that the following is true, though I am not 100% sure about this. Please comment if you know more.

Command	Timeframe
SHOW ENGINE INNODB STATUS	Since last time command was run
SELECT HIT_RATE FROM information_schema.INNODB_BUFFER_POOL_STATS;	Since last time the command was run
Selecting from global status variables innodb_buffer_pool_reads and innodb_buffer_pool_read_requests	Since server startup

If you want to calculate how high the number is in a fixed time window, you need to store the variables innodb_buffer_pool_reads and innodb_buffer_pool_read_requests regularly and compute the hit rate on the difference of consecutive measurements.

	30 Minutes ago	Now	Difference
innodb_buffer_pool_reads	857970	858845	875
innodb_buffer_pool_read_requests	400926957061	403038674948	2111717887

This blog post on INNODB SHOW ENGINE STATUS walk-through by renowned MySQL performance expert Peter Zaitsev of Percona has little additional information about a good or bad value, but mentions that it depends on the application when a bad cache hit ratio starts leading to increased I/O.

How big is my MySQL dataset including indexes?

To understand how big the InnoDB Buffer pool should be, you need to look at how big the dataset actually is and there is this handy query that you can run to find that out:

For my Shopware Demo shop application this results in the following, with most rows cut for readability:

You can see the size in MB of individual tables, and the “rollup” meaning the sum over all the tables combined. Here we have 132MB of data in the database, which is not very hard to all keep in memory. Even with the just 8 MB Buffer Pool from above, the Hit Rate is at 97% when just browsing through the UI, because not all of the 132 MB are needed.

Contrast with the Tideways database, which currently has 300 GB of data. It still achieves a nearly 100% cache hit rate, because the “working set” of data is much smaller. About 290 GB are historical data that are rarely read.

The query for the data size thus only gives the upper limit of what you might need for the InnoDB Buffer pool size. Each application is different with respect to what its “working data set size” is.

Changing the InnoDB Buffer Pool Size

Now to the final important piece, changing the value. This should be done on server startup and requires root access to the Database server, or maybe is changeable through the control panel of your hosting or cloud provider. There is most likely a mysqld.conf somewhere in /etc/mysql or a subfolder, this differs between Linux distribution.

In this file you either already find the setting, or you add the setting with the value you want and restart the MySQL server afterwards:

If you want to change the value at runtime without restart, that is also possible. But be aware that it reverts back to the configured server value when restarted without changing the configuration:

See the MySQL documentation for more information on changing this value.