Exadata & In Memory: Real World Performance (Teil 2)
Im ersten Teil dieses Artikels wurden unterschiedliche Abfrage-Profile basierend auf einem realen Kunden-Problem und deren Auswirkungen auf die Effektivität von Exadata SmartScans untersucht. Im zweiten Teil des Artikels vertiefen wir die Analyse noch weiter und schauen uns, basierend auf den Ergebnissen der Analyse, verschiedene Optimierungsmöglichkeiten für den vorliegenden Fall an.
Analyse auf Ausführungsplan-Ebene
Die Frage ist hier jetzt natürlich, was genau verursacht das zusätzliche TEMP-I/O und die massive Erhöhung der Datenbank- und CPU-Zeit?
Ein Blick in den entsprechenden Real-Time SQL Monitoring Report zeigt folgendes:
Im Vergleich zu den HASH JOINs darüber und darunter benötigen die beiden HASH JOIN BUFFERED jeweils 15GB an TEMP, was gelesen und geschrieben wird und die zusätzliche I/O-Menge von 30GB schreiben und lesen erklärt.
Was ist also das Besondere an diesen „BUFFERED“ HASH JOINs? Dazu muss man wissen, dass Oracle für die Implementierung von Parallel Execution das sogenannte Producer-Consumer Modell einsetzt. Hier sind die doppelte Menge an Parallel Execution Servern im Einsatz – bei einem DOP von 64 also 128 Stück – und diese agieren in zwei Sets mit jeweils 64 PX Servern. Wenn eine Redistribution der Daten laut Ausführungsplan notwendig ist (PX SEND / PX RECEIVE mittels einer sogenannten Table Queue (:TQxxxx im Plan)), sendet das eine PX Server Set die Daten an das andere PX Server Set, welches die Daten empfängt.
Nun kann es Situationen laut Ausführungsplan geben, in denen mehrere dieser PX SEND / PX RECEIVE-Operationen gleichzeitig aktiv wären, also mehrere Redistributionen der Daten zur gleichen Zeit notwendig wären – und dies ist von Oracle nicht implementiert/unterstützt (siehe rot umrandete PX Operationen in Abb. 18). Es kann also nur eine Redistribution der Daten gleichzeitig aktiv sein, und das ist genau der Grund und Zweck der HASH JOIN BUFFERED-Operationen: Ein non-BUFFERED HASH JOIN agiert so, dass er zuerst die erste Datenquelle einliest und das Hash Table gemäß der Join Keys erzeugt (idealerweise passt das Hash Table in den PGA-Speicher, wenn nicht, dann muss auf TEMP auslagert werden und der HASH JOIN wird zu einem One-Pass oder Multi-Pass HASH JOIN). Nachdem das Hash Table gebaut ist, kann die zweite Datenquelle verarbeitet werden. Dabei wird jeweils ein Lookup im Hash Table durchgeführt, und das Ergebnis kann sofort an die folgende Operation weitergereicht werden. Würden sich die oben im Plan markierten HASH JOINs so verhalten, müssten beide markierten PX SEND / RECEIVE-Pärchen gleichzeitig aktiv sein, was eben nicht unterstützt wird.
Daher ändert der BUFFERED HASH JOIN dieses Verhalten so ab, dass nach dem Verarbeiten der ersten Datenquelle, also nach dem Erzeugen des Hash Tables, die zweite Datenquelle gelesen und zwischengepuffert wird, ohne den tatsächlichen Join auszuführen. Dieser Schritt kann insofern optimiert werden, dass Oracle ein Bitmap aus der Verarbeitung der ersten Datenquelle vorliegt, die anzeigt, ob ein Hash Bucket Daten beinhaltet oder nicht. Sollten keine Daten in dem Hash Bucket aus der ersten Datenquelle liegen, können Daten aus der zweiten Datenquelle gleich verworfen werden, die diesem Hash Bucket zugeordnet werden, und müssen nicht zwischengepuffert werden (es gibt Ausnahmen wie den Right Outer Join, bei dem diese Optimierung nicht möglich ist, da alle Zeilen der zweiten Datenquelle laut Outer Join „überleben“).
Dieses „Zwischenpuffern“ der Daten sorgt also dafür, dass die entsprechende PX SEND / RECEIVE-Operation ausgeführt wird, bevor der eigentliche Join stattfindet, also bevor das Ergebnis des Joins an die folgende Operation weitergegeben werden kann. Natürlich bedeutet so ein Zwischenpuffern, dass zusätzlich PGA-Speicher benötigt wird, um die zweite Datenquelle des Hash Joins vorzuhalten. Je nach Datenmenge kann das zu massiver TEMP-Aktivität führen, falls der PGA-Speicher nicht zum Zwischenpuffern ausreicht.
Insbesondere „zerstört“ diese Form der Verarbeitung einen zentralen Vorteil von HASH JOINs im Allgemeinen, nämlich dass die zweite Datenquelle ohne weitere Verarbeitungsschritte direkt verarbeitet werden kann, sobald das Hash Table aus der ersten Datenquelle erzeugt wurde.
Es ist nicht einfach, zwischen der TEMP-Aktivität durch Bauen des Hash Tables und TEMP-Aktivität aufgrund des Zwischenpufferns zu unterscheiden, wobei Oracle hier durchaus Informationen zu den sogenannten „Workareas“ zur Verfügung stellt (Optimal/ One-Pass/ Multi-Pass, aber nicht im Monitoring-Report angezeigt). Wir können aber aufgrund des obigen Monitor- Reports davon ausgehen, dass die Hash Tables selbst in den PGA-Speicher passen, da sowohl der vorherige, als auch der nachfolgende HASH JOIN (ohne BUFFERED) keine TEMP-Aktivität verursachen und mit jeweils 2GB PGA-Speicher auskommen.
Einfluss der HASH JOIN BUFFERED auf Ausführungszeit
Um den Einfluss der HASH JOIN BUFFERED auf die Ausführungszeit näher untersuchen zu können, wurde das Kundenszenario in einem Test Case so nachgestellt, dass durch entsprechende Änderung des Joins-Kriteriums die Anzahl der HASH JOIN BUFFERED kontrolliert werden kann, ohne das Ergebnis der Abfrage zu beeinflussen.
Dies ist in einem Real-Life Szenario so einfach nicht möglich – wenn entsprechende Redistributionen notwendig sind, werden auch entsprechende HASH JOIN BUFFERED-Operationen im Ausführungsplan erzeugt werden. Man kann dies also normalerweise nur indirekt beeinflussen, in dem man die Menge der notwendigen Redistributionen beeinflusst, und dies ist in den allermeisten Fällen nur über den Einsatz von entsprechender Partitionierung der Daten möglich. Später dazu mehr.
Hier die entsprechenden Ergebnisse eines Best-Case (kein HASH JOIN BUFFERED) und eines Worst-Case (maximale Anzahl an HASH JOIN BUFFERED):
Best-Case:
X2-8: 22,83 Sekunden, im Durchschnitt ca. 10GB pro Sekunde gelesen, Peak 27GB pro Sekunde
X3-8: 12,81 Sekunden, im Durchschnitt ca. 17GB pro Sekunde gelesen, Peak 35GB pro Sekunde
Mit folgenden Session-Statistiken:
Hier haben wir also ein recht vergleichbares Profil mit der vorherigen einfachen SELECT MAX() UNION ALL-Abfrage, aber immer noch eine deutlich verlängerte Ausführungszeit (ca. 23 Sekunden anstatt 7 Sekunden).
Das Aktivitätsprofil ist auf Abb. 20 zu sehen, die Datenbankzeit auf Abb. 21.
Wir reden hier also fast von einer Halbierung der Laufzeit gegenüber der Original-Abfrage, einzig allein durch den Unterschied, dass zwei HASH JOIN BUFFERED durch non-BUFFERED-Varianten ersetzt wurden und dadurch kein TEMP-I/O benötigt wird, aber offensichtlich auch deutlich weniger CPU-Zeit verbraucht wird (ca. 23 Minuten anstatt ca. 35 Minuten).
Worst-Case:
X2-8: 123,0 Sekunden, im Durchschnitt ca. 1,8GB (4,2GB) pro Sekunde gelesen, Peak 15GB pro Sekunde
X3-8: 78,9 Sekunden, im Durchschnitt ca. 2,8GB (6,5GB) pro Sekunde gelesen, Peak 17GB pro Sekunde
Die Session-Statistiken deuten an, welche Mehrarbeit im Worst-Case Szenario notwendig ist:
Beachten Sie die Gesamtdatenmenge an I/O aus Datenbanksicht: 504GB (DB_PHYSICAL_IO = 516746MB) anstatt der ursprünglichen 212GB! Das ergibt sich, da ca. 146GB mehr gelesen (DB_PHYSICAL_READ = 367054MB) und geschrieben werden (DB_PHYSICAL_WRITE = 149692MB) müssen. Durch die Redundanz auf ASM-Ebene werden dadurch ca. 292GB auf die Festplatten geschrieben (SPINNING_DISK_WRITE = 299385MB) und eben 146GB wieder gelesen (SPINNING_DISK_READ = 149692MB), insgesamt also ca. 438GB an zusätzlicher I/O-Aktivität auf den Festplatten (SPINNING_DISK_IO = 449077MB) und um diese Menge erhöht sich eben auch die auszutauschende Datenmenge zwischen „Compute Nodes“ und „Storage Cells“ (TOTAL_INTERCONNECT = 467384MB).
Aber auch in diesem Fall handelt es sich im Grunde noch um das gleiche Optimierungsprofil durch die Exadata-Features, was man anhand der restlichen Statistiken ersehen kann (PHYSICAL_READ_FLASH_CACHE, STORAGE_INDEX_SAVED, SMART_SCAN_RETURNED).
Das Aktivitätsprofil (s. Abb. 23) und die Datenbankzeit (s. Abb. 24) sehen folgendermaßen aus:
Bei der Laufzeit zwischen Best-Case und Worst-Case liegen hier auf der X2-8 Umgebung über 100 Sekunden und auf der X3-8 Umgebung über 60 Sekunden. Wir reden hier also von einem Unterschied zwischen 25 und 150 Minuten Datenbankzeit zwischen Best-Case und Worst-Case und zwischen 212GB und 504GB an I/O-Menge aus Datenbank-Sicht!
Wichtig ist zu verstehen, dass wir hier bei Best-Case und Worst-Case von der gleichen Grunddatenmenge reden, die zu verarbeiten ist (18GB insgesamt), und die auch als Ergebnis des Joins entsteht, daran ändert sich nichts. Was sich ändert, ist die Join-Bedingung, so dass eine Umverteilung der Daten nach jedem Join notwendig wird. Diese zusätzlichen Umverteilungen benötigen Ressourcen (maßgeblich CPU-Zeit, aber auch Speicher und eventuell Netzwerk im Falle von RAC). Darüber hinaus verursachen diese zusätzlichen Umverteilungen aufgrund der beschriebenen Implementierungsbeschränkung mehr BUFFERED-Operationen, was wiederum mehr CPU-Zeit und mehr PGA-Speicher für die Zwischenpufferung der Daten benötigt.
Die Anzahl der HASH JOIN BUFFERED hat also maßgeblich Einfluss auf die Performance, umso mehr, umso weniger Zeit mit dem Scan der Daten verbracht wird. Man kann normalerweise, wie bereits erwähnt, die Erzeugung der BUFFERED-Operationen nicht direkt beeinflussen, insofern ist der Testfall hier nicht realistisch. Allerdings kann die Distribution der Daten beeinflusst werden und damit indirekt die Notwendigkeit für BUFFERED-Operationen.
Optimierungs- möglichkeiten
Welche Optimierungsmöglichkeiten ergeben sich aus dieser Analyse? Offensichtlich sind die HASH JOIN BUFFERED ein großes Problem, da sie sowohl mehr CPU-Zeit, als auch PGA-Speicher benötigen und damit auch indirekt I/O verursachen, wenn PGA-Speicher auf TEMP ausgelagert wird.
Obwohl PGA_AGGREGATE_TARGET bei diesen Tests auf 256GB (X2-8) bzw. 512GB (X3-8, 256GB pro Instanz) eingestellt war, wurde die Zwischenpufferung nicht im PGA durchgeführt, sondern auf TEMP ausgelagert. Durch Einsatz von manuellen PGA-Einstellungen kann dies zwar verhindert werden, führt aber zu massivem PGA-Verbrauch (in diesem Fall bis zu 40GB PGA-Speicher für eine Abfrage). Diese manuelle PGA Konfiguration (WORKAREA_SIZE_POLICY = MANUAL, HASH_AREA_SIZE, SORT_AREA_SIZE etc.) kann aber nur sinnvoll für einzelne Prozessschritte eingesetzt werden und sollte normalerweise nicht systemweit verwendet werden, da es ansonsten zu massiven Swap-Aktivitäten mit Systemstillstand bei Überallokation von PGA kommen kann.
Bei Einsatz solcher manuellen PGA-Einstellungen kann der Worst-Case um ca. 50 Sekunden in der Laufzeit reduziert werden. Interessanterweise ist das aber immer noch weit entfernt vom Best-Case-Szenario, da die HASH JOIN BUFFERED, auch wenn kein zusätzlicher TEMP I/O erzeugt wird, immer noch deutlich mehr CPU-Zeit benötigen als die NON-BUFFERED HASH JOINs.
Hier die entsprechenden Details einer entsprechenden Ausführung auf der X2-8:
X2-8: 70,0 Sekunden, im Durchschnitt ca. 3GB pro Sekunde gelesen, Peak 15GB pro Sekunde
Die Session-Statistiken zeigen, dass durch die Verwendung von zusätzlichem PGA-Speicher kein zusätzliches I/O mehr generiert wird (s. Abb. 25), während das Aktivitätsprofil (s. Abb. 26) zeigt, dass im Grunde nur noch die CPU-Zeit relevant für die Laufzeit ist.
Und die absolute Datenbankzeit zeigt, dass tatsächlich im Grunde der Anteil der zusätzlichen I/O-Zeit verursacht durch TEMP-Aktivitäten des Worst-Case Falls von oben wegfällt, aber die CPU-Zeit fast genau gleich bleibt wie oben durch die HASH JOIN BUFFERED (s. Abb. 27).
Aber auch das Best-Case Szenario ohne HASH JOIN BUFFERED ist immer noch weit entfernt von den Laufzeiten der UNION ALL-Abfragen (auf der X2-8 23 Sekunden vs. 7 Sekunden), obwohl im Grunde die gleiche Datenmenge zu verarbeiten ist. Natürlich ist eine Join-Operation deutlich komplexer als eine UNION ALL-Operation. Es stellt sich aber die Frage, ob diese Joins noch weiter optimiert werden können.
Wenn wir die Aktivitätsprofile der Best-Case Abfrage von oben anschauen, wird klar, dass die meiste Zeit in den Compute Nodes auf CPU verbracht wird – die HASH JOINs sind also sehr CPU-intensiv.
Bei genauerer Betrachtung der Aktivität auf Ausführungsplanebene wird deutlich, dass ein signifikanter Anteil der CPU-Zeit bei der Umverteilung/ Redistribution der Daten verbracht wird – gemäß Real-Time SQL Monitoring/ Active Session History reden wir hier von einem Anteil von 23% (X2-8) bzw. 26% (X3-8) der Samples, die CPU-Aktivität anzeigen.
Es ist also interessant, welche Laufzeiten erreicht werden können, wenn wir die Redistributionen weiter minimieren. Dazu werden die Daten geeignet nach dem Join-Kriterium HASH-partitioniert, so dass Full Partition Wise Joins möglich werden. Diese partitionsweisen Operationen haben mehrere Vorteile: Es wird potentiell deutlich weniger Speicher für die Join-Operationen benötigt, und es ist keine Redistribution der Daten notwendig – es können also möglicherweise die oben genannten ca. 25% CPU-Zeit eingespart werden.
Desweiteren sind durch die nicht notwendigen Redistributionen auch keine BUFFERED Operationen notwendig. Dies kann auch für sogenannte „Partial Partition Wise“-Operationen gelten, da hier nur eine der beiden Datenquellen umverteilt werden muss und insofern die Anzahl der Redistributionen potentiell immer noch geringer sind als ohne Partitionierung.
Insofern sollte im Fall von performance-kritischen Joins eine entsprechende Partitionierung zumindest erwogen werden. Natürlich kommt es dabei darauf an, wie die sonstigen Zugriffsmuster aussehen und ob durch die Partitionierung andere Probleme entstehen (zum Beispiel Verwendung von partitionsweisen Operationen nur bei bestimmten Parallelitätsgraden, die zu der Anzahl Partitionen passt, potentielle Ungleichverteilung von Daten in den Partitionen, Statistik-Management, Extent-Management, lokale Indizes, die pro Partition gelesen werden müssen etc.).
Hier die Ergebnisse des Full-Partition-Wise Joins:
X2-8: 14,8 Sekunden, im Durchschnitt ca. 15GB pro Sekunde gelesen, Peak 57GB pro Sekunde
X3-8: 7,6 Sekunden, im Durchschnitt ca. 29GB pro Sekunde gelesen, Peak 102GB pro Sekunde
Das ist nochmal eine signifikante Steigerung gegenüber den vorherigen Ergebnissen mit Joins. In diesem Falle hier gilt diese Laufzeit für alle Variationen (Best-Case / tatsächliche Abfrage / Worst-Case), so dass sich im Vergleich zu den Varianten mit mehreren BUFFERED-Operationen eine extreme Beschleunigung ergibt.
Die Session-Statistiken zeigen für alle Variationen folgendes Bild:
Hier sehen wir also wieder das von den vorherigen Abfragen optimale Bild ohne zusätzliches I/O, und ähnliche Einsparungen durch die Exadata-Optimierungen (eine leichte Verschiebung der Zahlen ergibt sich durch die Aufteilung der Daten auf mehrere HASH-Partitionen).
Die deutlich höhere Effizienz der Operation spiegelt sich auch in den Aktivitätsprofilen (s. Abb. 29) und der Datenbankzeit (s. Abb. 30) wider.
Mit Hilfe der Partition Wise Joins ist der Join nur noch doppelt so langsam wie der UNION ALL-Fall (auf der X2-8 14 Sekunden vs. 7 Sekunden).
Gegenüber dem Best-Case ohne entsprechende Partitionierung reden wir also hier von einer Reduktion von 35-40% sowohl der Laufzeit, als auch der Datenbankzeit. Die Peaks der Lesegeschwindigkeiten liegen wieder im erwarteten Bereich (57GB bzw. 102GB), auch wenn die Durchschnitts-Lesegeschwindigkeit immer noch deutlich unter dem theoretisch möglichen liegt.
Es wird also deutlich, dass die partitionsweise Verarbeitung der Daten eine Reduktion der Laufzeit/ Datenbankzeit sogar über dem vorhergesagten ermöglicht (ca. 25% CPU-Zeit in Umverteilung verbracht) und insofern offensichtlich auch beim eigentlichen Join wohl weniger CPU-Zeit benötigt.
Der Unterschied zwischen Partition-Wise-Joins und nicht-partitionierter Verarbeitung wird sogar noch signifikanter, wenn mit einem niedrigeren Parallelitätsgrad gearbeitet wird. Hier spielt eine ganz wichtige Eigenschaft von partitionsweisen Operationen eine Rolle, die bisher noch nicht erwähnt wurde: Bei den partitionsweisen Hash Joins werden die Hash-Tables partitionsweise erzeugt, unabhängig davon, wie viele Partitionen/ Daten insgesamt verarbeitet werden müssen, da immer genau eine Partition der einen Tabelle zusammen mit der passenden Partition der andere Tabelle (pro Parallel Execution Server) verarbeitet wird.
Bei nicht-partitionsweisen Operationen müssen alle Daten für das Erzeugen der Hash Tables auf die Parallel Execution Server verteilt werden, bevor mit der Verarbeitung der zweiten Datenquelle begonnen werden kann. Das heißt, der PGA-Speicher- und CPU-Bedarf liegt hier bei partitionsweiser Verarbeitung potentiell deutlich niedriger. Außerdem können die ersten Ergebnisse einer solchen Abfrage möglicherweise deutlich früher geliefert werden, wenn kein ORDER BY/ GROUP BY sowieso eine vollständige Verarbeitung der Daten erfordert.
Die wichtigsten Laufzeiten nochmal im Überblick
Zusammenfassung
- Die Exadata Plattform liefert bei entsprechendem Einsatz hervorragende Performance für Full Segment Scans durch die Smart Scan-Technologie und die zugrundeliegenden Features wie Parallelisierung auf Storage Ebene, Offloading, Storage Indexes und Cell Flash Cache.
- Eine hohe „Cell Offloading Percentage“ (in diesem Falle hier >90%) alleine sagt noch nichts aus über den tatsächlichen Vorteil – es kommt maßgeblich darauf an, wie viel Zeit mit anderen Aktivitäten als Scans verbracht werden.
- Je nachdem, wie viele Daten von den Storage Cells in die Compute Nodes transportiert werden müssen und wie viel CPU-Zeit in den Compute Nodes mit der Verarbeitung der Daten außerhalb der Scans zugebracht wird, kommen Vorteile der Smart Scan- oder In-Memory Column Store-Technologie nicht mehr so zur Geltung.
- Schon mittels einer relativ geringen Datenmenge (ca. 18GB) können die CPUs auf den Compute Nodes für mehrere Sekunden vollständig ausgelastet werden, wenn komplexere Operationen wie mehrfache Joins durchgeführt werden.
- Sowohl Exadata als auch der In-Memory Column Store bieten für Joins, die filtern, über die sogenannte Bloom Filter-Technologie die Möglichkeit, Joins über Smart Scans bzw. In Memory Scans zu optimieren, indem die weiter zu verarbeitende Datenmenge minimiert werden kann. In dem hier vorgestellten Szenario konnten diese aber nicht eingesetzt werden, da es sich um keine filternden Joins handelte, sondern Outer Joins.
- Die bei Parallelverarbeitung sehr häufig notwendige Umverteilung der Daten kann signifikant Zeit in Anspruch nehmen.
- Die aufgrund der Umverteilung und der damit einhergehenden Implementationslimitierung teilweise notwendigen BUFFERED Operationen beeinflussen die Laufzeit und den benötigten PGA- und TEMP-Speicher signifikant.
- Partitionsweise Verarbeitung (Partition Wise Operation) kann diesen Overhead minimieren und zu deutlich besseren Laufzeiten führen, insbesondere auch bei niedrigen Parallelitätsgraden oder auch serieller Verarbeitung.
- Auch beim Einsatz neuester Technologien und Features kann durch entsprechende Modellierung oder physisches Design eine signifikante Reduzierung der Laufzeit erreicht werden und je nach Anforderung auch notwendig sein.
