I recently rebuilt the Cornwall24 website in the hope that upgrading from a 3 year old verwion of PostNuke to a new version of WordPress might stop the site taking down the server by overloading MySQL at least once a week.
Unfortunately it actually made things worse!
So I decided to take the bull by the horns and find out what exactly was causing the overload and how I could increase MySQL performance.
my.cnf
A good starting point is the MySQL config file on your server. Mine is located at /etc/my.cnf. It seems the default set up and example files are for 1970s server with 32mb of RAM!
For a modern server with 1gb of RAM the my-large.cnf is probably a good starting point (you can find these in /usr/share/doc/mysl-server-[versio]) .
From here there are several tweaks but perhaps the biggest improvement will be by making sure caching is turned on.
Under the [mysqld] config settings add:
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
Log Slow Queries
The best way to find out what is hogging your MySQL resources is to log slow queries. This is done by adding something like the following lines of code to your my.cnf:
log-slow-queries = slow.log long_query_time = 2
This will write any query taking more than 2 seconds to the slow query log. I used a relative path here as absolute ones didn’t seem to work on my server. The slow log can be found in the MySQL data folder at /var/lib/mysql on my box
NOTE: you’ll need to restart MySQL for any of the above changes to take effect:
/etc/init.d/mysqld restart
Optimising Slow Queries
Armed with the information from the slow query log you can set about tweaking the queries that are slowing things up. The slow log gives us several useful bits of info besides the query itself. This includes time taken and number of rows examined – if this number is particularly high then it probably means your table’s indexes need looking at (it usually is)
The EXPLAIN Command
MySQL’s EXPLAIN command is perhaps your most useful tool in working out what indexing strategy to use. I’m only going to touch on this as it is a pretty complicated topic that I barely understand myself!
The useful info that can be gleaned from the EXPLAIN command are whether the indexes you have set up are being used for a particular query. To use the EXPLAIN command just place ‘EXPLAIN’ in front of the query you are analysing.
More to come…