MySQL 4.0.x : Mise en œuvre du cache de requêtes (Query Cache)

Introduction

A partir de la version 4.0.X de MySQL apparaît le cache de requête (Query Cache). Le cache de requêtes stocke le texte d’une commande SELECT ainsi que le result set correspondant qui a été adressé au client. Si la même requête est adressée par un client ultérieurement, le serveur MySQL extrait le résultat à partir du cache de requêtes au lieu de parser et d’exécuter à nouveau la requête.

Lorsqu’une donnée est modifiée, toutes les entrées dans le cache de requêtes concernant cette données sont flushées (vidées).

Le cache de requêtes apparaît comme très utile lorsqu’il s’agit de tables qui ne subissent pas de modifications transactionnelles régulières ou lorsque des requêtes identiques sont adressées très régulièrement par les clients.

Cette fiche technique a pour objectif de présenter rapidement cette nouvelle fonctionnalité de MySQL 4.0.x.

Pour désactiver le cache de requêtes, appliquer query_cache_size = 0 dans le fichier de configuration du serveur MySQL. Le cache de requête peut être également écarté lors de la compilation de MySQL :

--without-query-cache

Principe de fonctionnement du cache de requêtes

Les requêtes sont comparées avant le parsing de ces dernières, aussi les requêtes SELECT * FROM tbl_name et select * from tbl_name sont considérées comme différentes pour le cache de requêtes.

Il est donc impératif, pour bénéficier du cache de requêtes, que les requêtes soient syntaxiquement rigoureusement identiques (octet par octet).

Par ailleurs, une requête peut être vue comme différente par le moteur du cache de requêtes si un client utilise un protocole de communication ou un jeu de caractères différent d’un autre client.

Les requêtes qui utilisent des bases de données différentes, des versions différentes de protocoles ou des jeux de caractères différents impliquent des montées en caches séparées pour ces requêtes.

Le cache fonctionne pour les requêtes de type SELECT CALC_ROWS et SELECT FOUND_ROWS car le nombre de lignes trouvées est également stocké dans le cache.

Si le résultat d’une requête est retourné par le cache alors la variable de statut Com_select ne sera pas incrémentée, mais le variable Qcache_hits le sera.

Si des modifications de tables sont réalisées (INSERT, UPDATE, DELETE, TRUNCATE, ALTER ou DROP TABLE | DATABASE), toutes les requêtes en cache qui mettent en jeu la table deviennent invalides et sont supprimées du cache.

Pour les tables InnoDB transactionnelles qui sont modifiées, ces dernières sont invalidées dans le cache lorsque la commande COMMIT est lancée.

Une requête ne peut être mise en cache, si une des fonctions ci-dessous est sollicitée dans la requête :

User-Defined Functions CONNECTION_ID FOUND_ROWS
GET_LOCK RELEASE_LOCK LOAD_FILE
MASTER_POS_WAIT NOW SYSDATE
CURRENT_TIMESTAMP CURDATE CURRENT_DATE
CURTIME CURRENT_TIME DATABASE
ENCRYPT (avec 1 paramètre) LAST_INSERT_ID RAND
UNIX_TIMESTAMP (sans paramètres) USER
BENCHMA

Une requête ne peut également être mise en cache si :

  • la requête contient des variables utilisateur ;
  • la requête faire référence à une table système MySQL ;
  • la requête est de la forme
    • SELECT … IN SHARE MODE
    • SELECT … INTO OUTFILE …
    • SELECT … INTO DUMPFILE …
    • SELECT * FROM AUTOINCREMENT_FIELD IS NULL
  • la requête concerne une table temporaire

MySQL vérifie bien sur que l’utilisateur possède le privilège SELECT sur les tables et bases de données impliquées, dans le cas contraire le result set en cache de la requête n’est pas utilisé.

Configuration du cache de requêtes

Le cache de requête ajoute de nouvelles variables systèmes MySQL qui peuvent être appliquées dans le fichier de configuration ou dans la ligne de commande démarrant mysqld.

query_cache_limit empêche la mise en cache de result sets dont la taille est supérieure à la valeur de ce paramètre (valeur par défaut : 1M).
query_cache_min_res_unit (MySQL 4.1 seulement) Le résultat d’une requête est stockée dans le cache de requêtes durant l’extraction des données vers le client. Aussi les données ne sont pas écrites dans le cache en une seule fois. Le cache de requêtes alloue des blocs pour le stockage des données au cours de l’extraction des données, lorsqu’un bloc est rempli, un nouveau bloc est alloué. Parce que l’opération d’allocation en mémoire est coûteuse, le cache de requêtes alloue des blocs avec une taille minimale donnée par la variable système query_cache_min_res_unit.
  • La valeur par défaut pour query_cache_min_res_unit est de 4Kb, ce qui est dans la plupart des cas une valeur adéquate.
  • Si les requêtes en cache impliquent des result sets de petite taille, la taille par défaut du bloc peut conduire à une fragmentation importante de la mémoire pour le cache (fragmentation donnée par un nombre important de blocs alloués mais libres, Qcache_free_blocks), cette configuration oblige le cache de requêtes à supprimer des requêtes du cache suite à une saturation de la mémoire (Qcache_lowmem_prunes). Dans ce cas, il est recommandé de diminuer query_cache_min_res_unit .
  • Dans un contexte où des requêtes retournent de larges result sets (voir les valeurs Qcache_total_blocks et Qcache_queries_in_cache), les performances sont accrues en augmentant query_cache_min_res_unit.
query_cache_size Quantité de mémoire (en octets) allouée pour le stockage des result sets des anciennes requêtes. Si cette valeur vaut 0, le cache est désactivé (valeur par défaut).
query_cache_type Cette variable système ne peut être que numérique avec les valeurs suivantes :
Option Description
0 Aucune mise en cache
1 Mise en cache sauf pour les requêtes SELECT SQL_NO_CACHE
2 Mise en cache uniquement pour les requêtes SELECT SQL_CACHE
Au sein d’une connexion, le comportement du cache de requêtes peut être modifié de la valeur par défaut. Syntaxe :
QUERY_CACHE_TYPE = ON | OFF | DEMAND
QUERY_CACHE_TYPE = 0 | 1 | 2
Option Description
0 ou OFF Aucune mise en cache
1 ou ON Mise en cache sauf pour les requêtes SELECT SQL_NO_CACHE
2 ou DEMAND Mise en cache uniquement pour les requêtes SELECT SQL_CACHE

Options de mise en cache avec la commande SELECT

Deux options dans la commande SELECT contrôlent la mise en cache d’une requête :

Option Description
SQL_CACHE Si QUERY_CACHE_TYPE vaut DEMAND, la requête est autorisée à monter en cache. Si QUERY_CACHE_TYPE vaut ON, la requête monte en cache par défaut. Si QUERY_CACHE_TYPE vaut OFF, aucune action n’est menée. 
SQL_NO_CACHE Option qui désactive la mise en cache de la requête.


Maintenance et statut du cache de requêtes

La commande FLUSH QUERY CACHE défragmente le cache de requêtes afin d’optimiser l’utilisation de la mémoire. Cette commande ne supprime aucune requête du cache.

La commande FLUSH TABLES flush également le cache de requêtes.

La commande RESET QUERY CACHE supprime tous les result sets dans le cache.

Pour vérifier si le cache de requêtes est disponible dans la version MySQL utilisée :

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

Il est possible de monitorer les performances du cache de requêtes avec la commande SHOW STATUS :

Variable Description
Qcache_queries_in_cache Nombre de requêtes présentes en cache.
Qcache_inserts Nombre de requêtes ajoutées au cache.
Qcache_hits Nombre de fois où la requête a pu être traitée à partir du cache.
Qcache_lowmem_prunes Nombre de requêtes qui ont été supprimées du cache pour cause de mémoire restante trop faible.
Qcache_not_cached Nombre de requêtes non montées en cache (à cause de QUERY_CACHE_TYPE par exemple)
Qcache_free_memory Quantité de mémoire disponible pour la mise en cache.
Qcache_free_blocks Quantité de blocs libres dans le cache de requêtes.
Qcache_total_blocks Nombre total de blocs dans le cache de requêtes.

Nombre total de requêtes = Qcache_inserts + Qcache_hits + Qcache_not_cached.

Le cache de requête utilise des tailles de blocs variables, aussi Qcache_total_blocks et Qcache_free_blocks peuvent indiquer une fragmentation de la mémoire dédiée au cache.

Après un lancement de la commande FLUSH QUERY CACHE, il demeure un unique bloc libre.

Note : chaque requête requiert deux blocs (un pour le texte de la requête et un autre pour le result set)

La variable Qcache_lowmem_prunes permet de tuner la taille du cache. Cette variable compte les requêtes qui ont été supprimées du cache pour libérer de l’espace mémoire afin de monter en cache de nouvelles requêtes. Le cache utilise la stratégie LRU (least recently used) pour supprimer les requêtes du cache.