Comment puis-je résoudre les problèmes de latence élevée des réplicas avec Amazon RDS for MySQL ?

Lecture de 9 minute(s)
0

Je souhaite trouver la cause du retard de réplication lorsque j'utilise Amazon Relational Database Service (Amazon RDS) for MySQL.

Brève description

Amazon RDS for MySQL utilise la réplication asynchrone. Cela signifie qu'il arrive que le réplica ne puisse pas suivre le rythme de l'instance de base de données principale. Par conséquent, un retard de réplication peut se produire.

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.

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

Le champ Seconds_Behind_Master indique la différence entre l'horodatage actuel sur l'instance de base de données de réplica. Il indique également l'horodatage d'origine enregistré sur l'instance de base de données principale pour le traitement des événements sur l'instance de base de données de réplica.

La réplication MySQL fonctionne avec trois threads : Binlog Dump, IO_THREAD et SQL_THREAD. Pour plus d'informations sur le fonctionnement de ces threads, consultez la documentation MySQL relative aux threads de réplication. En cas de retard dans la réplication, déterminez 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

Pour identifier le thread de réplication qui est en retard, consultez les exemples suivants :

1.    Exécutez la commande SHOW MASTER STATUS sur l'instance de base de données principale, puis examinez la sortie. Le résultat ressemble à ce qui suit :

mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.066552|      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Remarque : Dans cet exemple de sortie, l'instance de base de données source ou principale écrit les journaux binaires dans le fichier mysql-bin.066552.

2.    Exécutez la commande SHOW SLAVE STATUS sur l'instance de base de données de réplica, puis examinez le résultat. Le résultat ressemble aux exemples qui suivent :

Exemple 1 :

mysql> SHOW SLAVE STATUS\G;
*************************** 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 1, le fichier Master_Log_File: mysql-bin.066548 indique que le réplica IO_THREAD procède à la lecture depuis 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. Cette sortie indique que le réplica IO_THREAD est en retard de quatre journaux binaires. Mais le fichier Relay_Master_Log_File est mysql-bin.066548, ce qui indique que le réplica SQL_THREAD lit depuis le même fichier que le réplica IO_THREAD. Cela indique que le réplica SQL_THREAD suit le rythme, tandis que le réplica IO_THREAD est en retard.

Exemple 2 :

mysql> SHOW SLAVE STATUS\G
*************************** 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 2 indique que le fichier journal de l'instance principale est mysql-bin-changelog.066552. La sortie indique que IO_THREAD suit le rythme 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-changelog.066530. Par conséquent, SQL_THREAD est en retard de 22 journaux binaires.

Normalement, IO_THREAD n'entraîne pas de retards de réplication importants, car IO_THREAD ne lit les journaux binaires qu'à partir de l'instance principale ou source. Cependant, la connectivité et la latence réseau peuvent affecter la vitesse des lectures entre les serveurs. Le réplica IO_THREAD peut fonctionner plus lentement en raison d'une utilisation élevée de la bande passante.

Si le réplica SQL_THREAD est à l'origine des retards de réplication, les situations suivantes peuvent provoquer un retard :

  • Requêtes à longue durée sur l'instance de base de données principale
  • Taille ou stockage de classe d'instance de base de données insuffisants
  • Requêtes parallèles exécutées sur l'instance de base de données principale
  • Journaux binaires synchronisés sur le disque de l'instance de base de données du réplica
  • Binlog_format sur le réplica est défini sur ROW
  • Retard de création du réplica

Requêtes à longue durée sur l'instance principale

Les requêtes à 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 vous initiez une modification sur l'instance principale et que cette dernière nécessite une heure pour s'exécuter, le retard est d'une heure. Si la modification prend également une heure pour être effectuée sur le réplica, le retard total est d'environ deux heures une fois l'exécution terminée. Il s'agit d'un retard normal, mais vous pouvez surveiller le journal des requêtes lentes sur l'instance principale afin de réduire ce retard. Vous pouvez également identifier les instructions à longue durée afin de réduire le retard. Décomposez ensuite les instructions à longue durée en instructions ou transactions plus petites.

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 de réplica est inférieure à celle de l'instance de base de données principale, il se peut que le réplica ralentisse en raison de ressources insuffisantes. En effet, le réplica ne peut pas suivre le rythme des modifications apportées à l'instance principale. 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 correctement, chaque réplica en lecture nécessite la même quantité de ressources de calcul et de stockage que l'instance de base de données source. Pour plus d'informations, consultez la section Classes d'instances de base de données.

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

Si vous exécutez des requêtes en parallèle sur l'instance de base de données principale, ces requêtes sont validées sur le réplica dans un ordre séquentiel. En effet, la réplication MySQL est par défaut une réplication monothread (SQL_THREAD). Si un volume élevé d'écritures sur l'instance de base de données source se produit en parallèle, les écritures sur le réplica en lecture sont sérialisées. Les écritures sur le réplica en lecture utilisent un seul SQL_THREAD pour procéder à la sérialisation. Cela peut provoquer un retard entre l'instance de base de données source et le réplica en lecture.

La réplication multithread (parallèle) est disponible pour MySQL 5.6, MySQL 5.7 et les versions ultérieures. Pour plus d'informations sur la réplication multithread, consultez la documentation MySQL relative aux options et aux variables de journalisation binaires.

La réplication multithread peut provoquer des écarts dans la réplication. Par exemple, la réplication multithread n'est pas recommandée lorsqu'il s'agit d'ignorer les erreurs de réplication, car il est difficile d'identifier les transactions que vous ignorez. Cela peut entraîner des écarts de cohérence des données entre les instances de base de données principale et de réplica.

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

L'activation des sauvegardes automatiques sur le réplica peut entraîner une surcharge de synchronisation des journaux binaires avec le disque du réplica. La valeur par défaut du paramètre sync_binlog est définie sur 1. Si vous redéfinissez cette valeur sur 0, vous désactivez également la synchronisation du journal binaire avec le disque par le serveur MySQL. Au lieu de se connecter au disque, le système d'exploitation (OS) vide parfois les journaux binaires sur le disque.

La désactivation de la synchronisation des journaux binaires peut réduire la surcharge de performances requise pour synchroniser les journaux binaires avec le disque à chaque validation. 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 documentation MySQL relative à sync_binlog.

Binlog_format est défini sur ROW

Le thread SQL effectue une analyse complète de la table lors de la réplication lorsque les deux facteurs suivants sont vrais :

  • Le paramètre binlog_format sur l'instance de base de données principale est défini sur ROW.
  • Il manque une clé primaire dans la table source.

En effet, la valeur par défaut du paramètre slave_rows_search_algorithms est TABLE_SCAN,INDEX_SCAN.

Pour résoudre ce problème à court terme, remplacez l'algorithme de recherche par INDEX_SCAN,HASH_SCAN afin de réduire la surcharge de l'analyse complète de la table. À long terme, 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 documentation MySQL relative à slave_rows_search_algorithms.

Retard de création du réplica

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

Amazon RDS met du temps à créer de nouveaux réplicas en lecture. Une fois la réplication établie, un retard se produit au niveau du temps nécessaire à la création d'une sauvegarde de l'instance de base de données principale. Pour réduire ce retard, créez une sauvegarde manuelle avant de demander la création du réplica. L’instantané de base de données est 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 charges d'instantané Amazon Elastic Block Store (Amazon EBS) existantes créent un nouveau volume en arrière-plan.

**Remarque :**Pour les réplicas Amazon RDS for MySQL (volumes basés sur EBS), le retard de réplication peut initialement augmenter. Cela est dû au fait que l'effet de chargement lent peut influencer les performances de réplication.

Pour atténuer les effets du chargement lent sur les tables de votre réplica en lecture nouvellement créé, vous pouvez effectuer des opérations impliquant des analyses complètes de table. Par exemple, exécuter une opération mysqldump sur votre réplica en lecture pour des tables ou des bases de données spécifiques. Cela permet à Amazon RDS de hiérarchiser et de télécharger toutes les données de table sauvegardées depuis Amazon Simple Storage Service (Amazon S3).

Envisagez également d'utiliser la fonctionnalité de préparation du cache InnoDB « sur demande ». La fonctionnalité de préparation du cache InnoDB enregistre l'état du pool de mémoire tampon sur le disque, dans un fichier nommé ib_buffer_pool et situé dans le répertoire de données InnoDB. Cela peut apporter des gains de performances en annulant l'état actuel du pool de mémoire tampon de l'instance de base de données principale avant de créer le réplica en lecture. Rechargez ensuite le pool de mémoire tampon après avoir créé un réplica en lecture.

Informations connexes

Utilisation de la réplication MySQL dans Amazon RDS

Utilisation de réplicas en lecture MySQL