Improve MySQL Database Performance with the InnoDB Buffer Pool configuration
PHP Performance
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:
SELECT variable_value/1024/1024 as innodb_buffer_pool_size_mb
FROM performance_schema.global_variables
WHERE variable_name = 'innodb_buffer_pool_size';
SELECT @@innodb_buffer_pool_size / 1024 / 1024 as innodb_buffer_pool_size_mb;
+----------------------------+
| innodb_buffer_pool_size_mb |
+----------------------------+
| 8 |
+----------------------------+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.
mysql> SHOW ENGINE INNODB STATUS\GThe output is rather large so that sometimes it makes sense to set the pager tool to less before:
mysql> pager less
PAGER set to 'less'Search for the section “BUFFER POOL AND MEMORY” it will look like this:
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 1852330
Buffer pool size 1048448
Buffer pool size, bytes 17177772032
Free buffers 8216
Database pages 1028928
Old database pages 379656
Modified db pages 14757
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 867782, not young 1408418
0.07 youngs/s, 0.00 non-youngs/s
Pages read 885237, created 235215, written 33162859
0.00 reads/s, 0.00 creates/s, 46.88 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1028928, unzip_LRU len: 0
I/O sum[19968]:cur[0], unzip sum[0]:cur[0]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:
SELECT HIT_RATE FROM information_schema.INNODB_BUFFER_POOL_STATS;This can also be calculated through two global status variables, and you can run the following SQL query to get the same value:
SELECT 1000 * (1 - (
(SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'innodb_buffer_pool_reads')
/ (SELECT variable_value PageSize FROM performance_schema.global_status WHERE variable_name = 'innodb_buffer_pool_read_requests')
)) AS innodb_cache_hit_rate;Outputs:
+-----------------------+
| innodb_cache_hit_rate |
+-----------------------+
| 971.9755334547322 |
+-----------------------+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:
SELECT
table_schema as `database`,
table_name as `table`,
SUM(round(((data_length+index_length)/1024/1024),2)) as size_mb
FROM information_schema.tables
WHERE engine = "InnoDB" AND table_type = "BASE TABLE"
AND table_schema not in ("mysql", "sys", "information_schema", "perfomance_schema")
GROUP BY `database`, `table` WITH ROLLUP;For my Shopware Demo shop application this results in the following, with most rows cut for readability:
+------------+-------------------------------------------------+---------+
| database | table | size_mb |
+------------+-------------------------------------------------+---------+
| shopware67 | acl_role | 0.02 |
| shopware67 | category | 21.47 |
| shopware67 | category_tag | 11.61 |
| shopware67 | category_translation | 20.45 |
| shopware67 | product_search_keyword | 13.22 |
| shopware67 | seo_url | 27.70 |
| shopware67 | seo_url_template | 0.03 |
| shopware67 | shipping_method | 0.09 |
| shopware67 | NULL | 132.35 |
| NULL | NULL | 132.35 |
+------------+-------------------------------------------------+---------+
229 rows in set (0.01 sec)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:
innodb_buffer_pool_size=16gIf 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:
mysql> SET GLOBAL innodb_buffer_pool_size=16g;See the MySQL documentation for more information on changing this value.
About the author
I am the founder and CEO of Tideways. I started the company over 10 years ago with the mission to move the PHP ecosystem forward, starting with performance. As managing director, I work across product, strategy, and the day-to-day of building a developer-focused SaaS business.
I’m a core contributor to the Doctrine open-source project and a founding board member of the PHP Foundation, which reflects my long-standing commitment to the PHP ecosystem. I particularly enjoy working at the intersection of application performance, developer experience, and the open-source community that makes PHP what it is. Outside of work, I enjoy board games, hiking, and coffee.