Risoluzione dei problemi relativi al ritardo di replica dei log binari per Aurora MySQL

Questa sezione fornisce indicazioni sul ritardo di replica dei log binari per Aurora MySQL configurata come replica di log binario. Copre l'architettura di replica, la replica parallela, il monitoraggio delle dipendenze, il monitoraggio e le migliori pratiche di configurazione.

La replica binaria dei log replica i dati tra database. MySQL-compatible La replica del log binario trattata in questa sezione è asincrona: l'origine non attende che le repliche confermino l'applicazione delle modifiche prima di eseguire il commit delle transazioni. Ciò non si applica alla replica semisincrona. Nella replica semisincrona, l'origine attende che almeno una replica confermi la ricezione della transazione prima di effettuare il commit. Ad esempio, i cluster Multi-AZ DB per Amazon RDS for MySQL utilizzano la replica semisincrona, che non rientra nell'ambito di questa sezione. La replica mantiene una connessione persistente all'origine tramite il I/O thread per trasmettere continuamente eventi di log binari. Questa sezione si applica a tutte le topologie di replica binlog in cui Aurora MySQL è la replica. L'origine può essere un altro cluster Aurora MySQL, Amazon RDS per MySQL, MySQL locale o MySQL su Amazon EC2. Questa sezione si applica anche quando Aurora MySQL è l'origine che si replica su qualsiasi destinazione. MySQL-compatible

Stai riscontrando un picco di lag in questo momento? Salta il materiale di base e inizia con Identificazione del collo di bottiglia del ritardo di replica per determinare se il collo di bottiglia è il I/O thread o il thread SQL, quindi segui il link per quello scenario: o. Risoluzione dei problemi relativi I/O al ritardo del thread Risoluzione dei problemi relativi al ritardo dei thread SQL

Architettura di replica MySQL

La replica MySQL è implementata tramite thread specializzati:

Il processo di replica funziona come segue:

Identificazione del collo di bottiglia del ritardo di replica

Il ritardo di replica può verificarsi in due aree: il thread o il I/O thread SQL. Il primo passo è determinare quale componente è in ritardo.

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

Risoluzione dei problemi relativi I/O al ritardo del thread

Il I/O thread è responsabile del recupero degli eventi di log binari dall'origine e della loro scrittura nel log di inoltro. Cause e soluzioni comuni:

Strategie di ottimizzazione per il ritardo I/O del thread

Risoluzione dei problemi relativi al ritardo dei thread SQL

Il thread SQL è il collo di bottiglia quando il I/O thread è collegato all'origine ma è in crescita. Seconds_Behind_Source Per quantificare il ritardo, monitora una finestra di 15 minuti. Confronta il tasso di generazione del binlog sull'origine (il Position delta daSHOW MASTER STATUS) con il tasso di applicazione del thread SQL sulla replica (il Exec_Source_Log_Pos delta).

Cause e soluzioni comuni:

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;

Riduci il carico di lavoro delle applicazioni con filtri di replica lato replica

Aurora MySQL versione 3.01.0 e successive supporta i filtri di replica lato replica. Usali per saltare database o tabelle irrilevanti durante l'applicazione, riducendo il carico di lavoro dei thread SQL. Replica-side i filtri vengono applicati dal thread SQL: il I/O thread recupera comunque tutti gli eventi di log binari dall'origine, quindi questi filtri riducono solo il lavoro di applicazione, non il trasferimento di rete. A differenza dei filtri sul lato sorgente (che funzionano solo a livello di database), i filtri lato replica supportano la granularità a livello di tabella utilizzando e. replicate-do-table replicate-ignore-table Per ulteriori informazioni, consulta Configurazione dei filtri di replica con Aurora MySQL.

Multi-threaded replica (MTR)

Con MTR, la replica applica transazioni indipendenti in parallelo. Non può suddividere una singola transazione di grandi dimensioni in parti parallele. Il grado di parallelismo è limitato dalle informazioni sulle dipendenze scritte dalla fonte.

Quando MTR è abilitato (replica_parallel_workers >= 2), l'applicatore SQL viene suddiviso in un thread coordinatore e più thread di lavoro. Il coordinatore legge le transazioni dal log di inoltro. Esamina i timestamp logici (last_committedesequence_number) che la fonte incorpora in ogni transazione. Quindi assegna transazioni indipendenti ai thread di lavoro disponibili per l'esecuzione parallela. Due transazioni sono indipendenti se non condividono le dipendenze dei dati, ovvero se modificano righe diverse. L'origine determina queste dipendenze al momento del commit utilizzando il metodo di tracciamento delle dipendenze configurato e le registra nel registro binario. La replica non può aumentare il parallelismo oltre quanto consentito dalla fonte.

MTR è l'impostazione predefinita consigliata

Consigliamo di eseguire le repliche con MTR abilitato. MTR è abilitato di default (replica_parallel_workers=4) in MySQL 8.0.27 e versioni successive, incluse le versioni Aurora MySQL 3 basate su tali versioni. Nelle versioni precedenti (Aurora MySQL 2.12.1 e successive o versioni basate su versioni di MySQL precedenti alla 8.0.27), abilitalo esplicitamente impostando un valore pari o superiore a 2. replica_parallel_workers Mantenere MTR abilitato costa poco quando il parallelismo non è disponibile e consente alla replica di sfruttarlo ogni volta che il carico di lavoro lo consente.

L'MTR non riduce il ritardo nei seguenti casi:

Tracciamento delle dipendenze dall'origine

La fonte determina quali transazioni possono essere applicate in parallelo utilizzando il binlog_transaction_dependency_tracking parametro. Valore consigliato:WRITESET.

Tipo parallelo (replica_parallel_type)

Il replica_parallel_type parametro determina la modalità di distribuzione delle transazioni tra i thread di lavoro. Sono disponibili due opzioni:

Configurazione MTR

Dopo aver modificato le impostazioni dei lavoratori paralleli, riavvia la replica:

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Aurora-specific ottimizzazioni della replica

Aurora MySQL offre le seguenti funzionalità per migliorare le prestazioni di replica dei log binari. La tabella seguente riassume la disponibilità, lo stato predefinito e come abilitare ciascuna funzionalità.

Monitoraggio della replica parallela

Utilizza i seguenti metodi per monitorare le prestazioni di replica e identificare parallelamente i colli di bottiglia:

Schema delle prestazioni

Tabelle chiave per il monitoraggio dell'MTR:

Per valutare la distribuzione uniforme del lavoro tra i thread di lavoro, utilizzate la seguente query:

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;

Se uno o due lavoratori gestiscono la maggior parte delle transazioni, ciò indica un'elevata dipendenza dalle transazioni che limita il parallelismo. Prendi in considerazione la possibilità di passare al monitoraggio delle dipendenze di WRITESET sull'origine.

Se le posizioni del I/O thread e del thread SQL sono vicine l'una all'altra ma Seconds_Behind_Source sono ancora in crescita, il collo di bottiglia potrebbe essere il thread coordinatore stesso. Verifica la presenza di ritardi nel performance_schema.replication_applier_status_by_coordinator buffering. Un collo di bottiglia del coordinatore indica in genere forti dipendenze tra transazioni o un singolo thread di coordinatore sovraccarico che non è in grado di distribuire gli eventi con sufficiente rapidità.

Registro degli errori: statistiche sulle repliche multi-thread

Quando log_error_verbosity=3 (impostazione predefinita), Aurora MySQL scrive periodicamente le statistiche delle repliche multithread nel log degli errori di MySQL. È possibile visualizzare queste voci tramite:

Per individuare le voci delle statistiche delle repliche multithread nel log degli errori, cercate la stringa mostrata nell'esempio seguente. Il log degli errori di MySQL utilizza una terminologia precedente in questa stringa interna; in questa documentazione viene utilizzato il termine preferito «replica» in tutte le sue parti.

Di seguito è riportato un esempio di voce nel log degli errori:

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

Campi chiave da analizzare:

Esempi pratici: diagnosi delle strozzature applicate in parallelo

Gli esempi seguenti mostrano come utilizzare le fonti di monitoraggio precedenti per diagnosticare i più comuni colli di bottiglia nelle applicazioni parallele. Ogni esempio parte dallo stesso sintomo (è in costante crescita ed Seconds_Behind_Source è già stato confermato che il thread SQL è il collo di bottigliaIdentificazione del collo di bottiglia del ritardo di replica) e utilizza le statistiche delle repliche multithread e lo schema delle prestazioni per determinare l'azione correttiva.

Esempio 1: i lavoratori non dispongono di risorse sufficienti

In CloudWatch, la ReplicaLag metrica sale costantemente da quasi zero a circa 400 secondi nell'arco di 30 minuti. L'utilizzo della CPU sulla replica rimane intorno al 55%, quindi la replica no. CPU-bound

Per confermare dove si trova il ritardo, esegui SHOW REPLICA STATUS due volte, a 60 secondi di distanza. Read_Source_Log_Posavanza di circa 1,2 GB mentre Exec_Source_Log_Pos avanza solo di circa 300 MB. Il I/O thread è al passo con i sorgenti, ma il thread SQL è in ritardo: il thread SQL è il collo di bottiglia.

MTR è abilitato con. replica_parallel_workers=4 Recupera le statistiche di replica multithread più recenti dal registro degli errori (vedi): Registro degli errori: statistiche sulle repliche multi-thread

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

Interpreta i campi chiave:

Diagnosi: il coordinatore ha sempre più transazioni indipendenti pronte per essere applicate che dipendenti per eseguirle. Poiché l'attesa in caso di conflitto temporale è trascurabile, il parallelismo è limitato dal numero di lavoratori, non dalle dipendenze.

Azione: aumentare il numero replica_parallel_workers di vCPU della replica (ad esempio, 16 su a) e riavviare la replica: db.r6g.4xlarge

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Verifica: una riga statistica successiva mostra che l'attesa è quasi scomparsa e ReplicaLag ritorna a meno di 5 secondi:

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

l'espressione «attesa quando i lavoratori erano occupati» è scesa da circa 110 secondi a circa 6 secondi e la produttività (eventi assegnati) è più che triplicata. Smetti di aumentare il numero di dipendenti quando l'utilizzo della CPU si avvicina all'80% o l'attesa smette di diminuire.

Esempio 2: il monitoraggio delle dipendenze COMMIT_ORDER serializza un carico di lavoro a bassa concorrenza

L'origine è un'istanza Amazon RDS per MySQL che esegue un carico di lavoro OLTP con solo 4-8 thread di applicazione che eseguono il commit simultaneo. La replica è una db.r6g.4xlarge con e. replica_parallel_workers=16 replica_parallel_type=LOGICAL_CLOCK Nonostante i 16 dipendenti, ReplicaLag rimane stabile per circa 600 secondi e l'utilizzo della CPU di replica è solo del 20% circa: i lavoratori sembrano inattivi.

Innanzitutto, controlla in che modo le transazioni vengono distribuite uniformemente tra i lavoratori utilizzando la query di distribuzione dei lavoratori di: Schema delle prestazioni

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

L'output precedente mostra i primi 4 dei 16 thread di lavoro. Un lavoratore applica oltre il 96% delle transazioni mentre gli altri 15 sono quasi inattivi (ciascuno dei lavoratori rimanenti ha eseguito meno dello 0,05%). Apply è effettivamente seriale.

Quindi, confermalo con le statistiche di replica multithread nel registro degli errori:

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

Ciò indica un problema di dipendenza, non un problema di capacità. Controlla il metodo di tracciamento delle dipendenze sulla fonte:

SELECT @@global.binlog_transaction_dependency_tracking;

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

Causa principale: conCOMMIT_ORDER, la fonte decide quali transazioni possono essere eseguite in parallelo in base alle transazioni eseguite insieme nello stesso gruppo di log binario. In questo carico di lavoro a bassa concorrenza, solo una manciata di thread si esegue il commit alla volta, quindi i commit di gruppo sono esigui. Di conseguenza, la fonte contrassegna quasi tutte le transazioni con un last_committed valore uguale a quello della transazione precedentesequence_number, contrassegnandole come dipendenti da quella precedente. La replica deve quindi applicarli uno dopo l'altro, anche se modificano righe completamente indipendenti, motivo per cui 15 lavoratori su 16 rimangono inattivi.

Azione: passa dall'origine al monitoraggio delle dipendenze a livello di riga, che è indipendente dalla tempistica di commit. Impostalo in modo dinamico e conservalo nel gruppo di parametri del cluster (o DB):

SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

WRITESETcalcola un hash delle righe modificate da ogni transazione, in modo che le transazioni che toccano righe diverse ricevano timestamp indipendenti e possano essere applicate in parallelo indipendentemente dal momento in cui sono state eseguite il commit. Assicurati che tutte le tabelle abbiano chiavi primarie, perché WRITESET produce set di scrittura vuoti per le tabelle che ne sono prive (vedi). Risoluzione dei problemi relativi al ritardo dei thread SQL In MySQL 8.4, WRITESET è l'impostazione predefinita e questo parametro non esiste più.

Verifica: dopo la modifica, la query sulla distribuzione dei lavoratori mostra il lavoro distribuito uniformemente tra tutti i lavoratori (mostrando i primi 4 di 16; ogni lavoratore ora gestisce circa il 6% delle transazioni):

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

e le statistiche del registro degli errori mostrano che i conflitti di clock si riducono mentre ReplicaLag scendono quasi a zero:

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

I «conflitti in attesa» sono scesi da circa 99 secondi a circa 0,4 secondi, la produttività è aumentata di circa dieci volte e il collo di bottiglia è passato dalle dipendenze alla capacità dei lavoratori: a questo punto è possibile regolare il numero di lavoratori come nell'Esempio 1.

Esempio 3: una chiave primaria mancante impone la scansione completa della tabella sulla replica

ReplicaLagsi verifica un picco durante un processo batch notturno di grandi dimensioni UPDATE e DELETE istruzioni, per poi essere ripristinato in seguito. Durante il picco, la CPU di replica è moderata ma un lavoratore appare bloccato.

Sulla replica, esegui SHOW PROCESSLIST e controlla i thread di lavoro della replica. Un lavoratore rimane nello Updating stato della stessa dichiarazione per molti secondi alla volta:

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

Verifica quali tabelle replicate sono prive di una chiave primaria utilizzando la query di rilevamento diRisoluzione dei problemi relativi al ritardo dei thread SQL:

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

Causa principale: con la registrazione ROW-based binaria, ogni riga di un DELETE evento UPDATE or deve trovarsi sulla replica prima di poter essere applicata. Se la tabella non ha una chiave primaria o una chiave NOT NULL univoca, la replica esegue una scansione completa della tabella per ogni riga interessata. In una tabella con milioni di righe, un'operazione batch si trasforma in milioni di scansioni complete e il lavoratore che la applica si blocca, bloccando l'avanzamento dell'applicazione e aumentando il ritardo.

Azione: aggiungere una chiave primaria alla tabella. Se non è possibile definire immediatamente una chiave esplicita, abilita Generated Invisible Primary Keys (GIPK) impostando il sql_generate_invisible_primary_key parametro su ON (disponibile nelle versioni Aurora MySQL 3 basate su MySQL 8.0.30 e successive), in modo che le nuove tabelle ricevano una chiave primaria automatica (vedi). Risoluzione dei problemi relativi al ritardo dei thread SQL

Verifica: dopo aver aggiunto la chiave primaria, il lavoratore non indugia più, la frequenza di applicazione del thread SQL viene ripristinata e Updating ritorna alla linea di base durante la finestra batch successiva. ReplicaLag

Esempio 4: una singola transazione di grandi dimensioni non può essere parallelizzata

ReplicaLagsalta bruscamente a un'ora prevedibile ogni giorno, rimane per diversi minuti, quindi torna quasi a zero. Il monitoraggio delle dipendenze di WRITESET è abilitato e tutte le tabelle hanno chiavi primarie, quindi gli esempi precedenti non si applicano. replica_parallel_workers=16

Durante il picco, la query sulla distribuzione dei lavoratori mostra un lavoratore occupato e il resto inattivo, ma a differenza dell'Esempio 2, il log degli errori non mostra né conflitti di clock elevati né attese occupate dai lavoratori:

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

Sia le attese per dipendenza che le attese occupate dai lavoratori sono basse, ma è attivo un solo lavoratore. Ciò esclude sia il problema delle dipendenze (Esempio 2) che quello della capacità dei lavoratori (Esempio 1).

Ispeziona il lavoratore occupato che entra o scappa. performance_schema.replication_applier_status_by_worker SHOW PROCESSLIST Un singolo lavoratore applica una transazione per l'intera durata del picco. All'origine, un processo applicativo esegue una dichiarazione di grandi dimensioni, ad esempio DELETE FROM orders WHERE created_at < '2025-01-01' che interessa diversi milioni di righe, come un'unica transazione.

Causa principale: MTR esegue una parallelizzazione tra transazioni indipendenti; non può suddividere una singola transazione tra i lavoratori. Una transazione di grandi dimensioni viene applicata esattamente da un lavoratore, quindi aggiungere lavoratori, passare a WRITESET o aggiungere chiavi primarie non aiuta. Per ulteriori informazioni, consulta Multi-threaded replica (MTR).

Azione: suddividi l'operazione di grandi dimensioni in batch più piccoli sull'origine (ad esempio, elimina in blocchi di poche migliaia di righe per transazione, con una breve pausa tra i batch). Le transazioni più piccole vengono eseguite indipendentemente, in modo che la replica possa distribuirle tra i lavoratori. Pianifica i lavori in blocco durante le finestre a traffico limitato, ove possibile.

Verifica: dopo aver raggruppato il lavoro in batch, il ReplicaLag picco giornaliero si appiattisce e la query sulla distribuzione dei lavoratori mostra il batch distribuito su più lavoratori anziché su uno solo.

Le migliori pratiche di monitoraggio

Procedure consigliate per ridurre al minimo il ritardo nella replica

I seguenti consigli riassumono le azioni chiave per ridurre al minimo il ritardo di replica. Per una guida dettagliata su ciascun argomento, segui i collegamenti ai riferimenti incrociati.