Tune MySQL using MySQLTuner

Tune MySQL using MySQLTuner

Every once in a while you see your MySQL showing increased memory footprint. You want to optimize MySQL but you do not know where to start from as first you need to know what all you should be tuning.

This where MySQLTuner script can help you. As it’s README says:

“_MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions._”

Let’s first download this script. You can either download it from Github

https://github.com/rackerhacker/MySQLTuner-perl

Or, if you have git installed, you can directly do a git clone:

git clone git://github.com/rackerhacker/MySQLTuner-perl.git

Now let’s make this pl file executable.

cd MySQLTuner-perl/
chmod +x mysqltuner.pl

Now let’s execute this script and find out what is going on with your MySQL:

./mysqltuner.pl

This would give you an audit of your MySQL installation. A sample audit report might look like:

Follow the recommendation sections and make changes as suggested. After each config changes, make sure you restart and run the tuner script again to see that the changes are indeed improving your installation.

Also It is most like to recommend that you run OPTIMIZE TABLE.

You can optimize all the databases at one go using following command.

mysqlcheck -op -u root --all-databases=true

That’s it, you should have tuned your MySQL performance. Please drop us a line if you need any help.