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