Tweak InnoDB buffer pool size to increase MySQL performance
The InnoDB Buffer Pool is a cache for data and indexes that resides in memory (RAM), which means that the database software can perform queries much faster than if it needed to get the data from disk.
The InnoDB Buffer Pool is set to 128 MB by default, which is very low by today's standard for modern servers. It may be adequate on a server with only 512 MB of memory that also hosts other applications or if the dataset is small.
Most VPS servers from providers like DigitalOcean, Linode, UpCloud, Scaleways, Vultr etc. start at 1 GB nowadays, and span up to 32 GB for the lower end offerings.
When hosting MySQL or MariaDB on modern servers there's usually plenty of memory free even with several other applications running.
If a server is used solely for hosting a MySQL or MariaDB then a rule of thumb is to set the InnoDB Buffer Pool to 80% of available RAM, for servers with 8 GB or more.
There should always be at least 1-2 GB of RAM left for the operating system or other applications, otherwise there may be crashes and out of memory errors, worst case the whole system can go down.
If the cached data in RAM has to be swapped to disk the optimization is more than undone, even with SSD disks.
The best way is to benchmark the specific system with all applications running realistic workloads to determine the best settings for a particular system and setup.
Reducing contention by dividing the available InnoDB Buffer Pool #
The buffer pool can be divided into smaller instances, each managing its own structures and taking up an equal amount of the available pool size.
It helps to reduce contention concurrency, because certain operations are blocking. By dividing the pool only parts of the cache are blocked at a time, which means threads running other queries, that access other pool instances can be processed without waiting.
Set the available pool instances using innodb_buffer_pool_instances.x
The default value is 1 up until pool size is set to 1 GB, then it becomes 8.
Ideally each pool instance should be at least 1 GB in size.
General settings on dedicated database servers #
| Cores | RAM available | innodb_buffer_pool_size | Percent of RAM | innodb_buffer_pool_instances |
| 1 | 512 MB | 256 MB | 50% | 1 |
| 1 | 1 GB | 512 MB | 50% | 1 |
| 1 or 2 | 2 GB | 1536 MB | 75% | 1 |
| 2 | 4 GB | 3 GB | 75% | 3 |
| 4 | 8 GB | 6.5 GB | 81% | 4 |
| 8 | 16 GB | 14 GB | 87,5% | 8 |
General settings on general purpose servers #
For servers hosting MySQL / MariaDB and running other applications as PHP (WordPress, Magento, Joomla, Laravel, etc.), Node.js or other loads, there should be much more RAM available for the other applications running.
| Cores | RAM available | innodb_buffer_pool_size | Percent of RAM | innodb_buffer_pool_instances |
| 1 | 512 MB | 128 MB (default) | 25% | 1 |
| 1 | 1 GB | 384 MB | 38% | 1 |
| 1 | 2 GB | 1024 MB | 50% | 1 |
| 2 | 4 GB | 2 GB | 50% | 2 |
| 4 | 8 GB | 4 GB | 50% | 4 |
| 8 | 16 GB | 10 GB | 62,5% | 8 |
How to modify the necessary settings #
Run the below command to find the path to the MySQL config file, usually there are several locations which override each other.
mysql --help | grep "Default options" -A 1Open the appropriate my.cnf file and add the custom settings for innodb_buffer_pool_size and innodb_buffer_pool_instances.
Example:
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 2
Restart MySQL, after queries have been executed a couple of times they will be cached and respond faster.
Resources #
For more settings and information see this knowledge-base article from MariaDB.
This thread has some good information on how to find out how well the settings are working.