Hinweis: Dieser Artikel ist auch als DOAG Kolumne erschienen.
Dieser Artikel ist der zweite einer dreiteiligen Serie, in der wir den Einfluss von CPU, Hauptspeicher und Storage auf die Datenbankperformance beleuchten.
Datenbanken werden immer größer, oft haben diese hunderte GB oder gar viele TB. Auch der maximale verfügbare Hauptspeicher pro CPU-Sockel ist in den letzten Jahren deutlich größer geworden. Moderne CPUs bieten pro CPU-Sockel zumindest 1 TB Hauptspeicher, meist sogar mehr – wobei man aufpassen muss, da einige Intel Xeon CPUs den maximalen Hauptspeicherausbau nur mit PMEM (Persistent Memory) ermöglichen. PMEM wurde seitens Intel inzwischen abgekündigt.
- AMD Epyc Zen 4 unterstützen bis zu 1.5 TB pro CPU-Sockel
- Intel Xeon 4 Gen unterstützt zumindest 1-2 TB pro CPU-Sockel
- IBM Power 10 unterstützt bis zu 4 TB pro CPU-Sockel
Somit steht einer sinnvollen Nutzung von Hauptspeicher mittels Oracle Buffer Cache nichts mehr im Weg. Frei nach dem Motto: „Jeder I/O, den man nicht machen muss, ist ein guter I/O.“
Aber von Anfang an: Aus „historischen“ Gründen nutzen viele DBAs auch bei sehr großen Datenbanken noch relativ bescheidene SGAs. Da der Buffer Cache einen wesentlichen Faktor des Datenbank-Performance-Tunings darstellt, ist er oft deutlich zu klein.
Performance Views
Oracle liefert in der Performance View V$DB_CACHE_ADVICE in der Spalte ESTD_PCT_OF_DB_TIME_FOR_READS eine grobe Schätzung, wie viel Prozent der Statementlaufzeit die I/O Waits darstellen. Meiner Erfahrung nach wird dieser Wert meist zu gering geschätzt!
Führt man nachfolgende Abfrage aus,
select SIZE_FOR_ESTIMATE, ESTD_PCT_OF_DB_TIME_FOR_READS
from V$DB_CACHE_ADVICE
where SIZE_FACTOR=1;
SIZE_FOR_ESTIMATE ESTD_PCT_OF_DB_TIME_FOR_READS
----------------- -----------------------------
1840 67.5kann man die aktuelle Größe des Buffer Caches in MB (SIZE_FOR_ESTIMATE) sowie in der Spalte ESTD_PCT_OF_DB_TIME_FOR_READS den geschätzten I/O-Wait-Anteil der SQL-Verarbeitung ablesen.
In diesem Beispiel sind es 67,5 %.
Ein typisches SQL-Statement verbringt während der Ausführung somit 2/3 der Zeit mit Warten auf I/O. Anders gesagt: Wären alle benötigten Daten im Buffer Cache vorhanden, dann würde die Laufzeit des Statements auf 1/3 sinken (in der Praxis sogar noch mehr)!
Da jeder I/O auch CPU-Leistung für die Durchführung benötigt, reduziert man zusätzlich den CPU-Bedarf.
Wie bereits im ersten Artikel über den Einfluss der CPU auf die Datenbankperformance geschrieben, ist es wichtig zu wissen, welche Art von Datenbanklast hauptsächlich vorherrscht. Im Fall von OLTP sollte der Wert von ESTD_PCT_OF_DB_TIME_FOR_READS im niedrigen einstelligen Bereich liegen. Bei DWH/DSS/Reporting kann es auch mal zweistellig werden (15-20 % sollte man nie überschreiten).
Die passende Einstellung finden
Wie findet man nun die passende Einstellung? Dazu kann man die View V$DB_CACHE_ADVICE nutzen. Sie liefert bei der folgenden Abfrage ausführlichere Informationen:
select SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PCT_OF_DB_TIME_FOR_READS
from V$DB_CACHE_ADVICE
order by SIZE_FOR_ESTIMATE;Damit bekommt man alle relevanten Informationen. Bei SIZE_FACTOR=1 sieht man die aktuelle Größe des Buffer Caches. Oracle schätzt die entsprechenden Werte für den Buffer Cache von ca. 1/10 bis knapp zur doppelten Größe.
Was macht man, wenn auch bei der doppelten Größe ein viel zu hoher Wert bei ESTD_PCT_OF_DB_TIME_FOR_READS steht? In diesem Fall ist der Buffer Cache viel zu klein und muss drastisch erhöht werden. In der Regel hat man von Zeile zu Zeile jeweils eine ähnliche Reduktion der ESTD_PCT_OF_DB_TIME_FOR_READS. Diese kann man grob weiterführen. Wenn zum Beispiel der Unterschied von Zeile zu Zeile ca. 3 % beträgt und die letzte Zeile immer noch 45 % für eine OLTP-Datenbank ausweist, muss man den aktuellen Buffer Cache zumindest 4-6 mal größer machen als aktuell.
Berechnung
Eine Verdoppelung des Buffer Caches hat eine Reduktion des ESTD_PCT_OF_DB_TIME_FOR_READS von 67 % auf 45 % ergeben. In den unteren Zeilen wurde die ESTD_PCT_OF_DB_TIME_FOR_READS pro Zeile um ca. 3 % geringer. Bei OLTP-Datenbanken wollen wir einen niedrigen einstelligen Wert von ESTD_PCT_OF_DB_TIME_FOR_READS. Jeweils 10 weitere 3 %-Sprünge bedeuten eine Verdoppelung des Buffer Caches. Somit werden wir bei einer zweifachen Verdoppelung = Vervierfachung bei geschätzten 15 % ESTD_PCT_OF_DB_TIME_FOR_READS landen. Damit ist das Ziel aber noch nicht erreicht, erst eine Versechsfachung sollte uns ans Ziel bringen.
In der Realität ist es leider nicht ganz so einfach. Die Sprünge der ESTD_PCT_OF_DB_TIME_FOR_READS von Zeile zu Zeile sind nicht gleich groß. Das hat etwas damit zu tun, ob und wann die benötigten Blöcke von Datenbankobjekten vollständig im Buffer Cache liegen. Diese grobe Abschätzung sollte uns aber zumindest eine sinnvolle Größenordnung liefern.
Praxis
In der Praxis wird man diesen Vorgang nach einigen Tagen/Wochen Betrieb wiederholen, bis man in eine Größenordnung des Buffer Caches kommt, wo die Schätzung von Oracle endlich konkreten Nutzen bringt. Erst wenn sich die ESTD_PCT_OF_DB_TIME_FOR_READS beim SIZE_FACTOR=1 in der gewünschten Größenordnung befindet und sich der Wert auch bei größerem Buffer Cache praktisch nicht mehr ändert, hat man die optimale Buffer-Cache-Größe erreicht.
Warnhinweis
Man darf die SGA/den Buffer Cache nicht ohne vorherige Anpassung des für den Server/die VM zur Verfügung stehenden Hauptspeichers durchführen. Das würde zu Swapping führen, was in Summe noch viel schlechter für die Performance wäre. Daher gilt die Regel: Man muss den nutzbaren Hauptspeicher um 10-15 % mehr erhöhen, als man die SGA (bzw. den Buffer Cache) vergrößert. Der Grund liegt in der Verwaltung des Hauptspeichers, die ebenfalls physisches Memory benötigt.
Durch ein passendes Sizing des Buffer Caches kann man speziell in OLTP Environments neben einem deutlichen Performanceschub auch ein stabileres Laufzeitverhalten erreichen. Wenn gleichzeitig mehrere Statements Daten von der Storage lesen müssen, werden die I/O-Zeiten ebenfalls höher, wodurch die Laufzeit eines Statements deutlich schwanken kann.
Im nächsten Artikel dieser Serie wird das Thema Storage behandelt.