Memory Management in Oracle JDBC 12c
Ist eine höhere Fetch Size besser? Wie wirkt sich die Fetch Size aus?
Was ist die fetch size, und warum ist das relevant?
Lassen Sie mich mit einer wahren Geschichte anfangen. Aber zunächst ein paar kurze Worte, worum es hier überhaupt geht. Unter fetch size verstehen wir die Anzahl der Records, die der Client "am Stück" von der Datenbank holt. Die default fetch size in Oracle JDBC ist 10, und gesetzt wird sie durch einen Methode des (Prepared)Statement-Objekts: Statement.setFetchSize(n). Diese Zahl ist wichtig, da jeder fetch einen network roundtrip zwischen Client und DB-Server benötigt – und jeder network roundtrip braucht Zeit.
Eine wahre Geschichte
Für einen Oracle DBA ist es eine ziemlich klare Sache: Erhöhen der fetch size kann ein hervorragender Tuningansatz sein. Wer hat nicht schon mal eine diese Demos gesehen (z.B. mit Lasttest-Tools wie Swingbench, auf das wir weiter unten eingehen), wo durch Erhöhung der fetch size ein enormer Durchsatzgewinn erreicht wurde?
So war es auch im Fall eines (ungenannten) DBAs einer (ungenannten) Firma, der dachte, einen bisher dort nicht beachteten Ansatz zur Performance-Verbesserung der inhouse betriebenen Applikation gefunden zu haben. Er führte systematische Performance-Tests durch – mit realen Statements der Applikation und realistischen Datensätzen – die zeigten, dass die optimale fetch size für diese Applikation bei ungefähr 80 lag. Diese Zahl meldete er an die Entwickler, und der Code wurde entsprechend erweitert. Es kamen der Tag des Test-Releases und des Releases in die Preprod-Umgebung. Die Performance war gut, und alle waren zufrieden.
Bis zum Produktionsrelease. Dann war es mit der Ruhe vorbei: der Applikationsserver hatte einen Out of Memory Error nach dem anderen. Der Code wurde zurückgerollt, und es gab ein Emergency Release. Das ganze war kein Erfolg gewesen, und das Thema war gestorben.
Wie war das möglich? Wie kann ein immerhin noch gemässigter Wert von 80 zu solch massiven Memory-Problemen führen?
Laut dem Whitepaper "Oracle JDBC Memory Management" [1] hat Oracle die Art und Weise, wie Memory für Resultate alloziert wird, zwischen 11g und 12c grundlegend geändert. Das könnte bedeuten, dass massive Probleme wie die gerade beschriebenen nicht mehr oder nur unter wesentlich anderen Bedingungen auftreten sollen. Aber reicht das Zitieren eines Whitepapers, um nach einem Fehlschlag wie dem obigen die Kollegen und Vorgesetzten zu überzeugen, es noch einmal zu versuchen? Das ist nicht so sicher. Bessere Chancen hat der o.g. DBA wahrscheinlich mit einem handfesten Experiment, das den neuen Treiber einmal richtig auf die Probe stellt. So ein Experiment – damit der DBA es nicht selber machen muss – möchte ich im folgenden vorstellen.
Aber zunächst noch einmal kurz zurück zur Theorie. Jetzt, wo wir (hoffentlich ;-)) überzeugt sind, dass dies ein wichtiger Parameter ist, was genau ist denn nun die fetch size?
JDBC Fetch size "deep dive"
Wenn ein JDBC oder OCI Client Daten aus der Datenbank holen will, werden verschiedene Schritte durchlaufen. Ein Cursor wird geöffnet, und ein Statement wird geparst (PARSE). Falls das Statement ein Resultat zurückliefert, müssen Outputvariablen definiert werden. Werden Bindvariablen verwendet, müssen Werte gebunden werden. Dann folgt die Ausführung des Statements (EXECUTE), und schliesslich werden die betroffenen Records geholt (FETCH). Sind alle Records bezogen, wird der Cursor geschlossen.
Die in Klammern angegebenen Schritte sieht der DBA als Zeilen-Präfix, wenn er z.B. ein SQL Trace (Event 10046) zieht. Aber wenn wir z.B. in sqlplus eine Query absetzen, kommt das ganze Ergebnis auf einmal zurück. Der sqlplus Client führt für uns transparent alle erforderlichen Schritte aus, und wir bekommen nur das Ergebnis zu sehen.
Wie sieht es mit JDBC aus? Schauen wir uns ein einfaches Beispiel an:
Wir sehen den PARSE-Schritt (conn.prepareStatement()), das EXECUTE (stmt.executeQuery()), und dann einen Loop, der über ein ResultSet iteriert (FETCH). Einer nach dem anderen wird hier ein Record aus dem ResultSet gezogen. Wo ist hier die fetch size? Sie ist vom Java-typischen Iterator-Interface verborgen.
Tatsächlich erhalten wir einen grösseren Einblick in die Funktionsweise, wenn wir uns die entsprechenden Funktionen in OCI (Oracle Call Interface, [2] anschauen. (Tatsächlich gibt es auch einen OCI (sog. "Thick") JDBC Driver für Oracle, in der Praxis wird aber meist der Thin Driver benutzt, auf den sich diese Ausführungen beziehen.) Auch wenn man nicht selbst OCI pogrammiert (C/C++), kann es interessant sein, in der OCI Dokumentation nachzuschauen, da OCI im Vergleich zu JDBC mehr "low-level" ist und dadurch mehr über die Implementierung verrät.
Damit die folgende Erklärung verständlich ist, zunächst noch ein paar Worte über den "Cursor". Cursor ist ein vieldeutiges Wort, das je nach Kontext ganz unterschiedlich gebraucht wird – etwa vom DBA oder vom Applikationsentwickler. Selbst unter Entwicklern werden die Assoziationen unterschiedlich sein, je nachdem ob man z.B. PL/SQL oder Java programmiert.
Am umfassendsten und präzisesten ist m.E. die Definition, wie sie Christian Antognini in seinem Buch Troubleshooting Oracle Performance (Apress, 2. Auflage 2014) gibt: "A cursor is a handle (that is, a memory structure that enables a program to access a resource) that references a private SQL area with an associated shared SQL area."
Das bedeutet, ein Cursor ist zunächst eine Struktur (Handle/Pointer) im clientseitigen Memory. Dieser Handle referenziert einen Ort im privaten SQL – Bereich auf dem Server (in der sog. UGA). Und dieser wiederum enthält einen Verweis auf eine Struktur im Shared Memory des Servers (SGA).
In OCI nun kann der Entwickler zwei Dinge festlegen:
- Erstens, wieviele Records der Ergebnismenge eines Cursors in einem Network Roundtrip geholt werden sollen. Dies ist eine Eigenschaft des Statement Handles, hier der Parameter OCI_ATTR_PREFETCH_ROWS:
OCIAttrSet (stm, OCI_HTYPE_STMT, &rows, sizeof(rows), OCI_ATTR_PREFETCH_ROWS, err);
Wenn das Statement ausgeführt wird, kann es eine variable Anzahl Records aus diesem Result Set in die definierte Output-Variable laden. Default ist 0; im Falle eines grösseren Werts muss es sich bei der Output-Variable um einen Array handeln.
Diese Anzahl wird durch den hier iters genannten Parameter (Position 4) der Funktion OCIStmtExecute bestimmt:
OCIStmtExecute(svc, stm, err, iters, 0, 0, 0, OCI_DEFAULT);
Falls mehr Records aus der Ergebnismenge des Cursors geholt als jetzt in die Output Variable geladen werden, müssen die übrigen im Memory zwischengespeichert werden. Nehmen wir z.B. einmal an, OCI_ATTR_PREFETCH_ROWS sei auf 20 gesetzt und iters=0 – dann müssen im Falle dieses Codes
while (ret = OCIStmtFetch2(stm, err, 1, OCI_FETCH_NEXT, 0,
OCI_DEFAULT) == OCI_SUCCESS) {
//do something
} 20 – 1 = 19 Records im Client Memory zwischengespeichert werden.
Wir sehen also, dass es sich, wenn wir von FETCH reden, um zwei Dinge handeln kann:
- Abholen von Records aus der Ergebnismenge eines Cursors (erfordert Network Roundtrip) und
- Speichern von Records aus dem nun auf dem Client vorrätigen Set in einer Outputvariable
Für beide kann die gewünschte Menge an Records definiert werden. Wenn wir nun von fetch size sprechen, beziehen wir uns auf die Anzahl der abzuholenden Records vom Server, also auf die erstere Aktion mit dem Statement Handle-Parameter OCI_ATTR_PREFETCH_ROWS. Aus Sicht des Clients wäre tatsächlich die Benennung prefetch size klarer; da aber die fetch size auch serverseitig von Bedeutung ist, macht es Sinn, bei fetch size zu bleiben.
Das Verhältnis zwischen beiden Mengenkonfigurationen entscheidet, wieviel Memory zur Zwischenspeicherung erforderlich ist.
Zurück zu JDBC. Auch wenn wir hier beide Aktionen nicht getrennt sehen können, gilt, dass während des Iterierens durch das Result Set die noch nicht erreichten Records zwischengespeichert werden müssen.
Daraus ist offensichtlich, dass das Setzen der fetch size zwangläufig einen Trade-Off von Memory-Verbrauch und Durchsatz bedeutet. Schauen wir uns jetzt konkret an, wie der 11g Treiber und der 12c Treiber jeweils Memory allozieren.
Memory Management in Oracle JDBC 11g
Um die Daten, die vom Datenbankserver zu kommen, zu speichern, muss der JDBC Treiber Memory auf dem Client allozieren. In 11g hat jedes Statement-Objekt 2 buffer, ein char[] und ein byte[]. Im char[] werden (N)CHAR und (N)VARCHAR2 Daten gespeichert, im byte[] alle anderen.
Die Grösse der Arrays wird berechnet, wenn das Statement geparst wird, basierend auf Datentyp und -länge der der Spalten. Was an Daten tatsächlich zurückkommt, spielt keine Rolle – ausschlaggebend ist, welche Datenmenge maximal – aufgrund der Spaltendefinion – möglich ist.
Schauen wir uns ein Beispiel an.
CREATE TABLE allocs (id NUMBER, created DATE, short_desc VARCHAR2(30), long_desc VARCHAR2(4000));
Um einen Record aus dieser Tabelle zu laden, muss der JDBC Treiber wie folgt Memory allozieren:
- 22 bytes für die NUMBER Spalte (im byte[]),
- 22 bytes für die DATE Spalte (im byte[]),
- 60 bytes für die VARCHAR2(30) Spalte (im char[]), sowie
- 8000 bytes für die VARCHAR2(4000) Spalte (im char[]).
Weshalb 8000 (bzw. 60) und nicht 4000 (bzw. 30) für die VARCHAR2 Spalten? Der Grund ist, dass in Java ein Character in zwei bytes gespeichert wird.
Stellen wir uns jetzt vor, wir hätten eine Tabelle mit 255 VARCHAR2 Spalten. Unabhängig davon, wie diese gefüllt ist, müssen wir abhängig von der fetch size wie folgt Memory allozieren:
| Fetch size | Memory usage |
|---|---|
| 1 | ~ 2 MB |
| 10 | ~ 20 MB |
| 100 | ~ 200 MB |
| 1000 | ~ 2 GB |
Wenn man diese Zahlen sieht, gehört nicht viel dazu sich vorzustellen, dass je nach Tabellendefinition auch bei alles andere als exzessiven fetch sizes massive Memory Probleme auftauchen können. Wie sieht es nun dagegen im 12c Treiber aus?
Memory Management in Oracle JDBC 12c
In 12c gibt es nur noch einen buffer, vom Type byte[], in dem alles gespeichert wird. Und jetzt kommt der grosse Unterschied: Wieviel Memory tatsächlich alloziert wird, hängt nicht von der Datendefinition, sondern von der tatsächlichen Datenmenge ab! Nehmen wir wieder die 255 x VARCHAR2(4000) Tabelle als Beispiel. Diesmal müssen wir auch entscheiden, wie sie tatsächlich gefüllt sein soll. Stellen wir uns also vor, 170 der 255 Spalten seien NOT NULL und hätten eine durchschnittliche Länge von 30 chars.
Jetzt verhält es sich mit dem Memory Bedarf doch etwas anders:
Tabelle 2
| Fetch size | Memory usage |
|---|---|
| 1 | ~ 5 kb |
| 10 | ~ 50 kb |
| 100 | ~ 500 kb |
| 1000 | ~ 5 MB |
Soweit so gut – in der Theorie sieht das hervorragend aus. Wie ist es jetzt aber in der Praxis? Genau das wollte ich auch wissen und habe daher die Probe aufs Exempel gemacht.
Der Test
Für den Test habe ich das populäre Load Testing Tool Swingbench [3] benutzt. Swingbench beinhaltet – ausser dem Benchmarking Framework selber – derzeit vier Benchmarks, es ist aber auch möglich, eigene Tests zu entwerfen, mit eigenen Tabellen und eigenem Java Code.
Das gab mir die Möglichkeit, den Test auf die Fragestellung abzustimmen. Die Testtabelle bestand aus 200 VARCHAR2(4000) Spalten, im Schnitt 160 bytes lang. Das bedeutete für den 11g Treiber, dass pro Record ~1.5 M zu allozieren waren, für den 12c Treiber hingegen ~30k. Die Query war so gewählt, dass genügend Raum zur Variation der fetch size bestand: Die Abfragen gaben im Durchschnitt 1000 Records zurück.
Unabhängige Variablen waren die Treiberversion - 11.2.0.4 vs. 12.1.0.2 – sowie die fetch size. Für beide Treiber wurde, um die Bedingungen möglichst konstant zu halten, ojdbc6.jar verwendet. Die abhängigen Variablen waren:
- die Anzahl der abgeschlossenen Transaktionen, wie sie vom Swingbench Framework zurückgegeben wird
- die maximale und die durchschnittliche Heap Size, sowie das allozierte Memory für char[] und byte[] Objekte, gemessen durch Java Flight Recorder (Achtung: lizenzpflichtig im produktiven Einsatz)
- die Art der Auslastung des Datenbankservers, gemessen durch SQL trace. Die Trace- bzw. daraus generierten tkprof-Dateien dienten im Wesentlichen der Kontrolle ("Sanity Check"), liefern aber auch einen hervorragenden Einblick, wie sich das Geschehen aus Sicht des DB-Servers darstellt.
Die heap size war dabei auf maximal 1 G limitiert. Getestet wurde mit 4 gleichzeitigen Usern, und die Laufzeit der Tests war jeweils 20 Minuten pro Kombination von Treiber-Version und fetch size. Wie stichprobenartige Tests zeigten, war diese Zeit komplett ausreichend, um stabile Resultate zu erhalten.
Und das Ergebnis?
Abgeschlossene Transaktionen – 11.2.0.4
Schauen wir zunächst, wieviele Transaktionen unter 11.2.0.4 erfolgreich abgeschlossen wurden, abhängig von der fetch size:
Unsere baseline ist 28 - der Wert für die default fetch size von 10. Wenn wir fetch size auf 50 erhöhen, ist der Durchsatz nahezu verdoppelt (78). Dann aber geht es wieder bergab. Eine weitere Stufe bergab geht es bei 200; ab 350 wird keine einzige Transaktion mehr erfolgreich abgeschlossen.
Abgeschlossene Transaktionen – 12.1.0.2
Schauen wir nun, wie es sich beim 12c Treiber verhält.
Für fetch sizes von 10 und 50 ist das Ergebnis noch ähnlich, dann aber ändert sich das Bild: Mit Erhöhung der fetch size steigt die Anzahl abgeschlossener Transaktionen weiter an. Die maximale Anzahl von 147 wird bei 1000 erreicht, danach geht wieder ein bisschen bergab – aber ohne wesentliche Verschlechterung. Da die Query so formuliert war, dass sie immer ~1000 Records zurückgab, war es unmöglich, mit Werten grösser als 1000 eine weitere Verbesserung zu erzielen. D.h. 1000 ist – wenn die Memory-Auslastung dies zulässt – die ideale fetch size für dieses Statement. Und wie wir sehen, ist es mit dem 12c Treiber möglich, diese fetch size tatsächlich zu benutzen!
Woran liegt es nun, dass wir mit dem 12c Treiber weitaus höheren Durchsatz erreichen? Zeit, auf die Memory-Auslastung zu schauen.
Heap Usage – 11.2.0.4
Noch einmal zur Erinnerung, die maximale heap size war bei diesem Test auf 1GB gesetzt. Wir sehen, dass schon bei einer fetch size von 50 die maximale heap usage bei über 600 MB liegt. Das war die Konfiguration, mit der beim 11g Treiber der maximale Durchsatz erreicht wurde. Für fetch sizes von 100 und 150 liegt die maximale heap usage bei ~800 MB, danach beginnt sie zu sinken auf ~650-700 MB. Das sind genau die Fälle, wo der Durchsatz "eine Stufe nach unten" gerutscht war – offenbar konnten hier nicht mehr alle 4 Clients ausreichend Memory allozieren. Ab einer fetch size von 350 liegt dann die heap usage bei unter 50 MB, und ist damit kleiner als bei einer fetch size von 1. Hier konnte offenbar kein Client mehr Memory für die buffers allozieren, und dementsprechend wurde auch keine einzige Transaktion mehr erfolgreich abgeschlossen.
Wie sieht es dagegen mit dem 12cTreiber aus?
Heap Usage – 12.1.0.2
Wie beim Durchsatz sehen wir auch bei der Memory-Auslastung mit dem 12c Treiber ein komplett anderes Bild. Die maximale heap usage steigt kontinuierlich und sehr langsam an (Achtung: die Skala der x-Achse ist nicht metrisch). Bei einer fetch size von 1000, der optimalen Konfiguration für diese Query und diesen Datensatz, liegt die Heap Usage bei 148 MB – das sind nur ~60 MB mehr, als der 11g Treiber bei der default fetch size von 10 gebraucht hat.
Wird nun die fetch size weiter erhöht – auf in diesem Fall komplett sinnlose Werte wie 2000, 5000, 10000 – steigt die heap usage weiter an. Theoretisch würden wir eigentlich keinen weiteren Anstieg erwarten, da es keine zusätzlichen Records gibt, die noch geholt werden könnten, und Memory ja nur on demand alloziert wird.
Interessant ist es hier auch, auf die byte[] buffers zu schauen. Wie man sieht, werden pro fetch size – Erhöhung von 50 ~ 4 MB zusätzlich alloziert (zwischen fetch sizes von 50 und 1000). Dies entspricht ungefähr den erwarteten ~ 6 MB (32 kB für einen Record * 50 (Anzahl zusätzlicher Records) * 4 (Anzahl gleichzeitiger User).
Fazit und Empfehlungen zur JDBC Fetch Size
Aufgrund des Experiments können wir bestätigen, dass sich mit der neuen Memory Management – Strategie im 12c Treiber definitiv neue Möglichkeiten der Performance-Verbesserung eröffnen. Trotzdem sollte man einige Dinge beachten:
- Es hat keinen Sinn, unnötig hohe Werte zu konfigurieren. Statements, die nicht mehr als n Records zurückliefern, profitieren nicht von einer fetch size > n.
- Exzessive fetch sizes können zu einer unnötigen Memory Belastung führen (wenn auch mit sehr viel geringeren Auswirkungen als beim 11g Treiber).
- Gegebenenfalls kann es sinnvoll sein, für unterschiedliche Statements unterschiedliche fetch sizes zu konfigurieren.
- Wenn eine Anwendung ohnehin durch die Ergebnismenge "blättert", also nur n Records auf einmal darstellt, ist eine fetch size > n sinnlos.
- Hier wurde als Kriterium für Performance der Durchsatz (Throughput) betrachtet. Je nach Anwendung ist es empfehlenswert, auch die Latenz (Response Time / Latency) zu testen.
