16.3 Using the Query Cache

< Day Day Up >

16.3 Using the Query Cache MySQL supports a query cache that greatly increases performance if the server's query mix includes SELECT statements that are processed repeatedly and return the same results each time. If you enable the query cache, the server uses it as follows: The server compares each SELECT query that arrives to any already in the cache. If the query is already 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. 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 factor that distinguishes otherwise identical queries. Among these are the character set used by each client and the default database. For example, two SELECT * FROM table_name queries might be lexically identical but are semantically different if each applies to a different default database. The query cache is global, so a query result placed in the cache can be returned to any client. Using the query cache can result in a tremendous performance boost and reduction in server load, especially for disk- or processor-intensive queries. Three system variables control query cache operation: mysql> SHOW VARIABLES LIKE 'query_cache%'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | query_cache_limit | 1048576 | | query_cache_size | 0 | | query_cache_type | ON | +-------------------+---------+ The default value of query_cache_type is ON (caching allowed). However, the cache is not operational unless its size is set larger than the default value of zero. To enable the query cache, set the value of query_cache_size to a nonzero size in bytes to indicate how much memory to allocate to it. You may optionally set the query_cache_limit variable as well. It places an upper bound on how large an individual query result can be and still be eligible for caching. The default limit is 1MB. Typically, you set the query cache variables in an option file where you list the server's startup options. For example, to allocate 10MB of memory to the query cache 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_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_size = 10485760; SET GLOBAL query_cache_limit = 2097152; If you set the variables that way, the changes will be lost at the next server restart, so SET is useful primarily for testing cache settings. When you find suitable values, record them in the option file. 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_queries_in_cache | 360 | | Qcache_inserts | 12823 | | Qcache_hits | 21145 | | Qcache_lowmem_prunes | 584 | | Qcache_not_cached | 10899 | | Qcache_free_memory | 231008 | | Qcache_free_blocks | 98 | | Qcache_total_blocks | 861 | +-------------------------+--------+ 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. Qcache_hits indicates how many times a query did not have to be executed because its result could be served from the cache.

< Day Day Up >