Avant la version 5.6.24 ou 5.7.6 de MySQL, il n’y a aucun moyen de paramétrer la taille maximale du texte SQL capturé dans P_S.events_statements_summary_by_digest et events_statements_(current,history,history_long). Il est fixé à 1024 octets sans possibilité de le changer, et sur ces versions on peut parfois se retrouver frustré de ne pouvoir capturer qu’une partie seulement du texte SQL normalisé ou complet pour pouvoir récupérer le plan d’exécution :
mysql> select version(), DIGEST, DIGEST_TEXT, length(DIGEST_TEXT)
-> from events_statements_summary_by_digest
-> where DIGEST='34db15d11344f6c9896a852321c737ea' \G
*************************** 1. row ***************************
version(): 5.6.17-1~dotdeb.1-log
DIGEST: 34db15d11344f6c9896a852321c737ea
DIGEST_TEXT: SELECT COUNT ( blankblankblank) AS blankblankblank FROM blankblankblank AS blankblankblank
JOIN blankblankblank AS blankblankblank ON ( blankblankblank . blankblankblank = blankblankblank . blankblankblank )
JOIN blankblankblank AS blankblankblank ON ( blankblankblank . blankblankblank = blankblankblank . blankblankblank )
LEFT JOIN blankblankblank AS blankblankblank ON ( blankblankblank . blankblankblank = blankblankblank . blankblankblank
AND blankblankblank . blankblankblank = ? AND blankblankblank . blankblankblank = ? AND blankblankblank . blankblankblank = ?
AND blankblankblank . blankblankblank = ? ) JOIN blankblankblank AS blankblankblank ON ( blankblankblank . blankblankblank = ? )
LEFT JOIN blankblankblank AS blankblankblank ON ( blankblankblank . blankblankblank = blankblankblank . blankblankblank
AND blankblankblank . blankblankblank = ? AND blankblankblank . blankblankblank = ? ) JOIN blankblankblank AS blankblankblank
ON ( blankblankblank . blankblankblank = ? ) LEFT JOIN blankblankblank AS blankblankblank
ON ( blankblankblank . blankblankblank = blankblankblank . blankblankblank AND blankblankblank . blankblankblank = ?
AND blankblankblank . blankblankblank = ? ) JOIN blankblankblank AS blankblankblank
ON ( blankblankblank . blankblankblank = blankblankblank . blankblankblank ) JOIN ...
length(DIGEST_TEXT): 1013
1 row in set (0,00 sec)
Ici le DIGEST_TEXT aura été anonymisé volontairement, mais on voit qu’il est tronqué à 1013 octets et se termine par ‘…’
En effet, une fois de plus si l’on suit la documentation officielle :
“(…) In MySQL 5.6.24 and 5.6.25, performance_schema_max_digest_length is not available and max_digest_length applies to all digest computation. Before MySQL 5.6.24, neither max_digest_length nor performance_schema_max_digest_length are available and a fixed maximum of 1024 bytes applies to all digest computation.”
Il faut donc migrer dans une version supérieure ou égale à 5.6.24, 5.7.6 ou 8.0. Dans ces versions de nouveaux paramètres permettent de lever cette limitation:
mysql> select version(), VARIABLE_NAME, VARIABLE_VALUE
-> from performance_schema.global_variables
-> where VARIABLE_NAME like '%digest%';
+--------------+--------------------------------------+----------------+
| version() | VARIABLE_NAME | VARIABLE_VALUE |
+--------------+--------------------------------------+----------------+
| 5.7.10-3-log | max_digest_length | 1024 |
| 5.7.10-3-log | performance_schema_digests_size | 10000 |
| 5.7.10-3-log | performance_schema_max_digest_length | 1024 |
+--------------+--------------------------------------+----------------+
3 rows in set (0.00 sec)
– performance_schema_digests_size était déjà présente avant la 5.6.24 et 5.7.6, elle sert à spécifier la taille maximale de la vue dynamique (le ‘consumer’ events_statements_summary_by_digest) stockant les digests. Il est important de monitorer la taille de cette vue car une fois la limite atteinte, tous les nouveaux digests vont chasser les plus anciens et ceux-ci seront perdus. Attention le paramètre est statique…
– performance_schema_max_digest_length et max_digest_length sont donc les deux petites nouveautés. max_digest_length est la quantité de mémoire maximale que mysqld réserve par session pour calculer une version ‘normalisée’ d’une requête, c’est à dire sans la partie littérale (principalement les arguments). C’est donc une limite haute. performance_schema_max_digest_length quand à lui est la quantité maximale d’octets que performance_schema va stocker pour la colonne DIGEST_TEXT, dans une vue type events_statements_summary_by_digest. Il arrive en 5.6.26 et 5.7.8, deux sauts de puce de version après max_digest_length.
Par défaut les 2 paramètres sont fixés à 1024 octets, il faut toujours essayer de les aligner l’un avec l’autre sinon il y aura des risques de texte tronqué, lorsque le second es plus grand que le premier notamment.
Si on choisi d’augmenter ces valeurs, il faut faire attention à deux facteurs :
– Le nombre de connexions simultanées: ces 1024 octets sont alloués à chaque session, donc la charge mémoire de l’instance globale augmente avec les connexions.
– La versatilité du code: plus il y a de types de requêtes différents fondamentalement les unes des autres, et plus le nombre de digests va augmenter, donc la saturation de la vue à 10000 lignes par défaut risque d’arriver plus vite.
Mais ça veut dire aussi qu’en augmentant ces paramètres on améliore la pertinence de ce que l’on échantillonne : en effet, on réduit ainsi les chances de se retrouver avec deux textes normalisés pour la même requête, alors qu’en réduisant le paramètre, on risque d’augmenter la duplicité de ces requêtes fonctionnellement identiques et nous induire en erreur…
Pour illustrer simplement ce concept, dans cet exemple on part du principe que notre taille de digest maximale sera de 32 octets, et on calcule un digest via la fonction md5():
mysql> select md5(substring('SELECT COL1, COL2, COL3 FROM T1 WHERE COL1=?',1,32))
-> UNION
-> select md5 (substring('SELECT COL1, COL2, COL3 FROM T1 WHERE COL2=?',1,32)) ;
+---------------------------------------------------------------------+
| md5(substring('SELECT COL1, COL2, COL3 FROM T1 WHERE COL1=?',1,32)) |
+---------------------------------------------------------------------+
| c35bb84d1fe037fa656a026bdf3a7738 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
On n’a qu’une seule signature alors que les 2 requêtes sont différentes. P_S va cumuler des statistiques pour 2 requêtes différentes, ce qui n’est pas l’idéal. Si j’augmente notre ‘faux’ paramètre de 32 à 64, j’obtiens bien deux signatures distinctes :
mysql> select md5(substring('SELECT COL1, COL2, COL3 FROM T1 WHERE COL1=?',1,64))
-> UNION
-> select md5(substring('SELECT COL1, COL2, COL3 FROM T1 WHERE COL2=?',1,64))
-> ;
+---------------------------------------------------------------------+
| md5(substring('SELECT COL1, COL2, COL3 FROM T1 WHERE COL1=?',1,64)) |
+---------------------------------------------------------------------+
| cbed77bff188dd322f7a18e10d011b93 |
| 239d970bb69a1f94216aecaa14252e63 |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
Conclusion si vous utilisez les vues de P_S et notamment events_statements_summary_by_digest, pensez à vérifier ces paramètres ainsi que votre version de MySQL. Si vous êtes en dessous de la version 5.6.24 et 5.7.6, pensez à migrer vers les dernières versions mineures ou vers une version majeure supérieure.
A+. ~David.
Continuez votre lecture sur le blog :
- MySQL & Performance Schema : mais où sont passés les compteurs Com_% ?? (David Baffaleuf) [MySQL]
- Faciliter la lecture des mesures de temps dans performance_schema (David Baffaleuf) [MySQL]
- MySQL et les tables temporaires internes (Benjamin VESAN) [MySQL]
- Fatal : too many connections (Sarah FAVEERE) [Non classé]
- Nouveautés MySQL 8.0 : Variables persistés (Capdata team) [MySQL]