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 servers. It may be adequate on a server with only 512 MB of memory that hosts other applications. 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 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, always leave enough RAM for the OS.
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 always to benchmark the specific system with all applications running a realistic load to determine the best settings.
Reducing contention by dividing the available InnoDB Buffer Pool #
The buffer pool can be divided into smaller instances, which each manage its own structures and takes 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 instances can be processed without waiting.
Set the available 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 places 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, now the queries should run faster after they have been cached.
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.