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 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 CacheSeveral 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:
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 UtilizationThe 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 ... ; |