Comment résoudre les problèmes de latence élevée de réplica du journal binaire avec Amazon RDS pour MySQL et Aurora MySQL ?

Brève description

Amazon RDS for MySQL utilisant la réplication asynchrone, un réplica est parfois en retard sur l'instance de base de données principale. Pour surveiller le retard de réplication, utilisez un réplica en lecture Amazon RDS for MySQL avec réplication basée sur la position du fichier journal binaire.

Pour vérifier la métrique ReplicaLag pour Amazon RDS, utilisez Amazon CloudWatch. La métrique ReplicaLag indique la valeur du champ Seconds_Behind_Master de la commande SHOW SLAVE STATUS.

Pour Aurora, la métrique AuroraBinlogReplicaLag évalue la latence de réplica entre les clusters de bases de données Aurora qui utilisent des journaux binaires.

Remarque : Pour les versions 8.0.22 et ultérieures de MySQL, la commande SHOW REPLICA STATUS a remplacé la commande SHOW SLAVE STATUS. Pour plus d’informations, consultez la page SHOW SLAVE | REPLICA STATUS sur le site Web de MySQL.

Le champ Seconds_Behind_Master indique le décalage actuel en secondes entre l'instance de base de données de réplication et l'instance source. Il indique également la valeur d'origine qui est journalisée sur l'instance de base de données principale pour l’événement traité sur l'instance de base de données de réplica.

La réplication MySQL utilise le vidage de journaux binaires, le récepteur d'E/S de réplication et les threads de l'applicateur SQL de réplication. Pour plus d'informations sur le fonctionnement des threads, consultez la page Threads de réplication sur le site Web de MySQL. En cas de retard dans la réplication, vérifiez si le réplica IO_THREAD ou SQL_THREAD en est la cause. Vous pouvez ensuite identifier la cause première de ce retard.

Résolution

Identifier le thread de réplication qui est en retard

Procédez comme suit :

1. Pour identifier l'endroit où l'instance de base de données source ou principale écrit les journaux binaires, exécutez la commande SHOW MASTER STATUS sur l'instance de base de données principale :

   mysql> SHOW MASTER STATUS;

   Exemple de sortie :

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   Remarque : Dans l’exemple de sortie précédent, l'instance de base de données source ou principale écrit les journaux binaires dans le fichier mysql-bin.066552.
2. Pour connaître l'état de votre sortie, exécutez la commande SHOW SLAVE STATUS ou SHOW REPLICA STATUS sur l'instance de base de données de réplication :

   mysql> SHOW SLAVE STATUS\G;

Exemple de sortie 1 :

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Dans l'exemple de sortie précédent, le Master_Log_File: mysql-bin.066548 montre que le réplica IO_THREAD lit le fichier journal binaire mysql-bin.066548. L'instance de base de données principale écrit les journaux binaires dans le fichier mysql-bin.066552. Le réplica IO_THREAD est en retard de quatre journaux binaires. Cependant, étant donné que le Relay_Master_Log_File est mysql-bin.066548, le réplica SQL_THREAD lit le même fichier que IO_THREAD. Le réplica SQL_THREAD conserve sa vitesse, mais le réplica IO_THREAD est en retard.

Exemple de sortie 2 :

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

L'exemple de sortie précédent montre que le fichier journal de l'instance principale est mysql-bin.066552. Le réplica IO_THREAD maintient la vitesse de l'instance de base de données principale. Dans la sortie du réplica, le thread SQL exécute Relay_Master_Log_File: mysql-bin.066530. Par conséquent, SQL_THREAD est en retard de 22 journaux binaires.

Étant donné que IO_THREAD ne lit les journaux binaires qu'à partir de l'instance principale ou source, IO_THREAD n'entraîne pas de retards de réplication importants. Cependant, la connectivité et la latence réseau peuvent affecter la vitesse des lectures entre les serveurs. Une utilisation élevée de la bande passante peut ralentir les performances du réplica IO_THREAD.

Si le réplica SQL_THREAD est à l'origine des retards de réplication, suivez les étapes de dépannage suivantes pour résoudre votre problème.

Requêtes d’écriture de longue durée sur l'instance principale

Les requêtes d’écriture de longue durée sur l'instance de base de données principale qui prennent autant de temps à s'exécuter sur l'instance de base de données de réplica peuvent augmenter le paramètre seconds_behind_master. Par exemple, si l'exécution d'une modification de l'instance principale prend une heure, le retard est d'une heure. Si la modification prend également 1 heure pour être effectuée sur le réplica, la latence totale est de 2 heures.

Pour minimiser la latence, surveillez le journal de requêtes lentes sur l'instance principale et identifiez les requêtes qui doivent être optimisées. Vous pouvez également réduire les instructions de longue durée à des instructions ou à des transactions de plus petite taille.

Pour résoudre les problèmes de performance liés au journal binaire, analysez les événements d'attente trouvés dans Performance Insights pour plus d'informations. Vous pouvez également ajuster les niveaux d'isolation.

Taille ou stockage de classe d'instance de base de données insuffisants

Si la configuration de classe ou de stockage de l'instance de base de données du réplica est inférieure à celle de l'instance principale, il se peut que le réplica ralentisse en raison de ressources insuffisantes. Le réplica ne peut pas conserver le nombre de modifications sur l'instance principale.

Pour résoudre ce problème, assurez-vous que le type d'instance de base de données du réplica est identique ou supérieur à celui de l'instance de base de données principale. Pour que la réplication fonctionne efficacement, chaque réplica en lecture requiert la même quantité de ressources de calcul et de stockage que l'instance de base de données source.

Requêtes parallèles exécutées sur l'instance de base de données principale

Par défaut, la réplication MySQL est un thread unique. Lorsque vous exécutez des requêtes en parallèle sur l'instance principale, les requêtes sont validées sur le réplica dans un ordre séquentiel. Lorsqu'un volume élevé d'écritures sur l'instance source se produit en parallèle, les écritures sur le réplica en lecture utilisent un seul réplica SQL_THREAD pour la sérialisation. Un retard entre l'instance de base de données source et le réplica en lecture peut alors se produire.

Avec les versions 8.0.27 et ultérieures de MySQL, la valeur par défaut de replica_parallel_workers est 4. Cette valeur signifie que les répliques sont multithread par défaut. De même, pour Aurora MySQL version 3.04 et versions ultérieures, la réplication est multithread par défaut, la valeur de replica_parallel_workers étant définie sur 4. Vous pouvez modifier ce paramètre dans votre groupe de paramètres personnalisés.

Pour plus d'informations sur la réplication multithread, consultez la page Options et variables de journalisation binaires.

La réplication multithread peut provoquer des écarts dans la réplication. Par exemple, il est difficile d'identifier les transactions que vous ignorez. Lorsque vous utilisez la réplication multithread, il n'est pas recommandé d'ignorer les erreurs de réplication. Des écarts de cohérence des données entre les instances de base de données principale et de réplica peuvent se produire.

Journaux binaires synchronisés sur le disque de l'instance de base de données du réplica

Lorsque vous activez les sauvegardes automatiques sur le réplica, la synchronisation des journaux binaires avec le disque du réplica entraîne une surcharge. La valeur par défaut du paramètre sync_binlog est définie sur 1. Si vous modifiez la valeur à 0, le serveur MySQL ne peut pas synchroniser le journal binaire avec le disque. À la place, le système d'exploitation vide occasionnellement les journaux binaires sur le disque.

Pour réduire la surcharge de performances requise pour synchroniser les journaux binaires avec le disque à chaque validation, désactivez la synchronisation des journaux binaires. Toutefois, en cas de panne de courant ou de panne du système d'exploitation, certaines validations risquent de ne pas être synchronisées avec les journaux binaires. Cette asynchronisation peut affecter les fonctionnalités de restauration ponctuelle (PITR). Pour plus d'informations, consultez la page sync_binlog sur le site Web de MySQL.

Binlog_format est défini sur ROW

Lorsque la réplication se produit dans les scénarios suivants, le thread SQL effectue une analyse complète de la table :

• Le paramètre binlog_format sur l'instance de base de données principale est défini sur ROW.
• La table source n’utilise pas de clé primaire.

Cette analyse se produit car la valeur par défaut du paramètre slave_rows_search_algorithms est TABLE_SCAN,INDEX_SCAN.

Pour résoudre temporairement ce problème, remplacez l'algorithme de recherche par INDEX_SCAN,HASH_SCAN afin de réduire la surcharge d’une analyse complète de la table. Pour une solution plus permanente, il est recommandé d'ajouter une clé primaire explicite à chaque table.

Pour plus d'informations sur le paramètre slave-rows-search-algorithms, consultez la page slave_rows_search_algorithms sur le site Web de MySQL.

Latence de création de réplicas (applicable à RDS MySQL)

Pour créer un réplica en lecture d'une instance principale MySQL, Amazon RDS prend un instantané de base de données. Amazon RDS restaure ensuite l'instantané pour créer une nouvelle instance de base de données et établit la réplication entre les deux.

Une fois qu'Amazon RDS a établi la réplication, un retard se produit lorsqu'Amazon RDS crée une sauvegarde de l'instance de base de données principale. Pour réduire le retard, créez une sauvegarde manuelle avant de demander la création du réplica. L'instantané de base de données devient alors une sauvegarde incrémentielle.

Lorsque vous restaurez un réplica en lecture à partir d'un instantané, le réplica n'attend pas que toutes les données soient transférées depuis la source. L'instance de base de données de réplica est disponible pour effectuer les opérations de base de données. Les instantanés Amazon Elastic Block Store (Amazon EBS) existants créent un nouveau volume en arrière-plan.

Remarque : Pour les réplicas Amazon RDS for MySQL (volumes sauvegardés par Amazon EBS), le retard du réplica peut initialement augmenter car le chargement différé peut affecter les performances de réplication.

Pour réduire les effets du chargement différé sur les tables de votre nouveau réplica en lecture, effectuez des opérations qui utilisent des analyses de tables complètes. Par exemple, exécutez mysqldump sur votre réplica en lecture pour des tables ou des bases de données spécifiques afin de donner la priorité à toutes les données de table sauvegardées depuis Amazon Simple Storage Service (Amazon S3).

Vous pouvez également utiliser la fonctionnalité de préparation du cache InnoDB à la demande pour RDS MySQL. La fonctionnalité de préparation du cache InnoDB enregistre l'état du pool de mémoires tampons sur le disque, dans un fichier nommé ib_buffer_pool et situé dans le répertoire de données InnoDB. Les performances s'améliorent avec la charge moins importante car Amazon RDS extrait l'état actuel du pool de mémoires tampons de l'instance de base de données principale avant que vous ne créiez le réplica en lecture. Vous pouvez ensuite recharger le pool de mémoires tampons après avoir créé un réplica en lecture.

Effets de la latence de réplica

Une latence élevée de réplica sur l'instance ou le cluster de réplica peut entraîner une accumulation de journaux binaires sur la source. Pour éviter d'éventuels problèmes, surveillez et gérez la taille des fichiers du journal binaire et l'utilisation de l'espace disque. Pour RDS, vous pouvez surveiller la métrique BinLogDiskUsage sur l’instance de base de données source.

Configurez une période de conservation optimale des journaux binaires pour équilibrer les capacités de restauration instantanée avec l'utilisation du stockage dans une instance RDS MySQL et sur Aurora. En outre, pour tirer parti des journaux binaires pour la réplication et la restauration, assimilez les principes de convention de dénomination des fichiers de journaux binaires et de comportement de rotation. Pour obtenir une liste des journaux disponibles, exécutez la commande SHOW BINARY LOGS.

Informations connexes

Utilisation de la réplication MySQL dans Amazon RDS

Utilisation de réplicas en lecture MySQL

Optimisation de la réplication de journaux binaires pour Aurora MySQL