Improving MySQL stability & performance using MySQLTuner - part 1
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.
Installing MySQLTuner
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
The prepackaged 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
Running MySQLTuner
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
Analyzing output
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.
$ sudo service mysql restart
Scheduling runs
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 crontabs.
The following schedules MySQLTuner to run every Thursday at 4:30 am.
$ (crontab -l; echo "30 04 * * 4 perl
Please refer to my earlier post for details about crontabs.
By default, 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 <<< '
[client]
user=
password=
Finally, protect the db login credentials by tightening up access permissions for the .my.cnf file.
$ chmod 600 /home/peter/.my.cnf
What's next?
The remainder of this series will guide you through taking actions based on MySQLTuner recommendations.
