MySQL Query Cache
=================
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.
help of configure option `--without-query-cache')
How the Query Cache Operates
----------------------------
Queries are compared before parsing, thus
SELECT * FROM tbl_name
and
Select * from tbl_name
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. *Note
Query Cache Status and Maintenance::.
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.
`RELEASE_LOCK' `LOAD_FILE'
`MASTER_POS_WAIT' `NOW' `SYSDATE'
`CURRENT_TIMESTAMP' `CURDATE' `CURRENT_DATE'
`CURTIME' `CURRENT_TIME' `DATABASE'
`ENCRYPT' (with one `LAST_INSERT_ID' `RAND'
parameter)
`UNIX_TIMESTAMP' `USER' `BENCHMARK'
(without parameters)
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.
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.
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'
*Option* *Description*
0 or OFF Don't cache or retrieve results.
1 or ON Cache all results except `SELECT SQL_NO_CACHE
...' queries.
2 or DEMAND Cache only `SELECT SQL_CACHE ...' queries.
Query Cache Options in `SELECT'
-------------------------------
There are two possible query cache related parameters that may be
specified in a `SELECT' query:
*Option* *Description*
`SQL_CACHE' If `QUERY_CACHE_TYPE' is `DEMAND', allow the query to be
cached. If `QUERY_CACHE_TYPE' is `ON', this is the
default. If `QUERY_CACHE_TYPE' is `OFF', do nothing.
`SQL_NO_CACHE' Make this query non-cachable, don't allow this query to
be stored in the cache.
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':
*Variable* *Description*
`Qcache_queries_in_cache'Number of queries registered in the
cache.
`Qcache_inserts' Number of queries added to the
cache.
`Qcache_hits' Number of cache hits.
`Qcache_lowmem_prunes' Number of queries that were deleted
from cache because of low memory.
`Qcache_not_cached' Number of non-cached queries (not
cachable, or due to
`QUERY_CACHE_TYPE').
`Qcache_free_memory' Amount of free memory for query
cache.
`Qcache_free_blocks' Number of free memory blocks in
query cache.
`Qcache_total_blocks' Total number of blocks in query
cache.
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.
[Назад] [Содержание] [Вперед]
| Главная |