Ich lade Sie ein, sich mit der Entschlüsselung des Berichts von Anfang 2016 von Wladimir Sitnikov "PostgreSQL und JDBC - wir schöpfen alles aus" vertraut zu machen.
Guten Tag! Mein Name ist Wladimir Sitnikov. Ich arbeite seit 10 Jahren bei NetCracker. Und hauptsächlich beschäftige ich mich mit der Leistung. Alles, was mit Java zu tun hat, alles, was mit SQL zu tun hat - das ist, was ich liebe.
Und heute werde ich darüber sprechen, womit wir in der Firma konfrontiert wurden, als wir begannen, PostgreSQL als Datenbankserver zu verwenden. Und wir arbeiten hauptsächlich mit Java. Aber das, was ich heute erzählen werde, betrifft nicht nur Java. Wie die Praxis zeigt, tritt dies auch in anderen Sprachen auf.
Wir werden sprechen über:
- die Datenauswahl.
- Die Datenspeicherung.
- Und auch über die Leistung.
- Und über die Stolpersteine, die dort verborgen sind.
Lassen Sie uns mit einer einfachen Frage beginnen. Wir wählen eine Zeile aus der Tabelle nach dem Primärschlüssel aus.
Die Datenbank befindet sich auf dem gleichen Host. Und all das dauert 20 Millisekunden.
Diese 20 Millisekunden sind sehr viel. Wenn Sie 100 solcher Anfragen haben, verschwenden Sie Zeit pro Sekunde, um diese Anfragen abzuwickeln, d. h. wir verschwenden Zeit.
Das machen wir nicht gerne und schauen, was uns die Datenbank dafür anbietet. Die Datenbank bietet uns zwei Möglichkeiten zur Ausführung von Abfragen an.
Die erste Möglichkeit ist eine einfache Abfrage. Was ist daran gut? Dass wir sie nehmen und senden, und nichts weiter.
Die Datenbank hat auch eine erweiterte Abfrage, die trickreicher, aber funktionaler ist. Man kann die Anfrage zum Parsen, Ausführen, Binden von Variablen usw. separat senden.
Super erweiterte Abfrage - das werden wir im aktuellen Bericht nicht behandeln. Vielleicht möchten wir etwas von der Datenbank und es gibt eine Wunschliste, die in gewisser Weise formuliert ist, d. h. das ist, was wir wollen, aber jetzt und im kommenden Jahr nicht möglich ist. Daher haben wir es einfach aufgeschrieben und werden die Hauptverantwortlichen konsultieren.
Was wir tun können, sind die einfache Abfrage und die erweiterte Abfrage.
Was ist das Besondere an jedem Ansatz?
Die einfache Abfrage eignet sich gut für einmalige Ausführungen. Einmal ausgeführt und vergessen. Das Problem ist, dass sie das binäre Datenformat nicht unterstützt, d. h. für einige Hochleistungsysteme eignet sie sich nicht.
Erweiterte Abfragen ermöglichen es, Zeit beim Parsen zu sparen. Das haben wir gemacht und angefangen zu verwenden. Das hat uns extrem geholfen. Es gibt nicht nur Einsparungen beim Parsen. Es gibt auch Einsparungen bei der Datenübertragung. Daten im binären Format 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 hier der Fehler? Was ist das Problem? Es gibt keine Probleme. So steht es in allen Büchern. So muss man schreiben. Wenn Sie maximale Leistung möchten, schreiben Sie so.
Aber die Praxis hat gezeigt, dass das nicht funktioniert. Warum? Weil wir die Methode „close“ haben. Und wenn wir es so machen, ist es aus Sicht der Datenbank wie die Arbeit eines Rauchers mit der Datenbank. Wir haben „PARSE EXECUTE DEALLOCATE“ gesagt.
Warum diese überflüssigen Erstellungen und das Entladen von Statements? Die braucht niemand. Aber normalerweise ist es im PreparedStatement so, wenn wir sie schließen, schließen sie alles in der Datenbank. Das ist nicht das, was wir wollen.
Wir wollen, dass wir wie gesunde Menschen mit der Datenbank arbeiten. Einmal haben wir unser Statement vorbereitet, dann führen wir es viele Male aus. Tatsächlich viele Male – das ist einmal im Laufe der gesamten Lebensdauer der Anwendung geparst. Und für unterschiedliche REST verwenden wir die gleiche Statement-ID. Das ist unser Ziel.
Wie erreichen wir das?
Ganz einfach – man muss die Statements nicht schließen. Wir schreiben so: „prepare“ „execute“.
Wenn wir das ausführen, ist klar, dass irgendwo etwas überlaufen wird. Wenn das unklar ist, kann man es messen. Lassen Sie uns einen Benchmark schreiben, in dem wir eine so einfache Methode haben. Wir erstellen ein Statement. Führen es mit einer bestimmten Treiberversion aus und stellen fest, dass es ziemlich schnell mit dem Verlust des gesamten Speichers, den wir haben, abstürzt.
Es ist klar, dass solche Fehler leicht zu beheben sind. Ich werde nicht darüber sprechen. Aber ich kann sagen, dass es in der neuen Version viel schneller funktioniert. Die Methode ist sinnlos, aber dennoch.
Wie arbeitet man richtig? Was müssen wir dafür tun?
In der Realität schließen Anwendungen immer Statements. In allen Büchern steht, dass man sie schließen soll, sonst gibt es Speicherlecks.
Und PostgreSQL kann keine Abfragen cachen. Jede Sitzung muss diesen Cache selbst für sich erstellen.
Und Zeit für Parsing wollen wir auch nicht verschwenden.
Wie gewohnt haben wir zwei Möglichkeiten.
Die erste Variante – wir nehmen und sagen, dass wir alles in PgSQL verpacken. Dort gibt es einen Cache. Er cached alles. Das wird großartig. Das haben wir uns angeschaut. Wir haben 100500 Anfragen. Es funktioniert nicht. Wir sind nicht einverstanden, Anfragen manuell in Prozeduren umzuwandeln. Nein, nein.
Wir haben eine zweite Variante – selbst etwas entwickeln. Wir öffnen den Quellcode und fangen an zu programmieren. Wir programmieren und programmieren. Es stellte sich heraus, dass es gar nicht so kompliziert ist.
Das erschien im August 2015. Jetzt gibt es bereits eine modernere Version. Und alles läuft großartig. Es funktioniert so gut, dass wir nichts in der Anwendung ändern. Und wir haben sogar aufgehört, in die Richtung PgSQL zu denken, das heißt, es hat uns völlig gereicht, um alle Nebenkosten praktisch auf null zu senken.
Entsprechend werden Server-prepared statements beim fünften Ausführen aktiviert, um zu vermeiden, dass Speicher in der Datenbank für jede einmalige Anfrage verbraucht wird.
Man kann fragen – wo sind die Zahlen? Was bekommt ihr? Und hier kann ich keine Zahlen geben, weil jeder Anfrage ihre eigenen hat.
Unsere Anfragen waren so, dass wir bei OLTP-Anfragen ungefähr 20 Millisekunden für das Parsen benötigt haben. Dort waren es 0,5 Millisekunden für die Ausführung, 20 Millisekunden für das Parsen. Anfrage – 10 KiB Text, 170 Zeilen Plan. Das ist eine OLTP-Anfrage. Sie fragt 1, 5, 10 Zeilen ab, manchmal mehr.
Aber wir wollten überhaupt keine 20 Millisekunden verschwenden. Wir haben es auf 0 reduziert. Alles läuft großartig.
Was könnt ihr daraus mitnehmen? Wenn ihr Java habt, nehmt ihr die aktuelle Version des Treibers und seid glücklich.
Wenn ihr eine andere Sprache habt, dann überlegt – braucht ihr das vielleicht auch? Denn aus der Sicht der Zielsprache, wenn ihr PL 8 oder LibPQ habt, ist euch nicht offensichtlich, dass ihr Zeit nicht für die Ausführung, sondern für das Parsen aufwendet und das sollte überprüft werden. Wie? Alles kostenlos.
Mit Ausnahme der Fehler, einiger Besonderheiten. Und genau darüber werden wir jetzt sprechen. Der größte Teil wird über industrielle Archäologie sein, darüber, was wir gefunden haben, worauf wir gestoßen sind.
Wenn die Anfrage dynamisch generiert wird. Das kommt vor. Jemand verkettet Strings, und es entsteht eine SQL-Anfrage.
Warum ist das schlecht? Es ist schlecht, weil wir jedes Mal am Ende eine unterschiedliche Zeile erhalten.
Und bei dieser variierenden Zeile muss der hashCode erneut berechnet werden. Es ist tatsächlich eine CPU-Aufgabe – einen langen Anfrage-Text im vorhandenen hash zu finden, ist nicht so einfach. Daher die einfache Empfehlung – generieren Sie keine Anfragen. Speichern Sie sie in einer einzigen Variable. Und freuen Sie sich.
Das nächste Problem. Datentypen sind wichtig. Es gibt ORM, die sagen, dass es egal ist, welcher NULL-Wert, lassen Sie irgendeinen sein. Wenn es ein Int ist, sagen wir setInt. Und wenn NULL, dann soll es immer VARCHAR sein. Und was macht das schon für einen Unterschied am Ende? Die Datenbank wird es schon verstehen. Und so ein Bild funktioniert nicht.
In der Praxis ist es der Datenbank 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, kann man Server-prepared statements nicht wiederverwenden. In diesem Fall muss unser statement neu erstellt werden.
Wenn Sie die gleiche Anfrage mehrmals ausführen, achten Sie darauf, dass die Datentypen in der Spalte nicht durcheinandergeraten. Man muss auf NULL achten. Das ist ein häufiger Fehler, der aufgetreten ist, nachdem wir begonnen haben, PreparedStatements zu verwenden.
Gut, eingeschaltet. Vielleicht haben wir einen Treiber verwendet. Und die Leistung ist gesunken. Alles wurde schlecht.
Wie kann das passieren? Ist das ein Bug oder ein Feature? Leider konnte ich nicht verstehen – ist es ein Bug oder ein Feature. Aber es gibt ein recht einfaches Szenario zur Reproduktion dieses Problems. Es hat uns völlig unerwartet erwischt. Und es besteht darin, dass wir buchstäblich aus einer einzigen Tabelle auswählen. Natürlich hatten wir mehr solcher Anfragen. Diese beinhalteten in der Regel zwei bis drei Tabellen, aber es gibt so ein Reproduktionsszenario. Nehmen Sie Ihre Datenbank in jeder Version und reproduzieren Sie es.
Der Sinn ist, dass wir zwei Spalten haben, von denen jede indiziert ist. In einer Spalte stehen eine Million Zeilen mit dem Wert NULL. In der anderen Spalte sind es nur 20 Zeilen. Wenn wir ohne gebundene Variablen ausführen, funktioniert alles gut.
Wenn wir anfangen, mit gebundenen Variablen auszuführen, d. h. wir setzen ein Zeichen „?“ oder „$1“ für unsere Anfrage, was erhalten wir schließlich?
Erste Ausführung – wie gewohnt. Zweite – etwas schneller. Etwas wurde zwischengespeichert. Dritte-vierte-fünfte. Dann zack – und so ist es. Und das Schlimmste ist, dass das beim sechsten Ausführen geschieht. Wer hätte gewusst, dass man genau sechs Ausführungen machen muss, um zu verstehen, welcher Plan dort wirklich verwendet wird?
Wer ist schuld? Was ist passiert? Die Datenbank enthält eine Optimierung. Und sie ist irgendwie für den generischen Fall optimiert. Dementsprechend wechselt sie ab einer bestimmten Anzahl an Durchläufen auf den generischen Plan, der leider anders ausfallen kann. Er kann gleich sein, aber auch anders. Und es gibt einen bestimmten Schwellenwert, der zu diesem Verhalten führt.
Was kann man damit machen? Hier ist es natürlich schwieriger, etwas zu vermuten. Es gibt eine einfache Lösung, die wir verwenden. Das ist +0, OFFSET 0. Sicher kennen Sie solche Lösungen. Wir fügen einfach „+0“ zu der Anfrage hinzu und alles ist gut. Ich zeige es später.
Und es gibt noch eine Möglichkeit – die Pläne genauer zu betrachten. Der Entwickler muss nicht nur die Anfrage schreiben, sondern auch 6 Mal „explain analyze“ sagen. Wenn es nur 5 Mal ist, funktioniert es nicht.
Und es gibt noch eine dritte Möglichkeit – einen Brief an pgsql-hackers zu schreiben. Ich habe geschrieben, aber es ist bisher 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 wir „+0“ hinzu. Alles ist gut. Zwei Zeichen und man muss nicht darüber nachdenken, wie das dort läuft. Sehr einfach. Wir haben der Datenbank einfach verboten, den Index dieser Spalte zu verwenden. Wir haben keinen Index für die Spalte „+0“, also verwendet die Datenbank keinen Index, alles gut.
Das ist das Regelwerk der 6 Erklärungen. In den aktuellen Versionen muss man 6 Mal erklären, wenn Sie verwandte Variablen haben. Wenn Sie keine verwandten Variablen haben, tun wir es einfach so. Und letztendlich schlägt genau diese Anfrage fehl. Es ist nichts kompliziertes.
Es schien, wie lange kann das dauern? Hier ein Bug, dort ein Bug. Tatsächlich gibt es überall Bugs.
Lassen Sie uns noch einmal schauen. Zum Beispiel haben wir zwei Schemas. Schema A mit Tabelle Y und Schema B mit Tabelle Y. Die Anfrage – wählen Sie Daten aus der Tabelle aus. Was haben wir dann? Wir haben einen Fehler. Wir haben alles, was oben erwähnt wurde. Die Regel ist – überall Bugs, wir haben alles, was oben erwähnt wurde.
Jetzt die Frage: „Warum?“. Es schien, dass es eine Dokumentation gibt, dass es, wenn wir ein Schema haben, eine Variable „search_path“ gibt, die angibt, wo die Tabelle gesucht werden soll. Es scheint, dass die Variable vorhanden ist.
Was ist das Problem? Das Problem ist, dass server-prepared statements nicht ahnen, dass search_path von jemandem geändert werden kann. Dieser Wert bleibt wie eine Konstante für die Datenbank. Und einige Teile könnten die neuen Werte nicht übernehmen.
Natürlich hängt das von der Version ab, auf der Sie testen. Es hängt davon ab, wie stark Ihre Tabellen unterschiedlich sind. Und Version 9.1 führt einfach die alten Abfragen aus. Neuere Versionen können einen Trick erkennen und sagen, dass Sie einen Fehler haben.
Wie behandelt man das? Es gibt ein einfaches Rezept – machen Sie es nicht so. Ändern Sie den search_path nicht während der Ausführung der Anwendung. Wenn Sie ihn ändern, sollten Sie besser eine neue Verbindung herstellen.
Wir können diskutieren, das heißt, öffnen, diskutieren, ergänzen. Vielleicht überzeugen wir die Datenbankentwickler, dass die Datenbank dem Client sagen sollte: „Schauen Sie, hier hat sich ein Wert geändert. Vielleicht sollten Sie die Statements zurücksetzen oder neu erstellen?“ Zurzeit verhält sich die Datenbank heimlich und informiert nicht darüber, dass sich irgendwo intern die Statements geändert haben.
Und ich möchte nochmals betonen – das ist nicht typisch für Java. Das gleiche werden wir in PL/pgSQL eins zu eins sehen. Aber dort wird es reproduzierbar sein.
Lassen Sie uns noch einmal versuchen, Daten auszuwählen. Wir wählen, wählen. Wir haben eine Tabelle mit einer Million Zeilen. Jede Zeile hat ein Kilobyte. Etwa ein Gigabyte an Daten. Und wir haben 128 Megabyte Arbeitsspeicher in der Java-Maschine.
Wie in allen Büchern empfohlen, verwenden wir die Streaming-Verarbeitung. Das heißt, wir öffnen das resultSet und lesen die Daten nach und nach. Wird das funktionieren? Wird es nicht zu einem Speicherüberlauf kommen? Wird es wenig lesen? Lassen Sie uns an die Datenbank glauben, an Postgres glauben. Glauben wir nicht. Werden wir OutOfMemory-Fehler bekommen? Wer hat OutOfMemory-Fehler bekommen? Und wer konnte das danach reparieren? Jemand konnte es reparieren.
Wenn Sie eine Million Zeilen haben, können Sie nicht einfach so auswählen. Sie müssen immer OFFSET/LIMIT verwenden. Wer ist für diese Option? Und wer sagt, dass man mit autoCommit spielen sollte?
Hier ist, wie üblich, die unerwartetste Option die richtige. Und wenn Sie plötzlich autoCommit ausschalten, wird es helfen. Warum so? Die Wissenschaft weiß es nicht.
Aber standardmäßig wählen alle Clients, die sich mit der Postgres-Datenbank verbinden, die Daten vollständig aus. PgJDBC ist in dieser Hinsicht keine Ausnahme und wählt alle Zeilen aus.
Es gibt eine Variation zum Thema FetchSize, das heißt, Sie können auf Ebene eines einzelnen Statements sagen, dass hier bitte die Daten in Gruppen von 10, 50 ausgewählt werden sollen. Aber das funktioniert nicht, solange Sie autoCommit nicht ausschalten. Wenn Sie autoCommit ausschalten – fängt es an zu funktionieren.
Es ist unpraktisch, im Code überall setFetchSize zu setzen. Deshalb haben wir eine Einstellung vorgenommen, die den Standardwert für die gesamte Verbindung festlegt.
Wir haben das gesagt. Den Parameter eingestellt. Und was haben wir nun? Wenn wir in kleinen Mengen auswählen, zum Beispiel 10 Zeilen, haben wir ziemlich hohe Overheadkosten. Daher sollten wir diesen Wert auf etwa 100 setzen.
Idealerweise sollten wir natürlich auch lernen, in Bytes zu limitieren, aber die Rezeptur ist folgende: wir setzen defaultRowFetchSize auf über 100 und freuen uns.
Lassen Sie uns zur Dateninstituierung übergehen. Die Einfügung – einfach, es gibt verschiedene Varianten. Zum Beispiel, INSERT, VALUES. Das ist eine gute Option. Man kann auch ‚INSERT SELECT‘ sagen. In der Praxis ist das dasselbe. Es gibt keinen Unterschied in der Leistungsfähigkeit.
Bücher sagen, dass man Batch-Statements ausführen sollte, Bücher sagen, dass man komplexere Befehle mit mehreren Klammern ausführen kann. Und in Postgres gibt es eine wunderbare Funktion – man kann COPY machen, d.h. es schneller erledigen.
Wenn man es misst, kann man wieder einige interessante Entdeckungen machen. Wie wollen wir, dass das funktioniert? Wir wollen nicht parsen und keine überflüssigen Befehle ausführen.
In der Praxis erlaubt uns TCP so etwas nicht. Wenn der Client mit dem Senden einer Anfrage beschäftigt ist, liest die Datenbank in dem Versuch, uns Antworten zu senden, die Anfragen nicht. Am Ende wartet der Client auf die Datenbank, während diese auf den Client wartet, bis er die Antwort liest.
Und deshalb muss der Client regelmäßig ein Synchronisationspaket senden. Überflüssige Netzinteraktionen, überflüssiger Zeitverlust.
Je mehr wir sie hinzufügen, desto schlechter wird es. Der Treiber ist ziemlich pessimistisch und fügt sie ziemlich häufig hinzu, etwa alle 200 Zeilen, abhängig von der Größe der Zeilen usw.
Manchmal korrigiert man nur eine Zeile und alles wird zehnmal schneller. Solche Dinge passieren. Warum? Wie gewohnt wurde irgendwo bereits eine solche Konstante verwendet. Und der Wert „128“ bedeutete – kein Batching zu verwenden.
Es ist gut, dass dies nicht in die offizielle Version gelangte. Wir entdeckten es, bevor wir mit der Veröffentlichung des Releases begannen. Alle Werte, die ich nenne, basieren auf modernen Versionen.
Lassen Sie uns messen. Wir messen das einfache InsertBatch. Wir messen das mehrfache InsertBatch, d.h. dasselbe, aber mit vielen values. Ein raffinierter Trick. Nicht jeder kann das, aber es ist ein einfacher Weg, viel einfacher als COPY.
Man kann COPY machen.
Und man kann das auf Strukturen machen. User Default Type deklarieren, ein Array übergeben und direkt in die Tabelle INSERT.
Wenn Sie den Link öffnen: pgjdbc/ubenchmsrk/InsertBatch.java, dann finden Sie diesen Code auf GitHub. Sie können sehen, welche Abfragen dort generiert werden. Das ist nicht so wichtig.
Wir haben gestartet. Und das Erste, was wir verstanden haben, ist, dass man Batch nicht weglassen sollte – das geht einfach nicht. Alle Batch-Optionen sind gleich Null, d. h. die Ausführungszeit ist praktisch Null im Vergleich zu einer einmaligen Ausführung.
Wir fügen Daten ein. Es ist eine ziemlich einfache Tabelle. Drei Spalten. Und was sehen wir hier? Wir sehen, dass all diese drei Optionen ungefähr vergleichbar sind. Und COPY ist natürlich besser.
Das ist, wenn wir stückchenweise einfügen. Wenn wir gesagt haben, ein Wert mit VALUES, zwei Werte mit VALUES, drei Werte mit VALUES oder wir haben dort 10 mit Kommas angegeben. Das ist jetzt quer. 1, 2, 4, 128. Man sieht, dass Batch Insert, das in Blau dargestellt ist, dadurch erheblich erleichtert wird. D. h. wenn Sie eins einzufügen oder sogar vier einzufügen, wird es doppelt so gut, einfach weil wir in VALUES etwas mehr reingepackt haben. Weniger EXECUTE-Operationen.
COPY für kleine Datenmengen zu nutzen, ist extrem unvorteilhaft. Ich habe bei den ersten beiden nicht einmal gezeichnet. Sie gehen in die Höhe, d. h. diese grünen Zahlen für COPY.
COPY sollte verwendet werden, wenn Sie mindestens mehr als hundert Zeilen an Daten haben. Die Nebenkosten für das Öffnen dieser Verbindung sind hoch. Und ehrlich gesagt, ich habe in diese Richtung nicht weiter gegraben. Batch habe ich optimiert, COPY nicht.
Was machen wir als Nächstes? Wir haben gemessen. Wir verstehen, dass wir entweder Strukturen oder eine raffinierte Batch-Methode verwenden müssen, die mehrere Werte kombiniert.
Was müssen wir aus dem heutigen Vortrag mitnehmen?
- PreparedStatement – das ist unser Alleskönner. Es bringt viel für die Leistung. Es bringt ein großes Fass voll Nachteile.
- Und man muss EXPLAIN ANALYZE 6 Mal durchführen.
- Und man muss OFFSET 0 auflockern und mit Tricks wie +0 arbeiten, um den restlichen Prozentsatz unserer problematischen Abfragen zu verbessern.
Quelle: habr.com
