Книга: Fedora™ Unleashed, 2008 edition

Using the Query Cache

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.

Оглавление книги