Felsöka replikeringsfördröjning i Azure Database for MySQL – flexibel server

GÄLLER FÖR: Azure Database for MySQL – Azure Database for MySQL – enskild server – flexibel server

Med funktionen skrivskyddad replik kan du replikera data från en Azure Database for MySQL-server till en skrivskyddad replikserver. Du kan skala ut arbetsbelastningar genom att dirigera läs- och rapporteringsfrågor från programmet till replikservrar. Den här konfigurationen minskar trycket på källservern och förbättrar programmets övergripande prestanda och svarstid när det skalar.

Repliker uppdateras asynkront med hjälp av MySQL-motorns inbyggda binärloggfil (binlog)-baserad replikeringsteknik. Mer information finns i Översikt över positionsbaserad replikeringskonfiguration i MySQL-binlogfil.

Replikeringsfördröjningen på de sekundära läsreplikerna beror på flera faktorer. Dessa faktorer inkluderar men är inte begränsade till:

• Nätverksfördröjning.
• Transaktionsvolym på källservern.
• Beräkningsnivå för källservern och den sekundära skrivskyddade replikservern.
• Frågor som körs på källservern och den sekundära servern.

I den här artikeln får du lära dig hur du felsöker replikeringsfördröjning i Azure Database for MySQL. Du får också en bättre uppfattning om några vanliga orsaker till ökad replikeringsfördröjning på replikservrar.

Replikeringsbegrepp

När en binär logg är aktiverad skriver källservern checkade transaktioner till den binära loggen. Binärloggen används för replikering. Den är aktiverad som standard för alla nyligen etablerade servrar som har stöd för upp till 16 TB lagringsutrymme. På replikservrar körs två trådar på varje replikserver. En tråd är I/O-tråden och den andra är SQL-tråden:

• I/O-tråden ansluter till källservern och begär uppdaterade binära loggar. Den här tråden tar emot uppdateringarna av binärloggen. Dessa uppdateringar sparas på en replikserver i en lokal logg som kallas reläloggen.
• SQL-tråden läser reläloggen och tillämpar sedan dataändringarna på replikservrar.

Övervaka replikeringsfördröjning

Azure Database for MySQL tillhandahåller måttet för replikeringsfördröjning i sekunder i Azure Monitor. Det här måttet är endast tillgängligt på skrivskyddade replikservrar. Det beräknas med måttet seconds_behind_master som är tillgängligt i MySQL.

För att förstå orsaken till ökad replikeringsfördröjning ansluter du till replikservern med hjälp av MySQL Workbench eller Azure Cloud Shell. Kör sedan följande kommando.

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

Så här ser upplevelsen ut i Cloud Shell-terminalen:

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>

Kör följande kommando i samma Cloud Shell-terminal:

mysql> SHOW SLAVE STATUS;

Här är en typisk utdata:

Utdata innehåller många information. Normalt behöver du bara fokusera på de rader som beskrivs i följande tabell.

Om Slave_IO_Running är Yes och Slave_SQL_Running är Yeskörs replikeringen bra.

Kontrollera sedan Last_IO_Errno, Last_IO_Error, Last_SQL_Errno och Last_SQL_Error. De här fälten visar felnumret och felmeddelandet för det senaste felet som gjorde att SQL-tråden upphörde. Ett felnummer och 0 ett tomt meddelande innebär att det inte finns något fel. Undersök eventuella felvärden som inte är noll genom att kontrollera felkoden i felmeddelandereferensen för MySQL-servern.

Vanliga scenarier för långa replikeringsfördröjningar

I följande avsnitt beskrivs scenarier där långa replikeringssvarstider är vanliga.

Nätverksfördröjning eller hög CPU-förbrukning på källservern

Om du ser följande värden orsakas replikeringsfördröjningen troligen av hög nätverksfördröjning eller hög CPU-förbrukning på källservern.

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

I det här fallet körs I/O-tråden och väntar på källservern. Källservern har redan skrivit till binär loggfil nummer 20. Repliken har bara tagit emot upp till filnummer 10. De främsta faktorerna för hög replikeringsfördröjning i det här scenariot är nätverkshastighet eller hög CPU-användning på källservern.

I Azure kan nätverksfördröjning inom en region vanligtvis mätas millisekunder. Mellan regioner varierar svarstiden från millisekunder till sekunder.

I de flesta fall orsakas anslutningsfördröjningen mellan I/O-trådar och källservern av hög CPU-användning på källservern. I/O-trådarna bearbetas långsamt. Du kan identifiera det här problemet med hjälp av Azure Monitor för att kontrollera processoranvändningen och antalet samtidiga anslutningar på källservern.

Om du inte ser hög CPU-användning på källservern kan problemet vara nätverksfördröjning. Om nätverksfördröjningen plötsligt är onormalt hög går du till azure-statussidan för kända problem eller avbrott.

Stora mängder transaktioner på källservern

Om du ser följande värden orsakar sannolikt en stor mängd transaktioner på källservern replikeringsfördröjningen.

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

Utdata visar att repliken kan hämta den binära loggen bakom källservern. Men replikens I/O-tråd anger att reläloggutrymmet redan är fullt.

Nätverkshastigheten orsakar inte fördröjningen. Repliken försöker komma ikapp. Men den uppdaterade binära loggstorleken överskrider den övre gränsen för reläloggutrymmet.

Om du vill felsöka det här problemet aktiverar du loggen för långsamma frågor på källservern. Använd långsamma frågeloggar för att identifiera långvariga transaktioner på källservern. Justera sedan de identifierade frågorna för att minska svarstiden på servern.

Replikeringsfördröjning av den här typen orsakas ofta av databelastningen på källservern. När källservrar har datainläsningar varje vecka eller månad är replikeringsfördröjningen tyvärr oundviklig. Replikservrarna kommer så småningom ikapp efter att databelastningen på källservern har slutförts.

Långsamhet på replikservern

Om du ser följande värden kan problemet bero på replikservern.

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

I det här scenariot visar utdata att både I/O-tråden och SQL-tråden fungerar bra. Repliken läser samma binära loggfil som källservern skriver. Vissa svarstider på replikservern återspeglar dock samma transaktion från källservern.

I följande avsnitt beskrivs vanliga orsaker till den här typen av svarstid.

Ingen primärnyckel eller unik nyckel i en tabell

Azure Database for MySQL använder radbaserad replikering. Källservern skriver händelser till den binära loggen och registrerar ändringar i enskilda tabellrader. SQL-tråden replikerar sedan ändringarna till motsvarande tabellrader på replikservern. När en tabell saknar en primärnyckel eller unik nyckel genomsöker SQL-tråden alla rader i måltabellen för att tillämpa ändringarna. Genomsökningen kan orsaka en replikeringsfördröjning.

I MySQL är primärnyckeln ett associerat index som garanterar snabba frågeprestanda eftersom den inte kan innehålla NULL-värden. Om du använder InnoDB-lagringsmotorn organiseras tabelldata fysiskt för att utföra ultrasnabba sökningar och sortering baserat på primärnyckeln.

Vi rekommenderar att du lägger till en primärnyckel i tabeller på källservern innan du skapar replikservern. Lägg till primära nycklar på källservern och skapa sedan läsrepliker igen för att förbättra replikeringsfördröjningen.

Använd följande fråga för att ta reda på vilka tabeller som saknar en primärnyckel på källservern:

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;

Tidskrävande frågor på replikservern

Arbetsbelastningen på replikservern kan göra att SQL-tråden släpar efter I/O-tråden. Långvariga frågor på replikservern är en av de vanligaste orsakerna till långa replikeringsfördröjningar. Om du vill felsöka det här problemet aktiverar du den långsamma frågeloggen på replikservern.

Långsamma frågor kan öka resursförbrukningen eller göra servern långsammare så att repliken inte kan komma ikapp källservern. I det här scenariot justerar du de långsamma frågorna. Snabbare frågor förhindrar blockering av SQL-tråden och förbättrar replikeringsfördröjningen avsevärt.

DDL-frågor på källservern

På källservern kan ett DDL-kommando ALTER TABLE (datadefinitionsspråk) ta lång tid. Medan DDL-kommandot körs kan tusentals andra frågor köras parallellt på källservern.

När DDL replikeras kör MySQL-motorn DDL i en enda replikeringstråd för att säkerställa databaskonsekvens. Under den här uppgiften blockeras alla andra replikerade frågor och måste vänta tills DDL-åtgärden har slutförts på replikservern. Även online-DDL-åtgärder orsakar den här fördröjningen. DDL-åtgärder ökar replikeringsfördröjningen.

Om du har aktiverat loggen för långsamma frågor på källservern kan du identifiera det här svarstidsproblemet genom att söka efter ett DDL-kommando som kördes på källservern. Genom att släppa, byta namn på och skapa index kan du använda INPLACE-algoritmen för ALTER TABLE. Du kan behöva kopiera tabelldata och återskapa tabellen.

Vanligtvis stöds samtidig DML för INPLACE-algoritmen. Men du kan kort ta ett exklusivt metadatalås i tabellen när du förbereder och kör åtgärden. Så för CREATE INDEX-instruktionen kan du använda satserna ALGORITHM och LOCK för att påverka metoden för tabellkopiering och samtidighetsnivån för läsning och skrivning. Du kan fortfarande förhindra DML-åtgärder genom att lägga till ett FULLTEXT-index eller SPATIAL-index.

I följande exempel skapas ett index med hjälp av ALGORITM- och LOCK-satser.

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

För en DDL-instruktion som kräver ett lås kan du tyvärr inte undvika replikeringsfördröjning. För att minska de potentiella effekterna gör du dessa typer av DDL-åtgärder under låg belastning, till exempel under natten.

Nedgraderad replikserver

I Azure Database for MySQL använder läsrepliker samma serverkonfiguration som källservern. Du kan ändra konfigurationen av replikservern när den har skapats.

Om replikservern nedgraderas kan arbetsbelastningen förbruka fler resurser, vilket i sin tur kan leda till replikeringsfördröjning. Om du vill identifiera det här problemet använder du Azure Monitor för att kontrollera processor- och minnesförbrukningen för replikservern.

I det här scenariot rekommenderar vi att du behåller replikserverns konfiguration på värden som är lika med eller större än källserverns värden. Med den här konfigurationen kan repliken hänga med i källservern.

Förbättra replikeringsfördröjningen genom att justera källserverparametrarna

I Azure Database for MySQL optimeras replikering som standard för att köras med parallella trådar på repliker. När arbetsbelastningar med hög samtidighet på källservern gör att replikservern hamnar på efterkälken kan du förbättra replikeringsfördröjningen genom att konfigurera parametern binlog_group_commit_sync_delay på källservern.

Parametern binlog_group_commit_sync_delay styr hur många mikrosekunder den binära loggincheckningen väntar innan den binära loggfilen synkroniseras. Fördelen med den här parametern är att i stället för att omedelbart tillämpa varje bekräftad transaktion skickar källservern binärlogguppdateringarna i bulk. Den här fördröjningen minskar I/O på repliken och förbättrar prestandan.

Det kan vara användbart att ange parametern binlog_group_commit_sync_delay till 1 000 eller så. Övervaka sedan replikeringsfördröjningen. Ange den här parametern försiktigt och använd den endast för arbetsbelastningar med hög samtidighet.

För arbetsbelastningar med låg samtidighet som omfattar många singleton-transaktioner kan inställningen binlog_group_commit_sync_delay öka svarstiden. Svarstiden kan öka eftersom I/O-tråden väntar på massuppdateringar av binär logg även om bara ett fåtal transaktioner har checkats in.

Avancerade felsökningsalternativ

Om kommandot visa slavstatus inte innehåller tillräckligt med information för att felsöka replikeringsfördröjning kan du prova att visa dessa ytterligare alternativ för att lära dig vilka processer som är aktiva eller väntar.

Visa trådtabellen

Tabellen performance_schema.threads visar processtillståndet. En process med tillståndet Väntar på lock_type lås indikerar att det finns ett lås på en av tabellerna, vilket hindrar replikeringstråden från att uppdatera tabellen.

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

Mer information finns i Allmänna trådtillstånd.

Visa tabellen replication_connection_status

Tabellen performance_schema.replication_connection_status visar den aktuella statusen för replikerings-I/O-tråden som hanterar replikens anslutning till källan och ändras oftare. Tabellen innehåller värden som varierar under anslutningen.

SELECT * FROM performance_schema.replication_connection_status;

Visa tabellen replication_applier_status_by_worker

Tabellen performance_schema.replication_applier_status_by_worker visar statusen för arbetstrådarna, Senast sett transaktion tillsammans med senaste felnummer och meddelande, som hjälper dig att hitta transaktionen som har problem och identifiera rotorsaken.

Du kan köra kommandona nedan i datareplikeringen för att hoppa över fel eller transaktioner:

az_replication_skip_counter

eller

az_replication_skip_gtid_transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker;

Visa INSTRUKTIONEN SHOW RELAYLOG EVENTS

Instruktionen show relaylog events visar händelserna i reläloggen för en replik.

· För GITD-baserad replikering (läsreplik) visar instruktionen GTID-transaktion och binlogfil och dess position. Du kan använda mysqlbinlog för att hämta innehåll och instruktioner som körs. · För replikering av position för MySQL-binlog (används för datareplikering) visas instruktioner som körs, vilket hjälper dig att veta vilka tabelltransaktioner som körs

Kontrollera innoDB-standardövervakaren och låsövervakarens utdata

Du kan också prova att kontrollera InnoDB Standard Monitor- och Lock Monitor-utdata för att lösa lås och dödlägen och minimera replikeringsfördröjningen. Låsövervakaren är samma som standardövervakaren förutom att den innehåller ytterligare låsinformation. Om du vill visa ytterligare information om lås och dödläge kör du kommandot show engine innodb status\G.

Nästa steg

Se replikeringsöversikten för MySQL-binlog.