ApsaraDB RDS:Causes et solutions de la latence de synchronisation des instances en lecture seule ApsaraDB RDS for MySQL

SHOW SLAVE STATUS \G

Cause 1 : Spécifications insuffisantes de l'instance en lecture seule

Mécanisme. La réplication utilise un thread I/O (pour récupérer les journaux binaires depuis l'instance principale) et un thread SQL (pour les appliquer localement). Ces deux threads entrent en concurrence pour les ressources I/O, y compris les opérations d'entrée/sortie par seconde (IOPS). Si l'instance en lecture seule ne peut pas maintenir les IOPS requis, elle prend du retard.

Confirmation. Dans la console ApsaraDB RDS, accédez à la section Monitoring and Alerts de l'instance en lecture seule. Vérifiez si l'utilisation du CPU, l'utilisation de la mémoire, la bande passante I/O ou les IOPS atteignent constamment leurs limites. Vous pouvez également consulter le type d'instance dans la section Configuration Information de la page Basic Information. Pour plus d'informations, consultez les rubriques Types d'instances pour les instances en lecture seule ApsaraDB RDS for MySQL (x86) et Types d'instances pour les instances en lecture seule ApsaraDB RDS for MySQL (ARM).

Résolution. Mettez à niveau l'instance en lecture seule vers des spécifications égales ou supérieures à celles de l'instance principale. Pour plus d'informations, consultez la rubrique Modifier les spécifications d'une instance ApsaraDB RDS for MySQL.

Cause 2 : Nombre élevé de transactions par seconde (TPS) sur l'instance principale

Mécanisme. Un seul thread SQL applique toutes les transactions répliquées sur l'instance en lecture seule de manière séquentielle. Si l'instance principale traite un volume élevé d'écritures simultanées, l'instance en lecture seule ne parvient pas à suivre le rythme.

Confirmation. Accédez à la page Dashboard de l'instance principale ou de l'instance en lecture seule et vérifiez la métrique TPS. Pour plus d'informations, consultez la rubrique Utiliser la fonctionnalité de tableau de bord pour une instance ApsaraDB RDS for MySQL.

Résolution. Optimisez ou divisez les transactions volumineuses sur l'instance principale afin de réduire le volume d'écriture par transaction et de raccourcir la durée pendant laquelle chaque transaction occupe le thread SQL.

Cause 3 : Transactions volumineuses sur l'instance principale

Mécanisme. Les opérations telles que UPDATE, DELETE, INSERT...SELECT et REPLACE...SELECT effectuées sur de grands volumes de données génèrent des entrées de journal binaire volumineuses. L'instance en lecture seule doit consacrer le même temps à l'application de la transaction que l'instance principale à son exécution. Par exemple, une suppression qui prend 80 secondes sur l'instance principale prend également 80 secondes à être répliquée.

De plus, bien que les transactions simultanées sur plusieurs tables soient prises en charge, un seul thread réplique les transactions sur une table donnée à la fois, ce qui peut amplifier le délai.

Confirmation. Exécutez SHOW SLAVE STATUS \G sur l'instance en lecture seule et observez si Seconds_Behind_Master augmente continuellement tandis que Exec_Master_Log_Pos reste identique. Exécutez ensuite SHOW PROCESSLIST pour identifier le thread SQL spécifique. Si la journalisation binaire est configurée au format ROW, vérifiez si un fichier de journal binaire dépasse le seuil max_binlog_size :

SHOW BINARY LOGS;

Si File_size dépasse max_binlog_size, les transactions volumineuses sont probablement la cause.

Vérifiez également les contentions de verrous de métadonnées (MDL) en examinant la sortie de SHOW SLAVE STATUS \G pour détecter les états d'attente de verrou.

Résolution. Divisez les transactions volumineuses en lots plus petits. Par exemple, ajoutez une clause WHERE avec une limite de lignes aux instructions DELETE afin que chaque lot traite un nombre gérable de lignes, permettant ainsi à l'instance en lecture seule de suivre le rythme.

Cause 4 : Opérations DDL de longue durée sur l'instance principale

Mécanisme. La réplication applique les instructions Data Definition Language (DDL) de manière séquentielle. Une opération DDL sur une grande table, telle que CREATE INDEX, ALTER TABLE ADD COLUMN ou REPAIR TABLE, peut bloquer toute la réplication suivante jusqu'à sa terminaison. Les requêtes lentes générant un grand nombre de tables temporaires aggravent le problème en consommant des E/S disque.

Par ailleurs, les requêtes en cours ou les transactions ouvertes sur l'instance en lecture seule peuvent créer une contention MDL qui bloque les instructions DDL arrivant depuis l'instance principale.

Confirmation.

• Vérifiez si les fichiers de journaux binaires s'accumulent sans être tronqués, ce qui indique un arriéré DDL.

• Exécutez l'instruction suivante sur l'instance en lecture seule pour vérifier la contention de verrous :

  SHOW ENGINE INNODB STATUS \G

• Exécutez l'instruction suivante pour identifier les tables actuellement utilisées :

  SHOW OPEN TABLES;

  Les tables avec in_use = 1 sont verrouillées et peuvent bloquer la réplication.

• Consultez les journaux de requêtes lentes pour les opérations liées aux DDL, telles que OPTIMIZE, ALTER, REPAIR et CREATE. Pour plus d'informations, consultez la rubrique Analyse des journaux de requêtes lentes.

Résolution.

• Planifiez les opérations DDL pendant les heures creuses.

• Activez la fonctionnalité d'extension automatique du stockage ou définissez le seuil d'extension manuelle du stockage à 90 %. Pour plus d'informations, consultez la rubrique Comment mettre à l'échelle une instance ApsaraDB RDS ?

• Activez SQL Explorer pour surveiller régulièrement les performances SQL. Pour plus d'informations, consultez la rubrique Utiliser la fonctionnalité SQL Explorer et Audit sur une instance ApsaraDB RDS for MySQL.

• Configurez des alertes d'utilisation du disque ou mettez à niveau les spécifications de l'instance. Pour plus d'informations, consultez la rubrique Modifier les spécifications d'une instance ApsaraDB RDS for MySQL.

Si une instruction DDL provenant de l'instance principale est bloquée par un verrou de métadonnées sur l'instance en lecture seule :

1. Exécutez SHOW PROCESSLIST; sur l'instance en lecture seule pour trouver le thread SQL en attente d'un verrou de métadonnées.

2. Terminez la session détenant le verrou pour reprendre la réplication. Pour plus d'informations, consultez la rubrique Utiliser DMS pour libérer les verrous de métadonnées.

Cause 5 : Table avec un index unique mais sans clé primaire

Mécanisme. Lorsqu'une table n'a pas de clé primaire et possède uniquement un index unique contenant des valeurs NULL, le thread SQL de réplication utilise le plan d'exécution de l'index unique au lieu du plan préféré par l'optimiseur, même si une clause WHERE permettrait autrement une recherche plus efficace. Cela force des analyses complètes de la table lors de la réplication, augmentant considérablement les lectures logiques et ralentissant le thread SQL.

Confirmation. Exécutez la requête suivante sur l'instance principale pour identifier les tables sans clé primaire :

SELECT tab.table_schema AS database_name, tab.table_name
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco
  ON tab.table_schema = tco.table_schema
  AND tab.table_name = tco.table_name
  AND tco.constraint_type = 'PRIMARY KEY'
WHERE tco.constraint_type IS NULL
  AND tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND tab.table_type = 'BASE TABLE'
ORDER BY tab.table_schema, tab.table_name;

Pour les tables renvoyées par la requête, utilisez la vue sys.schema_index_statistics pour confirmer si l'index unique contient des valeurs NULL.

Résolution. Ajoutez une clé primaire explicite à toute table qui en est dépourvue.