Pourquoi mon instance de base de données MySQL affiche-t-elle un nombre élevé de sessions actives en attente d'événements d’attente SYNCH dans Performance Insights ?

Brève description

Les événements d'attente MySQL SYNCH dans Performance Insights se produisent lorsque plusieurs sessions de base de données accèdent aux mêmes objets ou structures de mémoire protégés. Les objets suivants sont protégés dans Amazon Relational Database Service (Amazon RDS) pour MySQL, Amazon RDS for MariaDB et Amazon Aurora édition compatible avec MySQL :

• Le fichier journal binaire actif dans une instance source binlog qui contient un mutex permettant à une session unique de le lire ou de l'écrire à la fois.
• Le dictionnaire de données qui est protégé pour les écritures d'instructions en langage de contrôle des données (DCL) ou en langage de définition des données (DDL).
• L'index de hachage adaptatif qui contient un mutex qui permet à une seule session de le lire ou de l'écrire à la fois.
• Le cache de tables ouvertes qui permet à une seule session d'ajouter ou de supprimer une table du cache.
• Chaque bloc de base de données du pool de mémoire tampon InnoDB où une seule session peut modifier le contenu d'un bloc en mémoire à la fois.

Résolution

S’assurer que l'instance de base de données dispose de ressources de processeur suffisantes pour gérer la charge de travail

Un nombre élevé de sessions en attente d'événements SYNCH entraîne une utilisation importante du processeur. À une utilisation de 100 %, le nombre de sessions d'attente augmente. Pour résoudre ce problème, augmentez la taille de votre instance de base de données afin que le processeur soit suffisant pour traiter la charge de travail supplémentaire.

Comme les événements SYNCH ne durent généralement pas longtemps, la métrique Amazon CloudWatch CPUUtilization peut ne pas indiquer correctement le pic d'utilisation. Cette métrique n'a qu'une granularité de 60 secondes. Pour vérifier l'utilisation maximale, utilisez des compteurs de granularité d'une seconde dans Surveillance améliorée sur la console Amazon RDS.

Augmenter le mutex MySQL ou verrouiller le tableau d'attente

MySQL utilise une structure de données interne pour coordonner les threads avec une taille de table par défaut de 1. Cette configuration fonctionne pour les machines mono-processeur, mais peut entraîner des problèmes sur les machines dotées de plusieurs processeurs. Si votre charge de travail comporte un grand nombre de threads en attente, augmentez la taille de la table. Modifiez le paramètre innodb_sync_array_size afin qu'il soit égal ou supérieur au nombre de processeurs. Pour plus d'informations, consultez la page innodb_sync_array_size sur le site Web de MySQL.

Remarque : MySQL utilise le paramètre innodb_sync_array_size uniquement au démarrage de la base de données.

Réduire la simultanéité

Généralement, le parallélisme améliore le débit. Cependant, lorsqu'un grand nombre de sessions exécutent les mêmes activités ou des activités similaires, les sessions doivent avoir accès aux mêmes objets protégés. Plus le nombre de sessions est élevé, plus vous utilisez de processeur lorsque vous attendez.

Pour résoudre ce problème, répartissez les activités dans le temps ou programmez-les en séries. Vous pouvez également regrouper plusieurs opérations en une seule instruction, comme les insertions sur plusieurs lignes.

Résoudre votre problème en fonction des événements d'attente

Prenez les mesures de dépannage suivantes en fonction de l'événement d'attente que vous recevez.

synch/rwlock/innodb/dict sys RW lock ou synch/rwlock/innodb/dict_operation_lock

Ces événements se produisent lorsque vous invoquez simultanément un grand nombre de DCL ou DDL simultanés. Pour résoudre ces problèmes, réduisez la dépendance de l'application vis-à-vis des DDL lors de l'activité normale de l'application.

synch/cond/sql/MDL_context::COND_wait_status

Cet événement se produit lorsqu'un grand nombre de SQL, y compris des sélections, accèdent à une table modifiée par une DCL ou une DDL. Pour résoudre ce problème, n'exécutez pas d'instructions DDL sur les tables à fort trafic pendant l'activité normale de l'application.

synch/mutex/sql/LOCK_open ou synch/mutex/sql/LOCK_table_cache

Ces événements se produisent lorsque le nombre de tables ouvertes par vos sessions dépasse la taille du cache de définition de table ou du cache d'ouverture de table. Pour résoudre ce problème, augmentez la taille des caches. Pour plus d'informations, consultez la page 10.4.3.1 Comment MySQL ouvre et ferme les tables sur le site Web de MySQL.

synch/mutex/sql/LOG

Cet événement se produit lorsque votre base de données exécute un grand nombre d'instructions que les méthodes de journalisation actuelles ne peuvent pas prendre en charge. Si vous utilisez la méthode de sortie TABLE, utilisez plutôt FICHIER. Si vous utilisez le journal général, utilisez plutôt Audit avancé dans Aurora. Si vous définissez le paramètre long_query_time sur 0 ou sur une valeur inférieure à 1, augmentez la valeur.

synch/mutex/innodb/buf_pool_mutex, synch/mutex/innodb/aurora_lock_thread_slot_futex ou synch/rwlock/innodb/index_tree_rw_lock

Ces événements se produisent lorsqu'un grand nombre d'instructions DML (Data Manipulation Language) similaires accèdent au même objet de base de données en même temps. Pour résoudre ce problème, utilisez des instructions et des partitions multiligne pour répartir la charge de travail sur différents objets de base de données.

synch/mutex/innodb/aurora_lock_thread_slot_futex

Cet événement se produit lorsqu'une session verrouille une ligne pour une mise à jour, puis qu'une autre session essaie de mettre à jour la ligne. Résolvez ce problème en fonction des autres événements d'attente que vous recevez. Recherchez et répondez aux instructions SQL responsables de cet événement d'attente, ou recherchez et répondez à la session bloquante.

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

Ces événements se produisent lorsque vous avez activé la journalisation binaire et qu’il existe un volume important de débit de validation, de transactions de validation ou de réplicas lisant des journaux binaires. Pour résoudre ce problème, utilisez des instructions multiligne ou regroupez plusieurs instructions en une seule transaction.

Pour plus d'informations sur les événements d'attente compatibles avec Aurora MySQL, consultez les sections Configuration d'Aurora MySQL avec des événements d'attente et Événements d'attente Aurora MySQL.

Il est recommandé de mettre à niveau la base de données vers une version majeure compatible avec la version 8.0 ou ultérieure. Dans la mesure du possible, utilisez des instructions multiligne ou regroupez plusieurs instructions en une seule transaction. Dans Aurora, utilisez la base de données globale Aurora au lieu de la réplication de journaux binaires, ou utilisez les paramètres aurora_binlog.

Informations connexes

Surveillance de la charge de base de données avec Performance Insights sur Amazon RDS

Groupes de paramètres pour Amazon RDS