dasini.net – Journal d'un expert MySQL

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

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

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

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 |
+------------------------------+----------+

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.)

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

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

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.)

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

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

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

 

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%

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 |
+-------------------------+----------+

 

mysql> show global status like 'com_sel%';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_select | 4746219 |
+---------------+---------+

 

select 407451/(407451+4746219);
+-------------------------+
| 407451/(407451+4746219) |
+-------------------------+
|                  0.0791 |
+-------------------------+

=> Taux de réussite du cache de requête est d'environ 8%

 

Filed under:
bench, MariaDB, MySQL, Percona server by Olivier DASINI