Pourquoi mon instance DB MySQL affiche-t-il un nombre élevé de séances actives en attente sur les événements d'attente SYNCH dans Performance Insights ?

Date de la dernière mise à jour : 14/12/2020

J'ai une instance MySQL RDS, RDS MariaDB ou Amazon Aurora MySQL, et j'ai activé Performance Insights. Mon instance de base de données affiche un grand nombre d'événements d'attente AASS (Average Active Sessions) en attente de synchronisation (SYNCH). Pourquoi les événements SYNCH ralentissent-ils mes requêtes et comment puis-je améliorer les performances de mon instance de base de données ?

Brève description

Si vous voyez des événements d'attente MySQL SYNCH dans Performance Insights, cela signifie qu'un grand nombre de séances dans la base de données tentent d'accéder aux mêmes objets protégés ou structures de mémoire. Les éléments suivant font partie des objets protégés dans MySQL :

  • Le fichier journal binaire actif dans une instance source de journal binaire – contient un mutex qui permet à une seule séance de le lire ou de l'écrire à tout moment.
  • Le dictionnaire de données – pour les écritures, généralement causées par des instructions DCL (Data Control Language) ou DDL (Data Definition Language).
  • L'index de hachage adaptatif – contient un mutex qui permet à une seule séance de le lire ou de l'écrire à tout moment.
  • Le cache de table ouvert – une seule séance peut ajouter ou supprimer une table du cache.
  • Chaque bloc de base de données unique à l'intérieur du pool de tampons InnoDB – une seule séance peut modifier le contenu d'un bloc en mémoire à la fois.

Remarque : il existe un certain nombre d'étapes que vous pouvez prendre pour augmenter le parallélisme dans vos requêtes SQL. Dans certains cas, vous devez regarder de plus près l'architecture de l'application et comment l'application utilise la base de données pour résoudre ces problèmes.

Résolution

Assurez-vous que l'instance de base de données dispose de ressources CPU suffisantes pour gérer la charge de travail

Si vous avez un nombre élevé de séances en attente d'événements SYNCH, cela entraîne une utilisation élevée du CPU. Si l'utilisation atteint 100 %, cela augmente le nombre de séances en attente. Lors du dépannage, augmentez la taille de votre instance de base de données pour vous assurer qu'il y a suffisamment de capacité de CPU pour traiter la charge de travail supplémentaire.

Étant donné que ces événements sont généralement de courte durée, la métrique d'utilisation du CPU Amazon CloudWatch peut ne pas afficher correctement l'utilisation maximale. La meilleure façon de vérifier ceci est d'utiliser les compteurs CPU d'une seconde dans RDS Enhanced Monitoring. Ces compteurs sont plus granulaires et détaillés.

Augmenter le tableau d'attente mutex/verrouillage de MySQL

MySQL utilise une structure de données interne pour coordonner les threads. Ce tableau a une taille de 1, par défaut. Cela convient pour les machines à un seul processeur, mais peut causer des problèmes sur les machines avec plusieurs processeurs. Si votre charge de travail comporte un grand nombre de threads en attente, augmentez la taille du tableau. Faites correspondre le paramètre MYSQL innodb_sync_array_size à la quantité des CPU (ou plus, jusqu'à 1024).

Remarque : le paramètre innodb_sync_array_size s'applique uniquement au démarrage de la base de données.

Réduire la simultanéité

En général, le parallélisme aide à améliorer le débit. Néanmoins, lorsqu'un grand nombre de séances essaient de faire les mêmes activités ou des activités similaires, les séances ont besoin d'accéder aux mêmes objets protégés. Plus le nombre de séances est élevé, plus vous utilisez de CPU pendant l'attente.

Répartissez ces activités au fil du temps, ou planifiez les en séries. Vous pouvez également regrouper plusieurs opérations en une seule instruction, telles que des insertions à plusieurs lignes.

Examiner des événements d'attente spécifiques

Utilisez les exemples de solutions ci-dessous pour résoudre votre événement d'attente spécifique :

  • synch/rwlock/innodb/dict sys RW lock ou synch/rwlock/innodb/dict_operation_lock – Cela indique qu'un nombre élevé de DCL simultanés de DDL sont déclenchés en même temps. Réduisez la dépendance de l'application à l'égard de DDL lors de l'activité régulière de l'application.
  • synch/cond/sql/MDL_context::COND_wait_status – Ceci indique qu'un nombre élevé de SQL (y compris les sélections) essayent d'accéder à une table qu'une DCL ou DDL est en train de modifier. Évitez d'exécuter des instructions DDL sur les tables à trafic élevé pendant l'activité régulière de l'application.
  • synch/cond/sql/MYSQL_BIN_LOG::COND_done / synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit OU synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log – Vous avez activé la journalisation binaire, et il peut y avoir un débit de validation élevé, des transactions de grand nombre, des réplicas lecture de journaux binaires, ou une combinaison de ceux-ci. Envisagez la mise à niveau de la base de données vers une version majeure compatible avec la version 5.7 ou supérieure. En outre, utilisez des instructions à plusieurs lignes ou regroupez plusieurs instructions en une seule transaction. Dans Aurora, utilisez des bases de données globales au lieu de la réplication de journaux binaires ou utilisez les paramètres aurora_binlog_*.
  • synch/mutex/sql/LOCK_open ou synch/mutex/sql/LOCK_table_cache – Cela indique que le nombre de tables ouvertes par vos séances dépasse la taille du cache de définition de table ou du cache ouvert de table. Augmentez la taille de ces caches.
  • synch/mutex/sql/LOG – Votre base de données peut exécuter un grand nombre d'instructions, et les méthodes de journalisation actuelles ne peuvent pas suivre. Si vous utilisez la méthode de sortie TABLE, essayez d'utiliser FILE à la place. Si vous utilisez le journal général, utilisez plutôt l'audit avancé d'Aurora. Si vous utilisez zéro ou moins de 1 pour le paramètre long_query_time, essayez de l'augmenter.
  • synch/mutex/innodb/buf_pool_mutex or synch/mutex/innodb/aurora_lock_thread_slot_futex or synch/rwlock/innodb/index_tree_rw_lock – Il existe un grand nombre de DML similaires qui accèdent au même objet de base de données en même temps. Essayez d'utiliser des instructions à plusieurs lignes. En outre, répartissez la charge globale sur différents objets de base de données. Une façon de le faire est d'utiliser le partitionnement.

Cet article vous a-t-il été utile ?


Besoin d'aide pour une question technique ou de facturation ?