MySQL is the database engine behind many web applications on the Internet today. While it is relatively straightforward to install, configuring
MySQL to best support your particular application requires expertise and the right tools. This post introduces
MySQLTuner, a command-line program which offers suggestions to optimize MySQl performance and stability.
MySQLTuner is a read-only script: it won't actually write to the
MySQL configuration file. Based on your database's past usage, it recommends new values to assign to specific
MySQL configuration variables. It is your responsibility to understand each recommended change and its possible ramifications, select the changes you want to make, and to make them in a controlled manner.
Before you install
MySQLTuner, make sure that it supports your
MySQL version. You can find the up-to-date compatibility information on its website.
To identify the
MySQL version on your database server, run this command:
$ mysqladmin -u root -p version
Server version 5.5.43-0+deb7u1
MySQLTuner is a PERL script that you can install from the standard Debian and Ubuntu repositories. You can install it using the following command:
$ sudo apt-get install mysqltuner
MySQLTuner may not be the latest release available. If you want the latest, or you run a Linux distro other than Debian/Ubuntu, you can install the up-to-date version by downloading it directly. Simply download the PERL script to a directory of your choice using the command:
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
Your database should be up longer than 1 day before you run
MySQLTuner. This is because
MySQLTuner bases its recommendations on past database usage. The more data it has to analyze, the more accurate is its recommendations. If
MySQLTuner is run on a database that has been restarted in the last day, you will get a warning message: 'MySQL started within last 24 hours - recommendations may be inaccurate'.
To run the script, enter the following:
$ perl mysqltuner.pl
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
After you make a
MySQL configuration change, restart the
$ sudo service mysql restart
Database tuning is not a 'once and done' type of task. Conditions change over time. A good practice is to schedule regular
MySQLTuner runs using
The following schedules
MySQLTuner to run every Thursday at 4:30 am.
$ (crontab -l; echo "30 04 * * 4 perl <path-to-script>/mysqltuner.pl --nocolor 2>&1") | crontab -
Please refer to my earlier post for details about
MySQLTuner prompts the user for the database login credentials. For a
cronjob to run
MySQLTuner, you may provide the database account and password in the user-specific
MySQL configuration file.
$ cat >> /home/peter/.my.cnf <<< '
Finally, protect the db login credentials by tightening up access permissions for the
$ chmod 600 /home/peter/.my.cnf
The remainder of this series will guide you through taking actions based on