Wyłączenie hyperthreading poprawi wydajność naszej instalacji SQL Server

Powiązane z: Bieżąca wiedza na temat SQL Server i Hyperthreading

Niedawno zaktualizowaliśmy nasz serwer bazy danych Windows 2008 R2 z X5470 do X5560 . Teoria mówi, że oba procesory mają bardzo podobną wydajność, jeśli w ogóle X5560 jest nieco szybszy.

Jednak wydajność programu SQL Server 2008 R2 była dość słaba w ciągu ostatniego dnia, a użycie procesora było dość wysokie.

Oczekiwana długość życia strony jest ogromna, uzyskujemy prawie 100% trafienie w pamięć podręczną stron, więc pamięć nie stanowi problemu.

Kiedy pobiegłem:

SELECT * FROM sys.dm_os_wait_stats 
order by signal_wait_time_ms desc

Mam:

typ_elementu_liczenie_zadania czas_elementu_maks. maks. czas oczekiwania
-------------------------------------------------- ---------- -------------------- -------------------- -------------------- --------------------
XE_TIMER_EVENT 115166 2799125790 30165 2799125065
REQUEST_FOR_DEADLOCK_SEARCH 559393 2799053973 5180 2799053973
SOS_SCHEDULER_YIELD 152289883 189948844 960 189756877
CXPACKET 234638389 2383701040 141334 118796827
SLEEP_TASK 170743505 1525669557 1406 76485386
LATCH_EX 97301008 810738519 1107 55093884
LOGMGR_QUEUE 16525384 2798527632 20751319 4083713
WRITELOG 16850119 18328365 1193 2367880
PAGELATCH_EX 13254618 8524515 11263 1670113
ASYNC_NETWORK_IO 23954146 6981220 7110 1475699

(Dotyczy 10 wierszy)

Ja także pobiegłem

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS (
   SELECT 
        wait_type, 
        wait_time_ms / 1000. AS [wait_time_s],
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS [pct],
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [rn]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
    'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH',
    'BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))

SELECT W1.wait_type, 
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

I dostał

typ_elementu czas_elementu_ct pct running_pct
CXPACKET 554821.66 65,82 65,82
LATCH_EX 184123.16 21,84 87,66
SOS_SCHEDULER_YIELD 37541.17 4,45 92,11
PAGEIOLATCH_SH 19018,53 2,26 94,37
FT_IFTSHC_MUTEX 14306.05 1.70 96,07

Pokazuje to ogromną ilość zapytań synchronizujących czas z udziałem równoległości (wysoki CXPACKET). Dodatkowo, anegdotycznie wiele z tych zapytań problemowych jest wykonywanych na wielu rdzeniach (nie mamy żadnych wskazówek MAXDOP nigdzie w naszym kodzie)

Serwer nie był obciążony dłużej niż dzień. Występuje duża wariancja w wykonywaniu zapytań, zwykle wiele zapytań wydaje się być wolniejszych niż na naszym poprzednim serwerze DB, a procesor jest naprawdę wysoki.

Czy wyłączenie funkcji Hyperthreading pomoże zmniejszyć zużycie procesora i zwiększyć przepustowość?

Nadal uważam, że testowanie określonego obciążenia , zgodnie z pierwotną odpowiedzią, jest jedynym sposobem, aby się upewnić. Nie jest to idealna odpowiedź, gdy próbujesz dostroić system produkcyjny (więc zapytałbym, czy możliwe jest uzyskanie identycznego stanowiska testowego w systemach, w których liczy się zarówno wydajność, jak i dostępność), ale jest to jedyny, który naprawdę mi odpowiada z.

Możemy mówić o teorii, czy Hyperthreading powinien boleć lub ogólnie poprawiać rzeczy (uważam, że jest to bardziej prawdopodobne, że to boli niż pomoc na serwerach, więc dla „ogólnego” wdrożenia prawdopodobnie wyłączę to), ale jest jest tylko jeden sposób, aby przekonać się, czy zmieni to konkretny przypadek - wypróbuj i przekonaj się.

Zgadzam się z tym

• w najlepszym wypadku zaleceniem jest „wypróbuj HyperThreading na swoim obciążeniu i zobacz, co się stanie”. Robimy to teraz, gdy piszę, i ... to nie jest dobre!
• prawdopodobnie zawsze powinieneś zacząć od wyłączonego HyperThreading, ponieważ jest to najbezpieczniejsze

Wygląda na to, że powinniśmy dostrajać dwie rzeczy:

1. MAXDOP (maksymalne stopnie równoległości). Wszystko, co czytam, wskazuje, że posiadanie tego niezwiązanego jest prawdopodobnie złym pomysłem, a dokumentacja Microsoft mówi:

   Ustawienie tej opcji [MAXDOP] na wartość większą niż 8 powoduje często niepożądane zużycie zasobów i obniżenie wydajności.

   coś wyższego niż 8nie jest ogólnie zalecane. Ustawiłem to 4na razie. Początkowo było zero (bez ograniczeń).

2. Próg kosztów dla równoległości. Wygląda na to, że wartość domyślna 5tutaj jest uważana za dość niską wartość domyślną zgodnie z kilkoma wpisami SQL MVP, które znalazłem - możemy ją ulepszyć, aby zmniejszyć ilość prób równoległości nawet podejmowanych przez program planujący.

Ale szczerze mówiąc, czują się jak obejścia; Myślę, że prawdziwym rozwiązaniem naszego obciążenia (indeks pełnotekstowy jest duży) jest wyłączenie HT.

Anandtech stwierdził, że przy czystym obciążeniu odczytu trochę go bolało, a przy dużym obciążeniu zapisu było to trochę zwycięstwo. Nie widziałem niczego, co mogłoby sprawić, żebym pomyślał, że trafi cię znacznie gorzej niż -5% lub wygraną znacznie lepszą niż 15%. Zauważ, że w przypadku Atomu jest to ogromna wygrana, ale jest to bardzo dziwna jednostka centralna.

Zmieniłeś tylko procesor? Przeszedłeś z 12 MB pamięci podręcznej i 4 wątków, czyli 3 MB pamięci podręcznej na wątek, do 8 MB pamięci podręcznej i 8 wątków, czyli 1 MB na wątek. To jest nadmierne uproszczenie, ale założę się, że to cię zabija, kiedyś uruchamiałeś zapytania w pamięci podręcznej, a teraz uruchamiasz je z pamięci RAM, ponieważ potrzebują więcej niż 1 MB, ale mniej niż 3 MB. Wyłączenie HT prawdopodobnie pomoże, ale wrócę do starego procesora. Wyłącz HT, a otrzymasz 2 MB na wątek, ale jeśli twoje obciążenie jest tak duże, to nie pomoże. Może się zdarzyć, że stary procesor z pamięcią podręczną o pojemności 12 MB jest znacznie szybszy do obciążenia.

Spróbowałbym wyłączyć HT i sprawdzić, czy to poprawa, ale podejrzewam, że pamięć podręczna jest najważniejsza dla twojego obciążenia pracą i być może będziesz musiał wrócić do układu 12 MB.

Hyperthreading jest w najlepszym wypadku po prostu sposobem na wyodrębnienie przełączania zadań z systemu operacyjnego i umieszczenie go w martwym punkcie, z bezpośrednim dostępem do pamięci podręcznej L1 i L2, co sprawia, że ​​przełączanie zadań jest szybsze.

Testy z VMWare wykazały, że wyłączenie HT nie spowodowało zauważalnej różnicy przy standardowym obciążeniu, a wzrost o 5% przy dużym obciążeniu, ze względu na fakt, że ESXi jest wystarczająco inteligentny, aby znać różnicę między „prawdziwym” wątkiem a „fałszywym” wątkiem (jest o wiele więcej niż to, ale w kategoriach laików). SQL Server 2005 nie jest wcale tak inteligentny, ale w połączeniu z nowoczesnym systemem operacyjnym wyłączanie HT powinno być niewielkie.

To powiedziawszy, zgadzam się z Ronaldem, że najprawdopodobniej będzie to twoja pamięć podręczna L2. Zmniejszenie wielkości pamięci podręcznej o 33% jest znaczne, a kiedy określamy nasze serwery SQL, zawsze za każdym razem wybieramy pamięć podręczną w stosunku do pierwotnej prędkości zegara.

Na podstawie mojego doświadczenia, HT sprawiał, że operacje we / wy trwały wiecznie na moich aktywnych węzłach w klastrze Windows 2008 R2 (z uruchomionym SQL Server 2008 R2). Ciekawym faktem było to, że nie znalazło to odzwierciedlenia w statystykach oczekiwania ani w pssdiag, który uruchomiłem dla wsparcia Microsoft.

Zauważyłem, że zauważyłem niski poziom I / O, obserwując liczniki systemu operacyjnego na dysku fizycznym. Jak zauważył Sam, pisałem o tym tu i tutaj

Jeśli NIE występują problemy we / wy i są związane z procesorem, sugeruję, aby rozpocząć w ten sposób:

Wskaż, które procesy i bloki T-SQL powodują największe wykorzystanie procesora. Z naszego doświadczenia wynika, że ​​po naprawieniu problemu z I / O (przez wyłączenie HT) zidentyfikowaliśmy kod, który działał okropnie w 2008 R2 i radził sobie dobrze w 2005. Napisałem o tym tutaj .

Pod dużym obciążeniem uruchom sp_whoisactive Adama Machanica. Możesz go pobrać stąd . Występowało bardzo wysokie wykorzystanie procesora z powodu nadmiernej ilości odczytów logicznych (20 milionów na zapytanie) z powodu naprawdę złego planu. Nasze procesy przeprowadzały sprzężenia anty-semi z tabelami, które zostały podzielone na partycje.

Moim następnym zaleceniem jest uruchomienie profilera, aby zidentyfikować zestaw kodu T-SQL, który ma zarówno wysoki odczyt logiczny procesora, jak i operacji we / wy.

Dzięki powyższym krokom byliśmy w stanie dostroić przestępcze procesy i przejść z 85% trwałego wykorzystania procesora do prawie zerowego.

Powodzenia i proszę o kontakt z linią, jeśli znajdziesz poprawkę, ponieważ chciałbym dodać tę skrzynkę na moim blogu.

Dzięki

Oscar

Trudno jest ustalić, czy HT jest dobre, czy złe.

To naprawdę zależy od wzorca obciążenia serwera na podstawie doświadczenia i czytania. To znaczy, gdy wpływa na wydajność, robi to tak źle : w przeciwnym razie jej nie zauważysz.

Teoria, którą przeczytałem, była taka, że ​​wątki dzielą pamięć podręczną, co oznacza, że ​​w niesprzyjających warunkach każdy wątek może nadpisać pamięć podręczną drugiego wątku. Jeśli nie masz dużo równoległości lub twoje obciążenie zawiera wiele krótkich zapytań, może to nie mieć na ciebie wpływu.

Próbowałem z MAXDOP i powinowactwem do procesora (w mojej ostatniej prawdziwej roli DBA w SQL Server 2000), ale nigdy nie znalazłem niczego rozstrzygającego: ale tylko dla mojego sklepu w tym czasie.

W ramach szybkiego testu można ustawić koligację procesora tak, aby korzystał tylko z fizycznych rdzeni (niższe liczby) i sprawdzał, co się stanie.

Jednak najwyżej tracisz połowę swoich rdzeni. W dzisiejszych czasach może to nie mieć znaczenia w porównaniu z tym, w co grałem kilka lat temu, kiedy było 2 vs 4 lub 4 vs 8. Teraz jest 8 vs 16 lub 16 vs 32.

Edycja: Test Slava Oks

Niestety nie sądzę, że uzyskasz bardziej jednoznaczną odpowiedź niż „spróbuj wyłączyć hiperwątkowanie i sprawdź, czy to pomoże”.

Pomimo pomocnej odpowiedzi Jonathana w moim oryginalnym wątku (który podałeś w swoim pytaniu), nigdy nie byłem w stanie uzyskać żadnych ostatecznych dowodów na temat wpływu HT na określone serwery, które badałem. W moim przypadku serwery zostały już zaplanowane na wymianę, więc po prostu pozwalamy tym zamiennikom „zająć się problemem”, że tak powiem.

Moja rada:

Wypróbuj ustawienie MAX Degree of Parallelism na poziomie serwera wynoszące 1 . Równoległość w SQL jest najbardziej przydatna w przypadku większych, dłuższych zapytań, a twoje obciążenie (jak zakładam) składa się z ogromnie dużej liczby mniejszych zapytań. To powinno całkowicie wyeliminować oczekiwania CXPACKET. Może to spowodować, że niektóre zapytania będą działały nieco dłużej, ale powinno pozwolić na większą „przepustowość” wszystkich zapytań na serwerze.

Miałem dobre wyniki, robiąc to na serwerach OLTP. Inne rodzaje serwerów (serwery raportujące, serwery przetwarzające, hurtownie danych) zdecydowanie wymagają wyższego zestawu MAXDOP.

Żeby było jasne, to ustawienie nadal pozwalałoby SQLowi używać wielu wątków dla każdej tabeli w JOIN, więc tak naprawdę nie eliminujesz całkowicie równoległości.

Przynajmniej warto spróbować, ponieważ ta zmiana ustawienia wchodzi w życie natychmiast i nawet nie wymaga ponownego uruchomienia usługi SQL: http://msdn.microsoft.com/en-us/library/ms181007.aspx
Oznacza to, że możesz przełączyć wrócił natychmiast, jeśli wszystko zaczęło iść do piekła.

Wyłączenie hyperthreadingu w BIOSie wymagałoby pełnego ponownego uruchomienia serwera, więc jest to trochę bardziej ryzykowne.

Dla przypomnienia, mieliśmy również nieoczekiwanie niską wydajność po aktualizacji serwera. Okazało się, że jest to spowodowane problemami z oszczędzaniem energii przez BIOS i procesor. Domyślnym ustawieniem na serwerze (HP) było zignorowanie kontroli szybkości procesora przez system operacyjny i użycie własnego algorytmu. Zmiana tego na sterowanie systemem operacyjnym i aktualizacja systemu BIOS spowodowała znaczną poprawę. Było kilka uwag do wydania (nie można ich teraz znaleźć), że wystąpił błąd BIOS, który blokował procesor na najniższym poziomie wydajności.

https://serverfault.com/a/196329/6390