La Query Cache

La cache delle query registra i risultati delle SELECT, in modo che in futuro, se si riceverà una query identica, sarà possibile restituire i risultati velocemente.

Questo è molto utile in ambienti high-read e low-write (come la maggior parte dei siti web).

Configurare la Query Cache

Se MariaDB non è stata esplicitamente compilata senza la cache delle query, questa sarà sempre disponibile. La variabile server di sistema have_query_cache dice se la query cache è disponibile.

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Se è impostata a NO, non sarà possibile abilitate la cache delle query a meno di non ricompilare o reinstallare una versione di MariaDB con la cache.

Per sapere se la cache è abilitata, si veda la variabile di sistema query_cache_type. Per abilitarla, si può impostare query_cache_type a 1.

Come funziona la Query Cache

Quando la query cache è abilitata e una query SELECT viene elaborata, la cache delle query viene esaminata per vedere se contiene la query. Se non la contiene, la query verrà elaborata normalmente e registrata, con l'insieme dei risultati, nella query cache. Se la query è già nella cache, i risultati vengono estratti dalla cache, il che è molto più semplice di un'elaborazione normale.

Ogni volta che i dati della tabella vengono modificati, i risultati di tutte le query influenzate da quei dati vengono eliminati. Non è possibile in alcun modo leggere risultati obsoleti dalla cache delle query.

Quando lo spazio allocato per la query cache è pieno, i risultati più vecchi vengono eliminati.

Query registrate nella Query Cache

Se la variabile di sistema query_cache_type è impostata a 1, o ON, tutte le query che non superano le dimensioni massime vengono registrate nella cache, tranne il caso in cui contengano una clausola SQL_NO_CACHE, o il caso in cui per la loro natura il caching non abbia senso, per esempio se contengono una funzione che restituisce l'ora corrente.

Se una delle seguenti funzioni è presente in una query, questa non verrà registrata nella cache:

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

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_NO_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 system variable. About 40KB is needed for various query cache structures, and setting the variable to a size lower than this will result in a warning.

The query cache size is allocated in 1024 byte-blocks. Setting a size that is not a multiple of 1024 will result in it being rounded down the nearest multiple, and a warning being triggered.

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 67108864 |
+------------------+----------+

MariaDB [(none)]> SET GLOBAL query_cache_size = 8000000;
Query OK, 0 rows affected, 1 warning (0.03 sec)

MariaDB [(none)]> 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 system variable.

Examining the Query Cache

A number of status variables provide information about the query cache.

MariaDB [(none)]> 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.

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:

MariaDB [(none)]> FLUSH QUERY CACHE;

After this, there will only be one free block:

MariaDB [(none)]> 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.