Tous les systèmes de gestion de bases de données robustes utilisent des caches mémoire qui contribuent à améliorer la réactivité globale de leur moteur interne. Bien que ça ne soit assurément pas sans coût, accéder à des données applicatives, à des plans d'exécution ou à des méta-données directement dans la RAM est en temps normal beaucoup plus rapide que récupérer les mêmes informations sur le disque ou les reconstruire de zéro.
Cet article se propose de comparer les stratégies de cache de données utilisées par un certain nombre d'éditeurs de SGBD avec celles de MySQL, pour se concentrer ensuite sur le cache de requêtes de ce dernier et étudier pourquoi son approche spécifique du memory caching peut être très rentable si correctement utilisée par les administrateurs de bases de données et les spécialistes de l'analyse de performances.
Les stratégies de cache mémoire des éditeurs de SGBD
Une étude détaillée sur la manière dont les éditeurs de bases de données les plus connus mettent en oeuvre leur stratégie de mise en cache serait un travail très long, nous aborderons donc le sujet rapidement et de manière large. En temps normal, les SGBD utilisent 3 grands types de caches :
Les caches de données qui contiennent les données applicatives souvent demandées.
Les caches de code qui stockent le code SQL ou procédural souvent exécuté.
Les caches de méta-données qui conservent des renseignements de référence comme les informations sur la structure des objets, les informations de sécurité, et d'autres encore.
Les bases de données utilisent d'autres structures mémoire, mais les trois citées plus haut forment le gros des besoins en RAM d'un SGBD.
En partant du bas : les caches de méta-données sont standard dans presque tous les systèmes relationnels, ils stockent des définitions système, parmi eux on peut citer le dictionary cache d'Oracle (contenu dans sa zone de mémoire partagée), le cache de tables MySQL, et le cache dictionnaire de données du moteur de stockage MySQL InnoDB.
Les caches de code sont principalement utilisés pour limiter l'activité de hard parsing. On emploie ce terme lorsque, une fois que les commandes SQL (ou de code stocké) ont été lancées et vérifiées en termes de syntaxe et de sécurité, un processus parfois coûteux de génération de plan d'exécution est mis en oeuvre. L'objectif des caches de code est de réutiliser les instructions SQL ou de code procédural déjà exécutées en vue d'effectuer seulement un soft parsing (analyse sans génération de plan d'exécution, ni diverses autres étapes, ni chargement dans le cache). Les économies CPU découlant de tels caches peuvent bien entendu être très conséquentes.
Les exemples vont du library cache de la zone mémoire partagée d'Oracle aux caches SQL/Procedure de SQL Server et Sybase.
Les caches de données sont les caches mémoire SGBD les plus facilement compréhensibles, ils renferment (ô surprise !) les données applicatives. Les blocs de données sont lus sur le disque et chargés en mémoire par un procédé utilisant des chaînes LRU (least recently used) ou d'autres mécanismes avancés (comme l'algorithme Touch Count d'Oracle et la midpoint insertion strategy utilisée pour les blocs d'index MyISAM) afin que les blocs très demandés restent toujours en jeu.
Certains éditeurs de bases de données permettent au DBA de se tailler ses propres zones dans le cache de données pour des usages spécifiques. Par exemple, en plus d'un data cache global, Oracle fournit une zone de mémoire tampon KEEP qui maintient les données d'une table en RAM, et une zone RECYCLE qui empêche le cache principal d'être submergé de blocs de données peu demandés provenant de full table scans et pouvant potentiellement évincer des blocs nécessaires et fréquemment demandés. Sybase offre au DBA la possiblité de définir de multiples caches de données puis de leur assigner spécifiquement certains objets. SQL Server fournit une fonction de PIN (DBCC PINTABLE) qui fixe une table dans le data cache global dès lors qu'on y a accédé une première fois.
MySQL étant plus que capable de jouer sur le même terrain que les principaux autres éditeurs de SGBD, il propose différents caches pour les données gérées par ses moteurs de stockage. Le cache de clés MyISAM conserve les blocs d'index en RAM en vue d'un accès rapide, avec à la fois des caches par défaut et sur-mesure. Parce qu'il reflète Oracle de manière très proche, le système de stockage MySQL InnoDB offre un global buffer cache qui contient à la fois les blocs de données et d'index et supporte les AWE (Advanced Windowing Extensions) pour les installations Windows. MySQL comporte aussi un moteur de stockage Memory/Heap, qui comme son nom l'indique, garde toujours le contenu d'une table (et les index) dans la RAM.
L'accès aux données en mémoire est-il la panacée en termes de performance ?
Certains administrateurs de bases de données et spécialistes de l'analyse de performances estiment qu'un gros cache de données est le remède miracle à tous les soucis de performances. Après tout, puisque l'accès mémoire est beaucoup plus rapide qu'un accès au disque, la clé pour obtenir des performances excellentes doit être de stocker autant de données que possible en mémoire. Ceci fait, peu importe quel code SQL on enverra au système.
Afin d'analyser si un cache de données a été correctement configuré, un certain nombre de ratios ont été créés pour mesurer le taux d'entrées/sorties logiques (mémoire) par rapport aux physiques (disque). MySQL dispose du key cache hit ratio et du InnoDB buffer pool ratio. Oracle a un buffer cache hit ratio et Sybase et SQL Server utilisent le data cache hit rate. Les DBA se sont basés sur ces ratios pour déterminer si leurs caches de données étaient assez larges, avec comme règle communément admise qu'il fallait un taux de lectures de 90% ou plus pour qu'un cache soit qualifié « d'efficace ». Lorsque la proportion de sollicitation du cache de données s'approche de cette valeur, beaucoup d'administrateurs se félicitent d'avoir un système par essence hautement performant.
Vous serez peut-être surpris d'entendre cela, mais rien ne pourrait être plus éloigné de la réalité. Bien que des caches de données correctement configurés contribuent certainement à avoir un SGBD hautement performant, se baser sur des hauts taux d'accès aux caches comme principal indicateur de l'efficacité d'une base est une mauvaise habitude. Pourquoi ? Considérez l'illustration suivante, tirée d'un excellent article de l'expert Oracle Cary Millsap intitulé « Pourquoi un taux de hit du cache de données de 99%+ n'est pas satisfaisant » (1) :
Deux instructions SQL distinctes A et B retournent des jeux de résultats identiques, mais elles ont des plans d'exécution différents. Quelle requête préfèreriez-vous avoir sur votre système ?
Requête
Taux de hit cache
A
99,99%
B
90,00%
La réponse conventionnelle serait qu'on préfère la requête A parce qu'elle produit le plus haut (et sans doute le meilleur) taux de sollicitation du cache de données. Regardons un niveau plus bas dans le détail de la charge système générée par les requêtes. Etant donné les éléments d'information supplémentaires suivants, quelle instruction préfèreriez-vous à présent avoir sur votre système ?
Requête SQL
Taux de hit cache
E/S logiques
E/S physiques
Temps d'exécution
A
99,99%
10 000
1
0,405 sec
B
90,00%
10
1
0,05 sec
Comme Cary le fait remarquer, les entrées/sorties logiques comptent, et parfois elles comptent beaucoup. Vous ne pouvez pas vous contenter de parachuter de la mémoire à une base de données submergée de code SQL peu performant et espérer que ça se passe bien. La reconstruction cohérente de jeux de résultats complexes à partir des caches de données, combinée à une forte activité de latch (verrouillage mémoire) peut engendrer une grosse occupation CPU à même de paralyser un système en très peu de temps.
Ce qu'apporte le cache de requêtes de MySQL
Tous les DBA devraient avoir pour objectif de créer et configurer leurs bases, de stocker leurs données et tirer parti des caractéristiques du SGBD de façon à ce que les requêtes les plus courantes des utilisateurs finaux leur soient retournées le plus vite possible.
Comme nous l'avons vu, vous ne pouvez pas vous contenter d'assigner aveuglément de la mémoire à une base dans l'espoir d'arranger les choses, car même si les données brutes sont dans la RAM, du SQL mal écrit peut monopoliser le CPU d'un serveur pour recréer des resultsets complexes à travers des E/S logiques.
Bien qu'il ne soit certainement pas un remède à du code SQL mal écrit, le cache de requêtes de MySQL, introduit dans la version 4.0.1, peut avoir un impact incroyablement positif sur le temps de réponse des requêtes SQL, qu'elles soient basiques ou gourmandes en ressources. Comme vous pouviez vous y attendre, le cache de requêtes conserve les requêtes SELECT envoyées par les clients au serveur de bases de données MySQL. A cet égard, il agit de manière très similaire aux caches SQL traditionnels des SGBD, le moteur de base de données détectant et réexécutant les requêtes déjà demandées sans recours au hard parsing.
Cependant, à la différence de tous les autres SGBD, MySQL stocke non seulement l'instruction SQL envoyée par le client, mais aussi le jeu de résultats correspondant à la requête. Ainsi, le cache de requêtes de MySQL apporte un gain substantiel par rapport aux stratégies de cache des autres moteurs car non content d'éviter les frais préalables de hard parsing pour les requêtes identiques, ceux parfois coûteux de recréation de resultsets complexes à partir des caches disque ou mémoire sont également éludés. Ceci réduit grandement le nombre d'E/S physiques et logiques. Le résultat qui en découle est des temps de réponse extrêmement rapides pour les applications métier (entrepôts de données, décisionnel, applications Web, systèmes transactionnels OLTP, etc.) où les clients finaux exécutent les mêmes requêtes SQL de manière répétée.
Donc, dans quelles situations tirera-t-on le mieux parti des bénéfices du cache de requêtes de MySQL ?
Même si le query cache peut être efficace dans presque tous les cas de figure, les conditions métier idéales sont les suivantes :
Des requêtes identiques sont envoyées par un ou plusieurs clients sur une base régulière.
Les données sous-jacentes auxquelles on accède sont de nature statique ou semi-statique.
Les requêtes sont potentiellement gourmandes en ressources et/ou rapides à construire mais avec des jeux de résultats complexes à calculer.
Les cas des datawarehouses, des systèmes d'aide à la décision, des applications orientées Web et des systèmes OLTP traditionnels y satisfont tous.
Le cache de requêtes cherche les requêtes identiques (espacement, majuscules/minuscules, tout compte), d'où le premier point ci-dessus.
En ce qui concerne la nature semi-statique des données : parce que le jeu de résultats est stocké à côté de la requête associée, un quelconque changement dans les données sous-jacentes concernant un ou plusieurs objets impliqués dans la requête oblige MySQL à invalider celle-ci dans le cache. Elle sera rechargée dedans avec le jeu de résultats recalculé la prochaine fois qu'elle sera exécutée. Enfin, comme certaines requêtes peuvent retourner un volume important de données/lignes, MySQL fournit une limite de stockage mémoire paramétrable qui conditionne la taille maximum du resultset de chaque requête. C'est pourquoi le cache de requêtes marche mieux pour les requêtes qui ne renvoient pas de grandes quantités de données (bien que vous puissiez assurément le permettre si vous le souhaitez).
Voyons maintenant le cache de requêtes de MySQL en action pour nous rendre compte de la manière dont il est configuré et utilisé en pratique.
Le cache de requêtes MySQL au banc d'essai
Avant de commencer, une petite remarque : tous les tests qui suivent ont été réalisés sur une machine Dell Pentium IV 3 GHz mono-processeur (avec hyperthreading activé) dotée d' 1 Go de RAM, sous Linux Red Hat Fedora Core 4. La version de MySQL utilisée est la 5.0.7 béta.
Dans certaines installations de MySQL le cache de requêtes est désactivé par défaut, vous devrez donc effectuer quelques paramétrages pour le mettre sur pied. Afin de vérifier ce point, connectez-vous à MySQL et entrez la commande suivante :
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
Bien que la variable query_cache_type soit sur ON, query_cache_size est à zéro, ce qui se traduit dans les faits par un cache de requêtes désactivé. Mettre query_cache_type sur ON (mise en cache de toutes les requêtes SELECT sauf celles avec le mot-clé SQL_NO_CACHE) ou DEMAND (mise en cache des requêtes avec le mot-clé SQL_CACHE uniquement) et affecter à la variable query_cache_size une valeur supérieure à zéro a pour effet d'activer le cache de requêtes.
Pour le moment, cependant, le cache n'est pas actif. Exécutons deux fois de suite une requête particulière qui calcule quels sont les 5 premiers courtiers d'une société de placement, et voyons ce qui se passe :
Chaque jeu de résultats est retourné assez rapidement en 0,11 secondes, ce qui n'est pas trop mal. Toutefois, activons maintenant le cache de requêtes en le fixant à 50 Mo (MySQL permet le paramétrage dynamique de beaucoup de variables sans arrêt/redémarrage du serveur) et regardons ce qu'il advient lorsque nous exécutons la même requête deux fois encore :
La première requête met encore une fois 0,11 seconde à s'exécuter, mais la deuxième ne prend littéralement pas de temps du tout car elle est servie par le cache de requêtes. Une vérification des variables d'état associées au query cache le confirme, le compteur Qcache_inserts affiche une valeur de 1 (ce qui indique que la première requête a été mise en cache) et le compteur Qcache_hits affiche également 1 (ce qui montre 1 correspondance dans les combinaisons requêtes envoyées / requêtes déjà en cache).
Le cache de requêtes fonctionne avec des requêtes SQL simples, comme on vient de le voir, mais aussi pour des requêtes exécutées à l'intérieur de procédures stockées MySQL :
Nous avons d'abord créé une procédure stockée qui envoie la même requête sur les commissions des courtiers que nous avions utilisée à l'origine, vidé les compteurs d'état pour remettre les statistiques du cache de requêtes à zéro, effacé le query cache pour enlever toutes les requêtes s'y trouvant. Puis on a appelé deux fois la procédure stockée afin de vérifier que MySQL met bien en cache la requête exécutée depuis l'intérieur de la procédure.
A noter que ça fonctionne aussi avec les requêtes appelées dans des vues (nouveauté de la version 5.0).
Administration du query cache
Vous aurez parfois besoin d'effectuer des réglages sur le cache de requêtes MySQL pour garantir des performances optimales, passons donc en revue quelques-uns des compteurs d'état et des variables de configuration liés à celui-ci. Pour commencer, le compteur Qcache_free_blocks indique la nature contigüe de la mémoire assignée au cache. Un chiffre élevé peut être synonyme de problèmes de fragmentation, qu'on peut résoudre en exécutant l'instruction FLUSH QUERY CACHE. Notez bien que cette commande ne retire pas les requêtes du cache, mais fusionne les fragments d'espace mémoire libres.
Le compteur Qcache_free_memory permet d'avoir une vision détaillée de la mémoire libre du cache. De faibles valeurs par rapport au total de mémoire alloué peuvent être le signe d'un cache trop petit, ce à quoi on peut remédier en modifiant la variable globale query_cache_size.
Qcache_hits et Qcache_inserts montrent le nombre de fois qu'une requête a été retrouvée dans le cache et combien de requêtes y ont été insérées. Un faible rapport hits/inserts indique une réutilisation des requêtes limitée, ou un paramétrage trop bas du query_cache_limit qui spécifie la quantité de RAM individuellement dédiée à chaque entrée du cache de requêtes. De gros jeux de résultats demanderont un paramétrage plus large de cette variable.
Un autre indicateur de la faible réutilisation des requêtes est une valeur de Qcache_lowmem_prunes en augmentation. Elle désigne le nombre de fois où MySQL a dû retirer des requêtes du cache pour faire place à celles qui arrivent. D'autres raisons pour lesquelles le Qcache_lowmem_prunes pourrait augmenter sont un cache trop petit qui ne pourrait pas contenir la quantité d'instructions SQL et de resultsets souhaitée, et une fragmentation de la mémoire du cache qui peut être atténuée en faisant un FLUSH QUERY CACHE. Vous pouvez effacer toutes les requêtes du cache à l'aide de la commande RESET QUERY CACHE.
Le compteur Qcache_not_cached fournit une indication sur le nombre d'instructions exécutées par MySQL qui n'étaient pas cachables, soient parce que ce n'étaient pas des SELECT soit parce qu'on leur avait barré l'entrée avec un mot-clé SQL_NO_CACHE.
Les autres variables serveur sur lesquelles vous pouvez jouer pour régler le query cache sont :
query_alloc_block_size - la taille des blocs mémoire alloués pour les objets placés en cache.
query_cache_min_res_unit - la taille minimum des blocs alloués par le cache.
query_cache_wlock_invalidate - oblige le query cache à invalider toute requête du cache si on vient à poser un verrou en écriture sur un objet qu'elle utilise.
query_prealloc_size - la taille du buffer persistant utilisée par le cache pour le parsage et l'exécution. Les requêtes complexes nécessitent un paramétrage plus large.
Pour finir, en plus du paramétrage des variables qui gouvernent le fonctionnement global du cache de requêtes, notez que ce dernier peut être administré individuellement au niveau client.
Par exemple, un client peut couper le query cache pour ses propres requêtes en exécutant la commande :
mysql> set session query_cache_type=0;
Prérequis et notes sur l'utilisation du cache de requêtes MySQL
Bien entendu, il y a des conditions et des limites à l'utilisation du cache de requêtes de MySQL, les plus importantes étant :
Seules les requêtes identiques peuvent être servies par le cache. Sont pris en compte les espacements, la casse, etc.
Toute modification (opération DML ou autres) d'une table utilisée par une requête en cache provoque l'invalidation et le retrait du cache de cette dernière. Le moteur InnoDB n'évince la requête qu'au moment du COMMIT.
De nombreuses fonctions comme CURRENT_DATE, NOW, RAND et d'autres empêchent l'utilisation du cache.
Aucune requête utilisant des variables bind (prepared statements) ne peut être réutilisée.
Une requête utilisant des user defined functions ne peut être mise en cache.
Pour une liste complète et à jour des limitations du cache de requêtes, référez-vous au manuel MySQL.
Conclusion
Le cache de requêtes de MySQL met en oeuvre une stratégie de mise en cache unique qui n'est à l'heure actuelle utilisée par aucun autre moteur de bases de données, et qui peut grandement améliorer les performances de presque tous les systèmes connaissant une forte activité en termes de lectures. Parce que tout travail d'E/S à la fois logique et physique est pratiquement éliminé par le cache de requêtes, même les systèmes qui subissent le poids d'instructions SQL peu efficaces accomplissent leur tâche souvent plus rapidement que sous d'autres plateformes de bases de données.
Cet article a été publié dans sa version originale sur la page Tech Resources du site de MySQL (http://dev.mysql.com).
Merci à Robin Schumacher pour m'avoir autorisé à adapter son article, et à Olivier Lance pour la relecture de la version traduite.
(1)Why a 99%+ Database Buffer Cache Hit Ratio is Not OK, Cary Millsap/Hotsos Enterprises, Ltd., 2001.
