If you have tuned your key buffer and optimized your query cache and yet still find your site struggling, there are a handful more smaller changes you make that will add some more speed.
When reading from tables, MySQL has to open the file that stores the table data. How many files it keeps open at a time is defined by the table_cache setting, which is set to 64 by default. You can increase this setting if you have more than 64 tables, but you should be aware that Ubuntu does impose limits on MySQL about how many files it can have open at a time. Going beyond 256 is not recommended unless you have a particularly DB-heavy site and know exactly what you are doing.
The other thing you can tweak is the size of the read buffer, which is controlled by read_buffer_size and read_buffer_rnd_size. Both of these are allocated per connection, which means you should be very careful to have large numbers. Whatever you choose, read_buffer_rnd_size should be three to four times the size of read_buffer_size, so if read_buffer_size is 1MB (suitable for very large databases), read buffer rnd size should be 4MB.
Was this article helpful?