If you are seeing problems, the next step is to check how much of your current key buffer is being used. Use the SHOW VARIABLES command and look up the value of the key_buffer_size variable. It is probably something like 8388600, which is eight million bytes, or 8MB. Now, use the SHOW STATUS command and look up the value of Key_blocks_used.
You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1024, dividing by key_buffer_size, and multiplying by 100. For example, if Key_blocks_used is 8000, you multiply that by 1024 to get 8192000; then you divide that by your key_buffer_size (8388600) to get 0.97656, and finally multiply that by 100 to get 97.656. Thus, almost 98% of your key buffer is being used.
Now, on to the important part: You have ascertained that you are reading lots of keys from disk, and you also now know that the reason for reading from disk is almost certainly because you do not have enough RAM allocated to the key buffer. A general rule of thumb is to allocate as much RAM to the key buffer as you can, up to a maximum of 25% of system RAM — 128MB on a 512MB system is about the ideal for systems that read heavily from keys. Beyond that, you will actually see drastic performance decreases because the system has to use virtual memory for the key buffer.
Open /etc/my.cnf in your text editor and look for the line that contains key_buffer_size. If you do not have one, you need to create a new one — it should be under the line [mysqld]. When you set the new value, do not just pick some arbitrarily high number. Try doubling what is there right now (or try 16MB if there's no line already); then see how it goes. To set 16MB as the key buffer size, you need a line like this:
[mysqld]
set-variable = key_buffer_size=16M
datadir=/var/lib/mysql
Restart your MySQL server with service mysqld restart, and then go back into MySQL and run SHOW VARIABLES again to see the key_buffer_size. It should be 16773120 if you have set it to 16M. Now, because MySQL just got reset, all its values for key hits and the like will also have been reset. You need to let it run for a while so that you can assess how much has changed. If you have a test system you can run, this is the time to run it.
After your database has been accessed with normal usage for a short while (if you get frequent accesses, this might be only a few minutes), recalculate how much of the key buffer is being used. If you get another high score, double the size again, restart, and retest. You should keep repeating this until you see your key buffer usage is below 50% or you find you don't have enough RAM to increase the buffer further—remember that you should never allocate more than 25% of system RAM to the key buffer.
Using the Query Cache
Newer versions of MySQL enable you to cache the results of queries so that, if new queries come in that use exactly the same SQL, the result can be served from RAM. In some ways the query cache is quite intelligent: If part of the result changes because of another query, for example, the cached results are thrown away and recalculated next time. However, in other ways it isn't so smart. For example, it uses cached results only if the new query is exactly the same as a cached query, even down to the capitalization of the SQL.
The query cache works well in most scenarios. If your site has an equal mix of reading and writing, the query cache does its best but is not optimal. If your site is mostly reading with few writes, more queries are cached (and for longer), thus improving overall performance.
First, you need to find out whether you have the query cache enabled. To do this, use SHOW VARIABLES and look up the value of have_query_cache. All being well, you should get YES back, meaning that the query cache is enabled. Next, look for the value of query_cache_size and query_cache_limit — the first is how much RAM in bytes is allocated to the query cache, and the second is the maximum result size that should be cached. A good starting set of values for these two is 8388608 (8MB) and 1048576 (1MB).
Next, type SHOW STATUS LIKE 'Qcache%'; to see all the status information about the query cache. You should get output like this:
mysql> SHOW STATUS LIKE 'qcache%';
+.........................+........+
| Variable_name | Value |
+.........................+........+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 169544 |
| Qcache_hits | 698 |
| Qcache_inserts | 38 |
| Qcache_lowmem_prunes | 20 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 18 |
| Qcache_total_blocks | 57 |
+.........................+........+
8 rows in set (0.00 sec)
From that, you can see that only 18 queries are in the cache (Qcache_queries_in_cache), 169544 bytes of memory are free in the cache (Qcache_free_memory), 698 queries have been read from the cache (Qcache_hits), 38 queries have been inserted into the cache (Qcache_inserts), but 20 of them were removed for lack of memory (Qcache_lowmem_prunes), giving the 18 from before. Qcache_not_cached is 0, which means 0 queries were not cached — MySQL is caching them all.
From that, you can calculate how many total queries came in — it is the sum of Qcache_hits, Qcache_inserts, and Qcache_not_cached, which is 736. You can also calculate how well the query cache is being used by dividing Qcache_hits by that number and multiplying by 100. In this case, 94.84% of all queries are being served from the query cache, which is a great number.
In this example, you can see that many queries have been trimmed because there is not enough memory in the query cache. This can be changed by editing the /etc/my.cnf file and adding a line like this one, somewhere in the [mysqld] section:
set-variable = query_cache_size=32M
An 8MB query cache should be enough for most people, but larger sites might want 16MB or even 32MB if you are storing a particularly large amount of data. Very few sites have need to go beyond a 32MB query cache, but keep an eye on the Qcache_lowmem_prunes value to ensure that you have enough RAM allocated.
Using the query cache does not incur much of a performance hit. When MySQL calculates the result of a query normally, it simply throws the result away when the connection closes. With the query cache, it skips the throwing away, and so there is no extra work being done. If your site does have many updates and deletes, be sure to check whether you get any speed boost at all from the query cache.
Miscellaneous Tweaks
If you have tuned your key buffer and optimized your query cache and yet still find your site struggling, you can make a few additional small changes that will add some more speed.