MySQL Tuner is a Perl script that connects to a running instance of MySQL and provides configuration recommendations based on workload. Ideally, the MySQL instance should have been operating for at least 24 hours before running the tuner. The longer the instance has been running, the better advice MySQL Tuner will give.

Download MySQL Tuner to your home directory.

  1. # wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

2. # perl ./mysqltuner.pl

The output will show two areas of interest: General recommendations and Variables to adjust.

 

MySQLTuner offers suggestions regarding how to better the database. If you are wary about updating your database on your own, going by MySQLTuner’s suggestions is one of the safer ways to improve your database performance.

Tuning MySQL:

When altering the MySQL configuration, be aware of the changes and how they affect your database. Even when following the instructions of programs such as MySQLTuner, it is best to have some understanding of the process.

The file you are changing is located at /etc/my.cnf

IMPORTANT NOTE: Prior to updating the MySQL configuration, create a backup of the my.cnf file:

1. #cp /etc/my.cnf ~/my.cnf.backup
Best practices suggest that you make small changes at a time and monitor the server after each change. After each change restart MySQL:

On Debian/Ubuntu:

# service mysql restart

On CentOS/Fedora:

# /etc/init.d/mysqld restart

When changing values in the my.cnf file be sure that the line you are changing is not commented out with the pound (#) prefix.

MySQLTuner reports statistics about the database, and makes tuning recommendations. The top section of the report gives you useful database metrics, many of them actionable. The bottom section provides tuning suggestions for the MySQL configuration file.

You should thoroughly research a suggested configuration change before deciding to implement it. To change a configuration variable, edit the file /etc/mysql/my.cnf.

After you make a MySQL configuration change, restart the MySQL service.

# /etc/init.d/mysqld restart

You can then run MySQLTuner again to see if it has further recommendations to improve the MySQL performance. This way, you can optimize MySQL step by step.