Utiliser judicieusement MySQL Performance Schema pour améliorer les performances

Pour de meilleurs Performances MySQL J'utilise le schéma de performances pour analyser directement via SQL les données relatives à la durée d'exécution des requêtes, aux temps d'attente, aux verrous, à la mémoire et aux E/S. Cela me permet d'identifier plus rapidement les causes des instructions lentes et de prendre des mesures ciblées pour Tuning et le suivi, d'après [2][3][15].

Points centraux

Les points clés suivants m'aident à utiliser efficacement le schéma de performances.

• Activation et une configuration allégée avec des instruments et des équipements adaptés
• Résumés de déclarations utiliser pour détecter les modèles coûteux et les points sensibles
• Événements d'attente, analyser conjointement les verrous et les E/S afin d'identifier les véritables goulots d'étranglement
• Schéma Sys pour désigner des aperçus rapides et exploitables
• Itératif Processus : mesurer, isoler, modifier, mesurer à nouveau

Activer le schéma de performances et le configurer de manière optimale

Je vérifie d'abord si performance_schema est activée, car les versions actuelles de MySQL la fournissent généralement activée par défaut [1][12]. Si elle n'est pas présente, je la définis dans le [mysqld]-Bloc de la my.cnf la variable performance_schema=ON et je redémarre le serveur. Ensuite, je configure les instruments et les consommateurs de manière ciblée, au lieu de tout faire tourner à plein régime en permanence. Je me concentre sur déclaration/%, wait/% et les chemins d'E/S pertinents, afin de collecter des données significatives sans surcharge inutile [6]. Pour une nouvelle série de mesures, je vide les tables d'historique concernées et je recommence à zéro Base.

Des résultats rapides grâce au schéma Sys

Pour avoir un aperçu rapide, je me sers souvent du sys-Schema, car il synthétise de manière pertinente les données brutes du Performance Schema [13]. Cela me permet de repérer en quelques minutes les requêtes qui représentent la plus grande part du temps d'exécution. Je commence par les instructions les plus gourmandes, je vérifie les vues d’E/S sur les fichiers et j’examine les résumés d’attente pour les threads. Dès que j’ai identifié un point noir, je retourne aux tables brutes et j’affine l’analyse. En examinant les plans d’exécution, on peut, avec les Conseils pour l'optimiseur souvent perceptibles dès les premiers instants Gains atteindre.

Choisir les bons instruments et les bons consommateurs

Je commence par une approche globale, tout en gardant le contrôle de mon observation : je commence par activer les éléments les plus importants Instruments pour les instructions, les temps d'attente et les E/S ; ensuite, je désactive tout ce qui ne fournit pas d'informations utiles [6]. Les composants tels que l'historique des événements et les tables de synthèse doivent étayer les questions auxquelles je souhaite répondre. S'il s'agit par exemple de pics de latence, je consulte résumé_des_attentes_des_événements_au_niveau_global_par_nom_d'événement et compare cela avec résumé_des_déclarations_sur_les_événements_par_résumé. En cas de temps d'attente liés aux E/S, je vérifie résumé_des_fichiers_par_nom_d'événement et table_io_waits_summary_by_table. Cette sélection ciblée permet de limiter les frais généraux tout en fournissant des résultats fiables Données.

Résumés de déclarations : identifier les modèles, réduire la charge

Grâce aux résumés de requêtes, je peux identifier les modèles qui sont systématiquement coûteux, même si les requêtes individuelles contiennent des littéraux variables [17]. Je trie les résultats par temps total, nombre d'exécutions et latence moyenne afin de définir des priorités. Pour ce faire, j'utilise également le Analyser le journal des requêtes lentes pour ne pas passer à côté de valeurs aberrantes rares. Lorsque les résumés indiquent des pics, je vérifie les index, les stratégies de JOIN et l'ordre des filtres à l'aide de EXPLAIN. Je vérifie ensuite l'efficacité de ces mesures en effectuant de nouvelles mesures dans le schéma de performance, afin que les optimisations restent mesurables.

Interpréter les événements d'attente, les verrous et les E/S

Lorsque des requêtes sont bloquées, je consulte les tables « Wait » et « Lock » afin de déterminer la véritable Cause à consulter [3]. Si de nombreux threads accèdent aux mêmes tables, cela indique table_lock-J'attends de voir s'il y a de la concurrence. Si les événements d'E/S sur les fichiers présentent des latences élevées, je vérifie le stockage et la mise en cache, ainsi que les modèles de requêtes à l'aide de scans à grande échelle. Si je constate des verrous de ligne InnoDB, j'analyse les enregistrements fréquemment consultés, la durée des transactions et la couverture des index. Ce n’est que lorsque toutes ces pièces du puzzle s’emboîtent que je me penche sur les paramètres du serveur, le schéma ou le code.

Surveillance de la mémoire : mémoire et pool de tampons

Pour remédier aux problèmes de mémoire, je compare l'utilisation des tables de mémoire et celle du tampon InnoDB. Si les besoins en mémoire de certains composants augmentent, j'ajuste les limites et je vérifie si les caches contiennent des données erronées. Si le cache InnoDB s'avère insuffisant, j'augmente sa taille ou j'améliore la localité des requêtes. Pour aller plus loin, vous pouvez consulter Optimiser le pool de mémoire tampon réaliser des gains de latence significatifs. Je confirme cet effet grâce aux Résumé- Consultez les tableaux et vérifiez si les LRU-Hits et les temps d'attente d'E/S évoluent dans la bonne direction.

Processus de diagnostic itératif pour la pratique quotidienne

Je travaille toujours selon des boucles bien définies afin de ne pas perdre de temps et de garantir que les changements restent mesurables [3]. Je commence par reproduire le problème sous une charge contrôlée. Ensuite, je rassemble les valeurs mesurées dans quelques tableaux ciblés et j'isole les candidats les plus suspects. Je modifie ensuite ce qui semble le plus prometteur : index, requête, paramètre ou code. Pour finir, je procède à de nouvelles mesures et je documente brièvement Avant/après-des tableaux, afin que l'équipe puisse constater immédiatement l'effet.

Exemples de requêtes : des données brutes aux décisions

Pour répondre aux questions courantes, j'ai noté des extraits de code SQL concis que j'utilise directement au quotidien. Le tableau présente des exemples que j'utilise fréquemment, ainsi que leur fonction. J'adapte les filtres tels que LIMIT ou ORDER BY en fonction du cas concret. L'essentiel reste le suivant : d'abord une hypothèse, puis une évaluation ciblée et une décision claire. C'est ainsi que je veille à ce que l'analyse reste ciblée et que j'évite les Dernier.

Table(s) du schéma de performances	Objectif	Colonnes importantes	Exemple de requête
résumé_des_déclarations_sur_les_événements_par_résumé	Trouver des modèles haut de gamme	digest_text, count_star, sum_timer_wait	SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10;
résumé_des_attentes_des_événements_au_niveau_global_par_nom_d'événement	Points noirs de latence	event_name, sum_timer_wait	SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10;
table_io_waits_summary_by_table	Vérifier les E/S des tables	schéma_objet, nom_objet, read_timer_wait	SELECT object_schema, object_name, (read_timer_wait + write_timer_wait) / 1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10;
résumé_mémoire_globale_par_nom_d'événement	Identifier les applications gourmandes en mémoire	event_name, current_alloc	SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10;

Site de production : minimiser les frais généraux, maximiser l'efficacité

En situation réelle, je n'active pas les instruments à l'aveuglette, mais je ne sélectionne que ceux qui répondent à ma question [6]. Je traite les événements à haute fréquence avec prudence et je limite la durée des fenêtres d’historique. Pour les observations plus longues, je privilégie les résumés condensés et j’enregistre les instantanés en externe. Je veille à l’entrée dans performance_schema_setup_consumers, afin que je puisse piloter les collectes plutôt que de les laisser se dérouler toutes seules. Cette approche permet de maintenir l'analyse efficace et protège le serveur.

Réglage de précision : les instruments de configuration et les consommateurs dans la pratique

Afin d'obtenir rapidement des résultats fiables, je configure les instruments et les charges de manière ciblée. Les éléments suivants sont particulièrement importants : déclaration/%, wait/%, wait/io/% et, si nécessaire, certains mémoire/%-Chemins. Je n'active dans un premier temps que le strict nécessaire, puis j'étends l'analyse si certaines questions concrètes restent sans réponse. Les chronomètres du schéma de performance mesurent en picosecondes ; pour obtenir des valeurs en secondes, je divise les colonnes de latence par 1e12.

Point de départ typique lors de l'exécution :

-- Activer les instruments clés
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES', TIMED='YES'
  WHERE NAME LIKE 'statement/%'
 OR NAME LIKE 'wait/io/%'
 OR NAME LIKE 'wait/lock/%';

-- Sélectionner les consommateurs importants
UPDATE performance_schema.setup_consumers
  SET ENABLED='YES'
  WHERE NAME IN ('global_instrumentation',
 'thread_instrumentation',
 'statements_digest',
 'events_statements_current',
                 'events_statements_history',
 'events_waits_current',
 'events_waits_history');

-- Vider les résumés pour obtenir une nouvelle série de mesures
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

Lorsque j'ai besoin d'analyses de la mémoire, j'active de manière sélective mémoire/%-Outils. Cela entraîne des coûts supplémentaires, mais cela en vaut la peine en cas de fuites ou de forte pression sur l'allocateur.

Dimensions : comprendre les utilisateurs, les hôtes et les schémas

Les pics de puissance ne sont souvent pas globaux, mais limités à certains Utilisateur, Hôtes ou un Schéma limité. Le schéma de performances fournit à cet effet des résumés par compte et par hôte. De plus, le résumé contient la colonne nom_du_schéma, afin de délimiter les points chauds par base de données.

Exemples que j'utilise souvent :

• Principaux schémas en fonction de la durée totale : SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10;
• Utilisateurs/hôtes générant le plus de latence (par compte) : SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10;
• Threads présentant le temps d'attente le plus long : SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;

Ces vues me permettent de segmenter le trafic de manière ciblée et de limiter le débit, d'utiliser la mise en cache ou de mettre en place des variantes de requêtes pour chaque client.

Rendre visibles les transactions longues et les verrous de métadonnées

Les transactions qui s'exécutent depuis longtemps ou qui sont inactives bloquent les points de contrôle, les opérations de purge et les opérations DML concurrentes. Je vérifie donc régulièrement la vue des transactions et les temps d'attente MDL :

• Transactions en cours : SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
• Détecter les verrous de métadonnées (concurrence DDL/DML) : SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;

Lorsque MDL domine, je redéfinis les fenêtres DDL, je réduis au minimum les durées de verrouillage dans le code (transactions plus courtes) et je vérifie s'il y a des AUTOCOMMIT=0- Laisser les sessions ouvertes inutilement longtemps.

Réplication, sauvegardes et effets secondaires : tout ce qu'il faut savoir

Les processus de réplication et de sauvegarde apparaissent dans les vues « Waits » et « I/O ». Les retards peuvent être identifiés grâce à l'état des workers et aux files d'attente de fichiers. Je me concentre sur les workers Applier, le thread SQL et les événements d'E/S de fichiers :

• Worker d'application à forte latence : SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
• Points critiques d'E/S de fichiers pendant les sauvegardes : SELECT event_name, (sum_timer_read + sum_timer_write) / 1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;

Si je constate des goulots d'étranglement, je dissocie les phases d'E/S (par exemple, le fenêtrage, le planificateur d'E/S, la limitation des sauvegardes) ou j'augmente le nombre de workers Applier en parallèle, dans la mesure où la charge de travail évolue.

Fenêtres temporelles, instantanés et stratégies de réinitialisation

Les mesures nécessitent des plages horaires bien définies. Pour les comparaisons „ avant/après “, j'utilise des réinitialisations et des instantanés ciblés :

• Réinitialiser les résumés pour obtenir de nouveaux intervalles : TRUNCATE TABLE performance_schema.events_statements_summary_by_digest ;
• Sauvegarder un instantané sur un support externe : CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
• Conserver des fenêtres historiques courtes (consommateurs), collecter les tendances à long terme en externe.

Je peux ainsi comparer et documenter en toute sécurité les optimisations, qu'il s'agisse de déploiements, de modifications de paramètres ou de changements de schéma.

Gérer la surcharge et les besoins en mémoire

On entend souvent dire que le Performance Schema serait „ trop coûteux “. Dans la pratique, je limite la surcharge grâce à trois mesures : n’activer que les instruments pertinents, maintenir une durée réduite pour les consommateurs d’historique très sollicités et choisir les paramètres de mémoire adaptés. En cas de forte variance des digests, j’augmente de manière ciblée performance_schema_digests_size ainsi que, si nécessaire, performance_schema_max_sql_text_length, afin que les identités restent stables. Si des instruments de mémoire sont nécessaires, je les limite aux sous-systèmes problématiques.

Paramètres de réglage courants dans my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# Facultatif, en cas de nombreux modèles :
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

À chaque modification, je vérifie si l'utilisation du processeur, la latence et l'utilisation de la mémoire restent stables. Une fois le diagnostic terminé, je ramène la configuration au „ minimum opérationnel “.

Problèmes courants et solutions rapides

• Une somme importante en résumé_des_déclarations_sur_les_événements_par_résumé, de nombreuses numérisations : Vérifie les index, l'ordre des filtres et la « sargability » ; confirme avec EXPLAIN et je refais la mesure (le temps de digestion doit diminuer de manière visible).
• dominante table_io_waits à partir de quelques tables : Améliorer la localisation des E/S (accès aux index clusterisés, index couvrants), réduire le volume de données par instruction, recourir au traitement par lots plutôt qu'au traitement de la table entière si nécessaire.
• Temps d'attente pour wait/lock/innodb/%: Identifier les enregistrements « chauds », atténuer les conflits d'écriture grâce à des transactions plus petites, à des index adaptés ou à la mise en file d'attente.
• Nombreux wait/lock/metadata/sql/mdl: Planifier les fenêtres DDL, EN LIGNE- privilégier les opérations compatibles, découpler les lecteurs et les écrivains grâce à des transactions plus courtes.
• Augmentation des stocks en résumé_mémoire_globale_par_nom_d'événement: Raffiner les limites, limiter de manière ciblée les caches de requêtes, identifier les composants problématiques à l'aide de mémoire/% ventiler en détail.
• „Une latence “ irrégulière » alors que les valeurs moyennes sont par ailleurs normales : Utiliser les vues Sys avec des centiles et, si nécessaire, mesurer séparément les pics de charge (fenêtre plus étroite, historique court, instruments ciblés).

Corrélation : du thread à la déclaration et à l'attente

Pour établir rapidement des liens entre les causes, je relie performance_schema.threads grâce aux tables « Current » et « History » pour les instructions et les temps d'attente. Cela me permet de voir ce qu'un thread concerné a fait en dernier et ce qu'il attend. Voici un aperçu concis du déroulement :

1. personnes concernées PROCESSLIST_ID respectivement THREAD_ID de performance_schema.threads aller chercher.
2. Dernière déclaration via historique_des_événements_et_déclarations déterminer (selon THREAD_ID et trier par heure).
3. Attentes parallèles désactivées historique_des_attentes_d'événements vérifier afin d'identifier les causes liées aux verrous ou aux files d'attente d'E/S.

Ce modèle „ Drilldown & Join “ est ma méthode par défaut lorsque certaines sessions ou requêtes Web présentent des anomalies.

Contrôles de qualité et performance continue

Pour éviter que les optimisations ne tombent à l'eau, je mets en place des « Quality Gates » allégés : des requêtes définies issues du Performance Schema sont exécutées avant et après chaque mise en production. Je sauvegarde des instantanés, je compare les indicateurs clés (Top-Digests, Top-Waits, E/S par table) et je documente les écarts. Dans le cadre du CI/CD, j’ajoute des profils de charge représentatifs et des seuils pour le 95e centile. Si un indicateur sort du cadre, la marche à suivre est claire : vérifier l’hypothèse, cibler les outils, déployer le correctif, puis mesurer à nouveau.

Éviter les sources d'erreurs

• Trop d'instruments à long terme : Le diagnostic est temporaire ; en fonctionnement normal, ne laisser actif que l'ensemble minimal.
• Périodes de mesure mixtes : Vider les résumés avant de procéder à de nouveaux tests, sinon les anciennes données faussent les résultats.
• Unité de temps incorrecte : Les temporisations sont exprimées en pikosecondes ; de manière cohérente tout au long du texte 1e12 partage.
• Avalanche de résumés : Les littéraux variables peuvent perturber les modèles ; normaliser le SQL et performance_schema_max_sql_text_length vérifier.
• Historique trop long : Une fréquence élevée d'événements et un historique long génèrent une pression ; limiter la durée de l'historique, enregistrer les instantanés en externe.

Liste de contrôle pratique

• Définir la question, formuler l'hypothèse.
• Utiliser les outils adaptés / impliquer les consommateurs, limiter les frais généraux.
• Vider les résumés, sélectionner une fenêtre de mesure courte.
• Vérifier les Top-Digests, les temps d'attente et les E/S ; confirmer les points sensibles.
• Adapter de manière ciblée l'index, la requête, le code et les paramètres.
• Effectuer une nouvelle mesure, enregistrer des instantanés, consigner la décision.
• Ramener la configuration au minimum de fonctionnement.

En résumé : mon approche dans la pratique

J'active le schéma de performance de manière ciblée : je commence par une approche globale, puis je me concentre sur les éléments les plus utiles. Instruments [1][2][12]. Pour avoir un aperçu rapide, je me réfère au schéma Sys et, si nécessaire, je consulte les données brutes [13]. Je traite d'abord les points sensibles au niveau des « digests » et des « wait-events » avant de modifier les paramètres [3][15][17]. Je valide ensuite chaque modification par de nouvelles mesures afin que les progrès restent visibles et reproductibles. C'est ainsi que je garantis une fiabilité durable Temps de réponse et m'évite un travail inutile.