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:
[code]
innodb_buffer_pool_size=24G
[/code]
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.
[code]
innodb_flush_log_at_trx_commit = 2
[/code]
3. innodb_log_file_size – The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.
[code]
innodb_log_file_size = 512M
[/code]
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.
[code]
innodb_log_buffer_size = 8M
[/code]
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:
[code]
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
[/code]
