Nie ufaj Seconds_Behind_Master w replikacji MySQL

Replikacja w bazie MySQL jest bardzo przydatna w dużych środowiskach produkcyjnych. Mimo różnego rodzaju wad i przeszkód jakie nam stawia działa bardzo dobrze i wydajnie, zwłaszcza od wersji MySQL 5.6, gdzie pojawiła się wielowątkowa replikacja. Bardzo często wykorzystuje się bazę slave do wykonywania kopii zapasowych za pomocą mysqldump lub XtraBackup. Zazwyczaj aby sprawdzić czy baza slave nadąża za masterem wykorzystuje się do tego wartość Seconds_Behind_Master. Niestety potrafi ona płatać figle.

Wartość Seconds_Behind_Master, jak sama nazwa wskazuje, jest to ilość sekund określająca opóźnienie w przetwarzaniu danych przed bazę podrzędną w stosunku do głównej. Im większa jest ta wartość tym mamy większe opóźniania i niespójność danych w danej chwili.

Dla przykładu wykonując dump bazy danych możemy zobaczyć jak zmienia się ta wartość. Wartość ta rośnie ponieważ na czas wykonywania kopii zapasowej blokujemy zapis do tabel, przez co replikacja nie może wstawiać nowych rekordów. Wartość 0 zazwyczaj jest interpretowana jako fakt, że slave dogonił serwer master i wykonuje dokładnie te same zapytania.

Niestety nie jest to zawsze prawdą. Bardzo często w przypadku problemów sieciowych (duży ruch w sieci, wolne łącze, zrywanie połączenia) wartość tak może być przekłamana. Na przykład w momencie zerwania połączenia wartość ta będzie pokazywała 0 dopóki timeout wątekt I/O na bazie slave nie osiągnie wartości slave_net_timeout.

Kolejnym takim przykładem może być moment kiedy wątek SQL dogonił wątek I/O. W takim wypadku powinna pojawić się nam wartość 0. Natomiast kiedy I/O zaczyna pobierać nowe dane, Seconds_Behind_Master będzie wskazywał duże wartości nim wątek SQL ich nie przetworzy.

Opierając monitoring na wartości Seconds_Behind_Master możemy nie zauważyć sporego opóźnienia w przetwarzaniu zapytań. Duże wartości mogą pojawiać się okresowo, ale na zbyt krótki okres czasu aby monitoring zareagował. Warto wtedy sprawdzić, który plik logu znajduje się na serwerze master, a który jest obecnie przetwarzany przez bazę slave. W powyższym przypadku rozjazd był około 90 plików po 1GB danych. A to całkiem sporo.