MySQL - Query Cache

Consider using memcache instead of query cache. Memcache can be distributed, and it does not hit the database at all (unless the data does not exist in memcache). Memcache also has its quirks.

MySQL supports a query cache that greatly increases performance under conditions such that the server's query mix includes SELECT statements that are processed repeatedly and return the same result each time. Using the query cache can result in tremendous performance boost and reduction in server load, especially for disk or processor intensive queries.

If you enable the query cache, the server uses it as follows:

  • The server compares each SELECT query that it receives to any already present in the cache. If the query is present and none of the tables that it uses have changed since the result was cached, the server returns the result immediately.
  • If the query is not present in the cache or if any of the tables that it uses have changed, the server executes the query and caches its result.
  • The server determines whether a query is in the cache based on exact case-sensitive comparison of query strings. That means 'SELECT * FROM table_name' and 'select * from table_name' are not considered the same. The server also takes into account any factors that distinguish otherwise-identical queries. Among these are the default database and the character set used by each client. For example, two 'SELECT * FROM table_name' queries are lexically identical, but are semantically different if each applies to a different default database or were sent by clients that are using different default character sets.
  • Query cache is global, so a query result placed in the cache can be returned to any client that has the necessary privileges for the tables used by the query.
SHOW VARIABLES LIKE 'query_cache%';

query_cache_type specifies the type of caching to perform. The value is OFF if the query cache is disabled, ON if it is enabled, and DEMAND if caching is done only for statements that begin with SELECT SQL_CACHE. The default value of query_cache_type is ON (caching allowed). However, the query cache is not operational unless its size is also set to larger than the default value of zero. query_cache_type also controls retrieval of each results. If the cache contains query results and you disable it, no results are returned from the cache until you enable it again.

query_cache_size is the size of the query cache in bytes. If the size is 0, the cache is disabled even if query_cache_type is not OFF. If you do not intend to use the query cache, you should set the value of query_cache_size to zero. If the value is greater than zero, the server allocates that much memory for the query cache even if it is disabled.

query_cache_limit is upper bound on how large an individual query result can be and still be eligible for caching. The default limit is 1MB. If query results that you want to cache are larger than the default query_cache_limit value, increase it. The disadvantage of doing this is that large results leave less room for caching other queries, so you might find it necessary to increase the total cache size (query_cache_size) as well.

Other query cache system variables, query_cache_min_res_unit and query_cache_wlock_invalidate, are of lesser concern.

query_cache_min_res_unit is the allocation block size used when caching results. If you cache many small results, fragmentation can occur. In this case, you may get better allocation behavior by decreasing the variable value

query_cache_wlock_invalidate determines whether a write lock on a table causes other clients to wait for queries that could be served by cached results. Normally, a write lock does not cause a wait for a cached result. Setting this variable to ON causes query results for a table to become invalidated when it is write-locked, which also causes other clients to wait for queries on the table.

Typically you set the query cache variables in an option file. In an option file, the query_cache_type value should be given as a number: 0 for OFF, 1 for ON, and 2 for DEMAND:

 [mysqld]
  query_cache_type = 1
  query_cache_size = 10M
  query_cache_limit = 2M

If you have SUPER privilege, you can change these variables for a running server without restarting it by using the following statements:

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 10485760;
SET GLOBAL query_cache_limit = 2097152;

If you set the variables with SET statements, the changes will be lost at the next server restart, so SET is useful primarily for testing cache settings. When you find suitable values, set them in the option file.

query_cache_type also exists in a session variable, which enables clients to set query caching behavior for their own connection (assuming that the cache size is greater than 0). For example, a client can disable caching for its own queries by issuing:

SET SESSION query_cache_type = OFF;

query_cache_size: If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

Measuring Query Cache Utilization

mysql> SHOW STATUS LIKE 'Qcache%';

Qcache_hits indicates how many times a query did not have to executed because its result could be served from the cache.

Qcache_inserts is the total number of queries that have been put in the cache. Qcache_queries_in_cache indicates the number of queries currently registered in the cache. The difference between the two values indicates how many cached queries were displaced to make room for newer queries, or discarded because the became invalid.

Qcache_lowmem_prunes indicates how many query results were displaced due to lack of free memory in the cache.

If your hit count is low and insert count is high, this might be a symptom of a query cache that is two small. Increase its size to see if the ratio of hits to inserts improves. It might also be that the server is attempting to cache query results under conditions when it's really not worth it. Examine the server's query mix to see which tables have both selects and many updates. If a table changes often, it is not likely that results for SELECT statements that retrieve from the table will remain valid very long.

With frequent updates, cache results do not remain valid long and are unlikely to provide any real performance benefit. In this situation, you can avoid the overhead of caching the results by including the SQL_NO_CACHE modifier:

SELECT SQL_NO_CACHE ... FROM table_name;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License