Rozwiązywanie problemów z opóźnieniem replikacji w usłudze Azure Database for MySQL — serwer elastyczny
Uwaga
Ten artykuł zawiera odwołania do terminu slave (element podrzędny), który nie jest już używany przez firmę Microsoft. Po usunięciu terminu z oprogramowania usuniemy go z tego artykułu.
Funkcja repliki do odczytu umożliwia replikowanie danych z serwera usługi Azure Database for MySQL do serwera repliki tylko do odczytu. Obciążenia można skalować w poziomie, rozsyłając zapytania odczytu i raportowania z aplikacji do serwerów repliki. Ta konfiguracja zmniejsza obciążenie serwera źródłowego i zwiększa ogólną wydajność i opóźnienia aplikacji w miarę skalowania.
Repliki są aktualizowane asynchronicznie przy użyciu natywnej technologii replikacji opartej na pozycji pliku dziennika binarnego (binlog) aparatu MySQL. Aby uzyskać więcej informacji, zobacz MySQL binlog file position-based replication configuration overview (Omówienie konfiguracji replikacji opartej na położeniu w pliku MySQL).
Opóźnienie replikacji w pomocniczych replikach odczytu zależy od kilku czynników. Te czynniki obejmują, ale nie są ograniczone do:
- Opóźnienie sieci.
- Wolumin transakcji na serwerze źródłowym.
- Warstwa obliczeniowa serwera źródłowego i pomocniczego serwera repliki do odczytu.
- Zapytania uruchomione na serwerze źródłowym i serwerze pomocniczym.
W tym artykule dowiesz się, jak rozwiązywać problemy z opóźnieniem replikacji w usłudze Azure Database for MySQL. Uzyskasz również lepsze pojęcie o niektórych typowych przyczynach zwiększonego opóźnienia replikacji na serwerach repliki.
Pojęcia dotyczące replikacji
Po włączeniu dziennika binarnego serwer źródłowy zapisuje zatwierdzone transakcje w dzienniku binarnym. Dziennik binarny jest używany do replikacji. Jest ona domyślnie włączona dla wszystkich nowo zaaprowizowanych serwerów, które obsługują maksymalnie 16 TB miejsca do magazynowania. Na serwerach repliki na każdym serwerze repliki są uruchamiane dwa wątki. Jeden wątek to wątek we/wy, a drugi to wątek SQL:
- Wątek we/wy łączy się z serwerem źródłowym i żąda zaktualizowanych dzienników binarnych. Ten wątek otrzymuje aktualizacje dziennika binarnego. Te aktualizacje są zapisywane na serwerze repliki w dzienniku lokalnym nazywanym dziennikiem przekaźnika.
- Wątek SQL odczytuje dziennik przekaźnika, a następnie stosuje zmiany danych na serwerach repliki.
Monitorowanie opóźnienia replikacji
Usługa Azure Database for MySQL zapewnia metrykę opóźnienia replikacji w sekundach w usłudze Azure Monitor. Ta metryka jest dostępna tylko na serwerach repliki do odczytu. Jest obliczana przez metryki seconds_behind_master, która jest dostępna w usłudze MySQL.
Aby zrozumieć przyczynę zwiększonego opóźnienia replikacji, połącz się z serwerem repliki przy użyciu programu MySQL Workbench lub usługi Azure Cloud Shell. Następnie uruchom następujące polecenie.
Uwaga
W kodzie zastąp przykładowe wartości nazwą serwera repliki i nazwą użytkownika administratora. Nazwa użytkownika administratora wymaga @\<servername> usługi Azure Database for MySQL.
mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p
Oto jak wygląda środowisko w terminalu usługi Cloud Shell:
Requesting a Cloud Shell.Succeeded.
Connecting terminal...
Welcome to Azure Cloud Shell
Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell
user@Azure:~$mysql -h myreplicademoserver.mysql.database.azure.com -u myadmin@mydemoserver -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 64796
Server version: 5.6.42.0 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
W tym samym terminalu usługi Cloud Shell uruchom następujące polecenie:
mysql> SHOW SLAVE STATUS;
Oto typowe dane wyjściowe:
Dane wyjściowe zawierają wiele informacji. Zwykle należy skupić się tylko na wierszach opisanych w poniższej tabeli.
Jeśli Slave_IO_Running to Yes i Slave_SQL_Running to Yes, replikacja działa prawidłowo.
Następnie sprawdź Last_IO_Errno, Last_IO_Error, Last_SQL_Errno i Last_SQL_Error. Te pola zawierają numer błędu i komunikat o błędzie najnowszego błędu, który spowodował zatrzymanie wątku SQL. Liczba błędów 0 i pusty komunikat oznacza, że nie ma błędu. Zbadaj dowolną wartość błędu niezerowego, sprawdzając kod błędu w dokumentacji komunikatu o błędzie serwera MySQL.
Typowe scenariusze dotyczące dużego opóźnienia replikacji
W poniższych sekcjach opisano scenariusze, w których często występuje duże opóźnienie replikacji.
Opóźnienie sieci lub wysokie użycie procesora CPU na serwerze źródłowym
Jeśli widzisz następujące wartości, opóźnienie replikacji jest prawdopodobnie spowodowane dużym opóźnieniem sieci lub wysokim użyciem procesora CPU na serwerze źródłowym.
Slave_IO_State: Waiting for master to send event
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller than Master_Log_File, e.g. mysql-bin.00010
W takim przypadku wątek we/wy jest uruchomiony i czeka na serwerze źródłowym. Serwer źródłowy został już zapisany w binarnym pliku dziennika numer 20. Replika otrzymała tylko do pliku numer 10. Główne czynniki dotyczące dużego opóźnienia replikacji w tym scenariuszu to szybkość sieci lub wysokie wykorzystanie procesora CPU na serwerze źródłowym.
Na platformie Azure opóźnienie sieci w regionie zwykle może być mierzone w milisekundach. W różnych regionach opóźnienia wahają się od milisekund do sekund.
W większości przypadków opóźnienie połączenia między wątkami we/wy a serwerem źródłowym jest spowodowane wysokim użyciem procesora CPU na serwerze źródłowym. Wątki we/wy są przetwarzane powoli. Ten problem można wykryć za pomocą usługi Azure Monitor, aby sprawdzić użycie procesora CPU i liczbę współbieżnych połączeń na serwerze źródłowym.
Jeśli na serwerze źródłowym nie widzisz wysokiego użycia procesora CPU, problem może być opóźnieniem sieci. Jeśli opóźnienie sieci nagle jest nietypowo wysokie, sprawdź stronę stanu platformy Azure pod kątem znanych problemów lub awarii.
Duże wzrosty liczby transakcji na serwerze źródłowym
Jeśli widzisz następujące wartości, duża liczba transakcji na serwerze źródłowym prawdopodobnie powoduje opóźnienie replikacji.
Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller then Master_Log_File, e.g. mysql-bin.00010
Dane wyjściowe pokazują, że replika może pobrać dziennik binarny za serwerem źródłowym. Jednak wątek we/wy repliki wskazuje, że miejsce dziennika przekaźnika jest już pełne.
Szybkość sieci nie powoduje opóźnienia. Replika próbuje nadrobić zaległości. Jednak zaktualizowany rozmiar dziennika binarnego przekracza górny limit przestrzeni dziennika przekaźnika.
Aby rozwiązać ten problem, włącz dziennik wolnych zapytań na serwerze źródłowym. Użyj dzienników wolnych zapytań, aby zidentyfikować długotrwałe transakcje na serwerze źródłowym. Następnie dostosuj zidentyfikowane zapytania, aby zmniejszyć opóźnienie na serwerze.
Opóźnienie replikacji tego rodzaju jest często spowodowane obciążeniem danych na serwerze źródłowym. Gdy serwery źródłowe mają cotygodniowe lub miesięczne obciążenia danych, opóźnienie replikacji jest niestety nieuniknione. Serwery repliki w końcu nadrobią zaległości po zakończeniu ładowania danych na serwerze źródłowym.
Spowolnienie na serwerze repliki
Jeśli zaobserwujesz następujące wartości, problem może znajdować się na serwerze repliki.
Slave_IO_State: Waiting for master to send event
Master_Log_File: The binary log file sequence equals to Relay_Master_Log_File, e.g. mysql-bin.000191
Read_Master_Log_Pos: The position of master server written to the above file is larger than Relay_Log_Pos, e.g. 103978138
Relay_Master_Log_File: mysql-bin.000191
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: The position of slave reads from master binary log file is smaller than Read_Master_Log_Pos, e.g. 13468882
Seconds_Behind_Master: There is latency and the value here is greater than 0
W tym scenariuszu dane wyjściowe pokazują, że zarówno wątek we/wy, jak i wątek SQL działają prawidłowo. Replika odczytuje ten sam binarny plik dziennika, który zapisuje serwer źródłowy. Jednak niektóre opóźnienia na serwerze repliki odzwierciedlają tę samą transakcję z serwera źródłowego.
W poniższych sekcjach opisano typowe przyczyny tego rodzaju opóźnienia.
Brak klucza podstawowego ani unikatowego klucza w tabeli
Usługa Azure Database for MySQL używa replikacji opartej na wierszach. Serwer źródłowy zapisuje zdarzenia w dzienniku binarnym, rejestrując zmiany w poszczególnych wierszach tabeli. Następnie wątek SQL replikuje te zmiany do odpowiednich wierszy tabeli na serwerze repliki. Gdy w tabeli brakuje klucza podstawowego lub unikatowego klucza, wątek SQL skanuje wszystkie wiersze w tabeli docelowej w celu zastosowania zmian. Takie skanowanie może spowodować opóźnienie replikacji.
W programie MySQL klucz podstawowy jest skojarzonym indeksem zapewniającym szybką wydajność zapytań, ponieważ nie może zawierać wartości NULL. Jeśli używasz aparatu magazynu InnoDB, dane tabeli są fizycznie zorganizowane w celu wykonywania bardzo szybkich wyszukiwań i sortowania na podstawie klucza podstawowego.
Zalecamy dodanie klucza podstawowego w tabelach na serwerze źródłowym przed utworzeniem serwera repliki. Dodaj klucze podstawowe na serwerze źródłowym, a następnie utwórz ponownie repliki do odczytu, aby zwiększyć opóźnienie replikacji.
Użyj następującego zapytania, aby dowiedzieć się, które tabele nie mają klucza podstawowego na serwerze źródłowym:
select tab.table_schema as database_name, tab.table_name
from information_schema.tables tab left join
information_schema.table_constraints tco
on tab.table_schema = tco.table_schema
and tab.table_name = tco.table_name
and tco.constraint_type = 'PRIMARY KEY'
where tco.constraint_type is null
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys')
and tab.table_type = 'BASE TABLE'
order by tab.table_schema, tab.table_name;
Długotrwałe zapytania na serwerze repliki
Obciążenie na serwerze repliki może sprawić, że wątek SQL pozostaje w tyle za wątkiem we/wy. Długotrwałe zapytania na serwerze repliki są jedną z typowych przyczyn dużego opóźnienia replikacji. Aby rozwiązać ten problem, włącz dziennik wolnych zapytań na serwerze repliki.
Wolne zapytania mogą zwiększyć zużycie zasobów lub zwolnić serwer, aby replika nie mogła nadrobić zaległości z serwerem źródłowym. W tym scenariuszu dostosuj wolne zapytania. Szybsze zapytania uniemożliwiają zablokowanie wątku SQL i znaczne zwiększenie opóźnienia replikacji.
Zapytania DDL na serwerze źródłowym
Na serwerze źródłowym polecenie języka definicji danych (DDL), takie jak ALTER TABLE , może zająć dużo czasu. Gdy polecenie DDL jest uruchomione, tysiące innych zapytań może być uruchomionych równolegle na serwerze źródłowym.
Gdy język DDL jest replikowany, aby zapewnić spójność bazy danych, aparat MySQL uruchamia język DDL w jednym wątku replikacji. Podczas tego zadania wszystkie inne replikowane zapytania są blokowane i muszą czekać, aż operacja DDL zakończy się na serwerze repliki. Nawet operacje DDL online powodują to opóźnienie. Operacje DDL zwiększają opóźnienie replikacji.
Jeśli włączono dziennik wolnych zapytań na serwerze źródłowym, możesz wykryć ten problem z opóźnieniem, sprawdzając polecenie DDL uruchomione na serwerze źródłowym. Dzięki upuszczaniu indeksu, zmianie nazwy i utworzeniu można użyć algorytmu INPLACE dla tabeli ALTER TABLE. Może być konieczne skopiowanie danych tabeli i ponowne skompilowanie tabeli.
Zazwyczaj współbieżny kod DML jest obsługiwany dla algorytmu INPLACE. Możesz jednak krótko zastosować blokadę ekskluzywnych metadanych w tabeli podczas przygotowywania i uruchamiania operacji. Dlatego w przypadku instrukcji CREATE INDEX można użyć klauzul ALGORITHM i LOCK, aby wpłynąć na metodę kopiowania tabeli i poziom współbieżności do odczytu i zapisu. Nadal można zapobiec operacjom DML przez dodanie indeksu FULLTEXT lub indeksu PRZESTRZENNEgo.
Poniższy przykład tworzy indeks przy użyciu klauzul ALGORITHM i LOCK.
ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;
Niestety w przypadku instrukcji DDL wymagającej blokady nie można uniknąć opóźnienia replikacji. Aby zmniejszyć potencjalne skutki, wykonaj te typy operacji DDL poza godzinami szczytu, na przykład w nocy.
Obniżony serwer repliki
W usłudze Azure Database for MySQL repliki do odczytu używają tej samej konfiguracji serwera co serwer źródłowy. Konfigurację serwera repliki można zmienić po jego utworzeniu.
Jeśli serwer repliki zostanie obniżony, obciążenie może zużywać więcej zasobów, co z kolei może prowadzić do opóźnienia replikacji. Aby wykryć ten problem, użyj usługi Azure Monitor, aby sprawdzić użycie procesora CPU i pamięci serwera repliki.
W tym scenariuszu zalecamy zachowanie konfiguracji serwera repliki z wartościami równymi lub większymi niż wartości serwera źródłowego. Ta konfiguracja umożliwia repliki nadążanie za serwerem źródłowym.
Zwiększanie opóźnienia replikacji przez dostrajanie parametrów serwera źródłowego
W usłudze Azure Database for MySQL replikacja jest domyślnie zoptymalizowana pod kątem uruchamiania z równoległymi wątkami w replikach. Gdy obciążenia o wysokiej współbieżności na serwerze źródłowym powodują, że serwer repliki spadnie z tyłu, możesz poprawić opóźnienie replikacji, konfigurując parametr binlog_group_commit_sync_delay na serwerze źródłowym.
Parametr binlog_group_commit_sync_delay określa, ile mikrosekund oczekuje zatwierdzenie dziennika binarnego przed zsynchronizowaniem pliku dziennika binarnego. Zaletą tego parametru jest to, że zamiast natychmiast stosować każdą zatwierdzoną transakcję, serwer źródłowy wysyła zbiorcze aktualizacje dziennika binarnego. To opóźnienie zmniejsza liczbę operacji we/wy na replikę i pomaga zwiększyć wydajność.
Może być przydatne ustawienie parametru binlog_group_commit_sync_delay na 1000 lub tak. Następnie monitoruj opóźnienie replikacji. Ustaw ten parametr ostrożnie i użyj go tylko w przypadku obciążeń o wysokiej współbieżności.
Ważne
Na serwerze repliki zaleca się, aby parametr binlog_group_commit_sync_delay był 0. Jest to zalecane, ponieważ w przeciwieństwie do serwera źródłowego serwer repliki nie będzie miał wysokiej współbieżności i zwiększenie wartości dla binlog_group_commit_sync_delay na serwerze repliki może przypadkowo spowodować opóźnienie replikacji.
W przypadku obciążeń o niskiej współbieżności, które obejmują wiele transakcji pojedynczych, ustawienie binlog_group_commit_sync_delay może zwiększyć opóźnienie. Opóźnienie może wzrosnąć, ponieważ wątek we/wy czeka na zbiorcze aktualizacje dziennika binarnego, nawet jeśli tylko kilka transakcji zostanie zatwierdzonych.
Zaawansowane opcje rozwiązywania problemów
Jeśli za pomocą polecenia pokaż stan podrzędny nie udostępnia wystarczającej ilości informacji, aby rozwiązać problemy z opóźnieniem replikacji, spróbuj wyświetlić te dodatkowe opcje, aby dowiedzieć się, które procesy są aktywne lub oczekują.
Wyświetlanie tabeli wątków
W performance_schema.threads tabeli przedstawiono stan procesu. Proces ze stanem Oczekiwanie na blokadę lock_type wskazuje, że istnieje blokada w jednej z tabel, co uniemożliwia wątkowi replikacji aktualizowanie tabeli.
SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';
Aby uzyskać więcej informacji, zobacz Ogólne stany wątków.
Wyświetlanie tabeli replication_connection_status
Tabela performance_schema.replication_connection_status przedstawia bieżący stan wątku we/wy replikacji, który obsługuje połączenie repliki ze źródłem, i częściej się zmienia. Tabela zawiera wartości, które różnią się w czasie połączenia.
SELECT * FROM performance_schema.replication_connection_status;
Wyświetlanie tabeli replication_applier_status_by_worker
W performance_schema.replication_applier_status_by_worker tabeli przedstawiono stan wątków roboczych, Ostatnio widziana transakcja wraz z numerem ostatniego błędu i komunikatem, co ułatwia znalezienie transakcji, która ma problem i zidentyfikować główną przyczynę.
Poniższe polecenia można uruchomić w replikacji data-in, aby pominąć błędy lub transakcje:
az_replication_skip_counter
lub
az_replication_skip_gtid_transaction
SELECT * FROM performance_schema.replication_applier_status_by_worker;
Wyświetlanie instrukcji SHOW RELAYLOG EVENTS
Instrukcja show relaylog events pokazuje zdarzenia w dzienniku przekazywania repliki.
· W przypadku replikacji opartej na usłudze GITD (replika do odczytu) instrukcja zawiera plik transakcji GTID i binlog oraz jego położenie, można użyć mysqlbinlog, aby uzyskać zawartość i instrukcje są uruchamiane. · W przypadku replikacji pozycji binlogu MySQL (używanej do replikacji typu data-in) wyświetlane są instrukcje, które pomogą poznać, które transakcje tabeli są uruchamiane
Sprawdzanie danych wyjściowych monitora standardowego i monitora blokady bazy danych InnoDB
Możesz również spróbować sprawdzić dane wyjściowe monitora standardowego innoDB i monitora blokady, aby ułatwić rozwiązywanie blokad i zakleszczenia oraz zminimalizować opóźnienie replikacji. Monitor blokady jest taki sam jak monitor standardowy, z tą różnicą, że zawiera dodatkowe informacje o blokadzie. Aby wyświetlić te dodatkowe informacje dotyczące blokady i zakleszczenia, uruchom polecenie innodb status\G aparatu show.
Następne kroki
Zapoznaj się z omówieniem replikacji dziennika binlogu mySQL.