# Query Cache The query cache stores results of `SELECT` queries so that if the identical query is received in future, the results can be quickly returned. This is extremely useful in high-read, low-write environments (such as most websites). It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default. Note that the query cache cannot be enabled in certain environments. See [Limitations](#limitations). ## Setting Up the Query Cache Unless MariaDB has been specifically built without the query cache, the query cache will always be available, although inactive. The [have\_query\_cache](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#have_query_cache) server variable will show whether the query cache is available. ```sql SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ ``` If this is set to `NO`, you cannot enable the query cache unless you rebuild or reinstall a version of MariaDB with the cache available. To see if the cache is enabled, view the [query\_cache\_type](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_type) server variable. It is disabled by default — enable it by setting `query_cache_type` to `1` : ```sql SET GLOBAL query_cache_type = 1; ``` The [query\_cache\_size](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_size) is set to 1MB by default. Set the cache to a larger size if needed, for example: ```sql SET GLOBAL query_cache_size = 2000000; ``` The `query_cache_type` is automatically set to `ON` if the server is started with the `query_cache_size` set to a non-zero (and non-default) value. See [Limiting the size of the Query Cache](#limiting-the-size-of-the-query-cache) below for details. ## How the Query Cache Works When the query cache is enabled and a new `SELECT` query is processed, the query cache is examined to see if the query appears in the cache. Queries are considered identical if they use the same database, same protocol version and same default character set. Prepared statements are always considered as different to non-prepared statements, see [Query cache internal structure](#query-cache-internal-structure) for more info. If the identical query is not found in the cache, the query will be processed normally and then stored, along with its result set, in the query cache. If the query is found in the cache, the results will be pulled from the cache, which is much quicker than processing it normally. Queries are examined in a case-sensitive manner, so : ```sql SELECT * FROM t ``` Is different from : ```sql SELECT * from t ``` Comments are also considered and can make the queries differ, so : ```sql /* retry */SELECT * FROM t ``` Is different from : ```sql /* retry2 */SELECT * FROM t ``` See the [query\_cache\_strip\_comments](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_strip_comments) server variable for an option to strip comments before searching. Each time changes are made to the data in a table, all affected results in the query cache are cleared. It is not possible to retrieve stale data from the query cache. When the space allocated to query cache is exhausted, the oldest results will be dropped from the cache. When using `query_cache_type=ON`, and the query specifies `SQL_NO_CACHE` (case-insensitive), the server will not cache the query and will not fetch results from the query cache. When using `query_cache_type=DEMAND` and the query specifies `SQL_CACHE`, the server will cache the query. ## Queries Stored in the Query Cache If the [query\_cache\_type](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_type) system variable is set to `1`, or `ON`, all queries fitting the size constraints will be stored in the cache unless they contain a `SQL_NO_CACHE` clause, or are of a nature that caching makes no sense, for example making use of a function that returns the current time. Queries with `SQL_NO_CACHE` will not attempt to acquire query cache lock. If any of the following functions are present in a query, it will not be cached. Queries with these functions are sometimes called 'non-deterministic' — don't get confused with the use of this term in other contexts. | | | | ------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | [BENCHMARK()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/benchmark) | [CONNECTION\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/connection_id) | | [CONVERT\_TZ()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/convert_tz) | [CURDATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/curdate) | | [CURRENT\_DATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/current_date) | [CURRENT\_TIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/current_time) | | [CURRENT\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/current_timestamp) | [CURTIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/curtime) | | [DATABASE()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/database) | [ENCRYPT()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/encryption-hashing-and-compression-functions/encrypt) (one parameter) | | [FOUND\_ROWS()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/found_rows) | [GET\_LOCK()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/get_lock) | | [LAST\_INSERT\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id) | [LOAD\_FILE()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/load_file) | | [MASTER\_POS\_WAIT()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/master_pos_wait) | [NOW()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/now) | | [RAND()](https://mariadb.com/docs/server/reference/sql-functions/numeric-functions/rand) | [RELEASE\_LOCK()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/release_lock) | | [SLEEP()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/sleep) | [SYSDATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/sysdate) | | [UNIX\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/unix_timestamp) (no parameters) | [USER()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/user) | | [UUID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid) | [UUID\_SHORT()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid_short) | A query will also not be added to the cache if: * It is of the form: * SELECT SQL\_NO\_CACHE ... * SELECT ... INTO OUTFILE ... * SELECT ... INTO DUMPFILE ... * SELECT ... FOR UPDATE * SELECT \* FROM ... WHERE autoincrement\_column IS NULL * SELECT ... LOCK IN SHARE MODE * It uses TEMPORARY table * It uses no tables at all * It generates a warning * The user has a column-level privilege on any table in the query * It accesses a table from INFORMATION\_SCHEMA, mysql or the performance\_schema database * It makes use of user or local variables * It makes use of stored functions * It makes use of user-defined functions * It is inside a transaction with the SERIALIZABLE isolation level * It is quering a table inside a transaction after the same table executed a query cache invalidation using `INSERT`, `UPDATE` or `DELETE` The query itself can also specify that it is not to be stored in the cache by using the `SQL_NO_CACHE` attribute. Query-level control is an effective way to use the cache more optimally. It is also possible to specify that *no* queries must be stored in the cache unless the query requires it. To do this, the [query\_cache\_type](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_type) server variable must be set to `2`, or `DEMAND`. Then, only queries with the `SQL_CACHE` attribute are cached. ## Limiting the Size of the Query Cache There are two main ways to limit the size of the query cache. First, the overall size in bytes is determined by the [query\_cache\_size](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_size) server variable. About 40KB is needed for various query cache structures. The query cache size is allocated in 1024 byte-blocks, thus it should be set to a multiple of 1024. The query result is stored using a minimum block size of [query\_cache\_min\_res\_unit](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_min_res_unit). Check two conditions to use a good value of this variable: Query cache insert result blocks with locks, each new block insert lock query cache, a small value will increase locks and fragmentation and waste less memory for small results, a big value will increase memory use wasting more memory for small results but it reduce locks. Test with your workload for fine tune this variable. If the [strict mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode) is enabled, setting the query cache size to an invalid value will cause an error. Otherwise, it will be set to the nearest permitted value, and a warning will be triggered. ```sql SHOW VARIABLES LIKE 'query_cache_size'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | query_cache_size | 67108864 | +------------------+----------+ SET GLOBAL query_cache_size = 8000000; Query OK, 0 rows affected, 1 warning (0.03 sec) SHOW VARIABLES LIKE 'query_cache_size'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | query_cache_size | 7999488 | +------------------+---------+ ``` The ideal size of the query cache is very dependent on the specific needs of each system. Setting a value too small will result in query results being dropped from the cache when they could potentially be re-used later. Setting a value too high could result in reduced performance due to lock contention, as the query cache is locked during updates. The second way to limit the cache is to have a maximum size for each set of query results. This prevents a single query with a huge result set taking up most of the available memory and knocking a large number of smaller queries out of the cache. This is determined by the [query\_cache\_limit](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_limit) server variable. If you attempt to set a query cache that is too small (the amount depends on the architecture), the resizing will fail and the query cache will be set to zero, for example : ```sql SET GLOBAL query_cache_size=40000; Query OK, 0 rows affected, 2 warnings (0.03 sec) SHOW WARNINGS; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect query_cache_size value: '40000' | | Warning | 1282 | Query cache failed to set size 39936; new query cache size is 0 | +---------+------+-----------------------------------------------------------------+ ``` ## Examining the Query Cache A number of status variables provide information about the query cache. ```sql SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1158 | | Qcache_free_memory | 3760784 | | Qcache_hits | 31943398 | | Qcache_inserts | 42998029 | | Qcache_lowmem_prunes | 34695322 | | Qcache_not_cached | 652482 | | Qcache_queries_in_cache | 4628 | | Qcache_total_blocks | 11123 | +-------------------------+----------+ ``` `Qcache_inserts` contains the number of queries added to the query cache, `Qcache_hits` contains the number of queries that have made use of the query cache, while `Qcache_lowmem_prunes` contains the number of queries that were dropped from the cache due to lack of memory. The above example could indicate a poorly performing cache. More queries have been added, and more queries have been dropped, than have actually been used. Results returned by the query cache count towards `Com_select` (see [MDEV-4981](https://jira.mariadb.org/browse/MDEV-4981)). The [QUERY\_CACHE\_INFO plugin](https://mariadb.com/docs/server/reference/plugins/other-plugins/query-cache-information-plugin) creates the [QUERY\_CACHE\_INFO](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-query_cache_info-table) table in the [INFORMATION\_SCHEMA](https://mariadb.com/docs/server/reference/system-tables/information-schema), allowing you to examine the contents of the query cache. ## Query Cache Fragmentation The Query Cache uses blocks of variable length, and over time may become fragmented. A high `Qcache_free_blocks` relative to `Qcache_total_blocks` may indicate fragmentation. [FLUSH QUERY CACHE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush-query-cache) will defragment the query cache without dropping any queries : ```sql FLUSH QUERY CACHE; ``` After this, there will only be one free block : ```sql SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 6101576 | | Qcache_hits | 31981126 | | Qcache_inserts | 43002404 | | Qcache_lowmem_prunes | 34696486 | | Qcache_not_cached | 655607 | | Qcache_queries_in_cache | 4197 | | Qcache_total_blocks | 8833 | +-------------------------+----------+ ``` ## Emptying and disabling the Query Cache To empty or clear all results from the query cache, use [RESET QUERY CACHE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/reset). [FLUSH TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) will have the same effect. Setting either [query\_cache\_type](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_type) or [query\_cache\_size](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_size) to `0` will disable the query cache, but to free up the most resources, set both to `0` when you wish to disable caching. ## Limitations * The query cache needs to be disabled in order to use [OQGRAPH](https://mariadb.com/docs/server/server-usage/storage-engines/oqgraph-storage-engine). * The query cache is not used by the [Spider](https://mariadb.com/docs/server/server-usage/storage-engines/spider) storage engine (amongst others). ## LOCK TABLES and the Query Cache The query cache can be used when tables have a write lock (which may seem confusing since write locks should avoid table reads). This behaviour can be changed by setting the [query\_cache\_wlock\_invalidate](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_wlock_invalidate) system variable to `ON`, in which case each write lock will invalidate the table query cache. Setting to `OFF`, the default, means that cached queries can be returned even when a table lock is being held. For example: ```sql 1> SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ -- Here the query is cached -- From another connection execute: 2> LOCK TABLES T1 WRITE; -- Expected result with: query_cache_wlock_invalidate = OFF 1> SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ -- read from query cache -- Expected result with: query_cache_wlock_invalidate = ON 1> SELECT * FROM T1 -- Waiting Table Write Lock ``` ## Transactions and the Query Cache The query cache handles transactions. Internally a flag (`FLAGS_IN_TRANS`) is set to `0` when a query was executed outside a transaction, and to 1 when the query was inside a transaction ([begin](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/begin-end) / [COMMIT](https://mariadb.com/docs/server/reference/sql-statements/transactions/commit) / [ROLLBACK](https://mariadb.com/docs/server/reference/sql-statements/transactions/rollback)). This flag is part of the "query cache hash", in others words one query inside a transaction is different from a query outside a transaction. Queries that change rows ([INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert) / [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update) / [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete) / [TRUNCATE](https://mariadb.com/docs/server/reference/sql-functions/numeric-functions/truncate)) inside a transaction will invalidate all queries from the table, and turn off the query cache to the changed table. Transactions that don't end with `COMMIT` / `ROLLBACK` check that even without `COMMIT` / `ROLLBACK`, the query cache is turned off to allow row level locking and consistency level. Examples: ```sql SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ ``` ```sql BEGIN; SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ ``` ```sql SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ ``` ```sql INSERT INTO T1 VALUES(2); ``` ```sql SELECT * FROM T1 +---+ | a | +---+ | 1 | | 2 | +---+ ``` ```sql SELECT * FROM T1 +---+ | a | +---+ | 1 | | 2 | +---+ ``` ```sql COMMIT; ``` ```sql SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ ``` ```sql SELECT * FROM T1 +---+ | a | +---+ | 1 | +---+ ``` ## Query Cache Internal Structure Internally, each flag that can change a result using the same query is a different query. For example, using the latin1 charset and using the utf8 charset with the same query are treated as different queries by the query cache. Some fields that differentiate queries are (from "Query\_cache\_query\_flags" internal structure) : * query (string) * current database schema name (string) * client long flag (0/1) * client protocol 4.1 (0/1) * protocol type (internal value) * more results exists (protocol flag) * in trans (inside transaction or not) * autocommit ([autocommit](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#autocommit) session variable) * pkt\_nr (protocol flag) * character set client ([character\_set\_client](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#character_set_client) session variable) * character set results ([character\_set\_results](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#character_set_results) session variable) * collation connection ([collation\_connection](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#collation_connection) session variable) * limit ([sql\_select\_limit](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#sql_select_limit) session variable) * time zone ([time\_zone](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#time_zone) session variable) * sql\_mode ([sql\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#sql_mode) session variable) * max\_sort\_length ([max\_sort\_length](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#max_sort_length) session variable) * group\_concat\_max\_len ([group\_concat\_max\_len](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#group_concat_max_len) session variable) * default\_week\_format ([default\_week\_format](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#default_week_format) session variable) * div\_precision\_increment ([div\_precision\_increment](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#div_precision_increment) session variable) * lc\_time\_names ([lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names) session variable) ## Timeout and Mutex Contention When searching for a query inside the query cache, a `try_lock` function waits with a timeout of 50ms. If the lock fails, the query isn't executed via the query cache. This timeout is hard-coded ([MDEV-6766](https://jira.mariadb.org/browse/MDEV-6766) include two variables to tune this timeout). From the sql\_cache.cc, function "try\_lock" using `TIMEOUT` : ```c struct timespec waittime; set_timespec_nsec(waittime,(ulong)(50000000L)); /* Wait for 50 msec */ int res= mysql_cond_timedwait(&COND_cache_status_changed, &structure_guard_mutex, &waittime); if (res == ETIMEDOUT) break; ``` When inserting a query inside the query cache or aborting a query cache insert (using the [KILL](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/kill) command for example), a try\_lock function waits until the query cache returns; no timeout is used in this case. When two processes execute the same query, only the last process stores the query result. All other processes increase the [Qcache\_not\_cached](https://mariadb.com/docs/server/server-management/variables-and-modes/server-status-variables#qcache_not_cached) status variable. ## SQL\_NO\_CACHE and SQL\_CACHE There are two aspects to the query cache: placing a query in the cache, and retrieving it from the cache. 1. Adding a query to the query cache. This is done automatically for cacheable queries (see ([Queries Stored in the Query Cache](#queries-stored-in-the-query-cache)) when the [query\_cache\_type](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_type) system variable is set to `1`, or `ON` and the query contains no `SQL_NO_CACHE` clause, or when the [query\_cache\_type](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#query_cache_type) system variable is set to `2`, or `DEMAND`, and the query contains the `SQL_CACHE` clause. 2. Retrieving a query from the cache. This is done after the server receives the query and before the query parser. In this case one point should be considered: When using `SQL_NO_CACHE`, it should be after the first `SELECT` hint: ```sql SELECT SQL_NO_CACHE .... FROM (SELECT SQL_CACHE ...) AS temp_table ``` Don't use it like this: ```sql SELECT SQL_CACHE .... FROM (SELECT SQL_NO_CACHE ...) AS temp_table ``` The second query will be checked. The query cache only checks if `SQL_NO_CACHE`/`SQL_CACHE` exists after the first `SELECT`. (More info at [MDEV-6631](https://jira.mariadb.org/browse/MDEV-6631)) *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}