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 exactly 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 will do its best but will not be optimal. If your site is mostly reading with few writes, more queries will be 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_limitthe 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

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

j-

From that, we can see that only 18 queries are in the cache (Qcache_queries_in_cache), we have 169544 bytes of memory 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 due to lack of memory (Qcache_iowmem_prunes), giving the 18 from before. Qcache_not_cached is 0, which means 0 queries were not cachedMySQL is caching them all.

From that, we can calculate how many total queries came init is the sum of Qcache_ hits, Qcache_inserts, and Qcache_not_cached, which is 736. We 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 our example, we can see that many queries have been trimmed because there is not enough memory in the query cache. This can be changed by editing your /etc/my.cnf file and adding a line like this one, somewhere in the [mysqid] 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 will have need to go beyond a 32MB query cache, but keep an eye on the Qcache_iowmem_ prunes value to ensure 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 it 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.

Was this article helpful?

0 0

Post a comment