In alcuni casi e' necessario un intervento sulle applicazioni che debbono essere adatte ad
una configurazione in replica...
La funzionalita' di replica dati di
MySQL
e' semplice da configurare,
non richiede praticamente manutenzione,
e' molto flessibile
e non aggiunge carico al database Master.
E' percio' comprensibile che sia molto utilizzata.
La replicazione MySQL e' tipicamente asincrona, quindi le trasazioni occorse sul
master vengono applicate, con un breve ritardo, sugli slave.
Uno delle problematiche piu' importanti della replicazione e' quella verificarne il ritardo.
Questo documento descrive alcune tecniche per valutare il replication lag
ovvero il ritardo nella replica dei dati.
Per un'introduzione sugli argomenti trattati in questa pagina il documento MySQL Replication descrive in dettaglio la configurazione, la gestione, ... delle repliche MySQL.
Gli slave si controllano con il comando SHOW SLAVE STATUS\G
Il comando riporta molteplici informazioni...
La replicazione funziona se
sono attivi i due processi di slave per la raccolta dei binlog (Slave I/O Thread)
e la loro applicazione (Slave SQL Thread):
Utile e' anche Seconds_Behind_Master che riporta il ritardo dello slave nell'applicare i log rispetto al master!
Finito il documento?
Magari... in realta' il valore di Seconds_Behind_Master fornisce un'indicazione
che non e' sempre affidabile.
Si tratta comunque del punto di partenza utile in molti casi:
ad esempio per il monitoraggio.
Ma come facciamo ad avere un'indicazione piu' precisa ed affidabile? Continua a leggere!
Naturalmente possiamo scrivere un'applicazione che aggiorna i dati sul master
e verificare la scrittura sullo slave analizzando la differenza dei tempi tra i sistemi,
l'eventuale utilizzo di TZ, la latenza di rete, i dati, ...
Oppure possiamo utilizzare pt-heartbeat che e' uno degli ottimi tool distribuiti da
Percona
che fa esattamente quanto appena descritto:
Come visto nell'esempio pt-heartbeat e' di semplice utilizzo.
Sul master si lancia il demone di aggiornamento mentre sugli slave si controlla
il ritardo della replicazione.
Poiche' la valutazione e' "esterna" vengono valutati correttemente i ritardi
anche nel caso di configurazioni complesse in daisy-chain.
La prima colonna riporta il ritardo con la precisione del centesimo di secondo. I valori tra parentesi sono il ritardo medio su 1 minuto, 5 minuti e 15 minuti che e' la visualizzazione classica del Load Average su Unix.
pt-heartbeat puo' essere usato sia come test per verificare il funzionamento della replicazione appena configurata sia come monitoraggio su un sistema in produzione disponendo cosi' di una metrica affidabile e precisa sul ritardo della sincronizzazione.
Mi piacerebbe sapere come ridurre SEMPRE il ritardo della replicazione! In realta' non e' sempre possibile ridurre il ritardo della replica. Al di sotto di un certo limite non si puo' scendere per ragioni tecniche... pero' e' possibile dare qualche indicazione.
La banda e, sopratutto, la latenza di rete sono i limiti inferiori a cui tendere. Per conoscerli bastano i semplici comandi ping ed mtr per conoscere i valori di base ed una moltiplicazione per avere una stima dei tempi minimi.
La replica su MySQL e' una funzionalita' molto utilizzata e continuamente aggiornata. Ogni versione di MySQL introduce nuove funzionalita' sulla replicazione: conviene tenersi sempre aggiornati.
Dalla versione 5.5 di MySQL e' disponibile la replica sincrona. Con la replica sincona il master attende che almeno uno slave abbia raccolto la transazione prima di confermarla. La replica semi-sincrona non rende piu' veloci le repliche: al contrario rallenta le transazioni sul master. In termini relativi il risultato e' lo stesso. Nelle versioni successive la replica semi-sincrona e' stata ulteriormente migliorata riducendo la condizione in cui si verificano le phantom rows (righe visibili sull'Engine prima che la transazione sia stata confermata alla sessione) ed ottimizzando le prestazioni.
In alcuni casi e' necessario un intervento sulle applicazioni che debbono essere adatte ad
una configurazione in replica...
Se un'istruzione SQL richiede parecchio tempo sul master
questo introdurra' inevitabilmente un ritardo nella replica:
l'istruzione non potra' essere replicata prima di essere terminata!
In questo caso il trucco e' quello di non scrivere istruzioni SQL lente!
Ma se si debbono modificare 1.000.000 di record?
Realizzata come singola istruzione richiedera' un certo tempo...
Si puo' spezzare l'update in piu' comandi SQL di breve durata
utilizzando una qualche condizione basata su un indice
oppure effettuando un loop.
E' anche possibile utilizzare anche la clausola LIMIT ma attenzione...
va specificato l'ORDER BY per evitare di introdurre differenze nella replica.
E' tecnicamente piu' difficile da risolvere il caso di un OLTP/DWH
con migliaia di DML eseguite in parallelo in cui lo Slave ha un ritardo sempre crescente.
In questo caso l'unico modo di ridurre il ritardo nella replica e'...
rallentare il master!
Se anche attivando il MTS (Multi Threaded Slave) nella modalita'
piu' efficace (slave-parallel-type=LOGICAL_CLOCK) lo Slave non
segue il passo del Master e' possibile agire sul parametro
binlog_group_commit_sync_delay.
In pratica si introduce un ritardo in modo che piu' transazioni
vengano committate nello stesso momente
In realta' il througthput del Master resta praticamente lo stesso.
Risultano piu' thread attivi in stato query end per fare in modo
che i commit avvengano in gruppo consentendo un maggior parallelismo
sugli slave, ma il carico sul sistema non sale ed il numero di transazioni
al secondo puo' essere mantenuto.
La figura a destra riporta un esempio: si notano le migliaia di INSERT al secondo e l'effetto dell'aumento del parametro binlog_group_commit_sync_delay sul Master; il risultato sullo Slave e' stato che finalmente riusciva a tenere il passo!
Gli Slave, oltre che per l'HA, possono essere molto utili per ridurre il carico delle letture sul Master. Molte applicazioni MySQL prevedono due distinte connessioni al DB: quella in RW (Read Write), per effettuare le scritture e le letture delle funzioni OLTP, e quella in RO (Read Only), per le query piu' pesanti o la reportistica.
Per concludere questo paragrafo e' opportuno ricordare che qualche volta un DBA puo' essere utile... Master e Slave possono essere sistemi differenti e con differenti configurazioni (eg. RAM disponibile): un tuning approppriato puo' ridurre in modo significativo il ritardo della replica.
Non e' bello parlare male di qualcuno senza giustificazione.
Perche' Seconds_Behind_Master non e' affidabile?
Perche' e' il punto di vista dello slave!
E come la fisica relativistica ha dimostrato...
il tempo e' relativo e dipende dal punto di osservazione.
Se la connessione di rete tra il master e lo slave ha problemi
lo slave non riceve nulla, dal suo punto di vista
non ha nulla da fare e pensa di non essere in ritardo.
In realta' la replica e' in ritardo ma lo slave non lo sa.
Naturalmente la situazione verra' aggiornata ma fino ad
allora il ritardo riportato da Seconds_Behind_Master risultera' sempre pari a 0.
Nel caso in cui il thread di I/O dello slave sia rallentato il valore
di Seconds_Behind_Master puo' variare da 0 (quando il thread SQL ha applicato tutti i binlog ricevuti)
ad un valore molto alto (quando lo slave sta applicando i vecchi binlog che stanno lentamente arrivando).
Durante lo scarico dei redo bin spesso il valore di Seconds_Behind_Master oscilla tra zero e valori
molto alti.
Seconds_Behind_Master riporta un risultato in secondi... con le attuali tecnologie su un sistema con un carico normale l'ordine di grandezza e' quello dei millisecondi!
Se si utilizzano repliche in cascata il valore di Seconds_Behind_Master si riferisce solo al master intermedio cui si collega lo slave. La replica potrebbe essere rallentata o bloccata in un passo precedente e la statistica riporterebbe un valore errato e minore della realta'.
Gli esempi riportati dovrebbero aver chiarito perche' il valore di Seconds_Behind_Master non e' sempre affidabile ma ci sono anche casi in cui il valore e' alterato da errori di configurazione. Tra i piu' frequenti: la mancanza di sicronizzazione dei clock dei sistemi, l'utilizzo di TZ differenti, l'utilizzo di versioni particolarmente obsolete di MySQL o disallineate tra i server, ...
Il punto di vista di pt-heartbeat e' esterno alla replicazione, controlla l'arrivo dei dati generati dall'heartbeat attivo sul master ed ha una granularita' molto piu' elevata. E' pertanto molto piu' affidabile come metrica rispetto al valore di Seconds_Behind_Master.
Breaking news: MySQL 5.7!
Con la versione GA di MySQL 5.7
sono disponibili otto nuove viste (eg. replication_applier_status)
che riportano in notevole dettaglio la configurazione e lo stato della replicazione.
Tuttavia per quanto riguarda il nostro famigerato campo (cito testualmente la
documentazione ufficiale):
The Seconds_Behind_Master and ... are in to-be-decided status and are not preserved.
Anche se inaffidabile il risultato di Seconds_Behind_Master con il comando SHOW SLAVE STATUS \G resta il modo piu' comune di valutare il ritardo della replica.
Il risultato e' ancora piu' variabile se si utilizzano i multithreaded slaves introdotti nella versione 5.6 ed arricchiti della modalita' slave-parallel-type=LOGICAL_CLOCK nella versione 5.7.
Sarebbe comodo avere una query per valutare in modo approssimato il ritardo della replica sullo slave e che tenga conto dei multithreaded slaves. Purtroppo tale query non esiste ;-)
SELECT coalesce(max(PROCESSLIST_TIME), 0.1) lag
FROM performance_schema.threads
WHERE (NAME = 'thread/sql/slave_worker'
AND (PROCESSLIST_STATE IS NULL
OR PROCESSLIST_STATE != 'Waiting for an event from Coordinator') )
OR NAME = 'thread/sql/slave_sql';
Sono disponibili diversi complessi strumenti e metodi per analizzare in
dettaglio le prestazioni di un server MySQL...
ma raccogliere i dati fondamentali relativi alla replica e' molto facile!
La dimensione dei binlog e la frequenza di creazione sono il primo fondamentale elemento
e basta un ls -l per ottenerli.
Il dettaglio delle istruzioni SQL replicate e' ottenibile semplicemente con il comando mysqlbinlog se e' utilizzata la modalita' STATEMENT. Ecco come estrarre i 100 statement piu' utilizzati:
Come generare un ritardo nella replicazione per verificare il comportamento delle applicazioni?
CHANGE MASTER TO MASTER_DELAY = N; [NdE disponibile dalla versione 5.6]
Infine un Eco letterario... la replica arrivera' forse troppo tardi, in termini di millisecondi, forse troppo presto, in termini di ore.
Titolo: Valutare il ritardo della replica MySQL
Livello: Esperto
Data:
1 Aprile 2015
Versione: 1.0.4 - 1 Aprile 2016
Autore: mail [AT] meo.bogliolo.name