Essential MySQL server performance tuning

Most important MySQL server configuration options to improve performance on new server installation:

1. innodb_buffer_pool_size – Amount of memory MySQL will use for data cache.
Set to ~80% of server RAM. (If server is dedicated for DB). Example for server with 32GB RAM:

innodb_buffer_pool_size=24G

2. innodb_flush_log_at_trx_commit = 2 – log writes will happen once per second instead of every commit. Major I/O bottleneck with many writes.

innodb_flush_log_at_trx_commit = 2

3. innodb_log_file_size – The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.

innodb_log_file_size = 512M

After changing the value, gracefully shutdown MySQL and move /var/lib/mysql/ib_logfile* somewhere safe just in case. If MySQL starts successfully, delete moved files.

4. innodb_log_buffer_size – enables large transactions to run without writing the log to disk before the transactions commit, again – saving disk I/O. Default = 1MB.

innodb_log_buffer_size = 8M

5. innodb_file_per_table – Not a performance setting, however a very important parameter to set right after server installation as it will be hard to change it later.

In file-per-table mode, each newly created table will have its own data file, that will allow to reclaim used space after table deletion, and have other advantages

Summary:

innodb_buffer_pool_size=24G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_file_per_table

Leave a Reply

Your email address will not be published.