Attention au query cache

février 28, 2012

Selon le livre «Audit & optimisation, MySQL 5 – éditions Eyrolles», le cache de requêtes ou query cache est un système de cache mémoire interne à MySQL, transparent pour l’application, qui ne stocke que les requêtes SELECT et leurs résultats.

L’apport de ce cache est particulièrement dépendant de votre application. Il est coutume de dire qu’il est (très) pénalisant dans des environnements où les requêtes d’écritures sont nombreuses, notamment à cause de son mécanisme d’invalidation (et de problèmes de contentions de façon générale).

A l’opposé, il peut être intéressant de l’activer, dans des environnements à forte charges de lectures, si les mêmes requêtes reviennent très fréquemment, plus particulièrement lors de l’utilisation de tables MyISAM.

 

Cependant, un environnement à forte charge en lecture n’est pas une condition suffisante pour s’assurer de bonne performances avec le query cache, c’est ce que nous allons voir dans cet article.

 

Le contexte

L’idée principale de cet article, n’est pas de voir ce que MySQL « à dans le ventre », mais de connaître le coût du cache de requêtes, avec un hit ratio faible, dans une configuration «relativement proche» de ce que nous avons en production chez Viadeo.

J’ai utilisé sysbench pour générer 100 connexions simultanées, qui exécutent des SELECT sur une table InnoDB de 10 millions d’enregistrements :

sysbench –num-threads=100 –max-time=900 –max-requests=20000000 –test=oltp –oltp-table-size=20000000 –mysql-table-engine=innodb –oltp-test-mode=complex –oltp-read-only run

 

Les détails du bench sont dans le paragraphe divers, au bas de l’article.

 

Résumé

Avec un query cache hit très bas, même dans un contexte 100% SELECT, l’impact du cache de requêtes est désastreux sur les performances, que ce soit en TPS (graph 1) ou en 95ème centile (graph 2).

 

dasini.net - MySQL query cache

 

Les chiffres

— Query cache à 128 Mo

Chiffres clés :

  • QC size : 128 Mo
  • TPS : 216,45
  • 95%tile : 666,36 ms

 

 

— Query cache à 256 Mo

Chiffres clés :

  • QC size : 256 Mo
  • TPS : 444,47
  • 95%tile : 320,52 ms

 

 

— Query cache à 64 Mo

Chiffres clés :

  • QC size : 64 Mo
  • TPS : 580,84
  • 95%tile : 214,74 ms

 

 

— Query cache OFF à 0 Mo

 

Chiffres clés :

  • QC size : 0 Mo
  • TPS : 4801,84
  • 95%tile : 71,97 ms

 

 

Conclusion

 

Les requêtes créées par sysbench lors de ce test, génèrent un query cache hit ratio particulièrement faible < 10%

Ce dernier se calcul de la façon suivante : Qcache_hits / (Qcache_hits +Com_select)

 

Avec un QC hit ratio si bas, activer le QC à un impact désastreux sur les performances, même en lecture seule, est ce quelque soit sa taille (64 / 128 / 256 Mo ).

Lui mettre une taille beaucoup plus grande n’est pas une bonne idée (cf : « Audit & optimisation, MySQL 5 – éditions Eyrolles»).

 

Il est cependant possible de le paramétrer pour minimiser les pertes de performances (si il doit impérativement être activé). Dans le contexte de ce bench, la diminution du query_cache_min_res_unit, 512 au lieu de 4096 (car les résultat renvoyés sont très petit) associé à un query_cache_size de 256Mo à permit d’atteindre près de 2000 TPS (mieux que les 580 TPS obtenus avec 64Mo mais bien loin des 4800 TPS avec le QC désactivé).

 

A noter également qu’une diminution du nombre de connexions concurrentes n’inverse pas la tendance, même si l’écart diminue un peu (en d’autres termes plus il y a de connexions concurrentes, plus il y a de contentions (plutôt logique) !)

 

Une question que n’adresse pas ce test: A partir de quel pourcentage de hit ratio il est (serait) bénéfique d’activer le QC ?

J ‘essaierai d’y répondre dans un prochain article

 

Et vous, votre cache de requêtes vous veut il du bien ?

 

P.S. Les résultats sont (évidemment) les mêmes avec MariaDB 5.3.3 & Percona Server 5.5.19

 

 

Divers

Protocole

  • Start du serveur.
  • 2 run de 900 secondes
  • Je prend le meilleur des 2
  • Buffer pool suffisamment grand pour contenir toutes les données.

 

Serveur

  • Intel(R) Xeon(R) CPU X7542 @ 2.67GHz (x16)
  • RAM: 64 Go
  • OS: Ubuntu server

 

MySQL server version: 5.5.19 Percona Server with XtraDB (GPL), Release rel24.0, Revision 204

 

InnoDB configuration:

# InnoDB

  • innodb_file_per_table
  • innodb_buffer_pool_size = 8G
  • innodb_buffer_pool_instances = 2
  • innodb_log_file_size = 152428800
  • innodb_flush_log_at_trx_commit = 2
  • innodb_log_buffer_size = 8388608
  • innodb_support_xa = 1
  • innodb_thread_concurrency = 0
  • innodb_io_capacity = 200
  • innodb_doublewrite = 1
  • innodb_max_dirty_pages_pct = 75

 

Table information

 

 

 

Type de requêtes:

  • SELECT c from sbtest where id=9992481
  • SELECT DISTINCT c from sbtest where id between 9997686 and 9997786 order by c
  • SELECT c from sbtest where id=10068344
  • SELECT SUM(K) from sbtest where id between 10073907 and 10074006
  • SELECT c from sbtest where id between 9330963 and 9331062

 

Calcul du query cache hit ratio:

Qcache_hits / (Qcache_hits +Com_select) < 1%

 

 

 

9 Responses to “Attention au query cache”

  1. A lire concernant les benchmarks

  2. A lire concernant les benchmarks : http://www.mysqlperformanceblog.com/2012/02/25/introducing-new-type-of-benchmark/

  3. Quel est le point de contention ici ?
    Il serait interessant de savoir pourquoi le QC une fois activé pose pb : CPU, I/O…

  4. Un article qui soulève beaucoup d’interrogation…
    Vous nous démontrez que le cache de requêtes est clairement pénalisant dans une application.

    Je trouve cela étrange …

  5. Cédric et MrSlayers,
    effectivement à première vue cela peut paraître étrange, et pour être honnête, je ne m’attendais pas à un écart aussi important dans ce contexte d’utilisation (only select).

    Dans l’inconscient collectif, cache est un mot magique en ce qui concerne les performances, oui mais…
    Malheureusement le Query cache de MySQL est protégé par un Mutex unique, ce qui explique la contention lors d’accès concurrent importants.
    Sans parler des autres inconvénients liés aux requêtes d’écritures ou à Innodb.

    Néanmoins, l’idée de cet article, n’est pas de dire que le query cache c’est le mal !!!
    Il est juste du devoir du DBA, de se poser la question.

  6. Je n’ai pas encore pu travailler sur la question que je me pose depuis ce bench:
    « A partir de quel pourcentage de hit ratio il est (serait) bénéfique d’activer le QC ? »
    Dans notre contexte de production, chez Viadeo, pour un QC hit ratio de 20%, avec 60% de read et 40% de write, la désactivation du QC sur un de nos slave lui à semble t’il permit (difficile à dire en fait) de prendre plus de requêtes de lecture que ces petits camarades.
    A confirmer…

  7. Sa baser que le hit ratio est rarement pertinent en soit, si la valeur est basse, c’est effectivement mauvais signe mais il faut surtout regarder le Hit/Insert et le Insert/Prune ratio (prune étant les requêtes retirées pour libérer de l’espace) qu’on peux récupérer directement depuis ‘mysqlreport’.

    Les deux doivent être supérieur à 1:1.
    Dans le cas d’un Hit/Insert ratio trop bas, cela signifie qu’il y à plus de nouvelles requêtes mises en cache que d’interrogations, ça peut être le cas par exemple si le cache n’est pas encore rempli (cache flushé il y à peu de temps ou serveur redémarré) ou peu de requêtes identiques.

    Quant au Insert/Prune, s’il est trop bas, cela peux signifier que le query_cache_size est mal calibré et/ou que les requêtes sont rarement identiques.

    Il faut également vérifier que la valeur du ‘Block fragmentation’ ne soit pas trop haute, une valeur au delà de 15-20% signifie que vous avez beaucoup de petites requêtes en cache et que le ‘query_cache_min_res_unit’ est trop élevé.

    Il est également habituellement recommandé de ne pas mettre une valeur de query_cache_size trop élevée pour des raisons de performance car il se base sur un seul mutex, ce qui est d’autant plus limitant sur les machines multi-cpu.

    Une valeur trop basse de query_cache_size fera que le cache passera son temps à remplacer les anciennes requêtes par des nouvelles (prune) et perdra également en efficacité.

    Dans le cas de serveurs SQL d’hébergements Web mutualisé (beaucoup de bases et de tables mais un petit nombre de requêtes sur chaque bases et pas de jointures entre bases), il est recommandé d’avoir plusieurs serveurs de taille moyenne (voire petite) qu’un gros afin de pouvoir mieux exploiter le query_cache (mais consomme fatalement un peu plus de RAM au total qu’un seul serveur/cache).

    Un fonctionnalité propre à XtraDB est ‘query_cache_strip_comments’ qui comme son nom l’indique retire les commentaires des requêtes mises en cache (et permet au cache d’être plus efficace) car par défaut, deux requêtes identiques mais avec un commentaire différent ne seront pas prises en compte comme étant identiques par le query_cache.

    Sur ces serveurs ayant en moyenne entre 1000 et 3000 bases, traitant en moyenne entre 500 et 2000 requêtes/sec, entre 65% et 90% de SELECT, ayant un query_cache_size de 128M, un ‘query_cache_min_res_unit’ situé entre 2048 et 4096 ; tous ont un hit rate du query_cache >95% et des ratio Hit/Insert et Insert/Prune > 1:1 (mais de grosses disparités de ce coté d’un serveur à l’autre par contre).

    Il est donc prudent et simple de bien régler en fonction de ses besoins le query_cache (ou désactiver en cas de trop grosse prod), cela peut être fait simplement en se basant sur les données fournies par ‘mysqlreport’ sans oublier de prendre en compte les ordre de grandeur des valeurs et non simplement les pourcentages (notamment prunes/sec).

  8. Très intéressant cette article.

    Une question comment obtiens t – on les statistiques sur OLTP et en particulier le temps moyen d’ execution sur percentiles ?

    OLTP test statistics:

    queries performed:

    read: 60504052

    write: 0

    other: 8643436

    total: 69147488

    transactions: 4321718 (4801.84 per sec.)

    deadlocks: 0 (0.00 per sec.)

    read/write requests: 60504052 (67225.74 per sec.)

    other operations: 8643436 (9603.68 per sec.)

    Test execution summary:

    total time: 900.0132s

    total number of events: 4321718

    total time taken by event execution: 89974.6206

    per-request statistics:

    min: 1.14ms

    avg: 20.82ms

    max: 466.96ms

    approx. 95 percentile: 71.97ms

    OLTP test statistics:

  9. Bonjour,
    voici l’article Wikipedia, tu devrai trouver toutes les infos necessaires.

    Olivier

Leave a Reply