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.
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 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
Since each server’s performance requirement is unique, it is always upto 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.