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:
100 - ((Key_reads / Key_read_requests) * 100)
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:
Woefully bad. You clearly have serious problems somewhere and need to start from scratch. Make sure you have a key buffer enabled, aren't serious short on RAM, and that you have done the formula properly. If you are doing lots of updates and deletes, read the note below.
You probably have a default configuration of MySQL in place and have not adjusted the buffers to take into account the size of your dataset. Increase your buffer sizes.
Not bad - you have a highly optimised system that is in great working shape.
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.