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 <path-to-script>/mysqltuner.pl --nocolor 2>&1") | crontab -
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=<db_user>
password=<db_pass>'
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.
No comments:
Post a Comment