Corriger le retard de réplication MySQL : causes courantes et solutions

Un retard de réplication MySQL signifie que votre réplica est en retard par rapport à la source, de sorte que les lectures effectuées sur ce réplica peuvent renvoyer des données obsolètes et le basculement peut ne pas être aussi à jour que prévu. Les causes habituelles sont simples à nommer mais faciles à mal interpréter : récupération lente des journaux de relais, application lente des transactions, disques surchargés, transactions sources longues, dérive de schéma ou paramètres de réplication qui ne correspondent pas à votre charge de travail en écriture.

Ce guide passe en revue les vérifications qui comptent généralement en premier : l'état du réplica, les E/S, les transactions longues, la dérive de schéma et les paramètres d'application parallèle.

Comprendre brièvement la réplication MySQL

Avant de se lancer dans le dépannage, il est utile de comprendre le flux de base de la réplication MySQL :

1. Journal binaire (Binlog) sur la source : Toutes les instructions de modification de données (DML) et les modifications de schéma (DDL) sur le serveur source sont enregistrées dans son journal binaire. Ce journal sert d'enregistrement chronologique de toutes les modifications.
2. Thread d'E/S sur le réplica : Un thread d'E/S dédié sur le réplica se connecte au serveur source et demande les événements du journal binaire. Il copie ensuite ces événements dans un fichier local sur le réplica appelé le journal de relais.
3. Thread SQL sur le réplica : Un autre thread dédié sur le réplica, le thread SQL, lit les événements du journal de relais et les exécute sur la base de données du réplica, appliquant les modifications pour garantir sa synchronisation avec la source.

Le retard de réplication se produit lorsque le thread d'E/S ne peut pas suivre le rythme de récupération des événements de la source, ou plus couramment, lorsque le thread SQL ne peut pas suivre le rythme d'application des événements du journal de relais.

Diagnostiquer le retard de réplication

L'outil principal pour vérifier l'état de la réplication et le retard est la commande SHOW REPLICA STATUS (ou SHOW SLAVE STATUS dans les anciennes versions de MySQL) sur le serveur réplica.

SHOW REPLICA STATUS\G

Indicateurs clés à examiner dans la sortie :

• Replica_IO_Running ou l'ancien Slave_IO_Running : Doit être Yes, selon votre version de MySQL.
• Replica_SQL_Running ou l'ancien Slave_SQL_Running : Doit être Yes.
• Seconds_Behind_Source ou l'ancien Seconds_Behind_Master : Cela estime le retard en secondes en fonction des horodatages des événements. Une valeur supérieure à 0 indique un retard, mais ce n'est pas un compte des transactions non appliquées.
• Last_IO_Error : Toute erreur réseau ou d'E/S.
• Last_SQL_Error : Toute erreur rencontrée lors de l'application des événements.

Remarque importante sur les secondes de retard : Cette métrique est basée sur le temps, pas sur les transactions. Si la source valide une transaction volumineuse avec un horodatage d'événement plus ancien, le réplica peut signaler une valeur de retard importante pendant qu'il applique cette transaction. Cela ne vous indique pas combien de transactions sont en attente, alors associez-la à la taille du journal de relais, à l'état du thread d'application et aux métriques du serveur.

Pour une surveillance plus avancée, envisagez d'utiliser des outils comme Percona Monitoring and Management (PMM), Prometheus avec Grafana, ou d'autres solutions de surveillance spécifiques aux bases de données qui suivent les métriques de réplication au fil du temps.

Causes courantes et solutions pour le retard de réplication

Identifier la cause profonde est crucial. Voici les raisons les plus fréquentes du retard de réplication et leurs solutions correspondantes :

1. Latence réseau ou problèmes de bande passante

• Cause : Connexion réseau lente ou instable entre la source et le réplica, ou bande passante réseau insuffisante pour transférer rapidement les événements du journal binaire.
• Diagnostic : Secondes de retard élevées alors que le thread d'E/S du réplica est en cours d'exécution, mais Relay_Log_Space n'augmente pas de manière significative, ou entrées Last_IO_Error fréquentes liées à des problèmes réseau. Utilisez des outils de diagnostic réseau comme ping, mtr ou traceroute pour vérifier la latence et la perte de paquets.
• Solution :
  • Améliorer l'infrastructure réseau : Assurez des connexions stables et à haute bande passante entre vos serveurs.
  • Co-localiser les serveurs : Idéalement, la source et le réplica doivent être dans le même centre de données ou la même région cloud pour minimiser la latence.
  • Compression : Pour les liaisons à bande passante limitée, vérifiez les options de compression de connexion de réplication de votre version de MySQL. La compression peut réduire le trafic réseau, mais elle ajoute une surcharge CPU et ne remplace pas le placement des réplicas près de la source.

2. Goulots d'étranglement d'E/S sur le réplica

• Cause : Le sous-système de disque du réplica ne peut pas écrire les journaux de relais ou appliquer les modifications à ses fichiers de données assez rapidement. C'est particulièrement vrai si sync_binlog ou innodb_flush_log_at_trx_commit sont définis sur 1 (pour une durabilité maximale), ce qui provoque des vidages de disque fréquents.

• Diagnostic : iowait élevé dans la sortie de top ou vmstat sur le réplica, utilisation élevée du disque (iostat -x 1), et secondes de retard augmentant régulièrement. Les variables d'état MySQL comme Innodb_data_writes et Innodb_data_fsyncs peuvent également fournir des informations.

• Solution :

  • Stockage plus rapide : Passez à des disques SSD ou NVMe pour le réplica. Utilisez des configurations RAID appropriées (par exemple, RAID 10 pour les performances).
  • Ajuster les paramètres de durabilité (avec prudence !) :
    • innodb_flush_log_at_trx_commit : La valeur par défaut est 1 (la plus durable). La définir sur 2 (vidage vers le cache OS) ou 0 (vidage une fois par seconde) peut réduire considérablement les E/S mais risque une perte de données en cas de crash du réplica. Envisagez 0 ou 2 uniquement si le réplica n'est pas votre source de vérité principale et que vous pouvez vous permettre une certaine perte de données sur le réplica lui-même.
    • Si le réplica écrit également des journaux binaires, sync_binlog peut ajouter une surcharge de vidage sur le réplica. Le relâcher peut améliorer le débit, mais cela augmente également le risque de perdre des événements de journal binaire récents en cas de crash du serveur.

  # Exemple de paramètres /etc/my.cnf sur le réplica (à utiliser avec une extrême prudence)
  [mysqld]
  innodb_flush_log_at_trx_commit = 2 # Ou 0, selon la tolérance
  

3. Contention de ressources sur le réplica (CPU, mémoire)

• Cause : Le CPU ou la mémoire du serveur réplica est insuffisant pour traiter et appliquer les transactions entrantes, surtout s'il sert également des requêtes de lecture.
• Diagnostic : Utilisation élevée du CPU dans top ou htop, en particulier pour le processus mysqld, ou utilisation élevée de la mémoire. Les secondes de retard sont élevées et l'état du thread SQL du réplica peut montrer des instructions de longue durée.
• Solution :
  • Augmenter les ressources : Provisionnez plus de cœurs CPU et de RAM pour le serveur réplica.
  • Réplica dédié : Si possible, dédiez le réplica uniquement à la réplication et évitez de servir des requêtes de lecture lourdes à partir de celui-ci. Si les lectures sont nécessaires, assurez-vous qu'elles sont bien optimisées avec des index appropriés.
  • Optimiser les requêtes : Examinez et optimisez toutes les requêtes lentes s'exécutant sur le réplica qui pourraient entrer en contention pour les ressources avec le thread SQL.

4. Requêtes lentes ou transactions longues sur la source

• Cause : Une seule transaction très volumineuse ou de longue durée (par exemple, ALTER TABLE, UPDATE/DELETE massif sans LIMIT, grand LOAD DATA INFILE) sur la source peut bloquer le thread SQL sur le réplica pendant toute la durée, provoquant un retard important. Le réplica doit appliquer la transaction de la même manière qu'elle a été validée sur la source, ce qui peut prendre beaucoup de temps.
• Diagnostic : Les secondes de retard montrent des pics soudains et importants qui sont corrélés à des opérations spécifiques sur la source. Vérifiez le journal des requêtes lentes ou SHOW PROCESSLIST sur la source lors de ces événements.
• Solution :
  • Optimiser les requêtes source : Identifiez et optimisez les requêtes de longue durée sur la source. Ajoutez des index appropriés.
  • Opérations par lots : Décomposez les grandes instructions DELETE ou UPDATE en lots plus petits et gérables à l'aide de clauses LIMIT.
  • Modifications de schéma en ligne : Pour les opérations DDL, utilisez des outils comme pt-online-schema-change de Percona Toolkit pour effectuer des modifications de schéma non bloquantes, minimisant ainsi les perturbations de la réplication.

5. Réplication monothread (avant MySQL 5.7 ou configurations spécifiques)

• Cause : Dans les anciennes versions de MySQL, le thread SQL appliquait toutes les transactions de manière séquentielle, quel que soit le nombre de transactions parallèles sur la source. Si la source gère de nombreuses écritures simultanées, un seul thread SQL sur le réplica peut facilement devenir un goulot d'étranglement.

• Diagnostic : Secondes de retard élevées et l'état du thread SQL du réplica montre fréquemment une requête active, tandis que le CPU du réplica peut ne pas être complètement saturé sur tous les cœurs.

• Solution :

  • Réplication multithread : L'application parallèle peut aider lorsqu'un thread SQL de réplica ne peut pas suivre les écritures simultanées de la source. MySQL 5.6 a introduit le parallélisme basé sur la base de données, et les versions ultérieures ont ajouté une application parallèle basée sur l'horloge logique. Les versions plus récentes de MySQL utilisent la terminologie replica_parallel_workers, tandis que les configurations plus anciennes peuvent encore utiliser slave_parallel_workers.

  # Exemple de paramètres /etc/my.cnf sur le réplica pour MTS
  [mysqld]
  replica_parallel_workers = 4 # Commencez modestement, puis mesurez
  replica_parallel_type = LOGICAL_CLOCK
  replica_preserve_commit_order = ON # Utile lorsque l'ordre de validation est important pour les lectures
  

  • Redémarrer la réplication : Après avoir modifié les paramètres MTS, vous devrez redémarrer le thread SQL du réplica :

  STOP REPLICA;
  START REPLICA;
  

6. Schéma non optimisé ou index manquants sur le réplica

• Cause : Si le schéma du réplica est différent de celui de la source ou s'il manque des index essentiels, les requêtes appliquées par le thread SQL peuvent s'exécuter beaucoup plus lentement que sur la source. Cela peut se produire en raison d'une dérive de schéma ou de différences intentionnelles (par exemple, des index de rapport différents sur le réplica).
• Diagnostic : Similaire aux goulots d'étranglement CPU/E/S, mais des requêtes spécifiques dans l'état du thread SQL du réplica ou le journal des requêtes lentes sur le réplica peuvent indiquer le problème. Comparez les plans EXPLAIN pour des requêtes identiques sur la source et le réplica.
• Solution :
  • Cohérence du schéma : Assurez-vous que le réplica a un schéma identique et optimisé à celui de la source, y compris tous les index nécessaires.
  • Création d'index : Ajoutez les index manquants sur le réplica qui sont essentiels pour les performances des requêtes, à la fois pour les applications lisant à partir du réplica et pour le thread SQL lui-même.

7. Format du journal binaire (ROW vs. STATEMENT)

• Cause : La réplication basée sur STATEMENT peut être problématique car les instructions non déterministes (par exemple, utilisant NOW(), UUID()) peuvent produire des résultats différents sur le réplica, nécessitant une évaluation complexe du contexte, voire casser la réplication. La réplication basée sur ROW enregistre les modifications de ligne réelles, ce qui est généralement plus sûr et plus efficace pour les transactions complexes, bien qu'elle puisse générer des journaux binaires plus volumineux.

• Diagnostic : Messages Last_SQL_Error fréquents liés à des instructions non déterministes ou à une position de journal manquante ou à des erreurs de clé en double. SHOW VARIABLES LIKE 'binlog_format'.

• Solution :

  • Utiliser ROW ou MIXED : Généralement, binlog_format=ROW est recommandé pour la plupart des applications modernes pour sa fiabilité et son déterminisme. MIXED est un compromis qui utilise STATEMENT lorsque c'est sûr et ROW sinon.

  # Exemple de paramètre /etc/my.cnf sur la source
  [mysqld]
  binlog_format = ROW
  

  • Remarque : binlog_format peut être modifié au moment de l'exécution dans de nombreuses configurations MySQL, mais le changement du format de réplication sur une topologie de production doit être planifié avec soin. Assurez-vous que tous les réplicas et les modèles d'application sont compatibles avant de vous fier au nouveau format.

Bonnes pratiques pour prévenir le retard de réplication

Utilisez ces habitudes pour réduire les incidents de retard récurrents :

• Surveillance proactive : Mettez en œuvre une surveillance robuste des secondes de retard de réplication, des ressources du serveur (CPU, E/S, réseau) et de la taille du journal binaire. Configurez des alertes pour tout écart par rapport au comportement normal.
• Optimisation régulière : Examinez et optimisez régulièrement les requêtes lentes sur la source et le réplica. Assurez-vous que les index sont à jour et efficaces.
• Dimensionnement du matériel : Provisionnez des ressources matérielles suffisantes (CPU, RAM, stockage rapide) pour vos serveurs réplicas, en anticipant à la fois la charge de réplication et les éventuelles charges de travail de lecture qu'ils pourraient gérer.
• Opérations par lots : Éduquez les développeurs et les administrateurs sur les meilleures pratiques pour les modifications de données volumineuses, en encourageant le traitement par lots ou l'utilisation d'outils de modification de schéma en ligne.
• Tirer parti de GTID : Bien qu'il ne s'agisse pas d'une prévention directe du retard, les identifiants de transaction globaux (GTID) simplifient la gestion de la réplication, en particulier lors des basculements ou de la récupération après des ruptures de réplication, ce qui peut indirectement réduire les temps d'arrêt qui pourraient autrement provoquer un retard prolongé.
• Rester à jour : Maintenez vos versions MySQL raisonnablement à jour. Les versions plus récentes sont souvent accompagnées d'améliorations des performances et de fonctionnalités de réplication améliorées (comme des MTS plus avancés).

Conclusion

Traitez le retard de réplication MySQL comme un problème de file d'attente. Déterminez si le réplica est lent à récupérer les événements, lent à écrire les journaux de relais ou lent à appliquer les transactions. Corrigez ensuite la cause correspondante : placement réseau, stockage, transactions sources longues, index manquants ou paramètres d'application parallèle. Gardez des alertes sur le retard et les erreurs de réplication afin de détecter le prochain ralentissement avant que des lectures obsolètes ou des plans de basculement ne dépendent d'un réplica obsolète.