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

Brève description

Comme Amazon RDS for MySQL utilise la réplication asynchrone, le réplica ne peut parfois pas progresser avec l'instance de base de données principale, ce qui entraîne un retard de réplication.

Pour surveiller le retard de réplication, utilisez un réplica en lecture 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 l'horodatage actuel sur l'instance de base de données de réplica. Il indique également l'horodatage d'origine journalisé 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, déterminez si le réplica IO_THREAD ou SQL_THREAD en est la cause. Vous pouvez ensuite identifier la cause racine de ce retard.

Résolution

Identifiez le thread de réplication qui est en retard

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.

Exécutez la commande SHOW SLAVE STATUS sur l'instance de base de données du réplica :

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

Remarque : 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.

Généralement, 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. 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, le retard total est d'environ 2 heures.

Pour minimiser le retard, vous pouvez surveiller le journal des requêtes lent sur l'instance principale. Vous pouvez également réduire les instructions de longue durée à des instructions ou à des transactions de plus petite taille.

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 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 correctement, 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. 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

La réplication MySQL est un thread unique par défaut. Ainsi, 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. 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 page Options et 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. 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, vous désactivez également la capacité du serveur MySQL à 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

Le thread SQL effectue une analyse de table complète lors de la réplication dans les scénarios suivants :

• 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.

En effet, 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.

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 et établit la réplication entre les deux.

Une fois la réplication établie, 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 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 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, vous pouvez effectuer 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 qu'Amazon RDS donne 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. 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 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.

Informations connexes

Utilisation de la réplication MySQL dans Amazon RDS

Utilisation de réplicas en lecture MySQL