Tweak MySQL using MySQLTuner

Last modified: February 23, 2020
You are here:
Estimated reading time: 1 min

Tweak MySQL using MySQLTuner

MySQLTuner analyze 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. The download and installing steps are as follows:

Firstly, download the Tuner Script

Then, get http://mysqltuner.com/mysqltuner.pl

And, make the script executable

# chmod +x mysqltuner.pl

Then, run the script:
# ./mysqltuner.pl

It will show a result like 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)

These suggestions need 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.

Was this article helpful?
Dislike 0
Views: 14