Waarom je synchrone SQL-commit op de VPN-rondreis wacht

De schrijflatentie schoot omhoog nadat een replica naar de tweede locatie verhuisde, en de tunnel is de nieuwste component, dus hij krijgt de schuld. De tunnel kan onschuldig zijn: binnen een beschikbaarheidsgroep met synchrone commit kan de primaire een commit pas bevestigen als een externe logschrijfactie is voltooid, dus elke transactie betaalt een rondreis, hoeveel megabit de verbinding ook kan dragen. Doorvoer meet bytes per seconde, latentie meet hoe lang één uitwisseling duurt, en een commit heeft maar één kleine uitwisseling nodig om klaar te zijn. Eén getal beslecht de discussie: de duur van de commit van een kleine transactie, gemeten in de beschikbaarheidsdatabase en daarna opnieuw tegen een lokale tabel op dezelfde instantie.

Waar de primaire echt op wacht

Bij synchrone commit houdt de primaire de commit-bevestiging tegen tot de partner bevestigt dat zijn logrecords gehard zijn. Gehard betekent duurzaam weggeschreven naar het transactielog van de secundaire, niet opnieuw uitgevoerd in de datapagina's. Redo kan minuten achterlopen zonder één milliseconde aan de commit van de primaire toe te voegen.

Microsofts documentatie over de beschikbaarheidsmodi legt die handdruk uit en beschrijft wat het platform doet als een partner wegvalt. Een replica die als synchroon is gemarkeerd, is een regel over wachten, geen permanente belofte dat er voor elke commit een gezonde externe kopie bestaat. Het losgekoppelde pad is apart gedefinieerd, en het is niet simpelweg dat de commit mislukt.

Het wachten dat je ziet is dus een som: logtransport, planning op beide hosts en de schrijfactie naar de externe opslag. Een secundaire met een verzadigd logvolume voegt tijd toe die geen snelheidstest laat zien. Die schijf wordt zelden gemeten wanneer de klacht de VPN noemt.

Bandbreedte is geen rondreisvertraging

Een snelheidstest overspoelt het pad kort en meldt een snelheid. Een commit vraagt iets engers: hoe lang een klein bericht erover doet om de overkant te bereiken, te worden opgeslagen en terug te komen. Padlengte, wachtrijen en pakketverlies bepalen dat getal, en geen ervan verbetert wanneer de provider het plafond verhoogt.

Een «snelle» tunnel kan dus een zichtbare straf toevoegen aan een schrijfintensieve werklast die is opgebouwd uit veel kleine opeenvolgende transacties. Hetzelfde totale aantal bytes verdeeld over duizend commits betaalt de rondreis duizend keer; één bulkoverdracht betaalt hem één keer.

Vraag de netwerkeigenaar om vertraging en verlies gemeten over de replicaroute zelf onder representatieve belasting. Een ping vanaf een kantoorlaptop naar de VPN-gateway, of een snelheidstest naar een nabijgelegen knooppunt, schrijft bijna niets toe aan de route die het log echt aflegt. Zorg dat het meetvenster overlapt met de uren waarin schrijven traag is.

Lees de status van de replica voordat je iets verandert

Begin bij wat de instanties over zichzelf rapporteren, want de twee kanten kunnen het oneens zijn over hoe gezond het partnerschap is en een dashboardgemiddelde verbergt welke wachtrij beweegt. De query's hieronder beantwoorden verschillende vragen: de ene leest de replicatiestatus per database, de andere leest hoe elke replica is geconfigureerd. Voer beide uit voordat je een modus aanraakt.

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;

Het eerste resultaat scheidt logtransport van redo. log_send_queue_size dat stijgt terwijl redo_queue_size vlak blijft, betekent dat het log niet is aangekomen; het omgekeerde betekent dat het is aangekomen en dat het afspelen achterloopt. Alleen het eerste geval zit op het commit-pad.

Het tweede resultaat bevestigt welke replica's echt zijn geconfigureerd zoals je aanneemt, want een modus die tijdens een eerder incident is gewijzigd, blijft meestal gewijzigd en ongedocumenteerd. Lees de rij die je aangaat in plaats van de groepsstandaard: availability_mode_desc en failover_mode_desc zijn per replica. secondary_lag_seconds bestaat alleen op SQL Server 2016 en later, dus een oudere build laat je alleen de kolommen voor de verzendwachtrij en redo.

Meet dan een representatieve schrijfactie in plaats van een dashboardgemiddelde te vertrouwen. Voer het blok hieronder uit in de beschikbaarheidsdatabase en voer het daarna opnieuw uit tegen dezelfde tabelvorm in een database buiten de groep op dezelfde instantie. Het verschil tussen de twee duurmetingen is een ruwe prijs voor het externe log, en in een discussie met het netwerkteam houdt het veel beter stand dan een grafiek. Wijzig daarna één variabele tegelijk, anders betekent het verschil niets meer.

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;

Wat asynchrone commit koopt, en wat het kost

Met asynchrone commit stopt de primaire met wachten op de bevestiging van de externe verharding, zodat de rondreis het commit-pad verlaat. De commit-latentie daalt en de secundaire mag achterlopen met een hoeveelheid die niemand beheerst. Dat is een echte oplossing voor het wachten en een echte wijziging van de belofte.

De prijs is een beslissing over het herstelpunt. Als de primaire sterft voordat zijn log een failover-doel bereikt dat je echt kunt gebruiken, gooit het verplaatsen van de dienst naar de asynchrone kopie transacties weg die de applicatie al als commit had gemeld. Geen enkel configuratiedialoogvenster stelt het zo voor, want het is een bedrijfsvraag over aanvaardbaar verlies, geen prestatie-instelling. Schrijf het getolereerde verliesvenster op vóór het incident, niet tijdens.

Spreek de modus af met de eigenaar van de data, schriftelijk, samen met de failover-procedure voor het geval het voorkeursdoel niet beschikbaar is. Synchroon in een ontwerpdocument is geen operationele handleiding, en asynchroon is geen licentie om de wachtrijen niet meer te volgen.

Kies een oplossing die je achteraf kunt verdedigen

Er zijn vier eerlijke opties. Snijd vermijdbare commit-rondreizen weg waar de semantiek van de applicatie batchverwerking toestaat; repareer een gemeten transport- of opslagknelpunt, wat een sneller logvolume op de secundaire kan betekenen in plaats van een grotere pijp; verplaats de replica dichterbij zodat de rondreis krimpt; of versoepel het beschermingsbeleid en accepteer een breder verliesvenster. Elk koopt latentie door iets anders uit te geven, en de vierde geeft duurzaamheid uit. Benoem de uitgave voordat je kiest.

Beslis met het latentiedoel van de werklast en de duurzaamheidseis ervan tegelijk voor je, want het optimaliseren van het ene geeft stil het andere uit. Voer daarna dezelfde transactie opnieuw uit die je vóór de wijziging hebt gemeten. Een oplossing die dat getal niet kan bewegen, is een gok met een wijzigingsticket.

Bewaar de metingen voor en na in het wijzigingsrecord. Anders herhaalt de volgende persoon die trage schrijfacties ziet het onderzoek vanaf het begin en begint weer de tunnel de schuld te geven.