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 ?

Brève description

Performance Insights est activé sur l'un des services suivants :

• Amazon Relational Database Service (Amazon RDS) pour MySQL.
• Amazon RDS for MariaDB
• Amazon Aurora édition compatible MySQL.

Si vous voyez des événements d'attente MySQL SYNCH dans Performance Insights, 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 à la fois.
• 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 à la fois.
• 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.

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 %, le nombre de sessions d'attente augmente. 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 spécifiques 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 suivants pour résoudre un événement d'attente spécifique. Pour plus d'informations sur les événements d'attente d'Aurora MySQL, consultez la section Optimisation d'Aurora MySQL avec les événements d'attente.

• 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/mutex/sql/LOCK_open, OU
  synch/mutex/sql/LOCK_open – 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 plutôt d'utiliser FILE. Si vous utilisez le journal général, utilisez plutôt l'audit avancé d'Amazon Aurora. Si vous utilisez 0 ou moins de 1 pour le paramètre long_query_time, essayez de l'augmenter.
• synch/mutex/innodb/buf_pool_mutex,OU synch/mutex/innodb/aurora_lock_thread_slot_futex, OU synch/rwlock/innodb/index_tree_rw_lock : un grand nombre de DML similaires accèdent simultanément au même objet de base de données. Utilisez des instructions à plusieurs lignes et utilisez le partitionnement pour répartir la charge de travail entre différents objets de base de données.
• synch/mutex/innodb/aurora_lock_thread_slot_futex - Cela se produit lorsqu'une session a verrouillé une ligne pour une mise à jour, puis qu'une autre session essaie de mettre à jour la même ligne. Votre action dépend des autres événements d'attente que vous voyez. Recherchez et répondez aux instructions SQL responsables de cet événement d'attente, ou recherchez la session de blocage et répondez-y.
• synch/cond/sql/MYSQL_BIN_LOG::COND_done, OU
  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 se peut que l'une des situations suivantes se présente :

                         - un débit de validation élevé.

                         - un grand nombre de transactions validées.

                         - répliques en lisant des fichiers binaires.

                         - une combinaison de ces éléments.

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 Amazon Aurora, utilisez des bases de données globales au lieu de la réplication de journaux binaires ou utilisez les paramètres aurora_binlog.

---

Informations connexes

Utilisation d'Amazon RDS Performance Insights

Utilisation des groupes de paramètres de base de données

Événements MySQL Aurora