Using MySQL-Tuner to perfect MySQL server

Historically, in web development, MySQL has become the most popular database. Such a choice is natural: it is free, has extensive features, easy to use, and most importantly for the site operation, it is fast. However, it may also become a "bottleneck" of the site – without proper settings and optimization, the site pages may load slowly, there may be database connection errors, and so on.

 

Manual configuring of MySQL is a complex and non-trivial task, only experienced specialists can do it. For others, including novice webmasters, there is a simple and highly effective solution – the MySQLTuner utility, which will find problem areas in the MySQL database configuration and provide solutions to the identified problems.

 

Also, it is completely free, has open-source code published on GitHub, and is being actively developed.

Installation and initial configuration of MySQLTuner

 

MySQLTuner is a script written in Perl. Therefore, it can either be installed from operating system repositories or downloaded directly. The installation is performed by the following command:

 

# yum install mysqltuner (if you use CentOS or related OS)

 

In addition, the script can be downloaded directly from GitHub manually or using the command:

 

wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

 

If you downloaded the file without installation, before running the script mysqltuner.pl you need to transfer it to the server and set the file permissions that allow its launch (chmod 775). This can be done in the built-in file manager or using the command:

 

chmod u=rwx,g=rx,o=rx mysqltuner.pl

executed from the administrator account.

 

That is it, installation and configuration are finished, you can proceed to launch.

Starting and running MySQLTuner

MySQLTuner authors recommend running the script after your MySQL database has worked for at least 24 hours without reboots and changes in configs. The same goes for re-launches after changing a configuration. If this condition is met, you can run MySQLTuner.

The launch depends on how the installation was conducted:

• if the utility was installed through repositories, the launch is performed by the command:

msqltuner

after that, you need to specify the account login and password with the database administrator rights;

• if the script was downloaded from GitHub, the launch looks like this:

perl mysqltuner.pl

 

and then the login and password of the administrator account are also specified.

 

After launch, the script will give information about the used database (MySQL version, operating time since the last restart, the amount of memory used, cache size, number of simultaneous connections, and other operation parameters). Here, special attention should be paid to the lines starting with [!!] - they can affect MySQL performance.

 

 

Next, there are recommendations for perfecting the database. They can be found in the utility Recommendations section. Under the recommendations, there are names of specific variables to be changed and recommended values for them. The Variables to adjust section manages this.

 

 

 

The changed variable values are entered in the my.cnf configuration file. If there are no parameters in the file, you can add them there along with the recommended value. To apply new parameters, the MySQL server must be restarted. You can do this with the command service mysqld restart (for CentOS 6) or systemctl restart mariadb.service (for CentOS 7).

 

When the database has worked without restarts enough for collecting statistics, you need to run MySQLTuner again and see the changed recommendations. If everything is done correctly, you will surely experience a performance boost of MySQL on your server.

 

And remember that no matter how well you set up databases, your site will work fast and without failures, only if you have taken care of reliable high-quality hosting. Read reviews about our work and ask for competent advice.

 

We work 24×7.