Come posso risolvere il ritardo di replica elevato di Amazon RDS per MySQL?

8 minuti di lettura
0

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

Breve descrizione

Poiché Amazon RDS per MySQL utilizza la replica asincrona, a volte la replica non può avanzare con l'istanza DB primaria e causa un ritardo di replica.

Per monitorare un ritardo di replica, utilizza una replica di lettura RDS per MySQL con replica basata sulla posizione del file di registro binario.

In Amazon CloudWatch, controlla il parametro ReplicaLag per Amazon RDS. Il parametro ReplicaLag riporta il valore del campo Seconds_Behind_Master del comando SHOW SLAVE STATUS.

Il campo Seconds_Behind_Master mostra la differenza del timestamp corrente sull'istanza database di replica. Mostra anche il timestamp originale registrato sull'istanza database primaria per l’evento che viene elaborato sull'istanza database di replica.

La replica 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 Thread di replica sul sito Web MySQL. Se si verifica un ritardo di replica, identifica se la replica IO_THREAD o la replica SQL_THREAD causa il ritardo. Dopodiché puoi identificare la causa principale del ritardo.

Risoluzione

Identifica il thread di replica in ritardo

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'output di esempio precedente, l'istanza database di origine o primaria scrive i log binari nel file mysql-bin.066552.

Esegui il comando SHOW SLAVE 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

Nota: nell’esempio di output precedente, il Master_Log_File: mysql-bin.066548 mostra che la replica IO_THREAD legge dal file di registro binario 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.

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

Se la replica SQL_THREAD causa ritardi, 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. Per 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 circa 2 ore.

Per ridurre al minimo il ritardo, puoi monitorare il log delle query lente sull'istanza primaria. Puoi anche ridurre le istruzioni di lunga durata in istruzioni o transazioni più piccole.

Classe dell’istanza database con dimensioni o spazio di archiviazione insufficienti

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

Per risolvere questo problema, assicurati che il tipo di istanza database della replica sia uguale o superiore all'istanza database primaria. Affinché la replica sia eseguita in modo efficace, ogni replica di lettura richiede la stessa quantità di risorse di elaborazione e di archiviazione dell'istanza database di origine. Per ulteriori informazioni, consulta Classi delle istanze database.

Query in parallelo eseguite sull'istanza database primaria

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

La replica multi-thread (parallela) è disponibile per MySQL 5.6 e versioni successive. Per ulteriori informazioni sulla replica multi-thread, consulta Opzioni e variabili di registrazione binaria sul sito Web MySQL.

Il processo di replica multi-thread può causare discontinuità nella replica. Per esempio, la replica multi-thread non è consigliabile quando si ignorano gli errori di replica perché è difficile identificare le transazioni che si saltano. Ciò può 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 si modifica il valore impostandolo su 0, si disattiva anche la capacità del server MySQL di sincronizzare il log binario sul disco. Invece, il sistema operativo salva 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

Il thread SQL esegue una scansione completa della tabella durante la replica nei seguenti scenari:

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

Questo perché il valore predefinito del parametro slave_rows_search_algorithms è TABLE_SCAN,INDEX_SCAN.

Per risolvere temporaneamente questo 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 nella creazione della replica

Amazon RDS esegue un’istantanea del database per creare una replica di lettura di un'istanza primaria MySQL. Quindi, Amazon RDS ripristina l’istantanea per creare una nuova istanza database (replica) e stabilisce una relazione di replica tra le due.

Dopo aver stabilito la relazione di 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. Così facendo, l’istantanea del database è un backup incrementale.

Quando ripristini una replica di lettura da un'istantanea, 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 di lettura, puoi eseguire operazioni che utilizzano scansioni complete della tabella. Per esempio, esegui mysqldump sulla tua replica di lettura per tabelle o database specifici in modo che Amazon RDS dia la 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 InnoDB “su richiesta”. La funzione di precaricamento della cache 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 perché Amazon RDS esegue il dump dello stato corrente del buffer pool delle istanze database primarie prima di creare la replica di lettura. Dopodiché puoi ricaricare il buffer pool dopo aver creato una replica di lettura.

Informazioni correlate

Utilizzo della replica MySQL in Amazon RDS

Utilizzo delle repliche di lettura MySQL