double the size again, restart, and retest. You should keep repeating this until
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 allow you to cache the results of queries so that, if
new queries come in that use the same SQL, the result can be served from
RAM. In some ways the query cache is quite intelligent: If, for example, part
of the result changes due to another query, the cached results are thrown away
and recalculated next time. However, in other ways, it is very simple. 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 8,388,608 (8MB) and 1,048,576 (1MB).
Next, type SHOW STATUS LIKE ‘qcache%’; to see all the status
information about the query cache. You should get output like this:
Click here to view code image
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 |