39.4. Using the Query Cache

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 results each time. Using the query cache can result in a 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 without executing the query again.

• If the query is not present in the cache or if any of the tables that it uses have changed (thus invalidating the saved result), 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 the following two queries are not considered the same:

   SELECT * FROM table_name; select * from table_name; 

  
  

  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 may be 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.

The 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 referred to by the query.

39.4.1. Enabling the Query Cache

Several system variables are associated with the query cache:

 mysql> SHOW VARIABLES LIKE 'query_cache%'; +------------------------------+---------+ | Variable_name                | Value   | +------------------------------+---------+ | query_cache_limit            | 1048576 | | query_cache_min_res_unit     | 4096    | | query_cache_size             | 8388608 | | query_cache_type             | ON      | | query_cache_wlock_invalidate | OFF     | +------------------------------+---------+ 

Three of those variables exert primary control over the query cache:

• query_cache_type specifies the type of caching to perform. The value is OFF if the 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 cache is not operational unless its size also is set larger than the default value of zero.

  query_cache_type also controls retrieval of cached 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 cache even if it is disabled.

• query_cache_limit is the 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 cached 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.

The other query cache system variables, query_cache_min_res_unit and query_cache_wlock_invalidate, are of lesser concern than the three primary variables. 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 where you list the server's startup options. In an option file, the query_cache_type value should be given as a number: 0 for OFF, 1 for ON, and 2 DEMAND. For example, to enable the query cache, allocate 10MB of memory to it, and allow individual query results up to 2MB to be cached, put the following lines in the option file, and restart the server:

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

If you have the 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 as a session variable, which enables clients to set query caching behavior for their own connection (assuming that the cache size is greater than zero). For example, a client can disable caching for its own queries by issuing this statement:

 SET SESSION query_cache_type = OFF; 

39.4.2. Measuring Query Cache Utilization

The server provides information about the operation of the query cache by means of a set of status variables. To view these variables, use the following statement:

 mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+--------+ | Variable_name           | Value  | +-------------------------+--------+ | Qcache_free_blocks      | 98     | | Qcache_free_memory      | 231008 | | Qcache_hits             | 21145  | | Qcache_inserts          | 12823  | | Qcache_lowmem_prunes    | 584    | | Qcache_not_cached       | 10899  | | Qcache_queries_in_cache | 360    | | Qcache_total_blocks     | 861    | +-------------------------+--------+ 

Qcache_hits indicates how many times a query did not have to be 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 they 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 the insert count is high, this might be a symptom of a query cache that is too small. Try increasing 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's not likely that results for SELECT statements that retrieve from the table will remain valid very long.

Suppose that an inventory table records stock level values for items in the inventory. This table might be queried frequently to obtain current stock levels, but also modified frequently as items are sold or restocked. 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 in SELECT statements for the table:

 SELECT SQL_NO_CACHE ... FROM inventory ... ;