Introduction
À 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-cachePrincipe 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 MODESELECT … 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.
|
||||||||||||||||
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 :
|
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.