Ten MySQL performance tuning settings after installation
In this blog we’re going to discuss the top ten MySQL performance tuning settings that you can implement after an installation.
When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest changing a few MySQL performance tuning settings after installation – even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.
We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!
Before we start…
Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:
- Change one setting at a time! This is the only way to estimate if a change is beneficial.
- Most settings can be changed at runtime with
SET GLOBAL
. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file. - A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the
[mysqld]
section) - The server refuses to start after a change: did you use the correct unit? For instance,
innodb_buffer_pool_size
should be set in bytes whilemax_connection
is dimensionless. - Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
- Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.
Basic settings fro MySQL performance
Here are 3 MySQL performance tuning settings that you should always look at. If you do not, you are very likely to run into problems very quickly.
innodb_buffer_pool_size
: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).
innodb_log_file_size
: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.
Starting with innodb_log_file_size = 512M
(giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G
.
max_connections
: if you are often facing the ‘Too many connections’ error, max_connections
is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.