Résolution des problèmes de latence de réplication des journaux binaires pour Aurora MySQL

Cette section fournit des conseils sur le décalage de réplication du journal binaire pour Aurora MySQL configuré en tant que réplique du journal binaire. Il couvre l'architecture de réplication, la réplication parallèle, le suivi des dépendances, la surveillance et les meilleures pratiques de configuration.

La réplication de journaux binaires permet de répliquer les données entre les MySQL-compatible bases de données. La réplication des journaux binaires abordée dans cette section est asynchrone : la source n'attend pas les répliques pour confirmer l'application des modifications avant de valider les transactions. Cela ne s'applique pas à la réplication semi-synchrone. Dans la réplication semi-synchrone, la source attend qu'au moins une réplique accuse réception de la transaction avant de valider. Par exemple, les clusters de Multi-AZ bases de données pour Amazon RDS pour MySQL utilisent la réplication semi-synchrone, ce qui est hors de portée de cette section. La réplique maintient une connexion permanente à la source via le I/O thread pour diffuser en continu les événements du journal binaire. Cette section s'applique à toutes les topologies de réplication binlog où Aurora MySQL est la réplique. La source peut être un autre cluster Aurora MySQL, Amazon RDS pour MySQL, MySQL sur site ou MySQL sur Amazon EC2. Cette section s'applique également lorsque Aurora MySQL est la source de réplication vers une MySQL-compatible cible quelconque.

Vous êtes confronté à un pic de décalage en ce moment ? Ignorez les informations de base et commencez par Identifier le goulot d'étranglement lié au retard de réplication déterminer si le I/O thread ou le thread SQL est le goulot d'étranglement, puis suivez le lien correspondant à ce scénario : Résolution des problèmes de décalage des I/O threads ou. Résolution des problèmes de latence des threads SQL

Architecture de réplication MySQL

La réplication MySQL est mise en œuvre via des threads spécialisés :

Le processus de réplication fonctionne comme suit :

Identifier le goulot d'étranglement lié au retard de réplication

Le retard de réplication peut survenir dans deux domaines : le I/O thread ou le thread SQL. La première étape consiste à déterminer quel composant est en retard.

Source_Log_File:       mysql-bin.000045
Read_Source_Log_Pos:   524288000
Relay_Source_Log_File: mysql-bin.000045
Exec_Source_Log_Pos:   524200000

File:     mysql-bin.000045
Position: 1073741824

Résolution des problèmes de décalage des I/O threads

Le I/O thread est chargé de récupérer les événements du journal binaire depuis la source et de les écrire dans le journal du relais. Causes courantes et solutions :

Stratégies d'optimisation pour le décalage des I/O threads

Résolution des problèmes de latence des threads SQL

Le thread SQL est le goulot d'étranglement lorsque le I/O thread est rattrapé par la source mais qu'il Seconds_Behind_Source est en train de croître. Pour quantifier le décalage, surveillez sur une période de 15 minutes. Comparez le taux de génération du binlog sur la source (le Position delta deSHOW MASTER STATUS) avec le taux d'application des threads SQL sur la réplique (le Exec_Source_Log_Pos delta).

Causes courantes et solutions :

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

SELECT trx_id, trx_started, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE trx_rows_modified > 10000
ORDER BY trx_started;

SELECT database_name, table_name,
    MIN(last_update) AS oldest_stat_update,
    DATEDIFF(NOW(), MIN(last_update)) AS stats_age_in_days
FROM mysql.innodb_index_stats
WHERE database_name NOT IN ('mysql', 'sys')
GROUP BY database_name, table_name
HAVING stats_age_in_days > 7
ORDER BY stats_age_in_days DESC;

Réduisez la charge de travail d'application grâce à des filtres de réplication côté réplication

Aurora MySQL version 3.01.0 et versions supérieures prend en charge les filtres de réplication côté réplication. Utilisez-les pour ignorer les bases de données ou les tables non pertinentes lors de l'application, réduisant ainsi la charge de travail des threads SQL. Replica-side les filtres sont appliqués par le thread SQL : le I/O thread récupère toujours tous les événements du journal binaire depuis la source, de sorte que ces filtres réduisent uniquement le travail d'application, pas le transfert réseau. Contrairement aux filtres côté source (qui fonctionnent uniquement au niveau de la base de données), les filtres côté réplication prennent en charge la granularité au niveau de la table à l'aide de et. replicate-do-table replicate-ignore-table Pour de plus amples informations, veuillez consulter Configuration des filtres de réplication avec Aurora MySQL.

Multi-threaded réplication (MTR)

Avec MTR, la réplique applique des transactions indépendantes en parallèle. Il ne peut pas scinder une seule transaction importante en plusieurs parties parallèles. Le degré de parallélisme est limité par les informations de dépendance écrites par la source.

Lorsque MTR est activé (replica_parallel_workers >= 2), l'applicateur SQL est divisé en un thread de coordination et plusieurs threads de travail. Le coordinateur lit les transactions dans le journal du relais. Il examine les horodatages logiques (last_committedetsequence_number) que la source intègre dans chaque transaction. Il attribue ensuite des transactions indépendantes aux threads de travail disponibles pour une exécution parallèle. Deux transactions sont indépendantes si elles ne partagent pas de dépendances de données, c'est-à-dire si elles modifient des lignes différentes. La source détermine ces dépendances au moment de la validation à l'aide de la méthode de suivi des dépendances configurée et les enregistre dans le journal binaire. La réplique ne peut pas augmenter le parallélisme au-delà de ce que permet la source.

MTR est la valeur par défaut recommandée

Nous vous recommandons d'exécuter des répliques avec la fonction MTR activée. Le MTR est activé par défaut (replica_parallel_workers=4) dans MySQL 8.0.27 et versions supérieures, y compris dans les versions Aurora MySQL 3 basées sur ces versions. Sur les versions antérieures (Aurora MySQL 2.12.1 et versions supérieures, ou versions basées sur des versions de MySQL antérieures à 8.0.27), activez-le explicitement en le définissant sur une valeur égale ou supérieure replica_parallel_workers à 2. Le maintien de l'activation du MTR coûte peu lorsque le parallélisme n'est pas disponible et permet à la réplique d'en tirer parti chaque fois que la charge de travail le permet.

Le MTR ne réduit pas le décalage dans les cas suivants :

Suivi des dépendances par rapport à la source

La source détermine quelles transactions peuvent être appliquées en parallèle à l'aide du binlog_transaction_dependency_tracking paramètre. Valeur recommandée :WRITESET.

Type parallèle (replica_parallel_type)

Le replica_parallel_type paramètre détermine la manière dont les transactions sont distribuées entre les threads de travail. Deux options s’offrent à vous :

Configuration du MTR

Après avoir modifié les paramètres du logiciel de travail parallèle, redémarrez la réplication :

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Aurora-specific optimisations de réplication

Aurora MySQL fournit les fonctionnalités suivantes pour améliorer les performances de réplication des journaux binaires. Le tableau suivant récapitule la disponibilité, l'état par défaut et la manière d'activer chaque fonctionnalité.

Surveillance de la réplication parallèle

Utilisez les méthodes suivantes pour surveiller les performances de réplication et identifier les goulots d'étranglement en parallèle :

Schéma de performance

Tableaux clés pour le suivi du MTR :

Pour évaluer la répartition uniforme du travail entre les threads de travail, utilisez la requête suivante :

SELECT
  a.channel_name,
  rw1.thread_id AS replica_worker_thread_id,
  ts1.count_star AS number_of_trx_executed,
  ROUND((ts1.count_star / sum_count_star) * 100, 2) AS percent_of_trx_executed_by_worker
FROM (
  SELECT rw.channel_name, SUM(ts.count_star) AS sum_count_star
  FROM performance_schema.events_transactions_summary_by_thread_by_event_name AS ts
    JOIN performance_schema.replication_applier_status_by_worker AS rw
      ON ts.thread_id = rw.thread_id
  GROUP BY rw.channel_name
) AS a
JOIN performance_schema.replication_applier_status_by_worker AS rw1
  ON a.channel_name = rw1.channel_name
JOIN performance_schema.events_transactions_summary_by_thread_by_event_name AS ts1
  ON rw1.thread_id = ts1.thread_id;

Si un ou deux travailleurs gèrent la majorité des transactions, cela indique de fortes dépendances entre les transactions, ce qui limite le parallélisme. Envisagez de passer au suivi des dépendances WRITESET sur la source.

Si les positions du I/O thread et du thread SQL sont proches l'une de l'autre mais Seconds_Behind_Source continuent de croître, le thread de coordination lui-même peut constituer le goulot d'étranglement. Vérifiez les délais performance_schema.replication_applier_status_by_coordinator de mise en mémoire tampon. Un goulot d'étranglement du coordinateur indique généralement de fortes dépendances entre les transactions ou un thread de coordinateur unique surchargé qui ne peut pas distribuer les événements assez rapidement.

Statistiques des répliques multithread dans le journal des erreurs

Quand log_error_verbosity=3 (par défaut), Aurora MySQL écrit régulièrement des statistiques de répliques multithread dans le journal des erreurs MySQL. Vous pouvez consulter ces entrées en :

Pour localiser les entrées statistiques des répliques multithread dans le journal des erreurs, recherchez la chaîne illustrée dans l'exemple suivant. Le journal des erreurs MySQL utilise la terminologie héritée dans cette chaîne interne ; cette documentation utilise le terme préféré « réplique » dans l'ensemble de cette documentation.

Voici un exemple d'entrée du journal des erreurs :

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 7215200; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1203644200 waited (count) when Workers occupied = 1022462 waited when Workers occupied = 62349203500

Principaux domaines à analyser :

Exemples pratiques : diagnostic des goulots d'étranglement liés à l'application parallèle

Les exemples suivants montrent comment utiliser les sources de surveillance précédentes pour diagnostiquer les goulots d'étranglement courants liés aux applications parallèles. Chaque exemple part du même symptôme (Seconds_Behind_Sourceil augmente régulièrement et vous avez déjà confirmé que le thread SQL est le goulot d'étranglementIdentifier le goulot d'étranglement lié au retard de réplication) et utilise les statistiques des répliques multithreads et le schéma de performances pour déterminer l'action corrective.

Exemple 1 : les travailleurs sont sous-approvisionnés

En CloudWatch, la ReplicaLag métrique augmente régulièrement, passant de près de zéro à environ 400 secondes en 30 minutes. L'utilisation du processeur sur la réplique reste autour de 55 %, ce qui n'est pas le cas de la réplique CPU-bound.

Pour vérifier où se situe le décalage, exécutez SHOW REPLICA STATUS deux fois, à 60 secondes d'intervalle. Read_Source_Log_Posavance d'environ 1,2 Go alors qu'elle n'Exec_Source_Log_Posavance que d'environ 300 Mo. Le I/O thread suit le rythme de la source, mais le thread SQL prend du retard : le thread SQL constitue le goulot d'étranglement.

Le MTR est activé avecreplica_parallel_workers=4. Récupérez les statistiques de répliques multithread les plus récentes à partir du journal des erreurs (voirStatistiques des répliques multithread dans le journal des erreurs) :

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 123; events assigned = 9876544; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 8455000 waited (count) when Workers occupied = 2456789 waited when Workers occupied = 110293847000

Interprétez les champs clés :

Diagnostic : le coordinateur dispose régulièrement d'un plus grand nombre de transactions indépendantes prêtes à être soumises qu'il n'a de travailleurs pour les gérer. L'attente en cas de conflit d'horloge étant négligeable, le parallélisme est limité par le nombre de travailleurs, et non par les dépendances.

Action : replica_parallel_workers augmentez le nombre de processeurs virtuels du réplica (par exemple, 16 sur adb.r6g.4xlarge) et relancez la réplication :

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Vérification : une ligne de statistiques ultérieure indique que l'attente a presque disparu et ReplicaLag revient à moins de 5 secondes :

2026-05-10 15:02:14.882201 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 31840552; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 9120000 waited (count) when Workers occupied = 41233 waited when Workers occupied = 5980411000

« a attendu lorsque les travailleurs étaient occupés » est passé d'environ 110 secondes à environ 6 secondes, et le débit (événements attribués) a plus que triplé. Arrêtez d'augmenter le nombre de travailleurs lorsque l'utilisation du processeur approche les 80 % ou que le temps d'attente cesse de diminuer.

Exemple 2 : le suivi des dépendances COMMIT_ORDER sérialise une charge de travail à faible concurrence

La source est une instance Amazon RDS pour MySQL exécutant une charge de travail OLTP avec seulement 4 à 8 threads d'application actifs simultanément. La réplique est une db.r6g.4xlarge avec replica_parallel_workers=16 etreplica_parallel_type=LOGICAL_CLOCK. Malgré 16 employés, il reste ReplicaLag stable pendant environ 600 secondes et l'utilisation du processeur des répliques n'est que d'environ 20 %. Les travailleurs semblent inactifs.

Tout d'abord, vérifiez dans quelle mesure les transactions sont réparties de manière uniforme entre les employés à l'aide de la requête de répartition des travailleurs provenant de : Schéma de performance

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                1903556 |                             96.41 |
|              |                       46 |                  23104 |                              1.17 |
|              |                       47 |                  18995 |                              0.96 |
|              |                       48 |                  16720 |                              0.85 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

La sortie précédente montre les 4 premiers des 16 threads de travail. Un travailleur applique plus de 96 % des transactions tandis que les 15 autres sont presque inactifs (chacun des travailleurs restants a exécuté moins de 0,05 %). Apply est effectivement une série.

Ensuite, confirmez-le à l'aide des statistiques des répliques multithread dans le journal des erreurs :

2026-05-10 15:22:11.110432 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 2014500; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 98712340000 waited (count) when Workers occupied = 50122 waited when Workers occupied = 1209847000

Cela indique un problème de dépendance et non un problème de capacité. Vérifiez la méthode de suivi des dépendances sur la source  :

SELECT @@global.binlog_transaction_dependency_tracking;

+-------------------------------------------------+
| @@global.binlog_transaction_dependency_tracking |
+-------------------------------------------------+
| COMMIT_ORDER                                    |
+-------------------------------------------------+

Cause première : avecCOMMIT_ORDER, la source décide quelles transactions peuvent être exécutées en parallèle en fonction des transactions validées ensemble dans le même commit de groupe de journaux binaires. Dans cette charge de travail à faible simultanéité, seule une poignée de threads sont validés à la fois. Les validations de groupe sont donc minimes. Par conséquent, la source estampille presque chaque transaction avec une last_committed valeur égale à celle de la transaction précédentesequence_number, la marquant comme dépendante de la transaction précédente. La réplique doit ensuite les appliquer les unes après les autres, même si elles modifient des lignes totalement indépendantes. C'est pourquoi 15 des 16 travailleurs restent inactifs.

Action : bascule la source vers le suivi des dépendances au niveau des lignes, qui est indépendant du calendrier de validation. Configurez-le dynamiquement et conservez-le dans le groupe de paramètres du cluster (ou de la base de données) :

SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

WRITESETcalcule un hachage des lignes que chaque transaction modifie, de sorte que les transactions touchant différentes lignes reçoivent des horodatages indépendants et peuvent être appliquées en parallèle quel que soit le moment où elles ont été validées. Assurez-vous que toutes les tables possèdent des clés primaires, car WRITESET produit des jeux d'écriture vides pour les tables qui n'en ont pas (voir). Résolution des problèmes de latence des threads SQL Sur MySQL 8.4, WRITESET est le paramètre par défaut et ce paramètre n'existe plus.

Vérification : après la modification, la requête sur la répartition des travailleurs montre que le travail est réparti de manière égale entre tous les travailleurs (indiquant les 4 premiers sur 16 ; chaque travailleur gère désormais environ 6 % des transactions) :

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                 132540 |                              6.30 |
|              |                       46 |                 125610 |                              5.97 |
|              |                       47 |                 129774 |                              6.17 |
|              |                       48 |                 122901 |                              5.84 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

et les statistiques du journal des erreurs indiquent que les conflits d'horloge disparaissent lorsqu'ils ReplicaLag tombent presque à zéro :

2026-05-10 15:41:55.204713 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 19874100; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 412300000 waited (count) when Workers occupied = 88122 waited when Workers occupied = 7019847000

Les « conflits d'horloge en attente » sont passés d'environ 99 secondes à environ 0,4 seconde, le débit a été multiplié par dix et le goulot d'étranglement est passé des dépendances à la capacité de travail. À ce stade, vous pouvez ajuster le nombre de travailleurs comme dans l'exemple 1.

Exemple 3 : Une clé primaire manquante force le scan complet de la table sur la réplique

ReplicaLagaugmente au cours d'un traitement par lots nocturne qui s'exécute sur de grandes UPDATE quantités de DELETE relevés, puis se rétablit par la suite. Pendant le pic, le processeur de la réplique est modéré mais un travailleur semble bloqué.

Sur le réplica, exécutez SHOW PROCESSLIST et examinez les threads du programme de réplication. Un travailleur reste dans l'Updatingétat indiqué sur le même relevé pendant plusieurs secondes à la fois :

+----+-------------+------+---------+----------+------+
| Id | User        | db   | Command | State    | Time |
+----+-------------+------+---------+----------+------+
| 12 | system user | app  | Connect | Updating |   38 |
+----+-------------+------+---------+----------+------+

Vérifiez quelles tables répliquées ne possèdent pas de clé primaire à l'aide de la requête de détection provenant de Résolution des problèmes de latence des threads SQL :

+--------------+----------------+
| table_schema | table_name     |
+--------------+----------------+
| app          | events_archive |
+--------------+----------------+

Cause première : avec la journalisation ROW-based binaire, chaque ligne d'un DELETE événement UPDATE ou doit se trouver sur la réplique avant de pouvoir être appliquée. Si la table ne possède pas de clé primaire ou une clé unique NOT NULL, la réplique effectue une analyse complète de la table pour chaque ligne affectée. Sur un tableau de plusieurs millions de lignes, une opération par lots se transforme en millions de scans complets, et le collaborateur qui l'applique se bloque, bloquant la progression de l'application et augmentant le décalage.

Action : ajoutez une clé primaire à la table. Si vous ne pouvez pas définir de clé explicite immédiatement, activez les clés primaires invisibles générées (GIPK) en définissant le sql_generate_invisible_primary_key paramètre sur ON (disponible dans les versions d'Aurora MySQL 3 basées sur MySQL 8.0.30 et versions ultérieures), afin que les nouvelles tables reçoivent une clé primaire automatique (voir). Résolution des problèmes de latence des threads SQL

Vérification : après avoir ajouté la clé primaire, le worker ne s'attarde plusUpdating, le taux d'application du thread SQL se rétablit et ReplicaLag revient à la base de référence lors de la prochaine fenêtre de traitement par lots.

Exemple 4 : Une seule transaction importante ne peut pas être parallélisée

ReplicaLagsaute brusquement à une heure prévisible chaque jour, se maintient pendant plusieurs minutes, puis redescend à près de zéro. Le suivi des dépendances WRITESET est activé et toutes les tables ont des clés primaires. Les exemples précédents ne s'appliquent donc pas. replica_parallel_workers=16

Pendant la période de pointe, la requête de répartition des employés indique qu'un travailleur est occupé et que les autres sont inactifs, mais contrairement à l'exemple 2, le journal des erreurs n'indique ni conflits d'horloge élevés ni temps d'attente occupé par les travailleurs :

2026-05-10 02:13:40.551922 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 95; events assigned = 4120000; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1530000 waited (count) when Workers occupied = 980 waited when Workers occupied = 210440000

Les temps d'attente liés à la dépendance et les temps d'attente occupés par les travailleurs sont faibles, mais un seul travailleur est actif. Cela permet d'éliminer à la fois le goulot d'étranglement lié à la dépendance (exemple 2) et le goulot d'étranglement lié à la capacité des travailleurs (exemple 1).

Inspectez le travailleur occupé à performance_schema.replication_applier_status_by_worker l'intérieur ou courezSHOW PROCESSLIST. Un seul travailleur applique une transaction pendant toute la durée du pic. Sur la source, une tâche d'application exécute une instruction volumineuse, DELETE FROM orders WHERE created_at < '2025-01-01' affectant par exemple plusieurs millions de lignes, en une seule transaction.

Cause principale : le MTR met en parallèle des transactions indépendantes ; il ne peut pas répartir une seule transaction entre les travailleurs. Une transaction importante est appliquée par un seul travailleur, donc l'ajout de travailleurs, le passage à WRITESET ou l'ajout de clés primaires n'aide pas. Pour de plus amples informations, veuillez consulter Multi-threaded réplication (MTR).

Action : divisez la grande opération en lots plus petits sur la source (par exemple, supprimez en morceaux de quelques milliers de lignes par transaction, avec une brève pause entre les lots). Les petites transactions sont effectuées de manière indépendante, de sorte que la réplique peut les répartir entre les employés. Planifiez des tâches en masse pendant les périodes de faible trafic, dans la mesure du possible.

Vérification : une fois la tâche groupée, le ReplicaLag pic quotidien s'aplatit et la requête de répartition des employés affiche le lot réparti entre plusieurs travailleurs au lieu d'un seul.

Supervision des meilleures pratiques

Meilleures pratiques pour minimiser les délais de réplication

Les recommandations suivantes résument les principales mesures à prendre pour minimiser le retard de réplication. Pour obtenir des conseils détaillés sur chaque sujet, suivez les liens de référence.