Ich lade Sie ein, den Bericht von Wladimir Sitnikov aus dem Jahr 2016 mit dem Titel "PostgreSQL und JDBC: Alles aus unseren Ressourcen herausholen" zu lesen.
Guten Tag! Mein Name ist Wladimir Sitnikov. Ich arbeite seit 10 Jahren bei NetCracker und beschäftige mich hauptsächlich mit Leistung. Alles, was mit Java und SQL zu tun hat, ist meine Leidenschaft.
Heute werde ich darüber sprechen, auf welche Herausforderungen wir gestoßen sind, als wir PostgreSQL als Datenbankserver einsetzten. Wir arbeiten hauptsächlich mit Java, aber das, was ich heute bespreche, betrifft nicht nur Java. Die Praxis hat gezeigt, dass dies auch in anderen Programmiersprachen auftritt.
Wir werden Folgendes besprechen:
- Datenabfragen.
- Datenspeicherung.
- Und auch über die Leistung.
- Und die versteckten Fallen, die dabei lauern.
Lassen Sie uns mit einer einfachen Frage beginnen. Wir wählen eine Zeile aus einer Tabelle nach dem Primärschlüssel aus.
Die Datenbank befindet sich auf demselben Host. Und all das benötigt 20 Millisekunden.
Diese 20 Millisekunden sind ziemlich viel. Wenn Sie 100 solcher Anfragen haben, verschwenden Sie eine Sekunde nur damit, um diese Anfragen zu verarbeiten.
Wir machen das nicht gerne und schauen, was uns die Datenbank dafür anbietet. Die Datenbank bietet uns zwei Optionen zur Ausführung von Anfragen an.
Die erste Option ist eine einfache Anfrage. Was ist daran gut? Dass wir sie einfach übernehmen und absenden können, ohne mehr zu tun.
Die Datenbank hat auch eine erweiterte Anfrage, die cleverer, aber funktioneller ist. Man kann Anfragen für Parsing, Ausführung, Verknüpfung von Variablen usw. separat senden.
Super erweiterte Anfrage – das werden wir im aktuellen Bericht nicht abdecken. Vielleicht haben wir bestimmte Wünsche an die Datenbank, und es gibt eine Wunschliste, die in irgendeiner Form erstellt wurde, d.h. das sind Wünsche, die wir haben, die jedoch aktuell und im nächsten Jahr nicht möglich sind. Daher haben wir sie einfach aufgeschrieben und werden die Hauptverantwortlichen darum bitten.
Was wir tun können, sind einfache Anfragen und erweiterte Anfragen.
Was ist das Besondere an jedem Ansatz?
Einfache Anfragen sind gut für eine einmalige Ausführung. Einmal ausgeführt und vergessen. Das Problem ist, dass sie kein binäres Datenformat unterstützen, das heißt, für hochleistungsfähige Systeme sind sie nicht geeignet.
Die erweiterte Abfrage ermöglicht eine Zeitersparnis beim Parsen. Das haben wir umgesetzt und begonnen, es zu verwenden. Es hat uns enorm geholfen. Dabei geht es nicht nur um Einsparungen beim Parsen, sondern auch um die Reduzierung des Datenverkehrs. Daten im Binärformat zu übertragen ist viel effizienter.
Kommen wir zur Praxis. So sieht eine typische Anwendung aus. Das kann Java usw. sein.
Wir haben ein Statement erstellt. Den Befehl ausgeführt. Ein Close erstellt. Wo liegt der Fehler? Was ist das Problem? Es gibt keine Probleme. So steht es in allen Büchern. So sollte man schreiben. Wenn Sie maximale Leistung erzielen wollen, schreiben Sie es so.
Aber die Praxis hat gezeigt, dass das nicht funktioniert. Warum? Weil wir die Methode „close“ haben. Und wenn wir das so machen, sieht es aus Sicht der Datenbank so aus – das ist wie die Arbeit eines Rauchers mit der Datenbank. Wir haben „PARSE EXECUTE DEALLOCATE“ gesagt.
Warum diese unnötigen Erstellungen und das Entladen der Statements? Sie sind niemandem von Nutzen. Aber normalerweise geschieht das bei PreparedStatements, wenn wir sie schließen, wird alles in der Datenbank geschlossen. Das ist nicht das, was wir wollen.
Wir wollen, wie gesunde Menschen, mit der Datenbank arbeiten. Einmal haben wir unser statement vorbereitet, und dann führen wir es viele Male aus. Tatsächlich ist 'viele Male' in diesem Fall einmal für die gesamte Lebensdauer der Anwendung, die geparst wurde. Bei verschiedenen REST-Calls verwenden wir die gleiche statement id. Das ist unser Ziel.
Wie erreichen wir das?
Ganz einfach – wir schließen die statements nicht. Wir schreiben einfach: „prepare“ „execute“.
Wenn wir so etwas ausführen, ist klar, dass irgendwo etwas überlaufen wird. Sollte das nicht klar sein, können wir es messen. Lassen Sie uns einen Benchmark schreiben, der so eine einfache Methode enthält. Wir erstellen ein statement, führen es auf einer bestimmten Treiberversion aus und stellen fest, dass es ziemlich schnell mit dem Verlust des gesamten Speichers, den wir hatten, abstürzt.
Es ist klar, dass solche Fehler leicht behoben werden können. Darüber werde ich nicht sprechen. Aber ich werde sagen, dass es in der neuen Version viel schneller funktioniert. Die Methode ist nicht optimal, aber trotzdem.
Wie arbeitet man richtig? Was müssen wir dafür tun?
In der Realität schließen Anwendungen immer die statements. In allen Büchern steht, dass sie geschlossen werden sollten, sonst tritt ein Speicherleck auf.
Und PostgreSQL kann keine Abfragen cachen. Jede Sitzung muss ihren eigenen Cache erstellen.
Auch wir möchten keine Zeit mit dem Parsen verschwenden.
Wie gewohnt haben wir zwei Optionen.
Die erste Option ist, dass wir einfach sagen: Lassen Sie uns alles in PgSQL packen. Das hat einen Cache, der alles speichert. Das wird großartig werden. Allerdings haben wir uns das angesehen. Bei 100.500 Anfragen funktioniert das nicht. Wir sind nicht einverstanden, die Anfragen manuell in Prozeduren umzuwandeln. Nein, nein.
Die zweite Option ist, dass wir es selbst entwickeln. Wir öffnen den Quellcode und beginnen mit den Anpassungen. Wir arbeiten daran. Es stellte sich heraus, dass es nicht so kompliziert ist.
Das begann im August 2015. Mittlerweile gibt es eine modernere Version. Und es funktioniert so gut, dass wir im Anwendungsbereich keine Änderungen vornehmen. Wir haben sogar aufgehört, über PgSQL nachzudenken; das hat uns vollkommen gereicht, um alle Nebenkosten praktisch auf null zu reduzieren.
Dementsprechend wird das Server-prepared Statement beim fünften Durchlauf aktiviert, um den Speicher in der Datenbank für jede einmalige Anfrage zu schonen.
Man könnte fragen: Wo sind die Zahlen? Was bekommen Sie? Dabei kann ich hier keine Zahlen liefern, da jede Anfrage ihre eigenen Werte hat.
Wir hatten Anfragen, bei denen wir für OLTP-Anfragen etwa 20 Millisekunden für das Parsen aufwendeten. Es gab 0,5 Millisekunden für die Ausführung und 20 Millisekunden für das Parsen. Die Anfrage bestand aus 10 KiB Text und 170 Zeilen des Plans. Das ist eine OLTP-Anfrage. Sie fragt 1, 5, 10 Zeilen an, manchmal mehr.
Aber wir wollten überhaupt keine 20 Millisekunden verschwenden. Wir haben es auf 0 gesenkt. Alles lief großartig.
Was können Sie daraus lernen? Wenn Sie Java verwenden, nehmen Sie die moderne Version des Treibers und freuen sich.
Wenn Sie eine andere Sprache haben, überlegen Sie, ob das auch für Sie relevant sein könnte. Denn aus der Perspektive der Zielsprache, zum Beispiel wenn Sie PL 8 oder LibPQ verwenden, ist Ihnen möglicherweise nicht klar, dass Sie Zeit für das Parsen und nicht für die Ausführung verschwenden, und das sollte überprüft werden. Wie? Alles kostenlos.
Es sei denn, es gibt Fehler oder besondere Eigenschaften. Darüber werden wir jetzt sprechen. Der Großteil wird sich mit industrieller Archäologie befassen, mit dem, was wir entdeckt haben und worüber wir gestolpert sind.
Wenn die Anfrage dynamisch generiert wird. Das kommt vor. Jemand fügt die Zeilen zusammen und es entsteht eine SQL-Anfrage.
Was ist daran schlecht? Es ist schlecht, weil wir jedes Mal eine andere Zeile erhalten.
Und bei dieser vielfältigen Zeile muss der hashCode neu berechnet werden. Das ist wirklich eine CPU-intensive Aufgabe – es ist nicht einfach, in einem bestehenden Hash einen langen Anfrage-Text zu finden. Daher ist die einfache Empfehlung: Generieren Sie keine Anfragen. Bewahren Sie sie in einer einzigen Variable auf. Und freuen Sie sich darüber.
Das nächste Problem. Datentypen sind wichtig. Es gibt ORMs, die behaupten, es sei egal, welcher NULL-Typ verwendet wird, irgendein NULL reicht aus. Wenn es ein Int ist, verwenden wir setInt. Und wenn es NULL ist, dann sei es immer VARCHAR. Und was spielt am Ende schon für einen Unterschied, welcher NULL-Typ dort steht? Die Datenbank wird alles verstehen.
In der Praxis ist es der Datenbank jedoch völlig egal. Wenn Sie beim ersten Mal gesagt haben, dass es sich um eine Zahl handelt, und beim zweiten Mal gesagt haben, dass es VARCHAR ist, können die Server-prepared statements nicht wiederverwendet werden. In einem solchen Fall müssen wir unser Statement neu erstellen.
Wenn Sie dieselbe Anfrage mehrfach ausführen, stellen Sie sicher, dass die Datentypen in der Spalte nicht durcheinander geraten. Achten Sie auf NULL. Das ist ein häufiges Problem, das wir hatten, nachdem wir mit PreparedStatements angefangen haben.
Gut, wir haben es aktiviert. Vielleicht haben wir einen Treiber verwendet. Und die Leistung ist gefallen. Alles wurde schlecht.
Wie kann das sein? Ist das ein Bug oder ein Feature? Es war leider nicht möglich zu verstehen, ob es sich um einen Bug oder ein Feature handelt. Aber es gibt ein ganz einfaches Szenario zur Reproduktion dieses Problems. Es hat uns völlig überraschend erwischt. Es bezieht sich auf die Abfrage aus buchstäblich einer Tabelle. Natürlich hatten wir mehr solcher Abfragen. Diese umfassten in der Regel zwei bis drei Tabellen, aber es gibt auch dieses Szenario zur Reproduktion. Nehmen Sie Ihre beliebige Version der Datenbank und reproduzieren Sie es.
Der Kern der Sache ist, dass wir zwei Spalten haben, von denen jede indiziert ist. In einer Spalte gibt es eine Million Zeilen mit dem Wert NULL. In der anderen Spalte sind es nur 20 Zeilen. Wenn wir ohne verknüpfte Variablen ausführen, funktioniert alles gut.
Wenn wir beginnen, mit verknüpften Variablen auszuführen, d. h. wir führen das Zeichen „?“ oder „$1“ für unsere Abfrage aus, welches Ergebnis erhalten wir letztendlich?
Die erste Ausführung – wie es sein sollte. Die zweite – etwas schneller. Etwas wurde zwischengespeichert. Die dritte, vierte, fünfte. Dann kommt der Knack – und so passiert es. Und das Schlimmste ist, dass dies bei der sechsten Ausführung geschieht. Wer hätte gedacht, dass man genau sechs Ausführungen machen muss, um den tatsächlichen Ausführungsplan zu verstehen?
Wer ist schuld? Was ist passiert? Die Datenbank enthält eine Optimierung, die für einen generischen Fall optimiert ist. Entsprechend wechselt sie ab einem bestimmten Punkt auf einen generischen Plan, der leider anders sein kann. Er kann gleich sein oder auch anders. Es gibt einen bestimmten Schwellenwert, der zu diesem Verhalten führt.
Was kann man dagegen tun? Hier ist es natürlich schwieriger, Vermutungen anzustellen. Es gibt eine einfache Lösung, die wir verwenden: +0, OFFSET 0. Sicherlich kennen Sie solche Lösungen. Wir fügen einfach «+0» zur Anfrage hinzu, und alles ist gut. Das zeige ich später.
Eine weitere Möglichkeit ist, die Pläne genauer zu betrachten. Der Entwickler sollte nicht nur die Anfrage schreiben, sondern auch sechsmal «explain analyze» sagen. Wenn es nur fünf Mal ist, passt es nicht.
Und es gibt noch eine dritte Möglichkeit – einen Brief an pgsql-hackers zu schreiben. Ich habe das getan, aber es ist noch unklar, ob es ein Bug oder ein Feature ist.
Während wir darüber nachdenken, ob es ein Bug oder ein Feature ist, lassen Sie uns das Problem beheben. Nehmen wir unsere Anfrage und fügen ein „+0“ hinzu. Alles gut. Zwei Zeichen und wir müssen uns nicht einmal überlegen, wie es funktioniert. Es ist ganz einfach. Wir haben der Datenbank einfach untersagt, einen Index für diese Spalte zu verwenden. Wir haben keinen Index für die Spalte „+0“, und die Datenbank verwendet keinen Index, alles ist in Ordnung.
Das ist die Regel der sechs Erklärungen. In den aktuellen Versionen müssen Sie es sechs Mal machen, wenn Sie verbundene Variablen haben. Wenn Sie keine verbundenen Variablen haben, machen wir es so. Und letztendlich schlägt genau diese Anfrage fehl. Es ist kein kompliziertes Problem.
Es scheint, als könnte es nicht schlimmer werden. Hier ein Bug, dort ein Bug. Tatsächlich gibt es überall Bugs.
Lassen Sie uns noch einmal schauen. Nehmen wir an, wir haben zwei Schemata. Schema A mit Tabelle Y und Schema B mit Tabelle Y. Die Anfrage lautet: Daten aus der Tabelle auswählen. Was wird dabei passieren? Wir erhalten einen Fehler. Alles, was wir zuvor erwähnt haben, wird stattfinden. Die Regel ist: überall ein Bug, es wird alles oben Genannte auftreten.
Jetzt die Frage: „Warum?“. Es scheint, es gibt eine Dokumentation, die besagt, dass, wenn wir ein Schema haben, die Variable „search_path“ angibt, wo wir nach der Tabelle suchen müssen. Es scheint, die Variable existiert.
Was ist das Problem? Das Problem ist, dass server-prepared statements nicht erwarten, dass jemand den search_path ändern könnte. Dieser Wert bleibt quasi konstant für die Datenbank. Einige Teile können die neuen Werte möglicherweise nicht übernehmen.
Natürlich hängt das von der Version ab, auf der Sie testen. Es hängt davon ab, wie stark sich Ihre Tabellen unterscheiden. Die Version 9.1 führt einfach die alten Abfragen aus. Neuere Versionen können einen Fehler erkennen und sagen, dass Sie einen Fehler haben.
Wie kann man das beheben? Es gibt ein einfaches Rezept – tun Sie dies nicht. Ändern Sie den search_path nicht im laufenden Betrieb der Anwendung. Wenn Sie Änderungen vornehmen, ist es besser, eine neue Verbindung zu erstellen.
Wir können darüber diskutieren, d. h. es öffnen, besprechen, ergänzen. Vielleicht überzeugen wir die Datenbankentwickler, dass die Datenbank dem Client mitteilen sollte, wenn jemand den Wert ändert: „Achten Sie darauf, dass sich dieser Wert geändert hat. Vielleicht sollten Sie die statements zurücksetzen oder neu erstellen?“. Momentan verhält sich die Datenbank heimlich und gibt keine Informationen darüber, dass sich irgendwo innerhalb der statements etwas geändert hat.
Ich möchte erneut betonen – das ist nicht typisch für Java. Das Gleiche werden wir eins zu eins in PL/pgSQL sehen. Aber dort wird es reproduziert.
Lassen Sie uns versuchen, weitere Daten auszuwählen. Wir wählen und wählen. Wir haben eine Tabelle mit einer Million Zeilen. Jede Zeile hat ein Kilobyte. Ungefähr ein Gigabyte Daten. Und wir haben 128 Megabyte Arbeitspeicher in der Java-Maschine.
Wie in allen Büchern empfohlen, nutzen wir Streaming-Verarbeitung. Das heißt, wir öffnen das ResultSet und lesen die Daten nach und nach. Funktioniert das? Wird es wegen des Speichers abstürzen? Liest es nach ein paar Daten? Lassen Sie uns daran glauben, glauben wir an die Datenbank, an Postgres. Wir glauben nicht. Stürzen wir mit OutOfMemory ab? Wer hatte OutOfMemory? Wer konnte es danach reparieren? Jemand hat es geschafft, es zu reparieren.
Wenn Sie eine Million Zeilen haben, können Sie nicht einfach auswählen. OFFSET/LIMIT sind unbedingt erforderlich. Wer ist für diese Variante? Und wer ist dafür, dass man mit autoCommit spielen sollte?
Hier erweist sich wie gewohnt die unerwartetste Variante als die richtige. Und wenn Sie plötzlich autoCommit ausschalten, wird es helfen. Warum ist das so? Die Wissenschaft kennt die Antwort darauf nicht.
Aber standardmäßig wählen alle Clients, die sich mit der Postgres-Datenbank verbinden, die Daten vollständig aus. PgJDBC bildet hier keine Ausnahme und wählt alle Zeilen aus.
Es gibt eine Variation zum Thema FetchSize, d.h. man kann auf Ebene eines einzelnen Statements auswählen, dass hier bitte Daten in Schritten von 10 oder 50 ausgewählt werden. Aber das funktioniert nur, wenn Sie autoCommit deaktivieren. Wenn autoCommit deaktiviert ist, funktioniert es.
Aber im Code überall setFetchSize zu setzen ist unpraktisch. Deshalb haben wir eine Einstellung geschaffen, die für die gesamte Verbindung einen Standardwert vorgibt.
Das haben wir gesagt. Den Parameter eingestellt. Und was haben wir erreicht? Wenn wir in kleinen Schritten auswählen, z.B. 10 Zeilen, haben wir ziemlich hohe Overheads. Daher sollte dieser Wert etwa bei hundert liegen.
Idealerweise sollten wir das auch in Byte noch lernen zu begrenzen, aber die Empfehlung ist: setze defaultRowFetchSize auf über hundert und freue dich.
Lassen Sie uns zum Einfügen von Daten übergehen. Das Einfügen ist einfacher, da es verschiedene Varianten gibt. Zum Beispiel INSERT, VALUES. Das ist eine gute Variante. Man kann auch „INSERT SELECT“ verwenden. In der Praxis ist das das Gleiche. Es gibt keinen Unterschied in der Leistung.
Bücher behaupten, dass Batch-Befehle ausgeführt werden sollten, und sie sagen, dass es möglich ist, komplexere Befehle mit mehreren Klammern auszuführen. In Postgres gibt es eine großartige Funktion – man kann COPY verwenden, das heißt, man kann es schneller machen.
Wenn man misst, kann man wieder mehrere interessante Entdeckungen machen. Wie wollen wir, dass das funktioniert? Wir wollen nicht parsen und keine unnötigen Befehle ausführen.
In der Praxis erlaubt uns TCP das nicht. Wenn der Client beschäftigt ist, eine Anfrage zu senden, liest die Datenbank die Anfragen nicht, während sie versucht, uns die Antworten zu senden. Letztendlich wartet der Client auf die Datenbank, bis sie die Anfrage gelesen hat, während die Datenbank auf den Client wartet, bis dieser die Antwort gelesen hat.
Deshalb ist der Client gezwungen, regelmäßig Synchronisationspakete zu senden. Überflüssige Netzinteraktionen, verlorene Zeit.
Je mehr wir hinzufügen, desto schlimmer wird es. Der Treiber ist ziemlich pessimistisch und fügt sie oft hinzu, etwa alle 200 Zeilen, abhängig von der Länge der Zeilen, und so weiter.
Es kann vorkommen, dass eine einzige Zeile angepasst wird und alles sich zehnmal schneller anfühlt. So etwas passiert. Warum? Wie üblich wurde eine Konstante irgendwo bereits verwendet. Und der Wert '128' bedeutete – kein Batching zu verwenden.
Gut, dass das nicht in die offizielle Version gelangt ist. Wir haben es entdeckt, bevor wir mit der Veröffentlichung des Releases begonnen haben. Alle Werte, die ich nenne, basieren auf modernen Versionen.
Lass uns messen. Wir messen InsertBatch einfach. Wir messen InsertBatch mehrfach, also dasselbe, aber mit vielen values. Ein schlauer Zug. Nicht jeder kann das, aber es ist ein einfacher Schritt, viel einfacher als COPY.
Man kann COPY verwenden.
Und das kann man auch in Strukturen tun. Den User-Standardtyp deklarieren, ein Array übergeben und direkt in die Tabelle einfügen.
Wenn Sie den Link öffnen: pgjdbc/ubenchmsrk/InsertBatch.java, dann finden Sie diesen Code auf GitHub. So kann man sehen, welche spezifischen Anfragen dort generiert werden. Das ist nebensächlich.
Wir haben gestartet. Und das Erste, was wir festgestellt haben, ist, dass man Batch nicht meiden kann – das ist einfach nicht möglich. Alle Batching-Varianten sind gleich null, das heißt, die Ausführungszeit ist praktisch null im Vergleich zu einer einmaligen Ausführung.
Wir fügen die Daten ein. Es ist eine recht einfache Tabelle. Drei Spalten. Und was sehen wir hier? Wir sehen, dass alle drei Varianten ungefähr vergleichbar sind. Und COPY ist natürlich die bessere Wahl.
Das ist, wenn wir stückweise einfügen. Wenn wir gesagt haben, dass ein Wert VALUES, zwei Werte VALUES, drei Werte VALUES oder wir sie dort durch Kommas getrennt angegeben haben. Das ist tatsächlich jetzt horizontal. 1, 2, 4, 128. Man sieht, dass der Batch Insert, der blau gezeichnet ist, deutlich profitieren kann. Das heißt, wenn Sie einzeln einfügen oder sogar wenn Sie vier gleichzeitig einfügen, verbessert sich die Leistung um das Doppelte, nur weil wir ein bisschen mehr in VALUES gepackt haben. Weniger EXECUTE-Operationen.
COPY für kleine Datenmengen zu verwenden, ist äußerst unvorteilhaft. Ich habe die ersten beiden nicht einmal dargestellt. Sie gehen in die Höhe, d.h. diese grünen Zahlen stehen für COPY.
COPY sollte verwendet werden, wenn Ihr Datenvolumen mindestens mehr als hundert Zeilen beträgt. Die Overheadkosten für das Öffnen dieser Verbindung sind hoch. Und ehrlich gesagt, ich habe in diese Richtung nicht tiefer gegraben. Batch habe ich optimiert, COPY – nicht.
Was machen wir als Nächstes? Wir haben gemessen. Wir verstehen, dass wir entweder Strukturen oder ein ausgeklügeltes Batch verwenden müssen, das mehrere Werte kombiniert.
Was sollte aus dem heutigen Bericht mitgenommen werden?
- PreparedStatement – das ist unser Alleinstellungsmerkmal. Es verbessert die Leistung erheblich. Es gibt einen großen Nachteil.
- Und man sollte EXPLAIN ANALYZE 6 Mal durchführen.
- Zudem sollte man OFFSET 0 sowie Tricks wie +0 verwenden, um den verbleibenden Prozentsatz unserer problematischen Anfragen zu optimieren.
Quelle: habr.com
