Řešení potíží s latencí replikace na flexibilním serveru Azure Database for MySQL

PLATÍ PRO: Flexibilní server Azure Database for MySQL – Jednoúčelový server Azure Database for MySQL

Funkce repliky pro čtení umožňuje replikovat data ze serveru Azure Database for MySQL na server repliky jen pro čtení. Úlohy můžete škálovat směrováním dotazů pro čtení a generování sestav z aplikace na servery replik. Toto nastavení snižuje tlak na zdrojový server a zlepšuje celkový výkon a latenci aplikace při škálování.

Repliky se aktualizují asynchronně pomocí technologie replikace na základě pozice souboru (binlog) nativního binárního protokolu (binlog) modulu MySQL. Další informace najdete v tématu Přehled konfigurace replikace na základě pozice souboru MySQL.

Prodleva replikace na sekundárních replikách čtení závisí na několika faktorech. Mezi tyto faktory patří mimo jiné:

• Latence sítě.
• Svazek transakcí na zdrojovém serveru.
• Úroveň výpočetních prostředků zdrojového serveru a sekundárního serveru repliky pro čtení
• Dotazy spuštěné na zdrojovém serveru a sekundárním serveru

V tomto článku se dozvíte, jak řešit potíže s latencí replikace ve službě Azure Database for MySQL. Získáte také lepší představu o některých běžných příčinách zvýšené latence replikace na serverech replik.

Koncepty replikace

Pokud je povolen binární protokol, zdrojový server zapíše potvrzené transakce do binárního protokolu. Binární protokol se používá pro replikaci. Ve výchozím nastavení je zapnutá pro všechny nově zřízené servery, které podporují až 16 TB úložiště. Na serverech replik se na každém serveru repliky spouští dvě vlákna. Jedno vlákno je vstupně-výstupní vlákno a druhé je vlákno SQL:

• Vlákno vstupně-výstupních operací se připojí ke zdrojovému serveru a vyžádá aktualizované binární protokoly. Toto vlákno obdrží aktualizace binárního protokolu. Tyto aktualizace se ukládají na server repliky v místním protokolu nazývaném předávací protokol.
• Vlákno SQL načte předávací protokol a pak použije změny dat na serverech repliky.

Monitorování latence replikace

Azure Database for MySQL poskytuje metriku prodlevy replikace v sekundách ve službě Azure Monitor. Tato metrika je dostupná jenom na serverech replik pro čtení. Počítá se podle metriky seconds_behind_master, která je dostupná v MySQL.

Pokud chcete zjistit příčinu zvýšené latence replikace, připojte se k serveru repliky pomocí Aplikace MySQL Workbench nebo Azure Cloud Shellu. Pak spusťte následující příkaz.

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p 

Prostředí vypadá v terminálu Cloud Shellu takto:

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>

Ve stejném terminálu Cloud Shell spusťte následující příkaz:

mysql> SHOW SLAVE STATUS;

Tady je typický výstup:

Výstup obsahuje mnoho informací. Za normálních okolností se musíte zaměřit jenom na řádky, které popisuje následující tabulka.

Pokud je Yes Slave_IO_Running a Slave_SQL_Running, Yesreplikace je v pořádku.

Dále zaškrtněte Last_IO_Errno, Last_IO_Error, Last_SQL_Errno a Last_SQL_Error. Tato pole zobrazují číslo chyby a chybovou zprávu nejnovější chyby, která způsobila zastavení vlákna SQL. Číslo 0 chyby a prázdná zpráva znamená, že neexistuje žádná chyba. Prozkoumejte libovolnou nenulovou chybovou hodnotu kontrolou kódu chyby v referenčních informacích k chybové zprávě serveru MySQL.

Běžné scénáře vysoké latence replikace

Následující části řeší scénáře, ve kterých je běžná vysoká latence replikace.

Latence sítě nebo vysoká spotřeba procesoru na zdrojovém serveru

Pokud se zobrazí následující hodnoty, je latence replikace pravděpodobně způsobena vysokou latencí sítě nebo vysokým využitím procesoru na zdrojovém serveru.

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

V tomto případě je spuštěné vstupně-výstupní vlákno a čeká na zdrojovém serveru. Zdrojový server již zapisoval do binárního souboru protokolu číslo 20. Replika přijala pouze číslo souboru 10. Primárními faktory pro vysokou latenci replikace v tomto scénáři jsou rychlost sítě nebo vysoké využití procesoru na zdrojovém serveru.

V Azure se latence sítě v rámci oblasti obvykle měří v milisekundách. V různých oblastech se latence pohybuje od milisekund po sekundy.

Ve většině případů je zpoždění připojení mezi vstupně-výstupními vlákny a zdrojovým serverem způsobeno vysokým využitím procesoru na zdrojovém serveru. Vlákna vstupně-výstupních operací se zpracovávají pomalu. Tento problém můžete zjistit pomocí služby Azure Monitor ke kontrole využití procesoru a počtu souběžných připojení na zdrojovém serveru.

Pokud na zdrojovém serveru nevidíte vysoké využití procesoru, může se jednat o latenci sítě. Pokud je latence sítě náhle neobvykle vysoká, zkontrolujte na stránce stavu Azure známé problémy nebo výpadky.

Velké nárůsty transakcí na zdrojovém serveru

Pokud se zobrazí následující hodnoty, pak velký nárůst transakcí na zdrojovém serveru pravděpodobně způsobuje latenci replikace.

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

Výstup ukazuje, že replika může načíst binární protokol za zdrojovým serverem. Vlákno vstupně-výstupních operací repliky ale znamená, že prostor přenosového protokolu je už plný.

Rychlost sítě nezpůsobuje zpoždění. Replika se pokouší dohnat. Aktualizovaná velikost binárního protokolu ale překračuje horní limit prostoru přenosového protokolu.

Pokud chcete tento problém vyřešit, povolte na zdrojovém serveru protokol pomalých dotazů . Protokoly pomalých dotazů slouží k identifikaci dlouhotrvajících transakcí na zdrojovém serveru. Potom vylaďte zjištěné dotazy, aby se snížila latence na serveru.

Latence replikace tohoto typu je obvykle způsobena zatížením dat na zdrojovém serveru. Pokud zdrojové servery mají týdenní nebo měsíční načítání dat, latence replikace bohužel není možná. Servery repliky se nakonec zachytí po dokončení načítání dat na zdrojovém serveru.

Pomalé na serveru repliky

Pokud zjistíte následující hodnoty, může být problém na serveru repliky.

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

V tomto scénáři výstup ukazuje, že vlákno vstupně-výstupních operací i vlákno SQL běží dobře. Replika načte stejný binární soubor protokolu, který zapisuje zdrojový server. Určitá latence na serveru repliky ale odráží stejnou transakci ze zdrojového serveru.

Následující části popisují běžné příčiny této latence.

V tabulce není žádný primární klíč ani jedinečný klíč.

Azure Database for MySQL používá replikaci založenou na řádcích. Zdrojový server zapisuje události do binárního protokolu a zaznamenává změny v jednotlivých řádcích tabulky. Vlákno SQL pak replikuje tyto změny do odpovídajících řádků tabulky na serveru repliky. Pokud tabulka nemá primární klíč nebo jedinečný klíč, vlákno SQL prohledá všechny řádky v cílové tabulce, aby změny použilo. Toto prohledávání může způsobit latenci replikace.

V MySQL je primární klíč přidružený index, který zajišťuje rychlý výkon dotazů, protože nemůže obsahovat hodnoty NULL. Pokud používáte modul úložiště InnoDB, data tabulky jsou fyzicky uspořádaná tak, aby řídila ultrarychlé vyhledávání a řazení na základě primárního klíče.

Před vytvořením serveru repliky doporučujeme přidat primární klíč do tabulek na zdrojovém serveru. Přidejte na zdrojový server primární klíče a pak znovu vytvořte repliky pro čtení, aby se zlepšila latence replikace.

Pomocí následujícího dotazu zjistíte, které tabulky chybí primární klíč na zdrojovém serveru:

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;

Dlouhotrvající dotazy na serveru repliky

Úloha na serveru repliky může zpožďovat vlákno SQL za vláknem vstupně-výstupních operací. Dlouhotrvající dotazy na serveru repliky jsou jednou z běžných příčin vysoké latence replikace. Pokud chcete tento problém vyřešit, povolte na serveru repliky protokol pomalých dotazů .

Pomalé dotazy můžou zvýšit spotřebu prostředků nebo zpomalit server, aby replika nemohla dohnat zdrojový server. V tomto scénáři vylaďte pomalé dotazy. Rychlejší dotazy zabraňují zablokování vlákna SQL a výrazně zvyšují latenci replikace.

Dotazy DDL na zdrojovém serveru

Na zdrojovém serveru může trvat dlouhou dobu příkaz ALTER TABLE DDL (Data Definition Language). Zatímco příkaz DDL běží, na zdrojovém serveru můžou běžet paralelně tisíce dalších dotazů.

Pokud se DDL replikuje, aby se zajistila konzistence databáze, modul MySQL spustí DDL v jediném vlákně replikace. Během této úlohy jsou všechny ostatní replikované dotazy blokované a musí počkat, až se operace DDL dokončí na serveru repliky. I online operace DDL způsobují toto zpoždění. Operace DDL zvyšují latenci replikace.

Pokud jste na zdrojovém serveru povolili protokol pomalých dotazů, můžete tento problém s latencí zjistit tak, že vyhledáte příkaz DDL, který běžel na zdrojovém serveru. Prostřednictvím vyřazení indexu, přejmenování a vytvoření můžete použít algoritmus INPLACE pro ALTER TABLE. Možná budete muset zkopírovat data tabulky a znovu vytvořit tabulku.

Pro algoritmus INPLACE se obvykle podporuje souběžné DML. Při přípravě a spuštění operace však můžete na tabulce krátce vzít výhradní zámek metadat. Proto pro příkaz CREATE INDEX můžete použít klauzule ALGORITHM a LOCK k ovlivnění metody kopírování tabulek a úroveň souběžnosti čtení a zápisu. Operace DML můžete přesto zabránit přidáním indexu FULLTEXT nebo indexu SPATIAL.

Následující příklad vytvoří index pomocí klauzulí ALGORITHM a LOCK.

ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;

U příkazu DDL, který vyžaduje zámek, se bohužel nedá vyhnout latenci replikace. Chcete-li snížit potenciální účinky, proveďte tyto typy operací DDL mimo špičku, například v noci.

Downgradovaný server repliky

Ve službě Azure Database for MySQL používají repliky pro čtení stejnou konfiguraci serveru jako zdrojový server. Konfiguraci serveru repliky můžete po vytvoření změnit.

Pokud je server repliky downgradovaný, může úloha využívat více prostředků, což může zase vést k latenci replikace. K detekci tohoto problému použijte Azure Monitor ke kontrole využití procesoru a paměti serveru repliky.

V tomto scénáři doporučujeme zachovat konfiguraci serveru repliky na hodnotách rovna nebo vyšší než hodnoty zdrojového serveru. Tato konfigurace umožňuje replikě udržovat krok se zdrojovým serverem.

Zlepšení latence replikace laděním parametrů zdrojového serveru

Ve výchozím nastavení je replikace ve službě Azure Database for MySQL optimalizovaná tak, aby běžela s paralelními vlákny na replikách. Pokud úlohy s vysokou souběžností na zdrojovém serveru způsobí, že server repliky klesne, můžete latenci replikace zlepšit konfigurací parametru binlog_group_commit_sync_delay na zdrojovém serveru.

Parametr binlog_group_commit_sync_delay určuje, kolik mikrosekund čeká potvrzení binárního protokolu před synchronizací souboru binárního protokolu. Výhodou tohoto parametru je, že místo okamžitého použití každé potvrzené transakce zdrojový server hromadně odesílá aktualizace binárního protokolu. Toto zpoždění snižuje vstupně-výstupní operace repliky a pomáhá zlepšit výkon.

Může být užitečné nastavit parametr binlog_group_commit_sync_delay na 1000 nebo tak. Pak monitorujte latenci replikace. Tento parametr nastavte opatrně a používejte ho jenom pro úlohy s vysokou souběžností.

U úloh s nízkou souběžností, které obsahují mnoho transakcí s jednímtonem, může nastavení binlog_group_commit_sync_delay zvýšit latenci. Latence se může zvýšit, protože vlákno vstupně-výstupních operací čeká na hromadné aktualizace binárních protokolů, i když se potvrdí jenom několik transakcí.

Pokročilé možnosti řešení potíží

Pokud příkaz show slave status neposkytuje dostatek informací pro řešení potíží s latencí replikace, zkuste si prohlédnout tyto další možnosti pro informace o tom, které procesy jsou aktivní nebo čekají.

Zobrazení tabulky vláken

Tabulka performance_schema.threads zobrazuje stav procesu. Proces se stavem Čekání na lock_type zámek indikuje, že u jedné z tabulek existuje zámek, který brání vláknu replikace v aktualizaci tabulky.

SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';

Další informace naleznete v tématu Obecné stavy vláken.

Zobrazení tabulky replication_connection_status

Tabulka performance_schema.replication_connection_status zobrazuje aktuální stav vstupně-výstupního vlákna replikace, které zpracovává připojení repliky ke zdroji a častěji se mění. Tabulka obsahuje hodnoty, které se během připojení liší.

SELECT * FROM performance_schema.replication_connection_status;

Zobrazení tabulky replication_applier_status_by_worker

Tabulka performance_schema.replication_applier_status_by_worker zobrazuje stav pracovních vláken, poslední zobrazenou transakci spolu s posledním číslem chyby a zprávou, která vám pomůže najít transakci s problémem a identifikovat původní příčinu.

V replikaci data můžete spustit následující příkazy, které přeskočí chyby nebo transakce:

az_replication_skip_counter

nebo

az_replication_skip_gtid_transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker;

Zobrazení příkazu SHOW RELAYLOG EVENTS

Příkaz show relaylog events zobrazuje události v protokolu přenosu repliky.

· V případě replikace založené na GITD (replika pro čtení) příkaz zobrazuje transakce GTID a soubor binlogu a jeho pozici, můžete použít mysqlbinlog k získání obsahu a příkazů, které se spouští. · V případě replikace pozice binlogu MySQL (používá se pro replikaci příchozích dat) se zobrazují příkazy, které vám pomůžou zjistit, na kterých transakcích tabulek se spouští.

Kontrola výstupu monitorování úrovně Standard a uzamčení innoDB

Můžete také zkusit zkontrolovat standardní monitorování innoDB a uzamknout výstup monitorování, které vám pomůžou vyřešit zámky a zablokování a minimalizovat prodlevu replikace. Zámek monitoru je stejný jako standardní monitor s tím rozdílem, že obsahuje další informace o zámku. Pokud chcete zobrazit další informace o uzamčení a vzájemném zablokování, spusťte příkaz show engine innodb status\G.

Další kroky

Podívejte se na přehled replikace binlogu MySQL.