Query Cache
Contents
- Setting up the Query Cache
- How the Query Cache Works
- Queries Stored in Query Cache
- Limiting the size of the Query Cache
- Examining the Query Cache
- Query Cache Fragmentation
- Emptying and Disabling the Query Cache
- Limitations
- Transactions and the Query Cache
- Query cache internal structure
- Timeout and mutex contention
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).
Note that the query cache cannot be enabled in certain environments. See 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
server variable will show whether the query cache is available.
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
server variable. By default it is enabled - if not, enable it by setting query_cache_type
to 1
.
Although enabled, the query cache size is by default 0KB, which effectively disables the query cache. Enable it by setting to a large enough amount, for example:
SET GLOBAL query_cache_size = 1000000;
See 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 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 :
SELECT * FROM t
Is different from :
select * from t
Comments are also considered and can make the queries differ, so :
/* retry */SELECT * FROM t
Is different to :
/* retry2 */SELECT * FROM t
See the 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
(after MDEV-6631 feature request) and the query specifies SQL_CACHE
, the server will cache the query.
One important point of MDEV-6631 is : switching between query_cache_type=ON
and query_cache_type=DEMAND
can "turn off" query cache of old queries without the SQL_CACHE
string, that's not yet defined if we should include another query_cache_type
(DEMAND_NO_PRUNE) value or not to allow use of old queries
Queries Stored in Query Cache
If the 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. Check that SQL_NO_CACHE
will force server to don't use query cache locks.
If any of the following functions are present in a query, it will not be cached, queries with these functions sometimes are called 'non-deterministic' be careful with this to not confuse contexts.
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 partitioned table (>=MariaDB/MySQL 5.5.23)
- 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
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
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
. 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 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.
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
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 :
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.
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.
Note that queries returned from the query cache do not increment the Com_select
status variable, so to find the total number of valid queries run on the server, add Com_select
to Qcache_hits
.
The QUERY_CACHE_INFO plugin creates the QUERY_CACHE_INFO table in the 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
will defragment the query cache without dropping any queries :
FLUSH QUERY CACHE;
After this, there will only be one free block :
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
. FLUSH TABLES
will have the same effect.
Setting either query_cache_type
or 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.
- The query cache is not used by the Spider storage engine (amongst others).
- The query cache also needs to be disabled for MariaDB Galera cluster versions prior to "5.5.40-galera", "10.0.14-galera" and "10.1.2".
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 / COMMIT / 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 / UPDATE / DELETE / 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:
SELECT * FROM T1 <first insert to query cache, using FLAGS_IN_TRANS=0> +---+ | a | +---+ | 1 | +---+
BEGIN; SELECT * FROM T1 <first insert to query cache, using FLAGS_IN_TRANS=1> +---+ | a | +---+ | 1 | +---+
SELECT * FROM T1 <result from query cache, using FLAGS_IN_TRANS=1> +---+ | a | +---+ | 1 | +---+
INSERT INTO T1 VALUES(2); <invalidate queries from table T1 and disable query cache to table T1>
SELECT * FROM T1 <don't use query cache, a normal query from innodb table> +---+ | a | +---+ | 1 | | 2 | +---+
SELECT * FROM T1 <don't use query cache, a normal query from innodb table> +---+ | a | +---+ | 1 | | 2 | +---+
COMMIT; <query cache is now turned on to T1 table>
SELECT * FROM T1 <first insert to query cache, using FLAGS_IN_TRANS=0> +---+ | a | +---+ | 1 | +---+
SELECT * FROM T1 <result from query cache, using FLAGS_IN_TRANS=0> +---+ | 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 session variable)
- pkt_nr (protocol flag)
- character set client (character_set_client session variable)
- character set results (character_set_results session variable)
- collation connection (collation_connection session variable)
- limit (sql_select_limit session variable)
- time zone (time_zone session variable)
- sql_mode (sql_mode session variable)
- max_sort_length (max_sort_length session variable)
- group_concat_max_len (group_concat_max_len session variable)
- default_week_format (default_week_format session variable)
- div_precision_increment (div_precision_increment session variable)
- lc_time_names (lc_time_names session variable)
More information can be found by viewing the source code (MariaDB 10.1) :
- https://github.com/MariaDB/server/blob/10.1/sql/sql_cache.cc
- https://github.com/MariaDB/server/blob/10.1/sql/sql_cache.h
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 include two variables to tune this timeout).
From the sql_cache.cc, function "try_lock" using TIMEOUT :
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 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 status variable.