Frage Die Deaktivierung von Hyperthreading verbessert die Leistung unserer SQL Server-Installation


Bezüglich: Aktuelle Kenntnisse in SQL Server und Hyperthreading

Vor kurzem haben wir unseren Windows 2008 R2-Datenbankserver von einem aktualisiert X5470 zu einem X5560. Die Theorie ist, beide CPUs haben eine sehr ähnliche Leistung, wenn überhaupt ist der X5560 etwas schneller.

Allerdings war die Leistung von SQL Server 2008 R2 am letzten Tag ziemlich schlecht und die CPU-Auslastung war ziemlich hoch.

Die Seitenlebenserwartung ist enorm, wir erhalten fast 100% Cache-Treffer für die Seiten, so dass Speicher kein Problem darstellt.

Als ich lief:

SELECT * FROM sys.dm_os_wait_stats 
order by signal_wait_time_ms desc

Ich habe:

wait_type wait_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
-------------------------------------------------- ---------- -------------------- -------------------- -------------------- --------------------
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
SCHLAF_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

(10 Reihe (n) betroffen)

Ich rannte auch

-- 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

Und hab

wait_type wait_time_s 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

Das zeigt sehr viel Zeit beim Synchronisieren von Abfragen mit Parallelität (hoher CXPACKET). Darüber hinaus werden anekdotenmäßig viele dieser Problemanfragen auf mehreren Kernen ausgeführt (wir haben keine MAXDOP-Hinweise irgendwo in unserem Code)

Der Server wurde seit mehr als einem Tag nicht mehr belastet. Wir haben eine große Varianz bei der Ausführung von Abfragen. In der Regel scheinen viele Abfragen langsamer zu sein als auf unserem vorherigen DB-Server und die CPU ist wirklich hoch.

Wird Hyperthreading deaktiviert, um die CPU-Auslastung zu reduzieren und den Durchsatz zu erhöhen?


28
2017-10-25 04:53


Ursprung


siehe auch: ozamora.com/2010/09/sql-server-2008-r2-and-nehalem-processors und ozamora.com/2010/09/ ... - Sam Saffron
Denken Sie daran, dass CXPACKET nicht bedeutet, dass viel Zeit darauf wartet, dass Prozesse zusammengeführt werden. CXPACKET bedeutet, dass der Thread darauf wartet, dass ein anderer Thread seine Verarbeitung beendet. Sie müssen eine bestimmte Abfrage anzeigen, bei der ein Thread in CXPACKET wait angezeigt wird, und sehen, welche anderen Threads neben CXPACKET warten. Es ist normalerweise IO oder Netzwerk. In der obigen Ausgabe warten Sie auf Latches und werden entplaned. Einige Abfragen müssen angepasst werden, oder Sie müssen sehen, warum die Latchs ausgeführt werden. - mrdenny
In unserem Fall war CXPACKET hoch, da die anderen Threads gerade übermäßig aus dem Cache gelesen haben (20 Millionen logische Lesevorgänge pro Abfrage). Unser Fall war wieder ein schlechter Anti-Semijoin mit einem geteilten Tisch, der nur 700 K Reihen hatte. - ozamora
@mrdenny, yeah die hohe Wartezeit ist bedenklich wir untersuchen es im Moment. - Sam Saffron
blogs.msdn.com/b/repltalk/archive/2010/10/27/... - mxmissile


Antworten:


Das fühle ich immer noch Testen Sie Ihre spezifische Arbeitslast, wie die ursprüngliche Antwort, ist der einzige Weg, um sicher zu sein. Es ist keine ideale Antwort, wenn Sie versuchen, ein Produktionssystem abzustimmen (also würde ich fragen, ob es möglich ist, ein identisches Testbed in Systemen zu bekommen, wo sowohl Leistung als auch Verfügbarkeit wirklich wichtig sind), aber es ist das einzige, bei dem ich mich wirklich wohl fühle mit.

Wir können über die Hypothese sprechen, ob Hyperthreading generell etwas verletzen oder verbessern sollte (ich finde es eher schädlich als Hilfe auf Servern, also würde ich es für eine "generische" Bereitstellung wahrscheinlich deaktivieren), aber das ist es nur eine Möglichkeit, um sicher zu sehen, ob es in Ihrem speziellen Fall einen Unterschied machen wird, und das ist es versuchen und sehen.


10
2017-10-25 05:57



Hinweis: Ich habe keinen Downvote gemacht, wir brauchen alle Hilfe, die wir bekommen können, aber wir möchten vermeiden, dass ein Produktionssystem im Dunkeln gestochen wird. Ich möchte sicherstellen, dass wir genügend Informationen gesammelt haben, bevor wir mit dieser Einstellung spielen. - Sam Saffron
Ich bin mir sicher, dass Sie es vermeiden wollen, mit einem Produktionssystem zu "spielen". In einer idealen Welt würden wir deshalb Testumgebungen haben, die mit der Produktion identisch sind. Ich stimme zu, dass ich die Produktion nicht aufgrund von Spekulationen ändern möchte. Ich stehe jedoch zu meiner Antwort: Das Testen spezifischer Workloads ist ein wichtiger Teil von jede Bereitstellung und jeder, der dir etwas anderes sagt, ist ein Scharlatan. Für mich sind alle Anzeichen darauf, dass Hyperthreading hier ein Problem ist, aber wir können den ganzen Tag und die ganze Nacht über Dinge reden, und es wird immer noch nur einen Weg geben, das sicher zu wissen. - Rob Moir
Upvote hier - ich stimme der Antwort zu. Allgemeine Antwort ist: Schalten Sie Hyperthreading aus. Genauere Antwort ist: Es hängt von den Besonderheiten ab und MUSS GETESTET WERDEN. - TomTom
Seltsamerweise denke ich, dies ist die beste Antwort zu akzeptieren, Mist mit Maxdop-Einstellungen kann zu vielen Schwierigkeiten führen, nehalem CPU sind viel schneller als die Core-basierte Xeons selbst bei leicht langsameren Taktraten, finde ich die l2 zwischengespeicherten Argumente ein wenig von einem redhering, weil der l3 cache so viel größer ist. Als Anhang siehe: blog.stackoverflow.com/2010/10/database-upgrade Wenn jemand mehr als 20% Gewinn / Gewinn sieht ... ist das wahrscheinlich nicht auf HT zurückzuführen. - Sam Saffron
Ich hatte die entgegengesetzte Erfahrung mit @ TomTom und @ Robert. Ich habe festgestellt, dass HT on in der Regel 10-15% besser ist als off. Die Gelegenheit, die Leistung zu verbessern, war in der Tat selten. - Brian Knoblauch


I stimme zu

  • beim Beste Die Empfehlung lautet "versuchen Sie HyperThreading auf Ihrer Arbeitslast und sehen was passiert". Wir machen das gerade jetzt, während ich tippe, und .. es ist nicht gut!
  • Sie sollten wahrscheinlich immer mit deaktiviertem HyperThreading beginnen, da dies am sichersten ist

Es sieht so aus, als sollten wir zwei Dinge tunen:

  1. MAXDOP (maximale Parallelitätsgrade). Alles, was ich lese, deutet darauf hin, dass es eine schlechte Idee ist, dieses unbegrenzte zu haben, und das Microsoft-Dokumentation sagt:

    Wenn diese Option [MAXDOP] auf einen größeren Wert [als 8] gesetzt wird, führt dies oft zu unerwünschten Ressourcenverbrauch und Leistungseinbußen.

    alles höher als 8 wird nicht generell empfohlen .. also ich setze es auf 4 zur Zeit. Es war anfangs Null (unbegrenzt).

  2. Kostenschwelle für Parallelität. Anscheinend der Standard von 5 Hier wird ein ziemlich niedriger Standard als ein paar SQL MVP Posts betrachtet, die ich gefunden habe - wir können tune es auf um zu reduzieren, wie viel Parallelität sogar vom Scheduler versucht wird.

Aber ehrlich gesagt fühlen sich diese wie Workarounds an; Ich denke, die wahre Lösung für unsere Arbeitslast (Volltextindex schwer) ist die Deaktivierung von HT.


12
2017-10-25 09:48



MAXDOP verursacht auch Probleme mit HT, da es versuchen könnte, zwei Threads auf derselben CPU auszuführen, wenn Sie sagen, 8 Kerne und 16 Threads, und Ihr maxdop ist auf 10 gesetzt. Im Allgemeinen 1 MAXDOP pro logischer Prozessor sollte der max sein. Und das Ausführen von zwei Threads auf der gleichen CPU für den gleichen Prozess ist sinnlos. - Mark Henderson♦
@Farseeker, das nur passiert, wenn Sie kein HyperThreading-fähiges Betriebssystem haben. Windows neuer als 2000 ist sich dessen bewusst. - Mircea Chirea
Es ist erwähnenswert, dass diese Maxdop-Overrides nur Ärger verursachten. Standard war für uns in Ordnung - Sam Saffron
Die Standardversion von SQL Server maximiert bei MAXDOP von 4 anyways, wenn sie nicht gebunden ist. Brauchen Sie Enterprise, um höher zu gehen. Wir hatten einige Workloads, die am schnellsten mit MAXDOP 1 (Nicht-HT-Box, laufen mehrere 8-Core-AMDs) ... - Brian Knoblauch
@Brian Knoblauch - Ich weiß das über ein Jahr später, aber ich lief über diese "Standard-Version von SQL Server MaxDOP von 4 sowieso wenn links unbegrenzt" jede Chance, die Sie können mich auf einige Dokumentation zeigen. Wir sprechen gerade von der Verwendung von MAXDOP bei der Arbeit, aber nicht sicher, wofür wir es einstellen sollen. Dies bedeutet im Grunde 4 ist das gleiche wie ungebundene richtig? - Jeremy A. West


Anandtech fand heraus, dass es mit der reinen Leselast ein wenig weh tat, und mit einer hohen Schreiblast war es ein kleiner Gewinn. Ich habe nichts gesehen, was mich glauben macht, dass es Ihnen einen viel schlechteren Treffer als -5% bringt, oder einen Sieg viel besser als 15%. Beachte, dass es mit einem Atom ein großer Gewinn ist, aber das ist eine sehr merkwürdige CPU.

Alles, was du geändert hast, war die CPU? Sie gingen von 12 MB Cache und 4 Threads, also 3 MB Cache pro Thread, zu 8 MB Cache und 8 Threads, also 1 MB pro Thread. Nun, das ist zu simplifizierend, aber ich wette, das bringt Sie um, Sie haben Abfragen im Cache ausgeführt und führen sie jetzt aus dem RAM aus, weil sie mehr als 1 MB, aber weniger als 3 MB benötigen. Das Ausschalten von HT wird wahrscheinlich helfen, aber ich würde zur alten CPU zurückkehren. Schalten Sie HT aus und Sie erhalten 2MB pro Thread, aber wenn Ihr Arbeitspensum mit so viel zu kämpfen hat, wird es nicht helfen. Es kann gut sein, dass die alte 12MB Cache-CPU für Ihre Arbeitslast sehr viel schneller ist.

Ich würde versuchen, HT auszuschalten und zu sehen, ob das eine Verbesserung ist, aber ich vermute, dass der Cache für Ihre Arbeitslast sehr wichtig ist, und Sie müssen vielleicht wieder zum 12-MB-Chip gehen.


9
2017-10-25 06:32



Der L2-Cache pro Kernbeobachtung ist a massiv Vereinfachung, da die CPU eine volle Generation voraus ist (Nehalem / Core i7 vs Core 2 Quad Klasse). - Jeff Atwood
@Jess, @Ronald und Nehalem hat wenig L2-Cache. Die Masse ist L3, die über Kerne geteilt wird. - Mircea Chirea


Hyperthreading ist im besten Fall nur eine Möglichkeit, die Task vom Betriebssystem wegzuwechseln und auf den Chip zu setzen, mit direktem Zugriff auf den L1- und L2-Cache, wodurch Taskwechsel schneller werden.

Tests mit VMWare haben ergeben, dass die Deaktivierung von HT unter Standardlast keinen erkennbaren Unterschied und eine 5% ige Erhöhung unter schwerer Last ergibt, da ESXi schlau genug ist, den Unterschied zwischen dem "echten" Thread und dem "falschen" Thread zu erkennen (Da ist ein Menge mehr dazu als das, aber das ist in Laien ausgedrückt). SQL Server 2005 ist nicht ganz so intelligent, aber in Kombination mit einem aktuellen Betriebssystem sollte es wenig Vorteile haben, HT zu deaktivieren.

Alles in allem, stimme ich Ronald zu, dass es wahrscheinlich Ihr L2-Cache ist. Ein Rückgang der Cachegröße um 33% ist erheblich, und wenn wir unsere SQL-Server spezifizieren, gehen wir jedes Mal auf die rohe Taktgeschwindigkeit im Cache.


7
2017-10-25 06:49



Können Sie die Affinität extern einstellen, damit die richtigen 4 Kerne von SQL ignoriert werden? - Sam Saffron
Im Allgemeinen würden Sie die Affinität für jeden anderen CPU-Thread festlegen, aber solange MAXDOP richtig eingestellt ist, sehe ich keinen Grund, die Affinität überhaupt einzustellen. Mit HT wird jedoch der erste Thread, der auf einer CPU getroffen wird, der "Haupt" -Thread und der zweite Thread ist der "HT" -Thread. Es gibt jedoch keine echten "main" - und "ht" -Threads, weil es das ist, was zuerst dort angekommen ist, und wenn sie dann wechseln, ist die Reihenfolge umgekehrt. - Mark Henderson♦
Nehalem-basierte CPUs haben sehr, sehr wenig L2-Cache, die meisten davon L3 geteilt. - Mircea Chirea


Basierend auf meinen Erfahrungen machte HT die Nutzung von I / O-Operationen auf meinen aktiven Knoten in einem Windows 2008 R2-Cluster (mit SQL Server 2008 R2) unausweichlich. Eine interessante Tatsache war, dass sie sich weder in den Wartestatistiken noch in dem Pssdiag widerspiegelte, den ich für Microsoft-Support verwendete.

Die Art, wie ich niedrige I / O bemerkte, war nur, indem ich die OS-Zähler für die physische Festplatte beobachtete. Wie Sam sagte, habe ich darüber geschrieben Hier und Hier

Wenn Sie keine E / A-Probleme haben und CPU-gebunden sind, schlage ich vor, dass Sie folgendermaßen beginnen:

Bestimmen Sie, welche Prozesse und T-SQL-Blöcke am meisten CPU-Auslastung verursachen. Nachdem wir das Problem mit I / O behoben haben (indem wir HT deaktiviert haben), haben wir nach unserer Erfahrung Code identifiziert, der im Jahr 2008 R2 furchtbar lief und 2005 gut lief. Ich schrieb darüber Hier.

Führen Sie Adam Machanic unter hoher Last sp_whoisactive aus. Sie können es herunterladen von Hier. Wir hatten eine sehr hohe CPU-Auslastung aufgrund der übermäßigen Menge an logischen Lesevorgängen (20 Millionen pro Abfrage) aufgrund eines wirklich schlechten Plans. Unsere Prozesse führten Anti-Semi-Joins mit partitionierten Tabellen durch.

Meine nächste Empfehlung besteht darin, den Profiler auszuführen, um einen Satz von T-SQL-Code zu identifizieren, der sowohl hohe CPU- als auch logische E / A-Lesevorgänge aufweist.

Mit den obigen Schritten konnten wir die beleidigenden Prozesse optimieren und von 85% anhaltender CPU-Auslastung auf fast Null gehen.

Viel Glück und bitte zögern Sie nicht mich zu kontaktieren, wenn Sie eine Lösung finden, wie ich den Fall zu meinem Blog hinzufügen möchte.

Vielen Dank

Oscar


7
2017-10-25 12:15



+1 für den Profiler, speicherte mich oft, sobald ein Problempunkt identifiziert wurde - Mark Henderson♦
+1 Danke für all eure Vorschläge, unser SQL auf ein vernünftiges Level zu bringen ist ein absoluter Albtraum, wir sind ziemlich stark auf Volltexte angewiesen, oft suchen wir nach einer Liste von Items in bestimmten Tags, also greifen wir ins Ganze setze und filtere es runter. Wenn Sie zum Beispiel eine Liste von Fragen mit den Tags [x] und [y] nach Datum geordnet erhalten möchten, müssen Sie massive Datenmengen aus dem Volltext ziehen und dann einen massiven Join durchführen. - Sam Saffron
Verstanden. Nehmen Sie ein Beispiel und führen Sie es mit Statistik IO ON aus, um festzustellen, ob Sie eine Tabelle mit den logischsten Lesevorgängen ermitteln können. Wir waren 2005 wieder sehr gut und 2008 R2 wirklich schlecht. Wenn Sie nur eine hohe CPU-Auslastung feststellen und eine hohe CXPACKET-Wartezeit haben, versuchen Sie es zuerst, indem Sie den Kostenschwellenwert für Parallelität auf 10, 15 oder 20 erhöhen. - ozamora
Wenn nichts anderes hilft, offline die DB, HT ausschalten, und von dort aus gehen. Viel Glück - ozamora
sp_whoisactive ist ein ziemlich tolles Werkzeug, liebe die Art und Weise wie die Abfragen anklickbar sind - Sam Saffron


Ob HT gut oder schlecht ist, ist schwer zu bestimmen.

Es hängt wirklich vom Serverlastmuster ab, das auf Erfahrung und Lesen basiert. Das heißt, wenn es die Leistung beeinflusst, tut es dies schlecht: sonst merkt man es nicht.

Die Theorie, die ich las, war, dass die Threads Cache teilen, was bedeutet, dass unter ungünstigen Bedingungen jeder Thread den Cache des anderen Threads überschreiben kann. Wenn Sie nicht viel Parallelität haben oder Ihre Last viele kurze Abfragen enthält, hat das möglicherweise keinen Einfluss auf Sie.

Ich habe versucht mit MAXDOP und Prozessoraffinität (zurück in meiner letzten realen DBA-Rolle auf SQL Server 2000), konnte aber nie etwas Schlüssiges finden: aber nur für meinen Laden zu dieser Zeit.

Als schnellen Test können Sie die Prozessoraffinität so einstellen, dass nur physische Kerne (die niedrigeren Zahlen) verwendet werden und sehen, was passiert.

Sie verlieren jedoch höchstens die Hälfte Ihrer Kerne. Heutzutage ist das vielleicht egal, was ich vor ein paar Jahren gespielt habe, als es 2 gegen 4 oder 4 gegen 8 war. Jetzt sind es 8 gegen 16 oder 16 gegen 32.

Bearbeiten: Ein Test von Slava Oks


2
2017-10-25 19:20



sind die Kerne 0-3 physikalisch und 4-7 logisch? So funktioniert es? Wir konnten es nicht sagen, und ich konnte kein Werkzeug finden, um es mir mitzuteilen. - Jeff Atwood
@ Jeff Atwood: Ich werde später mehr finden. ich haben lies es irgendwo ... Vorerst: support.microsoft.com/kb/322385 - gbn
Dieser KB Artikel fasst es ziemlich zusammen. - pauska
Obwohl dieser KB-Artikel einige nützliche Informationen enthält, scheint er nicht direkt Jeffs Frage zu beantworten, wie genau die logischen Prozessoren physischen zugeordnet sind. Mein Gehirn ist ungefähr zur Hälfte durchgebraten, aber hoffentlich sollte dir dieser INTEL-Artikel geben, was du brauchst, um das Mapping herauszufinden: software.intel.com/de-de/articles/... siehe auch software.intel.com/de-de/blogs/2009/12/21/... mit den zugehörigen Links. - BradC
@ Jeff Atwood, @BradC: Lordy, schwer zu finden. Sehen Sie dies: Es beruht auf Intel-Empfehlungen. SQL Server verwendet die zugrunde liegende Windows-Enumeration download.microsoft.com/download/5/7/7/.... - gbn


Leider glaube ich nicht, dass Sie eine definitivere Antwort bekommen werden als "versuchen Sie Hyperthreading auszuschalten und sehen Sie, ob das hilft".

Trotz der hilfreichen Antwort von Jonathan in meinem ursprünglichen Thread (den Sie in Ihrer Frage verlinkt haben) war ich nie in der Lage, irgendwelche endgültigen Beweise über die Auswirkungen von HT auf die spezifischen Server zu erhalten, die ich untersuchte. In meinem Fall waren die Server bereits für den Austausch vorgesehen, also ließen wir diese Ersatzgeräte sozusagen "sich um das Problem kümmern".

Mein Rat:

Versuchen Sie eine MAX-Parallelitätseinstellung auf Serverebene von 1. Parallelität auf SQL ist die meisten nützlich für größere, länger laufende Abfragen sowieso, und Ihre Last (ich nehme an) besteht aus einer massiv hohen Anzahl von kleineren Abfragen sowieso. Dies sollte CXPACKET-Wartezeiten vollständig eliminieren. Dies könnte dazu führen, dass bestimmte einzelne Abfragen etwas länger ausgeführt werden, aber mehr "Durchsatz" von gesamten Abfragen auf dem Server ermöglichen sollten.

Ich habe gute Ergebnisse auf OLTP-Servern gemacht. Andere Arten von Servern (Berichtsserver, Verarbeitungsserver, Data Warehousing) benötigen definitiv den höheren MAXDOP.

Und um klar zu sein, diese Einstellung würde es SQL immer noch erlauben, mehrere Threads für jede einzelne Tabelle in einem JOIN zu verwenden, so dass Sie die Parallelität nicht wirklich eliminieren.

Zumindest einen Versuch wert, da diese Einstellungsänderung sofort wirksam wird und Sie nicht einmal den SQL-Dienst neu starten müssen: http://msdn.microsoft.com/en-us/library/ms181007.aspx
Das heißt, Sie könnten sofort zurückschalten, wenn die Dinge in die Hölle kommen.

Das Abschalten von Hyperthreading im BIOS würde einen vollständigen Serverneustart erfordern, ist also ein bisschen gefährlicher.


2
2017-10-26 16:08