Configuring MySQL may look challenging as hundreds of settings are available. However you can get a MySQL server with good performance by adjusting a few settings only.
Peter already gave some recommendations, but things have changed a lot in the MySQL world since then!
Considerations to keep in mind
Even experienced people can make mistakes that can cause a lot of troubles. 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 the change is beneficial.
- Most settings can be changed at runtime with
SET GLOBAL. It is very handy and it allows you to 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
- The server refuses to start after a change: did you use the correct unit? For instance, the buffer pool has a size in MB while max_connection is dimensionless.
- Do not allow duplicate settings. 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”.
- And finally, every rule has an exception.
InnoDB has been the default storage engine since MySQL 5.5 and it is by far the most common. That’s why it should be configured carefully.
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.
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.
innodb_file_per_table: this setting will tell InnoDB if it should store data and indexes in the shared tablespace (
innodb_file_per_table = OFF) or in a separate .ibd file for each table (
innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+).
With MySQL 5.6, the default value is ON so you have nothing to do in most cases. For previous versions, you should set it to ON prior to loading data as it has an effect on newly created tables only.