6.9 MySQL Query Cache

From version 4.0.1, MySQL server features a Query Cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If an identical query is later received, the server will retrieve the results from the query cache rather than parsing and executing the same query again.

NOTE: The query cache does not return stale data. When data is modified, any relevant entries in the query cache are flushed.

The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many web servers that use a lot of dynamic content.

Below is some performance data for the query cache. (These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500 MHz with 2 GB RAM and a 64 MB query cache):

• If all of the queries you're performing are simple (such as selecting a row from a table with one row); but still differ so that the queries can not be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. However, in real life, queries are much more complicated than our simple example so the overhead is normally significantly lower.
• Searches after one row in a one row table is 238% faster. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
• If you want to disable the query cache code set query_cache_size=0. By disabling the query cache code there is no noticeable overhead. (query cache can be excluded from code with help of configure option --without-query-cache)

6.9.1 How The Query Cache Operates

Queries are compared before parsing, thus

SELECT * FROM tbl_name

and

Select * from tbl_name

are regarded as different queries for query cache, so queries need to be exactly the same (byte for byte) to be seen as identical. In addition, a query may be seen as different if for instance one client is using a new communication protocol format or another character set than another client.

Queries that uses different databases, uses different protocol versions or the uses different default character sets are considered different queries and cached separately.

The cache does work for SELECT SQL_CALC_FOUND_ROWS ... and SELECT FOUND_ROWS() ... type queries because the number of found rows is also stored in the cache.

If query result was returned from query cache then status variable Com_select will not be increased, but Qcache_hits will be. See section Query Cache Status and Maintenance.

If a table changes (INSERT, UPDATE, DELETE, TRUNCATE, ALTER or DROP TABLE|DATABASE), then all cached queries that used this table (possibly through a MRG_MyISAM table!) become invalid and are removed from the cache.

Transactional InnoDB tables that have been changed will be invalidated when a COMMIT is performed.

In MySQL 4.0, the query cache is disabled inside of transactions (it does not return results). Beginning with MySQL 4.1.1, the query cache will also work inside of transactions when using InnoDB tables (it will use the table version number to detect if the data is still current or not).

Before MySQL 5.0, a query that begins with a leading comment might be cached, but could not be fetched from the cache. This problem is fixed in MySQL 5.0.

A query cannot be cached if it contains one of the functions:

Nor can a query be cached if it contains user variables, references the mysql system database, is of the form SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... or of the form SELECT * FROM AUTOINCREMENT_FIELD IS NULL (to retrieve last insert id - ODBC work around).

However, FOUND_ROWS() will return the correct value, even if the preceding query was fetched from the cache.

In case a query does not use any tables, or uses temporary tables, or if the user has a column privilege for any of the involved tables, that query will not be cached.

Before a query is fetched from the query cache, MySQL will check that the user has SELECT privilege to all the involved databases and tables. If this is not the case, the cached result will not be used.

6.9.2 Query Cache Configuration

The query cache adds a few MySQL system variables for mysqld which may be set in a configuration file, on the command-line when starting mysqld.

• query_cache_limit Don't cache results that are bigger than this. (Default 1M).
• query_cache_min_res_unit

  This variable is present from version 4.1.

  The result of a query (the data that is also sent to the client) is stored in the query cache during result retrieval. Therefore the data is usually not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (time wise), the query cache allocates blocks with a minimum size of query_cache_min_res_unit. When a query is executed, the last result block is trimmed to the actual data size, so that unused memory is freed.

  • The default value of query_cache_min_res_unit is 4 KB which should be adequate for most cases.
  • If you have a lot of queries with small results, the default block size may lead to memory fragmentation (indicated by a large number of free blocks (Qcache_free_blocks), which can cause the query cache to have to delete queries from the cache due to lack of memory (Qcache_lowmem_prunes)). In this case you should decrease query_cache_min_res_unit.
  • If you mostly have queres with big results (see Qcache_total_blocks and Qcache_queries_in_cache), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it to large (see the previous point).
• query_cache_size The amount of memory (specified in bytes) allocated to store results from old queries. If this is 0, the query cache is disabled (default).
• query_cache_type This may be set (only numeric) to

  Option	Description
  0	(OFF, don't cache or retrieve results)
  1	(ON, cache all results except SELECT SQL_NO_CACHE ... queries)
  2	(DEMAND, cache only SELECT SQL_CACHE ... queries)

Inside a thread (connection), the behaviour of the query cache can be changed from the default. The syntax is as follows:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

6.9.3 Query Cache Options in SELECT

There are two possible query cache related parameters that may be specified in a SELECT query:

6.9.4 Query Cache Status and Maintenance

With the FLUSH QUERY CACHE command you can defragment the query cache to better utilise its memory. This command will not remove any queries from the cache. FLUSH TABLES also flushes the query cache.

The RESET QUERY CACHE command removes all query results from the query cache. You can check whether the query cache is present in your MySQL version:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

You can monitor query cache performance in SHOW STATUS:

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

The query cache uses variable length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE only a single (big) free block remains.

Note: Every query needs a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query needs one block, but if two or more queries use same table only one block needs to be allocated.

You can use the Qcache_lowmem_prunes status variable to tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45