Come posso risolvere i problemi relativi al ritardo di replica elevato dei log binari con Amazon RDS per MySQL e Aurora MySQL?

9 minuti di lettura

Desidero sapere perché si verifica un ritardo di replica quando utilizzo Amazon Relational Database Service (Amazon RDS) per MySQL o Amazon Aurora MySQL.

Breve descrizione

Amazon RDS per MySQL utilizza la replica asincrona, e talvolta una replica è in ritardo rispetto all'istanza DB primaria. Per monitorare il ritardo di replica, utilizza una replica in lettura di Amazon RDS per MySQL con replica basata sulla posizione dei file di log binari.

Per verificare la metrica ReplicaLag per Amazon RDS, utilizza Amazon CloudWatch. La metrica ReplicaLag riporta il valore del campo Seconds_Behind_Master del comando SHOW SLAVE STATUS.

Per Aurora, la metrica AuroraBinLogReplicaLag misura il ritardo di replica tra i cluster di database Aurora che utilizzano log binari.

Nota: per le versioni di MySQL 8.0.22 e successive, il comando SHOW REPLICA STATUS ha sostituito il comando SHOW SLAVE STATUS. Per ulteriori informazioni, consulta SHOW SLAVE | REPLICA STATUS statement (Istruzione SHOW SLAVE | REPLICA STATUS) sul sito web MySQL.

Il campo Seconds_Behind_Master mostra il ritardo attuale in secondi dell'istanza database di replica rispetto all'istanza di origine. Mostra anche il valore originale registrato nell'istanza database primaria per l'evento che viene elaborato sull'istanza database di replica.

La replica di MySQL utilizza il dump del log binario, il ricevitore I/O di replica e i thread dell'applicatore SQL di replica. Per ulteriori informazioni sul funzionamento dei thread, consulta Replication Threadsx (Replication Threads) sul sito web MySQL. Se si verifica un ritardo nella replica, verifica se è causato dalla replica IO_THREAD o dalla replica SQL_THREAD. Dopodiché puoi identificare la causa principale del ritardo.

Risoluzione

Identifica il thread di replica in ritardo

Completa i seguenti passaggi:

Per identificare dove l'istanza database di origine o primaria scrive i log binari, esegui il comando SHOW MASTER STATUS sull'istanza database primaria:

mysql> SHOW MASTER STATUS;

Esempio di output:

+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin.066552           |      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Nota: nell'esempio di output precedente, l'istanza database di origine o primaria scrive i log binari nel file mysql-bin.066552.

Per individuare lo stato dell'output, esegui il comando SHOW SLAVE STATUS o SHOW REPLICA STATUS sull'istanza database di replica:
```
mysql> SHOW SLAVE STATUS\G;
```

Esempio di output 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

Nell'esempio di output precedente, Master_Log_File: mysql-bin.066548 mostra che la replica IO_THREAD legge dal file di log binari mysql-bin.066548. L'istanza database primaria scrive i log binari nel file mysql-bin.066552. La replica IO_THREAD è in ritardo di quattro log binari. Tuttavia, poiché il Relay_Master_Log_File è mysql-bin.066548, la replica SQL_THREAD legge dallo stesso file di IO_THREAD. La replica SQL_THREAD mantiene la velocità, ma la replica IO_THREAD è in ritardo.

Esempio di output 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'esempio di output precedente mostra che il file di registro dell'istanza primaria è mysql-bin.066552. IO_THREAD mantiene la velocità con l'istanza database primaria. Nell'output di replica, il thread SQL esegue il Relay_Master_Log_File: mysql-bin.066530. Di conseguenza, SQL_THREAD è in ritardo di 22 log binari.

Poiché IO_THREAD legge i log binari solo dall'istanza primaria o di origine, IO_THREAD di solito non causa grandi ritardi di replica. Tuttavia, la connettività e la latenza di rete possono influire sulla velocità di lettura tra i server. Un utilizzo elevato della larghezza di banda può causare un rallentamento delle prestazioni della replica IO_THREAD.

Se i ritardi sono causati dalla replica SQL_THREAD, utilizza i seguenti passaggi per risolvere il problema.

Query di scrittura di lunga durata sull'istanza primaria

Le query di scrittura di lunga durata sull'istanza database primaria che richiedono lo stesso tempo per essere eseguite sull'istanza database di replica possono aumentare il valore seconds_behind_master. Ad esempio, se una modifica all'istanza primaria richiede 1 ora, il ritardo è di 1 ora. Se il completamento della modifica richiede 1 ora anche sulla replica, il ritardo totale è di 2 ore.

Per ridurre al minimo il ritardo, monitora il log delle query lente dell'istanza principale e identifica le query che necessitano di ottimizzazione. Puoi anche ridurre le istruzioni di lunga durata in istruzioni o transazioni più piccole.

Per risolvere i problemi di prestazioni relativi al log binario, analizza gli eventi di attesa trovati in Approfondimenti sulle prestazioni per ulteriori informazioni. Puoi anche regolare i livelli di isolamento.

Classe di istanza database con dimensioni o spazio di archiviazione insufficienti

Se la classe di istanza database o la configurazione dell’archiviazione della replica è inferiore rispetto all'istanza primaria, la replica potrebbe essere rallentata da risorse insufficienti. La replica non è in grado di mantenere il numero di modifiche dell'istanza primaria.

Per risolvere il problema, assicurati che il tipo di istanza database della replica sia uguale o superiore all'istanza database primaria. Affinché le repliche siano eseguite in modo efficace, ogni replica in lettura richiede la stessa quantità di risorse di elaborazione e di archiviazione dell'istanza database di origine.

Query eseguite in parallelo sull'istanza database primaria

Per impostazione predefinita, la replica di MySQL è a thread singolo. Pertanto, quando esegui query in parallelo sull'istanza primaria, le query vengono eseguite sulla replica in ordine seriale. Se si crea in parallelo un volume elevato di scritture sull'istanza di origine, le scritture sulla replica in lettura utilizzano un solo SQL_THREAD per la serializzazione. Potrebbe dunque verificarsi un ritardo tra l'istanza database di origine e la replica in lettura.

Con le versioni di MySQL 8.0.27 e successive, il valore predefinito per replica_parallel_workers è 4. Questo valore indica che le repliche sono multi-thread per impostazione predefinita. Analogamente, per Aurora MySQL versione 3.04 e successive, la replica è multi-thread per impostazione predefinita, con il valore replica_parallel_workers impostato su 4. Puoi modificare questo parametro nel gruppo di parametri personalizzato.

Per ulteriori informazioni sulla replica multi-thread, consulta Binary Logging Options and Variables (Opzioni e variabili di registrazione binaria) sul sito web MySQL.

Il processo di replica multi-thread può causare discontinuità nella replica. Ad esempio, è difficile identificare le transazioni che salti. Quando utilizzi la replica multi-thread, non è consigliabile ignorare gli errori di replica. Ciò può infatti causare problemi di coerenza dei dati tra l'istanza database primaria e quella di replica.

Log binari sincronizzati sul disco dell'istanza database di replica

Quando si attivano i backup automatici sulla replica, si verifica un sovraccarico per la sincronizzazione dei log binari sul disco della replica. Il valore predefinito del parametro sync_binlog è impostato su 1. Se modifichi il valore impostandolo su 0, il server MySQL non può sincronizzare il log binario sul disco. Invece, il sistema operativo scarica occasionalmente i log binari su disco.

Per ridurre il sovraccarico delle prestazioni necessario per sincronizzare i log binari su disco a ogni commit, disattiva la sincronizzazione dei log binari. Qualora però si verificasse un'interruzione di corrente o se si bloccasse il sistema operativo, alcuni commit potrebbero non sincronizzarsi con i log binari. Questa mancanza di sincronizzazione può influire negativamente sulle funzionalità di ripristino point-in-time (PITR). Per ulteriori informazioni, consulta sync_binlog sul sito Web MySQL.

Binlog_format è impostato su ROW

Quando la replica si verifica nei seguenti scenari, il thread SQL esegue una scansione completa della tabella:

binlog_format nell'istanza database primaria è impostato su ROW.
La tabella di origine non ha una chiave primaria.

Questa scansione avviene perché il valore predefinito del parametro slave_rows_search_algorithms è TABLE_SCAN,INDEX_SCAN.

Per risolvere temporaneamente il problema, modifica l'algoritmo di ricerca in INDEX_SCAN,HASH_SCAN in modo da ridurre il sovraccarico causato della scansione completa della tabella. Per una soluzione più permanente, è consigliabile aggiungere una chiave primaria esplicita a ogni tabella.

Per ulteriori informazioni sul parametro slave-rows-search-algorithms, consulta slave_rows_search_algorithms sul sito web MySQL.

Ritardo di creazione della replica (applicabile per RDS MySQL)

Per creare una replica in lettura di un'istanza primaria MySQL, Amazon RDS acquisisce uno snapshot del database. Dopodiché Amazon RDS ripristina lo snapshot per creare una nuova istanza database (replica) e stabilisce una relazione di replica tra le due.

Una volta che Amazon RDS ha stabilito la replica, si verifica un ritardo quando Amazon RDS crea un backup dell'istanza database primaria. Per ridurre al minimo questo ritardo, crea un backup manuale prima di richiedere la creazione della replica. Lo snapshot del database diventa quindi un backup incrementale.

Quando ripristini una replica in lettura da uno snapshot, la replica non attende il trasferimento di tutti i dati dall'origine. L'istanza database di replica è disponibile per eseguire le operazioni sul database. Le istantanee esistenti di Amazon Elastic Block Store (Amazon EBS) creano un nuovo volume in background.

Nota: per le repliche di Amazon RDS per MySQL (volumi supportati da Amazon EBS), il ritardo di replica potrebbe inizialmente aumentare perché il caricamento lento può influire sulle prestazioni di replica.

Per ridurre gli effetti del lazy loading sulle tabelle per la nuova replica in lettura, puoi eseguire operazioni che utilizzano scansioni complete della tabella. Ad esempio, esegui mysqldump sulla replica in lettura per tabelle o database specifici in modo da dare priorità a tutti i dati delle tabelle di cui è stato eseguito il backup da Amazon Simple Storage Service (Amazon S3).

Puoi anche utilizzare la funzionalità di precaricamento della cache on demand di InnoDB per RSD MySQL. La funzione di precaricamento della cache di InnoDB salva lo stato del pool di buffer su disco in un file denominato ib_buffer_pool nella directory dei dati InnoDB. Le prestazioni migliorano con il minor carico perché Amazon RDS esegue il dump dello stato corrente del buffer pool dell'istanza database primaria prima di creare la replica in lettura. Dopodiché puoi ricaricare il buffer pool dopo aver creato una replica in lettura.

Effetti del ritardo di replica

Un ritardo di replica elevato nel cluster o nell'istanza di replica può causare l'accumulo di log binari nell'origine. Per prevenire possibili problemi, monitora e gestisci le dimensioni dei file dei log binari e l'utilizzo dello spazio su disco. Per RDS, puoi monitorare la metrica BinLogDiskUsage dell'istanza database di origine.

Configura il periodo di conservazione ottimale dei log binari per bilanciare le funzionalità di recupero point-in-time con l'utilizzo dello spazio di archiviazione in un'istanza RDS MySQL e in Aurora. Inoltre, per sfruttare i log binari per la replica e il recupero, è necessario comprendere le convenzioni di denominazione dei file dei log binari e il comportamento di rotazione. Per ottenere un elenco dei log disponibili, esegui il comando SHOW BINARY LOGS.