Serwer SQL napotkał wystąpienia żądań We / Wy dłużej niż 15 sekund

Na produkcyjnym serwerze SQL mamy następującą konfigurację:

3 serwery Dell PowerEdge R630 połączone w grupę dostępności Wszystkie 3 są podłączone do pojedynczej pamięci masowej Dell SAN, która jest macierzą RAID

Od czasu do czasu w PODSTAWIE widzimy komunikaty podobne do poniższych:

Program SQL Server napotkał 11 wystąpień żądań we / wy, których wykonanie zajmuje dłużej niż 15 sekund w pliku [F: \ Data \ MyDatabase.mdf] w bazie danych o identyfikatorze 8.
Uchwyt pliku systemu operacyjnego to 0x0000000000001FBC.
Przesunięcie ostatniego długiego wejścia / wyjścia wynosi: 0x000004295d0000.
Czas trwania długiego wejścia / wyjścia wynosi: 37397 ms.

Jesteśmy nowicjuszami w rozwiązywaniu problemów z wydajnością

Jakie są najczęstsze sposoby lub najlepsze praktyki rozwiązywania tego konkretnego problemu związanego z pamięcią masową? Jakich liczników wydajności, narzędzi, monitorów, aplikacji itp. Należy użyć, aby zawęzić do głównej przyczyny takich wiadomości? Czy może istnieć Wydarzenie Rozszerzone, które może pomóc, lub jakiś audyt / rejestrowanie?

Mamy podobną konfigurację i ostatnio napotkaliśmy te komunikaty w dziennikach. Korzystamy z DELL Compellent SAN. Oto kilka rzeczy, które należy sprawdzić po otrzymaniu tych wiadomości, które pomogły nam znaleźć rozwiązanie

• Przejrzyj liczniki wydajności systemu Windows dla dysków, na które wskazują komunikaty ostrzegawcze, w szczególności:
  • Średni dysk czas czytania
  • Średni dysk czas na napisanie
  • Dysk odczytany w bajtach / sek
  • Dysk zapisu bajtów / sek
  • Transfery dysku / s
  • Śr. długość kolejki dyskowej
• Powyższe wartości są średnimi. Jeśli masz wiele plików bazy danych na jednym dysku, te średnie mogą wypaczyć wynik i zamaskować szyjkę butelki dla określonych plików bazy danych. Sprawdź to zapytanie od Paula S. Randala, który zwraca średnie opóźnienie dla każdego pliku z dmv sys.dm_io_virtual_file_stats. W naszym przypadku zgłoszone średnie opóźnienie było akceptowalne, ale pod okładkami mieliśmy wiele plików o średnim opóźnieniu> 200 ms.
• Sprawdź czasy. Czy jest jakiś wzór? Czy zdarza się to częściej w nocy? Jeśli tak, sprawdź, czy w tym czasie są uruchomione zadania konserwacyjne lub czy zaplanowane działanie, które może zwiększyć aktywność dysku i narazić szyjkę butelki w twoim podsystemie IO.
• Sprawdź, czy w przeglądarce zdarzeń systemu Windows nie ma błędów. Jeśli twój przełącznik lub SAN jest przeciążony lub nie został poprawnie skonfigurowany dla twojej aplikacji, możesz znaleźć pewne wiadomości w tym dzienniku i dobrze jest przekazać te informacje administratorowi SAN. W naszym przypadku często otrzymywaliśmy błędy połączenia iSCSI w ciągu dnia, co sugeruje problem.
• Przejrzyj kod programu SQL Server. Po otrzymaniu tych wiadomości nie należy od razu myśleć, że jest to problem z podsystemem IO i przekazać go administratorowi SAN. Musisz wykonać swoją część i przejrzeć bazę danych. Czy masz naprawdę złe zapytania, które często przepełniają tony danych? Złe indeksowanie? Nadmierny zapis dziennika transakcji? Możesz użyć niektórych zapytań typu open source, aby sprawdzić poprawność bazy danych, przykładem sprawdzania wyglądu planu zapytań jest sp_blitzCache
• Nie ignoruj ​​ich. Dzisiaj możesz otrzymywać je kilka razy dziennie ... a następnie kilka miesięcy później, gdy zwiększa się obciążenie pracą i zapomniałeś je monitorować, zaczynają rosnąć. Otrzymywanie wielu takich wiadomości może uniemożliwić dostęp do określonego pliku przez SQL Server, a jeśli jest to tempdb , nie jest to dobre. W naszym przypadku stało się tak źle, że SQL Server sam się zamknął.

Naszym rozwiązaniem było uaktualnienie naszego przełącznika do przełącznika SAN. Tak, są to wszystkie punkty do omówienia w programie SQL Server. Doprowadziło nas to do stwierdzenia, że ​​zmiana polegała na tym, że codziennie otrzymywaliśmy około 1500 błędów rozłączenia iSCSI pdu w przeglądarce zdarzeń aplikacji Windows na serwerze SQL. To spowodowało, że nasi administratorzy SAN przeprowadzili dochodzenie w sprawie zmiany.

Natychmiast po aktualizacji błędy iSCSI zniknęły, a średnie opóźnienie spadło do około 50 ms dla wszystkich plików, co korelowało z lepszą wydajnością aplikacji. Mając to na uwadze, mam nadzieję, że znajdziesz rozwiązanie.

Jest to o wiele rzadziej problem z dyskiem, a znacznie częściej problem z siecią. Wiesz, N w SAN?

Jeśli pójdziesz do swojego zespołu SAN i zaczniesz mówić o tym, że dyski są wolne, pokażą ci fantazyjny wykres z opóźnieniem 0 milisekund, a następnie wskażą zszywacz.

Zamiast tego zapytaj ich o ścieżkę sieciową do SAN. Uzyskaj prędkości, jeśli jest to wielokrotność itp. Uzyskaj od nich liczby o prędkościach, które powinieneś zobaczyć. Zapytaj, czy mają testy porównawcze od momentu skonfigurowania serwerów.

Następnie możesz użyć Crystal Disk Mark lub diskpd, aby sprawdzić te prędkości. Jeśli się nie ustawią, to najprawdopodobniej sieć.

Powinieneś także przeszukać dziennik błędów w poszukiwaniu komunikatów zawierających „FlushCache” i „saturation”, ponieważ mogą to być również oznaki niezgodności sieci.

Jedną z rzeczy, które możesz zrobić, aby uniknąć tych rzeczy jako DBA, jest upewnienie się, że twoja konserwacja i inne zadania wymagające dużej ilości danych (takie jak ETL) nie są wykonywane w tym samym czasie. To z pewnością może wywrzeć dużą presję na sieci pamięci masowej.

Możesz również sprawdzić odpowiedzi tutaj, aby uzyskać więcej sugestii: Powolny punkt kontrolny i 15 sekundowe ostrzeżenia we / wy w pamięci flash

Blogowałem na podobny temat tutaj: od serwera do sieci SAN

Po co przechowywać dane w sieci SAN? Jaki jest sens? Cała wydajność bazy danych jest powiązana z dyskowymi operacjami we / wy, a używasz 3 serwerów z tylko jednym urządzeniem dla operacji we / wy za nimi. To nie ma sensu ... i niestety tak powszechne.

Całe życie spotyka się ze źle zaprojektowanymi platformami sprzętowymi, na których ludzie próbują zaprojektować komputer na dużą skalę. Cała moc procesora tutaj, wszystkie dyski tam ... mam nadzieję, że nie ma czegoś takiego jak zdalna pamięć RAM. A najsmutniejsze jest to, że rekompensują brak wydajności tego projektu ogromnymi serwerami, które kosztują dziesięć razy więcej niż powinny. Widziałem 400 tys. Dolarów infra wolniej niż laptopa o wartości 1 tys. Dolarów.

Oprogramowanie serwera SQL jest bardzo zaawansowanym oprogramowaniem, które zostało zaprojektowane tak, aby wykorzystywać wszelkie elementy sprzętowe, rdzenie procesora, pamięć podręczną procesora, TLB, RAM, kontrolery dysków, pamięć podręczną dysku twardego ... Prawie zawierają całą logikę systemu plików. Są one opracowywane na zwykłym komputerze i testowane na wysokiej klasy systemach. Dlatego serwer SQL musi mieć własne dyski. Zainstalowanie ich w sieci SAN jest jak „emulacja” komputera, tracisz wszystkie optymalizacje wydajności. Sieci SAN służą do przechowywania kopii zapasowych, niezmiennych plików i plików, do których po prostu dołączasz dane (dzienniki).

Administratorzy centrum danych zwykle umieszczają wszystko, co mogą, w sieciach SAN, ponieważ w ten sposób mają tylko jedną pulę pamięci do zarządzania, jest to łatwiejsze niż dbanie o pamięć na każdym serwerze. Jest to wybór „nie chcę wykonywać swojej pracy” i bardzo zły, ponieważ wtedy muszą poradzić sobie z problemami z wydajnością i cała firma cierpi z tego powodu. Wystarczy zainstalować oprogramowanie na sprzęcie, dla którego zostało zaprojektowane. Nie komplikuj. Dbaj o przepustowość we / wy, pamięć podręczną i obciążenie związane z przełączaniem kontekstu, fluktuacje zasobów (zdarza się, gdy zasoby są współdzielone). Skończysz utrzymywanie 1/10 urządzeń dla tej samej surowej mocy wyjściowej, zaoszczędzisz zespołowi operacyjnemu wiele problemów, zyskasz wydajność, która sprawi, że użytkownicy końcowi będą szczęśliwi i bardziej produktywni, sprawi, że Twoja firma będzie lepszym miejscem do pracy, i oszczędzaj dużo energii (planeta będzie Ci wdzięczna).

Powiedziałeś w komentarzach, że rozważasz umieszczenie SSD na swoim serwerze. Nie rozpoznasz swojej konfiguracji za pomocą dedykowanych dysków SSD, w porównaniu z siecią SAN uzyskasz coś w rodzaju 500-krotnego ulepszenia, nawet z danymi i plikami dziennika transakcji na tym samym dysku. Najnowocześniejszy SQL Server miałby szybki oddzielny dysk SSD do rejestrowania danych i transakcji na różnych kanałach kontrolerów sprzętowych (większość płyt głównych serwera ma kilka). Ale w porównaniu do twojej obecnej konfiguracji mówimy o science fiction. Po prostu spróbuj SSD.

Ok, dla wszystkich zainteresowanych

Rozwiązaliśmy problem w pytaniu kilka miesięcy temu, po prostu instalując bezpośrednio podłączone dyski SSD na każdym z 3 serwerów oraz przenosząc dane DB i pliki dziennika z SAN na te dyski SSD

Oto podsumowanie tego, co zrobiłem, aby zbadać ten problem (korzystając z rekomendacji ze wszystkich postów to pytanie), zanim zdecydowaliśmy się zainstalować dyski SSD:

1) rozpoczął zbieranie liczników PerfMon dla następujących napędów na wszystkich 3 serwerach:

Disk F:jest dyskiem logicznym opartym na sieci SAN, zawiera pliki danych MDF
Disk I:jest dyskiem logicznym opartym na sieci SAN, zawiera pliki dziennika LDF
Disk T:jest bezpośrednio podłączony dysk SSD, dedykowany wyłącznie do tempDB

Zdjęcie poniżej to średnie wartości zebrane dla okresu 2 tygodni

Disk I: (LDF)ma tak małe We /
Wy, a opóźnienie jest bardzo niskie, więc Dysk I: można zignorować Widać, że Disk T: (TempDB)ma większe We / Wy w porównaniu do Disk F: (MDF)i ma znacznie lepsze opóźnienie w tym samym czasie - 0 ms

Oczywiście coś jest nie tak z dyskiem F: gdzie znajdują się pliki danych, ma wysokie opóźnienia i średnią kolejkę zapisu dysku, pomimo niskiego IO

2) Sprawdzone opóźnienie dla poszczególnych baz danych za pomocą zapytania z tej witryny

https://www.brentozar.com/blitz/slow-storage-reads-writes/

Niewiele aktywnych baz danych na serwerze podstawowym miało opóźnienie odczytu 150-250 ms i opóźnienie zapisu 150-450 ms
Co ciekawe, pliki bazy danych master i msdb miały opóźnienie odczytu do 90 ms, co jest podejrzane, biorąc pod uwagę mały rozmiar ich danych i niskie IO - kolejna wskazówka, że ​​coś jest nie tak z SAN

3) Nie było konkretnych terminów

Podczas których pojawił się komunikat „SQL Server napotkał wystąpienia ...”
Podczas logowania te komunikaty nie wymagały konserwacji ani dużego obciążenia dysku ETL

4) Podgląd zdarzeń systemu Windows

Nie pokazywał żadnych innych wpisów wskazujących na problem, z wyjątkiem „SQL Server napotkał wystąpienia ...”

5) Rozpocząłem sprawdzanie 10 najważniejszych zapytań

Od sp_BlitzCache (procesor, odczyty itp.) I optymalizacja tam, gdzie to możliwe
Brak ciężkich zapytań super IO, które zmarnowałyby tony danych i miałyby duży wpływ na pamięć masową, chociaż
indeksowanie w bazach danych jest OK, utrzymuję to

6) Nie mamy zespołu SAN

Mamy tylko 1 sysadmin, który okazjonalnie pomaga
Ścieżka sieciowa do SAN - jest multipatowana, każdy z 3 serwerów ma 2 kable sieciowe prowadzące do przełączników, a następnie do SAN, i ma to być 1 Gigabajt / s

7) Brak wyników CrystalDiskMark

Lub jakikolwiek inny wynik testu porównawczego z czasów konfiguracji serwerów, więc nie wiem, jakie powinny być prędkości , i nie można w tym momencie przeprowadzić testu porównawczego, aby zobaczyć, jakie są obecnie prędkości, ponieważ miałoby to wpływ na produkcję

8) Skonfiguruj sesję Extended Events na zdarzeniu punktu kontrolnego dla danej bazy danych

Sesja XE pomogła odkryć, że podczas komunikatów „SQL Server napotkał wystąpienia ...” punkt kontrolny działał bardzo wolno (do 90 sekund)

9) Dziennik błędów programu SQL Server

Zawiera wpisy „FlushCache” „Nasycenie” Powinny
się pojawiać, gdy czas punktu kontrolnego dla danej bazy danych przekroczy ustawienia interwału odzyskiwania

Szczegóły pokazały, że ilość danych, które punkt kontrolny próbuje spłukać, jest niewielka i zajmuje dużo czasu, a ogólna prędkość wynosi około 0,25 MB / s ... dziwne

10) Na koniec to zdjęcie pokazuje tabelę rozwiązywania problemów z pamięcią:

Wygląda na to, że po prostu mamy „Problem sprzętowy: - Współpracuj z administratorem systemu / sprzedawcą sprzętu, aby naprawić wszelkie błędne konfiguracje SAN, starych / wadliwych sterowników, kontrolerów, oprogramowania układowego itp.”

W innym pytaniu „Powolny punkt kontrolny ...” Powolny punkt kontrolny i 15-sekundowe ostrzeżenia we / wy w pamięci flash Sean miał bardzo ładną listę elementów, które należy sprawdzić na poziomie sprzętu i oprogramowania, aby rozwiązać problemy

Nasz sysadmin nie mógł sprawdzić wszystkich rzeczy z listy, więc po prostu postanowiliśmy rzucić trochę sprzętu na ten problem - wcale nie było drogo

Rozkład:

Zamówiliśmy dyski SSD 1 TB i zainstalowaliśmy je bezpośrednio na serwerach

Ponieważ mamy Grupy dostępności, zmigrowałem pliki danych DB z SAN na SSD w replikach pomocniczych, a następnie przełączyłem awaryjnie i migrowałem pliki na byłych podstawach. Pozwoliło to na minimalny całkowity czas przestoju - mniej niż 1 minutę

Teraz każdy serwer ma lokalną kopię danych DB, a do wspomnianej sieci SAN wykonywane są kopie zapasowe pełne / diff / log.
Żadnych komunikatów o wystąpieniach „SQL Server napotkał wystąpienia ...” w dziennikach Podglądu zdarzeń systemu Windows oraz wydajności wykonywania kopii zapasowych, kontroli integralności, przebudowy indeksu, zapytania itp. znacznie wzrosły

Ile poprawiła się wydajność pod względem opóźnień we / wy od migracji plików DB na dysk SSD?

Aby ocenić wpływ, wykorzystana wydajność Dzienniki Monitora wydajności systemu Windows 2 tygodnie przed migracją i 4 tygodnie po migracji:

Poniżej znajduje się porównanie statystyk opóźnień na poziomie DB (używane statystyki przechwyconych plików wirtualnych programu SQL Server przed i po migracji)

streszczenie

Migracja z SAN do bezpośrednio podłączonych lokalnych dysków SSD była tego warta.
Miało to ogromny wpływ na opóźnienie pamięci i poprawiło się średnio o ponad 90% (szczególnie operacje WRITE), a my nie mamy już skoków 20-50 sekund na IO

Przejście na lokalny dysk SSD rozwiązało nie tylko problemy z wydajnością pamięci, ale także bezpieczeństwo danych, o które martwiłem się (jeśli SAN ulegnie awarii, wszystkie 3 serwery tracą swoje dane w tym samym czasie)