A quoi sert SQL_NO_CACHE ?

29 mars 2011 par stephane

Lorsqu’on essaie d’améliorer une requête, que ce soit en modifiant le plan d’exécution ou en réécrivant la requête, on finit par choisir la variante dont le temps d’exécution est le plus faible. Encore faut-il que ce temps d’exécution ne soit pas falsifié par un quelconque cache. En cherchant comment désactiver les caches de MySQL, vous avez certainement trouvé la directive SQL_NO_CACHE. Cet article va faire le point sur ce que fait cette directive, mais également sur ce qu’elle ne fait pas.

Si vous avez déjà eu besoin de mesurer le temps que prend une requête sur un serveur inactif, vous avez sans doute déjà rencontré ce cas de figure :

1ère exécution :

mysql> SELECT COUNT(*) AS total,YEAR(birth_date) AS birth_year
FROM employees INNER JOIN salaries USING(emp_no)
WHERE first_name LIKE '%m%' AND salary > 50000 AND to_date < '2010-12-31'
GROUP BY birth_year;
[...]
14 rows in set (11.70 sec)

Exécutions suivantes :

mysql> SELECT COUNT(*) AS total,YEAR(birth_date) AS birth_year
FROM employees INNER JOIN salaries USING(emp_no)
WHERE first_name LIKE '%m%' AND salary > 50000 AND to_date < '2010-12-31'
GROUP BY birth_year;
[...]
14 rows in set (0.00 sec)

Une telle différence ne peut s’expliquer que par la présence d’un cache : à la première exécution, le cache étant vide, la requête a été exécutée et mise en cache, alors que pour les exécutions suivantes, le cache était rempli et la requête a pu être servie directement. Une question demeure : de quel cache s’agit-il ?

Le 1er cache auquel on peut penser est le cache de requêtes (plus connu sous sa dénomination anglo-saxonne de query_cache). C’est le cache qui est visé par la directive SQL_NO_CACHE :

mysql> SELECT SQL_NO_CACHE COUNT(*) AS total,YEAR(birth_date) AS birth_year
FROM employees INNER JOIN salaries USING(emp_no)
WHERE first_name LIKE '%m%' AND salary > 50000 AND to_date < '2010-12-31'
GROUP BY birth_year;
[...]
14 rows in set (2.95 sec)


mysql> SELECT SQL_NO_CACHE COUNT(*) AS total,YEAR(birth_date) AS birth_year
FROM employees INNER JOIN salaries USING(emp_no)
WHERE first_name LIKE '%m%' AND salary > 50000 AND to_date < '2010-12-31'
GROUP BY birth_year;
[...]
14 rows in set (2.89 sec)


mysql> SELECT SQL_NO_CACHE COUNT(*) AS total,YEAR(birth_date) AS birth_year
FROM employees INNER JOIN salaries USING(emp_no)
WHERE first_name LIKE '%m%' AND salary > 50000 AND to_date < '2010-12-31'
GROUP BY birth_year;
[...]
14 rows in set (2.96 sec)

(Sujet annexe : je ne m’attache pas ici à la configuration du cache de requêtes, voici un excellent tutoriel pour ceux qui se posent la question)

SQL_NO_CACHE désactive-t-elle tous les caches ? Eh bien non, et il est facile de s’en convaincre : la toute 1ère exécution de la requête prenait près de 12s, la 1ère avec le SQL_NO_CACHE moins de 3s. Quels sont donc ces autres caches ?

Le seul cache géré par le serveur MySQL étant le cache de requêtes, il faut descendre au niveau des moteurs de stockage pour mieux comprendre ce qui se passe. Pour MyISAM, il existe un cache pour les index dont la taille est paramétrable via la variable key_buffer_size et un cache pour les données, qui est tout simplement le cache du système de fichiers (donc pour lequel il n’existe aucun moyen d’agir au niveau de MySQL). Pour InnoDB, un seul cache existe pour les données et les index; sa taille est donnée par la variable innodb_buffer_pool_size.

Il est très important de noter que SQL_NO_CACHE n’a aucune influence sur ces 3 caches. C’est pour cette raison qu’il faut faire très attention quand on fait des tests en mesurant des temps d’exécution sous peine d’obtenir des résultats fantaisistes, par exemple quand on cherche à déterminer si le passage en une version supérieure pourrait permettre d’améliorer les performances. Les effets des caches peuvent d’ailleurs biaiser les résultats dans les 2 sens, soit en les améliorant artificiellement, soit en les dégradant artificiellement. Un petit exemple ?

Imaginez une base dont toutes les tables sont en InnoDB. La base a une taille de 100 Go, le cache InnoDB (buffer pool) vaut 10Go. Pour avoir une idée du temps que va prendre une requête en production, vous remontez un environnement identique et après avoir démarré MySQL, vous exécutez votre requête plusieurs fois pour que le cache puisse se remplir et avoir ainsi un temps de réponse fiable. Vous obtenez les résultats suivants :

1er test : 4.5s
2nd test : 0.7s
3è test : 0.7s
4è test : 0.7s

Pourrez-vous dire qu’en production, votre cache étant bien évidemment chaud, la requête devrait s’exécuter en 0.7s ? Eh non ! Car le cache étant nettement plus petit que la taille de la base, il est tout à fait possible qu’au moment où vous exécutez votre requête en production, le cache ne contienne aucune donnée intéressante. Tout ce que vous pouvez dire, c’est que la requête va probablement mettre entre 0.7 et 4.5s pour s’exécuter en production.

Cette toute petite incursion dans le monde des caches montre qu’il n’est pas évident du tout de faire des tests de performances significatifs. Vous saurez maintenant vous méfier des benchmarks qui donnent des chiffres sans expliquer le protocole utilisé pour produire des résultats fiables…

Mots-clefs : , ,

6 commentaires sur “A quoi sert SQL_NO_CACHE ?”

  1. Jean Moniatte dit :

    Merci beaucoup pour ces informations, c’est tres utile et tres tres bien explique.

    Est ce qu’il y a un moyen simple de tester l’efficacite d’un nouvel index sur une table? Si on evite le cache des requetes avec SQL_NO_CACHE, comment peut on savoir si un index est caché ou pas? Est ce que MySQL le met dans le cache apres sa premiere utilisation?

  2. stephane dit :

    Pour estimer l’intérêt d’un nouvel index, il faut connaître les requêtes exécutées sur la table et vérifier avec EXPLAIN que cet index est utilisé.

    Avec un MySQL standard, tu ne pourras pas savoir si un index est en cache ou pas. Si tu utilises Percona Server, tu pourras le savoir

    En très très résumé, dès que MySQL lit sur disque un bloc d’index, ce bloc est mis en cache. Il pourra être sorti dès que le serveur a besoin de récupérer de la place pour mettre un autre bloc. A savoir aussi que le comportement n’est pas le même pour MyISAM et InnoDB.

  3. Cédric dit :

    Hello, cet article m’a donné une idée et j’ai donc publié un post en relation avec celui-ci : http://www.mysqlplus.fr/2011/04/dbnewz-examinez-le-profile-de-vos-requetes/

    Ne pas hésiter à rebondir…

  4. Jordan dit :

    Bonjour,

    Je rencontre un petit problème avec mon query_cache. Alors ça fait plusieurs jours que j’essaye de régler le problème mais sans succès. Je me permet de vous en faire part:

    Résultat obtenu avec MySQLTuner:
    [!!] Query cache efficiency: 4.4%

    J’ai beau l’augmenter le diminué sa revient au même. Voici la configuration du cache de mon my.cnf:

    query_cache_type = 1
    query_cache_size = 2G
    query_cache_limit = 3M
    query_cache_min_res_unit = 6K

    Et le résultat de la commande SHOW STATUS LIKE ‘Qcache%’; :

    http://img51.imageshack.us/img51/3412/qcache.png

    Que me conseillerez-vous?

    En espérant que cette demande n’est pas trop osé.

  5. stephane dit :

    Bonjour Jordan,

    Quelques réflexions :
    - D’une manière générale, un ratio ne donne pas de bonnes informations sur la qualité d’une configuration. Exemple ici : si l’application fait 80% d’écritures, le query cache sera invalidé tout le temps et il n’y aura aucune chance d’avoir un ‘Query cache efficiency’ élevé. Impossible donc de savoir si 4.4% est une bonne valeur ou pas.
    - Le query cache a un problème de scalabilité majeur : tous les accès sont sérialisés par une mutex. Ce qui signifie que sur un serveur à plusieurs coeurs (ce qui est certainement votre cas), il est courant que les performances soient meilleures sans le query cache :)
    - Si jamais, le query cache s’avère malgré tout utile, la taille que vous lui avez donné est nettement surestimé : si je calcule query_cache_size – Qcache_free_blocks, ce qui donne la quantité de mémoire utilisée, on obtient 4Mo !

    Tenez-nous au courant de la suite !

  6. Jordan dit :

    Bonjour Stéphane,

    Je vous remercie encore une fois de vos réponses très pertinentes encore une fois vous donnez d’excellent conseil. Je vais par la suite écouter votre conseil en désactivant le query_cache et comme vous vous en doutez je possède un serveur de 8 coeurs.

    Cordialement, Jordan

Laisser une réponse