Dlaczego twoje synchroniczne zatwierdzenie SQL czeka na podróż VPN w obie strony

Opóźnienie zapisu skoczyło po przeniesieniu repliki do drugiej lokalizacji, a tunel jest najnowszym elementem, więc to on zbiera winę. Tunel może być niewinny: w grupie dostępności z synchronicznym zatwierdzeniem replika podstawowa nie może potwierdzić zatwierdzenia, dopóki nie zakończy się zdalny zapis dziennika, więc każda transakcja płaci podróż w obie strony, niezależnie od tego, ile megabitów uniesie łącze. Przepustowość mierzy bajty na sekundę, opóźnienie mierzy, ile trwa jedna wymiana, a zatwierdzenie potrzebuje tylko jednej małej wymiany, aby się zakończyć. Jeden numer rozstrzyga spór: czas trwania zatwierdzenia małej transakcji, mierzony w bazie danych dostępności, a następnie ponownie wobec lokalnej tabeli w tej samej instancji.

Na co naprawdę czeka replika podstawowa

Przy synchronicznym zatwierdzeniu replika podstawowa wstrzymuje potwierdzenie, dopóki partner nie potwierdzi, że jego rekordy dziennika są utrwalone. Utrwalone oznacza trwale zapisane w dzienniku transakcji repliki wtórnej, a nie odtworzone w jej stronach danych. Redo może pozostawać w tyle o minuty, nie dodając ani milisekundy do zatwierdzenia repliki podstawowej.

Dokumentacja Microsoftu dotycząca trybów dostępności omawia ten uścisk dłoni i opisuje, co robi platforma, gdy partner wypada. Replika oznaczona jako synchroniczna to reguła dotycząca czekania, a nie trwała obietnica, że dla każdego zatwierdzenia istnieje zdrowa kopia zdalna. Ścieżka rozłączona jest zdefiniowana osobno i nie polega po prostu na tym, że zatwierdzenie się nie udaje.

Zatem obserwowane czekanie to suma: transport dziennika, planowanie na obu hostach i zapis do zdalnego magazynu. Replika wtórna z nasyconym wolumenem dziennika dodaje czas, którego nie pokaże żaden test prędkości. Ten dysk rzadko bywa mierzony, gdy skarga wymienia VPN.

Przepustowość to nie opóźnienie podróży w obie strony

Test prędkości zalewa ścieżkę na chwilę i podaje szybkość. Zatwierdzenie prosi o coś węższego: ile trwa dotarcie małej wiadomości na drugą stronę, utrwalenie i powrót. Długość ścieżki, kolejki i utrata pakietów wyznaczają tę liczbę, a żadna z nich nie poprawia się, gdy dostawca podnosi sufit.

Dlatego «szybki» tunel może dodać widoczną karę do obciążenia mocno zapisującego, zbudowanego z wielu małych kolejnych transakcji. Ta sama suma bajtów podzielona na tysiąc zatwierdzeń płaci podróż w obie strony tysiąc razy; jeden transfer zbiorczy płaci ją raz.

Poproś właściciela sieci o pomiar opóźnienia i strat na samej trasie repliki pod obciążeniem reprezentatywnym. Ping z laptopa biurowego do bramy VPN albo test prędkości do pobliskiego węzła prawie nic nie przypisuje trasie, którą dziennik faktycznie podróżuje. Dopilnuj, aby okno pomiaru pokrywało się z godzinami, w których zapis jest wolny.

Przeczytaj stan repliki, zanim cokolwiek zmienisz

Zacznij od tego, co instancje raportują o sobie, bo obie strony mogą się nie zgadzać co do tego, jak zdrowa jest współpraca, i średnia z pulpitu ukrywa, która kolejka się porusza. Poniższe zapytania odpowiadają na różne pytania: jedno czyta stan replikacji dla każdej bazy, drugie czyta, jak skonfigurowana jest każda replika. Uruchom oba, zanim dotkniesz trybu.

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;

Pierwszy wynik oddziela transport dziennika od redo. log_send_queue_size rosnący, gdy redo_queue_size stoi płasko, oznacza, że dziennik nie dotarł; odwrotnie oznacza, że dotarł, a odtwarzanie pozostaje w tyle. Tylko pierwszy przypadek leży na ścieżce zatwierdzenia.

Drugi wynik potwierdza, które repliki są naprawdę skonfigurowane tak, jak zakładasz, bo tryb zmieniony podczas wcześniejszego incydentu zwykle pozostaje zmieniony i nieudokumentowany. Czytaj wiersz, który cię interesuje, zamiast domyślnego ustawienia grupy: availability_mode_desc i failover_mode_desc są dla każdej repliki. secondary_lag_seconds istnieje tylko w SQL Server 2016 i nowszych, więc starsza kompilacja zostawia ci tylko kolumny kolejki wysyłania i redo.

Następnie zmierz reprezentatywny zapis zamiast ufać średniej z pulpitu. Uruchom poniższy blok w bazie danych dostępności, a potem uruchom go ponownie wobec tego samego kształtu tabeli w bazie poza grupą w tej samej instancji. Różnica między dwoma czasami to przybliżona cena zdalnego dziennika i w dyskusji z zespołem sieciowym trzyma się znacznie lepiej niż wykres. Potem zmieniaj jedną zmienną naraz, inaczej różnica przestaje cokolwiek znaczyć.

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;

Co kupuje asynchroniczne zatwierdzenie i ile kosztuje

Przy asynchronicznym zatwierdzeniu replika podstawowa przestaje czekać na potwierdzenie zdalnego utrwalenia, więc podróż w obie strony opuszcza ścieżkę zatwierdzenia. Opóźnienie zatwierdzenia spada, a replika wtórna może swobodnie pozostawać w tyle w stopniu, którego nikt nie kontroluje. To realna naprawa czekania i realna zmiana obietnicy.

Cena to decyzja o punkcie odzyskiwania. Jeśli replika podstawowa umrze, zanim jej dziennik dotrze do celu przełączenia awaryjnego, którego naprawdę możesz użyć, przeniesienie usługi na kopię asynchroniczną odrzuca transakcje, które aplikacja już zgłosiła jako zatwierdzone. Żadne okno konfiguracji tego tak nie przedstawia, bo to pytanie biznesowe o akceptowalną utratę, a nie ustawienie wydajności. Zapisz tolerowane okno utraty przed incydentem, nie w jego trakcie.

Uzgodnij tryb z właścicielem danych, na piśmie, wraz z procedurą przełączenia awaryjnego na wypadek, gdy preferowany cel jest niedostępny. Synchroniczność w dokumencie projektowym to nie instrukcja operacyjna, a asynchroniczność nie jest licencją na zaprzestanie obserwacji kolejek.

Wybierz naprawę, której potem możesz bronić

Istnieją cztery uczciwe opcje. Odetnij zbędne podróże zatwierdzenia w obie strony tam, gdzie semantyka aplikacji pozwala na grupowanie; napraw zmierzone wąskie gardło transportu lub magazynu, co może oznaczać szybszy wolumen dziennika na replice wtórnej zamiast większej rury; przybliż replikę, aby podróż w obie strony się skróciła; albo złagodź politykę ochrony i zaakceptuj szersze okno utraty. Każda kupuje opóźnienie, wydając coś innego, a czwarta wydaje trwałość. Nazwij wydatek, zanim wybierzesz.

Decyduj, mając jednocześnie przed sobą cel opóźnienia obciążenia i jego wymóg trwałości, bo optymalizacja jednego po cichu wydaje drugie. Potem uruchom ponownie tę samą transakcję, którą zmierzyłeś przed zmianą. Naprawa, która nie potrafi ruszyć tej liczby, jest domysłem w bilecie zmian.

Zachowaj odczyty przed i po w zapisie zmiany. Inaczej następna osoba, która zobaczy wolne zapisy, powtórzy dochodzenie od początku i znów zacznie obwiniać tunel.