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

Breve descrizione

Amazon RDS per MySQL utilizza la replica asincrona. Ciò significa che a volte la replica non è in grado di stare al passo con l'istanza database primaria. Potrebbe quindi verificarsi un ritardo di replica.

Per monitorare il ritardo di replica, utilizza una replica di lettura Amazon RDS per MySQL con replica basata sulla posizione dei file di log binari.

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 principale per l'elaborazione degli eventi sull'istanza database di replica.

La replica MySQL funziona con tre thread: i thread Binlog Dump, IO_THREAD e SQL_THREAD. Per ulteriori informazioni sul funzionamento di questi thread, consulta la documentazione MySQL sui Thread di replica. Se si verifica un ritardo nella replica, identifica se la replica IO_THREAD o la replica SQL_THREAD causa il ritardo. Dopodiché è possibile identificare la causa principale del ritardo.

Soluzione

Per stabilire quale thread di replica è in ritardo, consulta i seguenti esempi:

1.    Esegui il comando SHOW MASTER STATUS sull'istanza database primaria, quindi esamina l'output. L'output è simile al seguente:

mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.066552|      521 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

2.    Esegui il comando SHOW SLAVE STATUS sull'istanza database di replica, quindi esamina l'output. L'output sarà simile al seguente esempio:

Esempio 1:

mysql> SHOW SLAVE STATUS\G;
*************************** 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 1, Master_Log_File: mysql-bin.066548 indica che il thread di replica IO_THREAD sta leggendo dal file di log binario mysql-bin.066548. L'istanza database primaria scrive i log binari nel file mysql-bin.066552. Questo output mostra che la replica IO_THREAD è in ritardo di quattro binlog. Tuttavia, il parametro Relay_Master_Log_File è mysql-bin.066548, il che indica che il thread di replica SQL_THREAD sta leggendo dallo stesso file di IO_THREAD. Ciò significa che il thread di replica SQL_THREAD sta tenendo il passo, mentre il thread di replica IO_THREAD è in ritardo.

Esempio 2:

mysql> SHOW SLAVE STATUS\G
*************************** 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 2 mostra che il file di log dell'istanza primaria è mysql-bin-changelog.066552. L'output mostra che IO_THREAD è al passo con l'istanza database primaria. Nell'output di replica, il thread SQL restituisce Relay_Master_Log_File: mysql-bin-changelog.066530. Di conseguenza, SQL_THREAD è in ritardo di 22 log binari.

Di solito IO_THREAD non causa grandi ritardi di replica, poiché IO_THREAD legge solamente i log binari dell'istanza primaria o di origine. Tuttavia, la connettività e la latenza di rete possono influire sulla velocità di lettura tra i server. Il thread di replica IO_THREAD potrebbe avere prestazioni inferiori in presenza di un elevato utilizzo della larghezza di banda.

Se la replica SQL\ _THREAD è l'origine dei ritardi di replica, le seguenti situazioni potrebbero causare un ritardo:

• Query di lunga durata sull'istanza database primaria
• Classe di istanza database con dimensione o spazio di archiviazione insufficienti
• Query in parallelo eseguite sull'istanza database primaria
• Log binari sincronizzati sul disco dell'istanza database di replica
• Binlog_format sulla replica è impostato su ROW
• Ritardo nella creazione della replica

Query di lunga durata sull'istanza primaria

Le query di lunga durata sull'istanza database primaria che richiedono lo stesso tempo per essere eseguite sull'istanza database di replica possono aumentare il valore della variabile seconds_behind_master. Ad esempio, se si avvia una modifica sull'istanza primaria che richiede un'ora per essere eseguita, il ritardo sarà di un'ora. Se il completamento della modifica richiede anche un'ora sulla replica, il ritardo totale è di circa due ore al momento del completamento. Si tratta di un ritardo previsto, ma è possibile ridurlo al minimo monitorando il log delle query lente sull’istanza primaria. È anche possibile identificare le istruzioni a esecuzione prolungata per ridurre il ritardo. In seguito si suddividono le istruzioni a esecuzione prolungata in istruzioni o transazioni più piccole.

Classe di istanza database con dimensione 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. Questo perché la replica non riesce a tenere il passo con le modifiche apportate sull'istanza principale. Assicurati che il tipo di istanza database della replica sia uguale o superiore a quello dell'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 l’articolo Classi di istanze database.

Query in parallelo eseguite sull'istanza database primaria

Se esegui query in parallelo sul database primario, queste eseguono il commit sulla replica in ordine seriale. Questo perché per impostazione predefinita la replica di MySQL è a thread singolo (SQL_THREAD). Se un volume elevato di scritture sull'istanza database di origine avviene in parallelo, le scritture sulla replica di lettura vengono serializzate. Le scritture sulla replica di lettura utilizzano un singolo SQL\ _THREAD per serializzare. Ciò può determinare un ritardo tra l'istanza database di origine e la replica di lettura.

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

Il processo di replica multi-thread può causare discontinuità nella replica. Ad esempio, la replica multi-thread non è consigliabile quando si ignorano gli errori di replica, perché è difficile stabilire quali transazioni vengono ignorate. 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

L'attivazione dei backup automatici sulla replica potrebbe comportare un sovraccarico per la sincronizzazione dei log binari sul disco della replica. Il valore predefinito del parametro sync_binlog è impostato su 1. Se si sostituisce tale valore con 0, viene disattivata la sincronizzazione del log binario sul disco da parte del server MySQL. Invece di accedere al disco, il sistema operativo (OS) scarica occasionalmente sul disco i log binari.

Disattivando la sincronizzazione dei log binari si riduce il sovraccarico delle prestazioni che sarebbe causato dalla sincronizzazione dei log binari sul disco ad ogni commit. 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 la documentazione di MySQL relativa a sync_binlog.

Binlog_format è impostato su ROW

Il thread SQL esegue una scansione completa della tabella durante la replica quando questi due fattori sono true:

• Il binlog_format sull'istanza database primaria è impostato su ROW.
• Nella tabella di origine manca una chiave primaria.

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

Per risolvere questo problema nel breve periodo, modifica l'algoritmo di ricerca in INDEX_SCAN,HASH_SCAN per ridurre il sovraccarico causato della scansione completa della tabella. Sul lungo periodo è consigliabile aggiungere una chiave primaria esplicita a ciascuna tabella.

Per ulteriori informazioni sul parametro slave-rows-search-algorithms, consulta la documentazione MySQL su slave_rows_search_algorithms.

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.

Amazon RDS impiega del tempo per creare nuove repliche di lettura. Dopo aver stabilito la replica, si verifica un ritardo per la durata del tempo necessario per creare un backup dell'istanza del database principale. Per ridurre al minimo questo ritardo è possibile creare un backup manuale prima di richiedere la creazione della replica. Quindi, l’istantanea del database è un backup incrementale.

Quando si ripristina 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. Il nuovo volume viene creato a partire dai caricamenti in background di istantanee di Amazon Elastic Block Store (Amazon EBS) esistenti.

Nota: per le repliche Amazon RDS per MySQL (volumi basati su EBS), il ritardo di replica può inizialmente aumentare. Questo perché l'effetto del caricamento lento può influire sulle prestazioni di replica.

Per contribuire a ridurre gli effetti del caricamento lento sulle tabelle per la replica di lettura appena creata, è possibile eseguire operazioni che prevedono scansioni complete della tabella. Ad esempio, eseguire un'operazione mysqldump sulla replica di lettura per tabelle o database specifici. Ciò consente ad Amazon RDS di assegnare priorità e scaricare tutti i dati delle tabelle di cui è stato eseguito il backup da Amazon Simple Storage Service (Amazon S3).

Inoltre, considerare l'utilizzo della funzione 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. Ciò può fornire vantaggi in termini di prestazioni scaricando lo stato corrente del pool di buffer dell'istanza database primaria prima di creare la replica di lettura. Quindi, 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