Tuning MySql using mysqltuner.pl

There are lots of details online about how to create and edit web pages/sites and how to make things look pretty, however, there is a lot less about how to get things working nicely on servers be them development or live servers. Of course, hosting websites is maybe not something for everyone and certainly at Will Hall Online, my usual response may well be to get a server to run your website but if you are running a small website, with limited resource requirements, shared hosting can do a fantastic job. However, in an effort to let you know more about configuring your server (be it a development, staging or production server) I am sharing a few resources I found to be illuminating when looking at tuning your server.

So with the vast majority of websites in the world requiring a database (and over 50% using MySql as the backend for this) it is important to have some idea about how mysql is running. Although there are lots of ideas about how to tune your server, possibly the best way to do it is by doing little changes and monitoring how it affects your site. This is where the mysqltune.pl script comes in. So you have sucessfully installed a LAMP server on your system and you have Apache and Mysql running… Well done. Now some extra bits come into play.

Providing you have a Linux based server, and MySql you can probably download mysqltuner.pl. After that, run it and see the output: sudo perl mysqltuner.pl

[mysqltuner.pl output]

This is my output after a bit of fiddling earlier on a development server. As you can see it gives quite a bit of data on how your server is currently set up and how that set up is running. Basically your green [OK] are wins and [!!] are losses. You will see that after all of the details there are Recommendations. This is brilliant as it give real suggestions of things you can change in your setup of MySql to improve the performance of the database server. If you wish to act on the Variables to adjust part of the recommendations just go to your mysql config (/etc/mysql/my.cnf) change the required variables and restart the server (sudo service mysql restart [on Ubuntu/Debian]).

Brilliant. You now have both some idea of how your database server is performing, and recommendations to improve it and how you could improve it. And if you think you can improve the mysqltuner.pl script please contribute on GitHub.