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:
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.