What we do immediately after successful installation of MySQL 5.7 for performance and reliability ? We follow a very minimalistic approach for configuring my.cnf / MySQL system variables to optimally provision the available system resources and tracing execution of MySQL queries for performance .
Best practices for configuring my.cnf :
- Change only one system variable at a time in my.cnf, This helps in estimating and quantifying the benefits of the change .
- Never accept duplicate settings in my.cnf, We recommend using version control to track the system variable changes in my.cnf and always retain the copy / backup of my.cnf for restore / recovery .
- Most of the system variables can be changed at runtime with SET GLOBAL, It is really good because we can rollback if the change is causing the new problem but never forget to note down the changes which you need to add in the my.cnf before next MySQL restart (we often see this mistake happening) .
- Confirm MySQL system variables are added after [mysqld] in my.cnf .
- Never copy my.cf of another server presuming optimal performance, If you have simple queries in MySQL then there is no need to increase sort_buffer_size even if you have 512 GB RAM .
- We seriously care about your MySQL reliability above performance so we always recommend setting sync_binlog=1 (presuming binary logging enabled) and innodb_flush_log_at_trx_commit=1
The following below are 4 system variables / my.cnf values I consider changing immediately after successful MySQL installation for performance and reliability :
- innodb_buffer_pool_size – InnoDB buffer pool caches both data and indexes so always consider setting this value between 50%-75% of available RAM, Does not need to be larger than the database .
- innodb_flush_log_at_trx_commit – set this value to “1” (default) for maximum reliability and “0” or “2” for less reliability and more performance (we don’t recommend this setting)
- innodb_log_file_size – larger the value less checkpoint flush activity required in buffer pool (this also makes crash recovery slower) so there will be less disk I/O and benefit in the performance . If your application is write intensive you can set this value to “1 GB” (I don’t prefer setting this value too high !!)
- innodb_flush_method – You can set this value to “O_DIRECT” to avoid double buffering .
Advanced my.cnf configuration in MySQL 5.7
- innodb_io_capacity and innodb_io_capacity_max – consider setting these two system variables only if you have a lots of writes all the time (it does not apply for reads / SELECTS) and use solid-state disks (SSD), We recommend you to benchmark disk throughput (I use sysbench) before setting this value . If you have a server with 1 SSD we recommend innodb_io_capacity “4000” and innodb_io_capacity_max “8000” .
- Innodb_autoinc_lock_mode – Setting innodb_autoinc_lock_mode “2” (interleaved mode) improve the performance of MySQL by not depending on table-level AUTO-INC lock, This makes multi-row insert statements with auto-increment primary key more optimal, This requires binlog_format=ROW or MIXED (and ROW is the default in MySQL 5.7). There may be gaps in the auto-increment values generated by “bulk inserts” , but only if there are concurrently executing “INSERT – like” statements
MySQL 5.7 has much better default values
- innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_startup are enabled by default – This records the pages cached in the InnoDB buffer pool when MySQL server is shutdown and shorten the warm-up process at the next restart .
- Innodb_file_per_table = ON (DEFAULT)
- Innodb_stats_on_metadata = OFF (DEFAULT) , so statistics for queries like SHOW TABLE STATUS OR INFORMATION_SCHEMA.TABLES are not updated,This improves performance and stability of execution plans for queries that involve InnoDB tables .
- QUERY_CACHE_TYPE = 0 (DEFAULT) , so it does not acquire the query cache mutex at all and there is reduced overhead in query execution .
We use slow_query_log and pt-query-digest for tracing MySQL
We can dynamically enable slow_query_log and long_query_time (I usually log queries taking more than 1 sec. to execute) for tracing MySQL performance. The pt-query_digest (Percona Toolkit) from Percona makes aggregation of slow_query _log better
pt-query_digest usage :
pt-query-digest slow.log
P.S – We have copy of the same post available in SlideShare and PDF can be downloaded from here
