Tweak MySQL using MySQLTuner
What is MySQLTuner ?
MySQLtuner is an open-source Perl script that analyzes the setup of MySQL and we are able to modify it according to its recommendations. It will be necessary to install and execute the script and show the correct parameters to change to enhance performance in the’ my.cnf’ file. So, get started with us to download and install by following the steps below:
General recommendations:
- Run OPTIMIZE TABLE to defragment tables for better performance
- MySQL started within last 24 hours – recommendations may be inaccurate
- Enable the slow query log to troubleshoot bad queries
- When adjusting, make tmp_table_size/max_heap_table_size equal
- Reduce your SELECT DISTINCT queries without LIMIT clauses
- Set thread_cache_size to 4 as a starting value
- Increase table_cache gradually to avoid file descriptor limits
- Your applications are not closing MySQL connections properly
Variables to adjust:
- query_cache_size (>= 16M)
- sort_buffer_size (> 16M)
- read_rnd_buffer_size (> 256K)
- tmp_table_size (> 25M)
- max_heap_table_size (> 20M)
- thread_cache_size (start at 4)
- table_cache (> 64)
- innodb_buffer_pool_size (>= 35M)
Last but not least, you need to know that these suggestions have to be reviewed and modifications made within my.cnf file. Before you start, it is best to backup the old configuration. Once completed, restart the ‘ mysql ‘ service and see if it will improve efficiency.
