MySQL Query cache
En tant que boulimique de MySQL, je me promène souvent sur la toile à la recherche d’informations, de bonnes et de moins bonnes…
Je suis tombé sur un article traitant du cache de requêtes de MySQL (MySQL Query Cache) sur le blogue de Patrick Lafontaine (http://www.noidea.ca/)
Je me permet de faire quelques commentaires ici.
En préambule, quelques informations nécessaires sur le cache de requêtes:
Système de cache interne à MySQL qui ne stocke que les requêtes SELECT et leur résultat ie pas d’INSERT, UPDATE, DELETE…
Les requêtes ( SELECT donc) doivent être strictement identiques ie même casse, mêmes espaces entre les mots,…
Ex 3 requêtes différentes pour le cache :
SELECT nom, prenom FROM client WHERE client_id=123; select nom, prenom FROM client WHERE client_id=123; /* la casse du select*/ SELECT nom, prenom FROM client WHERE client_id=123; /*plusieurs espaces entre client et WHERE*/
Le cache est toujours à jour car en cas de modification d’une table, toutes les requêtes en relations avec cette table sont invalidées.
Le cache de requêtes est en général utile lorsque:
- Les modifications sur les tables ne sont pas très fréquentes
- Beaucoup de requêtes de lectures identiques
- Utilisation de tables MyISAM. Moins intéressant pour InnoDB
Pour rebondir sur l’article de Patrick Lafontaine
« puisqu’elle est activée par défaut. »
Le cache de requêtes n’est pas activé par défaut, car la variable query_cache_size vaut 0. Si vous voulez vous en servir, il faut lui donner une taille en octet. Mettez le tout dans votre fichier de configuration. Assurez vous également que la variable query_cache_type est différente de OFF
« c’est-à-dire que la ou les applications qui s’en servent n’ont pas besoin d’être modifiées »
Pour une optimisation optimale, il est parfois nécessaire de modifier les requêtes SELECT avec SQL_CACHE ou SQL_NO_CACHE. On choisit alors quelles requêtes seront mis en cache.
« Si quelqu’un modifie la valeur directement dans MySQL, la cache possèdera la vieille valeur jusqu’à ce qu’un processus l’invalide. »
Sur une machine de production, ce type de manipulation reste quand même exceptionnel, sinon c’est qu’il y a des choses à revoir dans les process.
« Puisque les données ne changent pratiquement jamais, je ne me casserais pas la tête à réinventer la roue. MySQL fait déjà pour vous ce que APC ferait, sans le moindre effort. »
Si le contenu ne change JAMAIS, il n’a à priori rien à faire en base ! Il vaut mieux utiliser un menu statique et laisser la base faire son boulot avec du contenu dynamique. Dans le même ordre d’idée, plus le cache est éloigné du disque plus il est performant. En d’autres termes, le goulet d’étranglement est souvent (parfois) la base de données, de plus elle est souvent (parfois) plus difficilement scalable que le reste. L’utilisation d’un cache applicatif est rarement une mauvaise idée (il suffit de connaître l’identité du plus gros consommateur de memcached au monde http://www.facebook.com/note.php?note_id=39391378919)
« Il est donc plus avantageux de cacher les processus lourds que les légers. »
Malheureusement, avec le cache de requêtes ce n’est pas aussi simple. Admettons qu’une requête renvoyant un gros résultat prenne plus de temps qu’une renvoyant un plus petit. Si cette grosse requête vire toutes les autres requêtes du cache, l’apport du cache pour les autres requêtes est perdu, elle devront être misent à nouveau dans le cache ca qui implique des recherches inutiles dans le caches et de nouveaux accès disque…
« Lorsque la Query Cache de MySQL est activée, le processus de cacher les résultats et de les invalider s’effectue tout seul de manière invisible. Ainsi, d’autres requêtes que vous ne soupçonnez même pas bénéficient de la cache »
L’efficacité du cache de requêtes est vraiment lié à l’application. Il dépend du type de requêtes SELECT, de leur fréquence et de la fréquence des écritures dans les tables. Le gain n’est pas évident et est loin d’être immédiat, car pour chaque requête « cachable » MySQL devra l’analyser, devra la hacher, devra vérifier s’il elle est dans le cache ou non. Et ceci à un coût…
Vous pouvez calculer le taux d’efficacité du cache de requêtes avec la formule suivant:
Qcache_hits / (Qcache_hits + Com_select )
Pour finir, quelques paramètres et commandes
Paramètres principaux:
query_cache_size: Doit être différent de zéro pour activer le cache
query_cache_type:
-
ON: les requêtes select sont misent en cache
-
Sauf (SQL_NO_CACHE, result set trop grand, fonction non déterministe..)
-
-
DEMAND: SELECT SQL_CACHE…
-
OFF
Commandes principales:
FLUSH QUERY CACHE
-
Défragmente le cache de requêtes
-
Ne vide pas le cache
Vider le cache de requêtes:
-
RESET QUERY CACHE
-
FLUSH TABLES
-
Redémarrer le serveur
Variables d’état: SHOW STATUS LIKE ‘Qcache%’ ;
Qcache_free_blocks : nombre de blocs libres
Qcache_free_memory : mémoire libre
Qcache_hits : nombre de fois qu’il a servi
Qcache_inserts : nombre de requêtes insérées
Qcache_lowmem_prunes : nombre de requêtes supprimées car plus de place
Qcache_not_cached : nombre de requêtes non « cachables »
Qcache_queries_in_cache : nombre de requêtes dans le cache
Qcache_total_blocks : nombre de blocs de mémoire
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
Merci pour l’article !
Ah parfait, tu viens de donner la réponse que j’ai posé sur le blog de http://www.noidea.ca/2009/10/10/mysql-query-cache/
Bonne continuation
Bonjour Olivier
Premièrement merci de spécifier que la grosseur de la cache est 0 par défaut. Il est vrai que la cache est activée par défaut, mais avec une grosseur de 0 ce qui la rend inutilisable. Je vais corriger l’article.
Je me permets par contre de préciser quelques points. Il est vrai qu’il n’est pas commun de modifier des valeurs directement dans la DB, mais ce sont des choses qui arrivent, même dans un environnement de production. Il faut aussi se remettre en contexte: il s’agit d’implémenter une cache sur un simple Site Web – pas sur une application. Si le menu du site se trouve dans une table, il a fort à parier qu’il devra être modifié directement dans la base de données pour être mis à jour. Je ne juge pas ici la pertinence de mettre ce type de données dans une base.
Je comprends que la cache de requête n’est pas une solution magique. Oui, le type de requête influence son comportement. Comme tu as expliqué, on peut facilement invalider une grosse partie de la cache et l’exercice de remettre en cache à bien sur un coût. L’invalidation elle-même a un coût non négligeable. Mais je reviens encore au contexte. Pour un Site Web possédant un CMS, la majorité des requêtes se font en lecture. C’est un cas typique où la cache de MySQL brille. Le gain est quasi assuré.
Il n’était pas question dans mon article d’optimisation très haut niveau, ce que tu sembles reprocher à ma réponse. Bien sur, je vais préférer memcached à la query cache de MySQL pour plusieurs raisons afin d’avoir une performance accrue. C’était un article qui se voulait une introduction aux deux différents types de cache.
[…] Olivier Dasini : MySQL Query cache () […]
[…] Olivier Dasini : MySQL Query cache (0 visite) […]
Excellent tout ça 😉
Un nouveau blog dans les flux 😉
Salut Olivier,
Un point important et que le cache APC ou Xcache se situe sur un autre niveau dans l’archi. Il est redondant sur tous les serveurs WEB et protege ainsi la BDD de chaque internaute , Il presente a la BDD un nombre de connections = au nombre de serveurs WEB. Tout comme celui de MySQL sa taille doit reste petite donc pour les grosses volumetries restant longtemps en cache il faut s’orriente vers un cache distribue comme memcached … une bonne architecture possede tout ces niveaux avec en plus un cache du statique sur CDN et navigateur :), Deplus APC , memcache ne souffre pas de la concurrence car il n’ont pas l’obligation d’etre exacte, car il peuvent toujours sortir la version anterieur pendant que la nouvelle version est ecrite , donc pas de MUTEX. Un autre atout et que le cache PHP local fait economiser le cout du reseau et la copie memoire inter process sous reserve de ne pas utiliser un wrapper lib client , enfin le cout de l’authentification MySQL est aussi supprimer , soit 3ms et plus si pas de skip-name-resolv, mais ca c’est pour un autre de tes billets 🙂
[…] This post was Twitted by HerveThouzard […]
merci pour l’article…juste une petite remarque, la variable Qcache_lowmem_prunes signifie plutôt de taux de fragmentation du cache. Il doit tendre vers 0 pour être efficace. S’il augmente, le cache doit être défragmenté.
@Pat: bien noté ! merci
@Greg, Macsim: Merci 🙂
@Stéphane: Merci pour ces précisions
@Benoit: Qcache_lowmem_prunes indique que MySQL est obligé de faire de la place dans le cache (en supprimant des requêtes) pour mettre en cache de nouvelles requêtes. Cela peut effectivement venir d’une fragmentation du cache mais pas obligatoirement. Il doit bien sur être bas.
Petite question, quel serait votre rétissance à activer le qwery_cache sur une application web php/MySQL 5.0.67 qui gère de facture, des inventaire ect..
Bonjour Christian,
t’as question est bien vague 🙂
Comme dit dans le billet, c’est plus sur des critères de types de requêtes, de type de moteurs , de RAM que sur des critères de fonctionnalités que les choix se font.
Maintenant le seul moyen vraiment efficace et de faire des tests en conditions proches de la prod.
Merci de ta réponse, c’est vrai que c’était un petite histoire courte! 🙂
[…] http://dasini.net/blog/2009/10/12/mysql-query-cache/ (lu rapidement, mais le cache de requête est visiblement fait par défaut sur une conf mysql récente) […]
[…] solliciter le serveur mysql, ce qui se traduira par une plus grande rapidité du serveur mysql (voir http://dasini.net/blog/2009/10/12/mysql-query-cache/). Ne pas oublier de flusher régulièrement le […]
[…] Activer le Query cache de Mysql permet de mettre en cache les requêtes les plus récurrentes et de moins solliciter le serveur mysql, ce qui se traduira par une plus grande rapidité du serveur mysql (voir http://dasini.net/blog/2009/10/12/mysql-query-cache/). […]
Je vais tester la configuration sur mon site.
merci beaucoup