MySQL Tuner quick run through

MySQL tuner is the simplest way to take a default MySQL install and tweaks its performance. I would highly recommend running a load test on the web application as this will help populate all of the caches and also help highlight any problems with indexes or similar.

wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
chmod 755 mysqltuner.pl

Run a series of load tests on the application and then run the command:

perl mysqltuner.pl

Now edit the MySQL config with the settings suggested by the tuner script:

nano /etc/mysql/my.cnf
/etc/init.d/mysql restart

This final few steps you will need to iterate a couple of times to get the best results but you should see variables that you need adjusting at the base like:

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 1G)

All you need to do is edit the /etc/mysql/my.cnf file adding the changes under the [mysqld] block making sure though that you do not exceed resources available with your hardware:

query_cache_size        = 32M
join_buffer_size        = 2M
table_cache             = 600
innodb_buffer_pool_size = 1G

You can either run a siege load test on a "non-live" site to get the database populated, or leave the live site running for a few hours/days and then repeat the process. After a couple of iterations your database should be optimised as best it can.