High Performance MySQL [Ch. 3 : Profiler les performances du serveur]

Les trois questions

Presque toutes les plaintes de performance se ramènent à l'une de trois questions : le serveur fait-il son travail efficacement, pourquoi cette requête précise est-elle lente, et qu'est-ce qui provoque le blocage occasionnel que personne n'arrive à reproduire ? Le profilage permet d'y répondre avec des preuves plutôt qu'à l'intuition.

Le livre définit la performance comme le temps de réponse ; le but du profilage est donc d'expliquer où passe ce temps. Parfois, la réponse est un travail que l'on peut supprimer : un index manquant, un tri inutile. Parfois, chaque étape est nécessaire, et c'est tout aussi précieux, car cela vous évite de peaufiner une requête qui est déjà aussi rapide que possible.

À quoi ressemble un profil

Chronométrer une tâche de bout en bout donne un seul chiffre. Un profil la découpe en sous-tâches, fusionne celles qui se répètent et les trie selon leur part du temps total. Le résultat se lit comme une facture classée :

Il en existe deux variantes. Le profilage du temps d'exécution montre où va le travail CPU et convient aux requêtes limitées par le calcul. L'analyse des attentes montre le temps passé bloqué sur les E/S ou les verrous, et c'est là que se trouvent en réalité la plupart des requêtes lentes.

Commencer par l'application

Avant de profiler MySQL, profilez le code qui l'appelle. Un outil de monitoring applicatif qui chronomètre chaque requête et la découpe en spans (appels à la base, API externes, rendu) vous dit si la base est seulement en cause, et quelles requêtes comptent le plus pour les utilisateurs. Le travail en base sans impact visible pour les utilisateurs peut généralement attendre.

Profiler la charge du serveur

Quand vous ne savez pas encore quelles requêtes posent problème, regardez-les toutes. Le slow query log enregistre les requêtes qui dépassent long_query_time ; fixer ce seuil à 0 capture tout. La journalisation coûte un peu d'E/S et de disque : faites tourner les fichiers et ne laissez pas la journalisation complète activée à l'aveugle. Des outils comme pt-query-digest normalisent ensuite les requêtes en empreintes et les classent par temps total.

Dans MySQL 8, le Performance Schema fournit les mêmes informations sans fichiers de log : events_statements_summary_by_digest agrège déjà les instructions par empreinte, avec le nombre d'exécutions, la latence et les lignes examinées par digest.

Profiler une seule requête

Une fois un suspect identifié, décomposez-le. Le livre utilise SHOW PROFILE, qui liste les étapes traversées par une requête (ouverture des tables, envoi des données, création d'une table temporaire, tri) avec leur durée. Il est déprécié dans MySQL 8 ; les tables d'étapes du Performance Schema (events_stages_history_long) contiennent les mêmes données et peuvent être regroupées en SQL ordinaire :

SELECT event_name, COUNT(*) AS calls, SUM(timer_wait) / 1e9 AS total_ms
FROM performance_schema.events_stages_history_long
WHERE nesting_event_id = <statement event id>
GROUP BY event_name
ORDER BY total_ms DESC;

Le regroupement compte : une étape qui apparaît cinq fois dans la sortie brute n'occupe qu'une ligne dans le profil. Une requête qui passe la moitié de son temps à copier dans une table temporaire vous dit exactement où regarder ; un tri qui prend 5 % vous dit de ne pas vous en soucier.

Les compteurs de statut complètent la mesure des étapes. Comparer SHOW SESSION STATUS avant et après une requête montre combien de lignes elle a lues par chaque chemin d'accès (Handler_read_*), si elle a créé des tables temporaires sur disque, et si elle a trié.

Problèmes intermittents

Les blocages sporadiques sont le cas le plus difficile, car ils ont disparu quand vous regardez. L'étude de cas du livre porte sur un serveur qui refusait les connexions pendant quelques secondes à quelques minutes, tous les un ou deux jours. La méthode se généralise :

• Rassemblez d'abord le contexte : versions, moteur, matériel, changements récents. Idéalement, personne n'a encore « essayé des choses ».
• Déclenchez sur un symptôme. Surveillez un compteur qui grimpe pendant l'incident (ici, Threads_connected passant de quelques dizaines à plusieurs centaines) et capturez tout quand il se déclenche.
• Méfiez-vous de la réponse familière. De fortes écritures disque ressemblaient à un bug de flush connu ; les compteurs de flush disaient le contraire.
• Suivez les preuves hors de la base. La vraie cause était un cache applicatif qui expirait, et tous les serveurs d'application qui le reconstruisaient en même temps : une ruée sur le cache (cache stampede). Une journalisation au niveau applicatif l'aurait montré immédiatement.

Comment cela apparaît dans QueryCrack

Le correcteur est un profileur minuscule : il prend un instantané des compteurs Handler_read_* de la session, exécute votre requête et rapporte la différence. Les lignes lues par recherche dans l'index, par parcours d'index et par parcours complet sont séparées : le verdict ne dit donc pas seulement « trop lent », mais aussi quel chemin d'accès a fait les dégâts.

Questions fréquentes