Monday, 8 January 2018

what is MySQL tuner

MySQL tuner is an open source perl script which evaluates your MySQL performance and provides information and recommendations on which variables in order to boost the performance of your MySQL server.

This article will guide you through installing mysql tuner and executing it on your MySQL server so that it provides valuable recommendations to change the variables and ultimately boost MySQL’s performance.

Step 1: Download MySQL tuner

Download the mysql tuning file by running the below command
# wget http://mysqltuner.pl/ -O mysqltuner.pl

Step 2: Change permission

Change the permission of the script to make it executable using the following command
# chmod +x mysqltuner.pl

Step 3: Run MySQL tuner

Now run the script on your MySQL server.
# ./mysqltuner.pl
You will be prompted to enter the root password and mysql root password. Provide both and continue. You will receive various details which represent various variables of your mysql server.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
A the end of the message you will be provided with various recommendations that you need to take note of as tuning these will most of the times result in better performance. Please note that the recommendations differ from server to server and the ones that are shown in this article might not be there in yours.

Step 4: Tuning

Now it’s time to tune the variables in my.cnf file to suit the recommendations provided by the mysql tuner. you can use any file editor of your choice in order to change the variables. Let’s use vim editor for this article.
# sudo vim /etc/mysql/my.cnf
The data in your my.cnf file will also vary depending on the mysql-client installed on your server. It is important to fully understand what the recommendations in the previous message suggest and then change the variables one by one. Then save and exit. (Press ESC, then enter :wq! and hit Enter to save and exit from vim editor).

Step 5: Restart mysql server

After making a change in the my.cnf file, you need to restart the mysql server for the changes to take effect. To restart mysql server, the command is
# sudo service mysql restart

Conclusion

Since each server’s performance requirement is unique, it is always up to the user to decide which variable to change in order to boost mysql performance. It is highly recommended to change one variable at a time and then check whether there is a change in the performance figures. This way one can tune MySQL step by step and revert any changes if need be.

No comments:

Step-by-step OpenLDAP Installation and Configuration on server

This tutorial describes how to install and configure an OpenLDAP server and also an OpenLDAP client. Step by Step Installation and Conf...