Hvorfor din synkrone SQL-commit venter på VPN-rundturen

Skrivelatensen spratt etter at en replika ble flyttet til det andre nettstedet, og tunnelen er den nyeste komponenten, så den får skylden. Tunnelen kan være uskyldig: inne i en tilgjengelighetsgruppe med synkron commit kan ikke den primære kvittere ut en commit før en ekstern loggskriving er fullført, så hver transaksjon betaler en rundtur uansett hvor mange megabit forbindelsen kan bære. Gjennomstrømning måler byte per sekund, latens måler hvor lang tid én utveksling tar, og en commit trenger bare én liten utveksling for å bli ferdig. Ett tall avgjør diskusjonen: varigheten av en liten transaksjons commit, målt inne i tilgjengelighetsdatabasen og deretter på nytt mot en lokal tabell på samme instans.

Hva den primære egentlig venter på

Under synkron commit holder den primære tilbake commit-kvitteringen til partneren bekrefter at loggpostene er herdet. Herdet betyr varig skrevet til den sekundæres transaksjonslogg, ikke kjørt på nytt i dens datasider. Redo kan henge minutter etter uten å legge til en eneste millisekund til den primæres commit.

Microsofts dokumentasjon om tilgjengelighetsmoduser går gjennom den håndtrykken og beskriver hva plattformen gjør når en partner faller ut. En replika merket som synkron er en regel om å vente, ikke et stående løfte om at det finnes en sunn ekstern kopi for hver commit. Den frakoblede veien er definert separat, og det er ikke bare det at commit feiler.

Så ventingen du ser er en sum: loggtransport, planlegging på begge verter og skrivingen til ekstern lagring. En sekundær med mettet loggvolum legger til tid som ingen hastighetstest viser. Den disken blir sjelden målt når klagen nevner VPN-en.

Båndbredde er ikke rundtursforsinkelse

En hastighetstest oversvømmer veien kort og rapporterer en hastighet. En commit spør om noe smalere: hvor lang tid en liten melding bruker på å nå den andre siden, bli lagret og komme tilbake. Veilengde, køer og pakketap bestemmer det tallet, og ingen av dem blir bedre når leverandøren hever taket.

En «rask» tunnel kan derfor legge til en synlig straff på en skrivetung arbeidsbelastning bygget av mange små sekvensielle transaksjoner. Den samme bytemengden fordelt på tusen commits betaler rundturen tusen ganger; én bulkoverføring betaler den én gang.

Be nettverkseieren om forsinkelse og tap målt over selve replikaruten under representativ last. En ping fra en kontorbærbar til VPN-gatewayen, eller en hastighetstest til en nærliggende node, tilskriver nesten ingenting til ruten loggen faktisk reiser. Sørg for at målevinduet overlapper timene da skriving er treg.

Les replikaens tilstand før du endrer noe

Start med det instansene rapporterer om seg selv, fordi de to sidene kan være uenige om hvor sunn partnerskapet er, og et gjennomsnitt på dashbordet skjuler hvilken kø som beveger seg. Spørringene nedenfor svarer på forskjellige spørsmål: én leser replikasjonstilstanden per database, den andre leser hvordan hver replika er konfigurert. Kjør begge før du rører en modus.

SELECT ag.name AS ag_name, drs.database_id, drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id;

SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas AS ar;

Det første resultatet skiller loggtransport fra redo. log_send_queue_size som stiger mens redo_queue_size står flatt betyr at loggen ikke har ankommet; det motsatte betyr at den har ankommet og at avspillingen henger etter. Bare det første tilfellet sitter på commit-veien.

Det andre resultatet bekrefter hvilke replikaer som faktisk er konfigurert slik du antar, fordi en modus som ble endret under en tidligere hendelse har en tendens til å forbli endret og udokumentert. Les raden du bryr deg om i stedet for gruppens standard: availability_mode_desc og failover_mode_desc er per replika. secondary_lag_seconds finnes bare på SQL Server 2016 og nyere, så en eldre versjon lar deg sitte igjen med bare kolonnene for sendekø og redo.

Tid deretter en representativ skriving i stedet for å stole på et dashbordgjennomsnitt. Kjør blokken nedenfor i tilgjengelighetsdatabasen, og kjør den deretter på nytt mot samme tabellform i en database utenfor gruppen på samme instans. Forskjellen mellom de to varighetene er en grov pris for den eksterne loggen, og i en diskusjon med nettverksteamet holder den seg mye bedre enn en graf. Bytt deretter én variabel om gangen, ellers slutter forskjellen å bety noe.

IF OBJECT_ID('dbo.CommitProbe') IS NULL
    CREATE TABLE dbo.CommitProbe (probe_id int PRIMARY KEY, touched_at datetime2(3));
IF NOT EXISTS (SELECT 1 FROM dbo.CommitProbe WHERE probe_id = 1)
    INSERT INTO dbo.CommitProbe (probe_id) VALUES (1);
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.CommitProbe SET touched_at = SYSUTCDATETIME() WHERE probe_id = 1;
COMMIT TRANSACTION;
SET STATISTICS TIME OFF;

Hva asynkron commit kjøper, og hva den koster

Med asynkron commit slutter den primære å vente på bekreftelsen av ekstern herding, så rundturen forlater commit-veien. Commit-latensen faller, og den sekundære står fritt til å henge etter i et omfang ingen kontrollerer. Det er en reell løsning på ventingen og en reell endring av løftet.

Prisen er en beslutning om gjenopprettingspunkt. Hvis den primære dør før loggen når et failover-mål du faktisk kan bruke, kaster flyttingen av tjenesten over på den asynkrone kopien transaksjoner som applikasjonen allerede har rapportert som committed. Ingen konfigurasjonsdialog fremstiller det slik, fordi det er et forretningsspørsmål om akseptabelt tap, ikke en ytelsesinnstilling. Skriv ned det tolererte tapsvinduet før hendelsen, ikke under den.

Avtal modusen med den som eier dataene, skriftlig, sammen med failover-prosedyren for tilfellet der det foretrukne målet er utilgjengelig. Synkron i et designdokument er ikke en driftshåndbok, og asynkron er ikke en lisens til å slutte å overvåke køene.

Velg en løsning du kan forsvare i ettertid

Det finnes fire ærlige alternativer. Kutt unødvendige commit-rundturer der applikasjonens semantikk tillater batching; fiks en målt flaskehals i transport eller lagring, noe som kan bety et raskere loggvolum på den sekundære i stedet for et større rør; flytt replikaen nærmere så rundturen krymper; eller lemp beskyttelsespolicyen og aksepter et bredere tapsvindu. Hver av dem kjøper latens ved å bruke noe annet, og den fjerde bruker holdbarhet. Navngi forbruket før du velger.

Bestem med arbeidsbelastningens latensmål og dens holdbarhetskrav foran deg samtidig, fordi optimalisering av det ene stille bruker det andre. Kjør deretter den samme transaksjonen på nytt som du målte før endringen. En løsning som ikke kan flytte det tallet, er en gjetning med en endringsbillett.

Ta vare på før- og etteravlesningene i endringsloggen. Ellers gjentar den neste personen som ser trege skrivinger undersøkelsen fra begynnelsen og begynner igjen med å gi tunnelen skylden.