MySQL Tuning on Linux Hosts

Get a benefit to your MySQL performance by using the MySQL Tuner. A complete tutorial on MySQL tuning on linux hosts.

MySQL Tuner

Sometimes you can find that default MySQL settings are not ideal for your installations. In most cases you can get a benefit to your MySQL performance by using the MySQL Tuner.

You can use mysqltuner.pl script to check the MySQL state and show optimization recommendations which may significantly improve the performance.

First of all ensure your MySQL server has been running for at least 24 hours without reboots as mysqltuner needs as much stats collected as possible.

1. Make a backup of the current my.cnf file. The location differs from linux to linux.

Example:

cp /etc/my.cnf /etc/my.cnf_original

2. Download the script from http://mysqltuner.pl

wget -O mysqtuner.pl http://mysqltuner.pl

3. Make that file executable

chmod +x mysqltuner.pl

4. Run it

./mysqltuner.pl

5. Carefully read the information the scripts generated. Please avoid just blindly copy/paste of any parameters shown there. If in doubt, Google what they mean and what damage might result if the changes are incorrect. We do not recommend making any changes with regards to innodb log file sizes without Googling how to do this. Most changes with regards to buffer and memory are safe to implement right away.

6. Insert recommended parameters or change existing ones in the current my.cnf file

nano /etc/my.cnf

When done press F2 and choose "Y" on prompt

7. Reload MySQL server (if possible avoid restarting, just reload):

service mysqld restart

8. Leave the system running for at least 24h more

9. Measure  server parameters, LA, wait%, cpu and memory utilization.

10. Run the mysqltuner.pl script again if required and see if there are other recommendations appeared