I’ve spent some time getting to know how to tune up MySQL a bit to speed up the Drupal sites I create, and wanted to share a bit of what I’ve learned.
To start - know that this is all based on using CentOS 6 (or 5.x - was mostly the same) and MySQL 5.x. Getting started was really easy:
wget mysqltuner.pl perl mysqltuner.pl
Look at the recommendations and add them to your my.cnf file. This is a bit of trial and error - make small changes, save it, let it run, and monitor your server. Don’t be too drastic - sometimes small changes can have a much greater effect than you think.
my.cnf is located in ‘/etc’
Use VI to edit my.cnf
Then save the file and restart MySQL with this command:
service mysqld restart
perl mysqltuner.pl
And adjust again if necessary.
It’s best to let it run for a little while (at least 20-30min) between tuning tweaks. Be sure not to make too many huge changes at once or you’re never know what made it better or worse. In the end though it was literally a several hundred precent increase in performance just from doing this.
To learn more about this check out a great presentation from Matt Westgate from Lullabot about performance tuning for the Grammy.com site: http://bit.ly/ijtXMT