Attention au query cache
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).
Les chiffres
— Query cache à 128 Mo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SHOW GLOBAL VARIABLES LIKE 'query_cache%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 134217728 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+ OLTP test statistics: queries performed: read: 2728040 write: 0 other: 389720 total: 3117760 transactions: 194860 (216.45 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 2728040 (3030.30 per sec.) other operations: 389720 (432.90 per sec.) Test execution summary: total time: 900.2544s total number of events: 194860 total time taken by event execution: 90014.3415 per-request statistics: min: 40.23ms avg: 461.94ms max: 1276.36ms approx. 95 percentile: 666.36ms Threads fairness: events (avg/stddev): 1948.6000/10.21 execution time (avg/stddev): 900.1434/0.08 |
Chiffres clés :
- QC size : 128 Mo
- TPS : 216,45
- 95%tile : 666,36 ms
— Query cache à 256 Mo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SET GLOBAL query_cache_type=1; SET GLOBAL query_cache_size=268435456; SHOW GLOBAL VARIABLES LIKE 'query_cache%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+ OLTP test statistics: queries performed: read: 5601204 write: 0 other: 800172 total: 6401376 transactions: 400086 (444.47 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 5601204 (6222.60 per sec.) other operations: 800172 (888.94 per sec.) Test execution summary: total time: 900.1385s total number of events: 400086 total time taken by event execution: 90006.3589 per-request statistics: min: 33.56ms avg: 224.97ms max: 601.63ms approx. 95 percentile: 320.52ms Threads fairness: events (avg/stddev): 4000.8600/11.78 execution time (avg/stddev): 900.0636/0.04 |
Chiffres clés :
- QC size : 256 Mo
- TPS : 444,47
- 95%tile : 320,52 ms
— Query cache à 64 Mo
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SET GLOBAL query_cache_type=1; SET GLOBAL query_cache_size=67108864; SHOW GLOBAL VARIABLES LIKE 'query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ |
|
1 2 3 4 5 6 7 8 9 10 |
OLTP test statistics: queries performed: read: 7319270 write: 0 other: 1045610 total: 8364880 transactions: 522805 (580.84 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 7319270 (8131.73 per sec.) other operations: 1045610 (1161.68 per sec.) |
|
1 2 3 4 5 6 7 8 9 |
Test execution summary: total time: 900.0881s total number of events: 522805 total time taken by event execution: 90002.3195 per-request statistics: min: 35.97ms avg: 172.15ms max: 380.79ms approx. 95 percentile: 214.74ms |
|
1 2 3 |
Threads fairness: events (avg/stddev): 5228.0500/16.33 execution time (avg/stddev): 900.0232/0.02 |
Chiffres clés :
- QC size : 64 Mo
- TPS : 580,84
- 95%tile : 214,74 ms
— Query cache OFF à 0 Mo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> SET GLOBAL query_cache_type=0; SET GLOBAL query_cache_size=0; SHOW GLOBAL VARIABLES LIKE 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_strip_comments | OFF | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 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.) |
|
1 2 3 4 5 6 7 8 9 |
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 |
|
1 2 3 |
Threads fairness: events (avg/stddev): 43217.1800/158.09 execution time (avg/stddev): 899.7462/0.01 |
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
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8_swedish_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Name: sbtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000060 Avg_row_length: 224 Data_length: 2247098368 Max_data_length: 0 Index_length: 137019392 Data_free: 4194304 Auto_increment: 10000001 Create_time: 2012-02-10 13:15:11 Update_time: NULL Check_time: NULL Collation: utf8_swedish_ci Checksum: NULL |
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%
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 21304 | | Qcache_free_memory | 76320360 | | Qcache_hits | 407451 | | Qcache_inserts | 4745672 | | Qcache_lowmem_prunes | 4691295 | | Qcache_not_cached | 543 | | Qcache_queries_in_cache | 54377 | | Qcache_total_blocks | 130059 | +-------------------------+----------+ |
|
1 2 3 4 5 6 |
mysql> show global status like 'com_sel%'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Com_select | 4746219 | +---------------+---------+ |
|
1 2 3 4 5 6 7 8 |
select 407451/(407451+4746219); +-------------------------+ | 407451/(407451+4746219) | +-------------------------+ | 0.0791 | +-------------------------+ => Taux de réussite du cache de requête est d'environ 8% |
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
—–
A lire concernant les benchmarks
A lire concernant les benchmarks : http://www.mysqlperformanceblog.com/2012/02/25/introducing-new-type-of-benchmark/
Quel est le point de contention ici ?
Il serait interessant de savoir pourquoi le QC une fois activé pose pb : CPU, I/O…
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 …
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.
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…
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).
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:
Bonjour,
voici l’article Wikipedia, tu devrai trouver toutes les infos necessaires.
Olivier