Keep tabs on your server

There are two key commands you should use regularly, as they give you detailed information about the running of your machine. Firstly, just type "STATUS;" at the MySQL prompt to get basic information - how many questions have come in, how many were considered slow queries, how many queries per second, etc. Secondly, type "SHOW STATUS;" to get more detailed information - how much of your query cache is being used, how often MySQL has had to trim the cache due to a RAM shortage, how often your key buffer has been read and how much space it has left, etc. Check SHOW STATUS once a week or so to make sure your buffers aren't overflowing.

RAM is cheap, so there is no harm giving your buffers all they can take and then some. Using the query cache has no impact on performance, which means if you have the RAM you can just allocate 1GB to the query cache and key buffer to watch your performance shoot up.

If you don't perform all that many repeated queries on your site, it is likely the key cache will be the most important thing for you, and you can check how well your key cache is being used with a few simple tests.

If you are using MyISAM, type this command into the MySQL monitor:

SHOW STATUS LIKE '%key_read%';

This should return two rows: key_read_requests and key_reads, of which the first is the number of times a key has been read from cache and the latter is the number of times a key has been read from disk. Now perform this simple calculation:

So, let's say you have had 2835 requests to read a key from disk (Key_reads) and 118,080 requests to read a key from cache (Key_read_requests) - you divide 2835 by 118,080 to get 0.024 (rounded - the exact accuracy is not too important), multiply it by a hundred to get 2.4, then subtract that 2.4 from 100, to get 97.6. This figure means that approximately 97.6% of your queries were pulled from cache as opposed to from disk.

If you are using InnoDB, you need to be logged in as the root user then type:

SHOW InnoDB STATUS;

This reports back with a lot of information specific to InnoDB, but what we're interested in is the figure listed next to "Buffer pool hit rate", towards the end of the listing - it is listed as out of 1000, e.g. 985/1000, so simply divide the left-hand figure by 10 to get our hit percentage, e.g. 98.5%

With that in mind, it is time to look at what this figure actually means:

Please remember the above table is very general. If you find you have a very low score, it is possible that the reason is because you are performing many updates and deletes on your data, particularly if these updates are done in small batches as opposed to being clumped together. If you are changing your data a lot, don't worry too much about the low Key_reads/Key_read_requests score, as it is much less important.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.