Ein industrieller Ansatz zur Optimierung von PostgreSQL: Experimente mit Datenbanken.“ Nikolai Samochwalow

Ich empfehle Ihnen, das Transkript von Nikolai Samokhvalovs Bericht „Industrieller Ansatz zur Optimierung von PostgreSQL: Experimente mit Datenbanken“ zu lesen.

Shared_buffers = 25 % – ist es viel oder wenig? Oder genau richtig? Woher wissen Sie, ob diese – eher veraltete – Empfehlung in Ihrem speziellen Fall angemessen ist?

Es ist an der Zeit, sich der Frage der Auswahl von postgresql.conf-Parametern „wie ein Erwachsener“ zu nähern. Nicht mit Hilfe blinder „Autotuner“ oder veralteter Ratschläge aus Artikeln und Blogs, sondern basierend auf:

1. streng überprüfte Experimente an Datenbanken, die automatisch, in großen Mengen und unter möglichst „kampfnahen“ Bedingungen durchgeführt werden,
2. tiefes Verständnis der Funktionen des DBMS und des Betriebssystems.

Verwenden der Nancy-CLI (https://gitlab.com/postgres.ai/nancy), werden wir uns ein konkretes Beispiel – die berüchtigten shared_buffers – in verschiedenen Situationen und in verschiedenen Projekten ansehen und versuchen herauszufinden, wie wir die optimale Einstellung für unsere Infrastruktur, Datenbank und Auslastung auswählen.

Wir werden über Experimente mit Datenbanken sprechen. Dies ist eine Geschichte, die etwas mehr als sechs Monate dauert.

Ein wenig über mich. Erfahrung mit Postgres seit mehr als 14 Jahren. Eine Reihe von Social-Networking-Unternehmen wurden gegründet. Postgres wurde und wird überall verwendet.

Auch die RuPostgres-Gruppe auf Meetup, 2. Platz weltweit. Wir nähern uns langsam der 2-Marke. RuPostgres.org.

Und an PCs verschiedener Konferenzen, darunter auch Highload, bin ich von Anfang an für Datenbanken, insbesondere Postgres, verantwortlich.

Und in den letzten Jahren habe ich meine Postgres-Beratungspraxis 11 Zeitzonen von hier aus neu gestartet.

Und als ich das vor ein paar Jahren gemacht habe, hatte ich eine Pause in der aktiven manuellen Arbeit mit Postgres, wahrscheinlich seit 2010. Ich war überrascht, wie wenig sich der Arbeitsalltag eines DBA verändert hat und wie viel Handarbeit immer noch eingesetzt werden muss. Und ich dachte sofort, dass hier etwas nicht stimmt, ich muss alles noch mehr automatisieren.

Und da alles abgelegen war, befanden sich die meisten Kunden in den Clouds. Und natürlich ist vieles bereits automatisiert. Mehr dazu später. Das heißt, all dies führte zu der Idee, dass es eine Reihe von Tools geben sollte, also eine Art Plattform, die fast alle DBA-Aktionen automatisiert, sodass eine große Anzahl von Datenbanken verwaltet werden kann.

Dieser Bericht enthält nicht:

• „Allheilmittel“ und Aussagen wie: Stellen Sie 8 GB oder 25 % Shared_Buffers ein und alles wird gut. Über shared_buffers wird es nicht viel geben.
• Hardcore-„Innere“.

Was wird passieren?

• Es wird Optimierungsprinzipien geben, die wir anwenden und weiterentwickeln. Auf dem Weg werden allerlei Ideen entstehen und verschiedene Tools, die wir größtenteils in Open Source erstellen, d. h. wir schaffen die Basis in Open Source. Darüber hinaus haben wir Tickets, die gesamte Kommunikation erfolgt praktisch Open Source. Sie können sehen, was wir gerade tun, was in der nächsten Version enthalten sein wird usw.
• Es wird auch einige Erfahrungen mit der Anwendung dieser Prinzipien, dieser Tools in einer Reihe von Unternehmen geben: vom kleinen Startup bis zum Großunternehmen.

Wie entwickelt sich das Ganze?

Erstens besteht die Hauptaufgabe eines DBA neben der Sicherstellung der Erstellung von Instanzen, der Bereitstellung von Backups usw. darin, Engpässe zu finden und die Leistung zu optimieren.

Jetzt ist es so aufgebaut. Wir schauen uns die Überwachung an, wir sehen etwas, aber uns fehlen einige Details. Wir fangen an, sorgfältiger zu graben, normalerweise mit unseren Händen, und verstehen, was wir auf die eine oder andere Weise damit machen sollen.

Und es gibt zwei Ansätze. Pg_stat_statements ist die Standardlösung zur Identifizierung langsamer Abfragen. Und Analyse von Postgres-Protokollen mit pgBadger.

Jeder Ansatz hat schwerwiegende Nachteile. Im ersten Ansatz haben wir alle Parameter verworfen. Und wenn wir die Gruppen SELECT * FROM-Tabelle sehen, in der die Spalte gleich dem „?“ ist. oder „$“ seit Postgres 10. Wir wissen nicht, ob es sich um einen Index-Scan oder einen Seq-Scan handelt. Es kommt sehr stark auf den Parameter an. Wenn Sie dort einen selten vorkommenden Wert ersetzen, handelt es sich um einen Indexscan. Wenn Sie dort einen Wert einsetzen, der 90 % der Tabelle einnimmt, ist der seq-Scan offensichtlich, da Postgres die Statistiken kennt. Und das ist ein großer Nachteil von pg_stat_statements, obwohl noch einige Arbeiten im Gange sind.

Der größte Nachteil der Protokollanalyse besteht darin, dass Sie sich „log_min_duration_statement = 0“ in der Regel nicht leisten können. Und auch darüber werden wir reden. Dementsprechend sieht man nicht das ganze Bild. Und einige Abfragen, die sehr schnell sind, verbrauchen möglicherweise eine große Menge an Ressourcen, werden aber nicht angezeigt, da sie unter Ihrem Schwellenwert liegen.

Wie lösen Datenbankadministratoren die Probleme, die sie finden?

Beispielsweise haben wir ein Problem festgestellt. Was wird normalerweise gemacht? Wenn Sie Entwickler sind, werden Sie etwas an einer Instanz tun, die nicht die gleiche Größe hat. Wenn Sie ein DBA sind, dann haben Sie Staging. Und es kann nur einen geben. Und er war sechs Monate im Rückstand. Und Sie denken, dass Sie in die Produktion gehen werden. Und selbst erfahrene Datenbankadministratoren überprüfen dann die Produktion anhand einer Replik. Und es kommt vor, dass sie einen temporären Index erstellen, sicherstellen, dass er hilft, ihn löschen und ihn den Entwicklern geben, damit diese ihn in die Migrationsdateien einfügen können. Das ist der Unsinn, der jetzt passiert. Und das ist ein Problem.

• Optimieren Sie Konfigurationen.
• Optimieren Sie den Indexsatz.
• Ändern Sie die SQL-Abfrage selbst (dies ist der schwierigste Weg).
• Kapazität hinzufügen (in den meisten Fällen der einfachste Weg).

Bei diesen Dingen ist viel los. Es gibt viele Handles in Postgres. Es gibt viel zu wissen. Es gibt viele Indizes in Postgres, auch dank der Organisatoren dieser Konferenz. Und das alles muss bekannt sein, und das ist es, was Nicht-DBAs das Gefühl gibt, dass DBAs schwarze Magie praktizieren. Das heißt, Sie müssen 10 Jahre lang studieren, um das alles normal zu verstehen.

Und ich bin ein Kämpfer gegen diese schwarze Magie. Ich möchte alles tun, damit es Technologie gibt, und in all dem steckt keine Intuition.

Beispiele aus dem Leben

Ich habe dies bei mindestens zwei Projekten beobachtet, darunter auch bei meinem eigenen. In einem anderen Blogbeitrag erfahren wir, dass ein Wert von 1 für default_statistict_target gut ist. Okay, versuchen wir es in der Produktion.

Und jetzt nutzen wir unser Tool zwei Jahre später und können mithilfe von Experimenten in den Datenbanken, über die wir heute sprechen, vergleichen, was war und was geworden ist.

Und dafür müssen wir ein Experiment erstellen. Es besteht aus vier Teilen.

• Der erste ist die Umwelt. Wir brauchen ein Stück Hardware. Und wenn ich zu einer Firma komme und einen Vertrag unterschreibe, fordere ich sie auf, mir die gleiche Hardware wie in der Produktion zu geben. Für jeden Ihrer Master benötige ich mindestens eine solche Hardware. Entweder handelt es sich hierbei um eine Instanz einer virtuellen Maschine bei Amazon oder Google, oder ich benötige genau die gleiche Hardware. Das heißt, ich möchte die Umgebung neu erstellen. Und in das Konzept der Umgebung beziehen wir die Hauptversion von Postgres ein.
• Der zweite Teil ist Gegenstand unserer Forschung. Dies ist eine Datenbank. Es kann auf verschiedene Arten erstellt werden. Ich zeige dir wie.
• Der dritte Teil ist die Last. Dies ist der schwierigste Moment.
• Und der vierte Teil ist, was wir überprüfen, d. h. was wir mit was vergleichen. Nehmen wir an, wir können einen oder mehrere Parameter in der Konfiguration ändern oder einen Index usw. erstellen.

Wir starten ein Experiment. Hier sind pg_stat_statements. Links ist, was passiert ist. Rechts - was passiert ist.

Links default_statistics_target = 100, rechts = 1. Wir sehen, dass uns das geholfen hat. Insgesamt wurde alles um 000 % besser.

Wenn wir jedoch nach unten scrollen, gibt es Gruppen von Anfragen von pgBadger oder von pg_stat_statements. Es gibt zwei Möglichkeiten. Wir werden sehen, dass einige Anfragen um 88 % zurückgegangen sind. Und hier kommt der technische Ansatz. Wir können tiefer ins Innere graben, weil wir uns fragen, warum es gesunken ist. Sie müssen verstehen, was mit den Statistiken passiert ist. Warum mehr Buckets in der Statistik zu schlechteren Ergebnissen führen.

Oder wir können nicht graben, sondern „ALTER TABLE ... ALTER COLUMN“ ausführen und 100 Buckets in die Statistik dieser Spalte zurückgeben. Und dann können wir mit einem weiteren Experiment sicherstellen, dass dieser Patch geholfen hat. Alle. Dies ist ein technischer Ansatz, der uns hilft, das große Ganze zu sehen und Entscheidungen auf der Grundlage von Daten statt auf der Grundlage von Intuition zu treffen.

Ein paar Beispiele aus anderen Bereichen. CI-Tests gibt es schon seit vielen Jahren im Testwesen. Und kein Projekt, das bei klarem Verstand ist, würde ohne automatisierte Tests leben.

In anderen Branchen: in der Luftfahrt, in der Automobilindustrie, wenn wir Aerodynamik testen, haben wir auch die Möglichkeit, Experimente durchzuführen. Wir werden nicht etwas von einer Zeichnung direkt ins All schießen oder ein Auto sofort auf die Strecke bringen. Es gibt zum Beispiel einen Windkanal.

Aus Beobachtungen anderer Branchen können wir Rückschlüsse ziehen.

Erstens haben wir ein besonderes Umfeld. Es ist nah an der Produktion, aber nicht nah dran. Sein Hauptmerkmal ist, dass es kostengünstig, wiederholbar und möglichst automatisiert sein sollte. Und es müssen auch spezielle Tools zur detaillierten Analyse vorhanden sein.

Wenn wir ein Flugzeug starten und fliegen, haben wir höchstwahrscheinlich weniger Gelegenheit, jeden Millimeter der Flügeloberfläche zu untersuchen als in einem Windkanal. Wir haben mehr Diagnosetools. Wir können es uns leisten, mehr schwere Sachen mitzunehmen, die wir uns nicht leisten können, in einem Flugzeug in die Luft zu transportieren. Das Gleiche gilt für Postgres. In einigen Fällen aktivieren wir möglicherweise die vollständige Abfrageprotokollierung während Experimenten. Und das wollen wir in der Produktion nicht machen. Möglicherweise planen wir sogar, dies mithilfe von auto_explain zu aktivieren.

Und wie gesagt, ein hoher Automatisierungsgrad bedeutet, dass wir auf den Knopf drücken und wiederholen. So sollte es sein, damit viel experimentiert wird, damit es in Betrieb ist.

Nancy CLI – die Gründung des „Datenbanklabors“

Und so haben wir das gemacht. Das heißt, ich habe im Juni, vor fast einem Jahr, über diese Ideen gesprochen. Und wir haben bereits die sogenannte Nancy CLI in Open Source. Dies ist die Grundlage für den Aufbau eines Datenbanklabors.

Nancy — Es ist in Open Source, auf Gitlab. Man kann es sagen, man kann es versuchen. Ich habe in den Folien einen Link bereitgestellt. Sie können darauf klicken und es wird dort sein Hilfe bei allem Respekt.

Natürlich ist noch einiges in der Entwicklung. Da gibt es viele Ideen. Aber das ist etwas, was wir fast täglich nutzen. Und wenn wir eine Idee haben – warum kommt es, dass beim Löschen von 40 Zeilen alles auf IO hinausläuft? Dann können wir ein Experiment durchführen und genauer hinsehen, um zu verstehen, was passiert, und dann versuchen, das Problem im Handumdrehen zu beheben. Das heißt, wir machen ein Experiment. Wir optimieren zum Beispiel etwas und schauen, was am Ende passiert. Und das machen wir in der Produktion nicht. Das ist der Kern der Idee.

Wo kann das funktionieren? Dies kann lokal funktionieren, d. h. Sie können es überall tun, Sie können es sogar auf einem MacBook ausführen. Wir brauchen einen Docker, los geht's. Und alle. Sie können es in einigen Fällen auf einer Hardware oder in einer virtuellen Maschine überall ausführen.

Und es besteht auch die Möglichkeit, punktuell in Amazon in der EC2-Instanz remote zu laufen. Und das ist eine sehr coole Gelegenheit. Gestern haben wir beispielsweise mehr als 500 Experimente mit der i3-Instanz durchgeführt, angefangen bei der jüngsten bis hin zur i3-16-xlarge. Und 500 Experimente kosten uns 64 $. Jeder dauerte 15 Minuten. Das heißt, aufgrund der Tatsache, dass Spots dort genutzt werden, ist es sehr günstig – 70 % Rabatt, Amazons sekundengenaue Abrechnung. Du kannst viel tun. Sie können echte Forschung betreiben.

Und drei Hauptversionen von Postgres werden unterstützt. Es ist nicht so schwierig, einige alte und auch die neue 12. Version fertigzustellen.

Wir können ein Objekt auf drei Arten definieren. Das:

• Dump/SQL-Datei.
• Die wichtigste Möglichkeit besteht darin, das PGDATA-Verzeichnis zu klonen. In der Regel wird es vom Backup-Server übernommen. Wenn Sie über normale Binärsicherungen verfügen, können Sie von dort aus Klone erstellen. Wenn Sie über Clouds verfügen, erledigt dies ein Cloud-Büro wie Amazon und Google für Sie. Dies ist die wichtigste Möglichkeit, die reale Produktion zu klonen. So entfalten wir uns.
• Und die letzte Methode eignet sich für Recherchen, wenn Sie verstehen möchten, wie etwas in Postgres funktioniert. Das ist pgbench. Sie können mit pgbench generieren. Es ist nur eine „db-pgbench“-Option. Du sagst ihm, in welchem ​​Maßstab. Und alles wird wie gesagt in der Cloud generiert.

Und laden:

• Wir können den Ladevorgang in einem SQL-Thread ausführen. Dies ist der primitivste Weg.
• Und wir können die Last nachahmen. Und wir können es zunächst einmal auf folgende Weise nachahmen. Wir müssen alle Protokolle sammeln. Und es ist schmerzhaft. Ich zeige dir warum. Und wir spielen mit pgreplay, das in Nancy integriert ist.
• Oder eine andere Option. Der sogenannte Bastelauftrag, den wir mit einem gewissen Aufwand erledigen. Wir analysieren unsere aktuelle Auslastung des Kampfsystems und ermitteln die häufigsten Anforderungsgruppen. Und mit pgbench können wir diese Belastung im Labor emulieren.

• Entweder müssen wir eine Art SQL durchführen, d. h. wir prüfen eine Art Migration, erstellen dort einen Index und führen dort ANALAZE aus. Und wir schauen uns an, was vor und nach dem Vakuum geschah. Im Allgemeinen jedes SQL.
• Entweder ändern wir einen oder mehrere Parameter in der Config. Wir können uns beispielsweise anweisen, 100 Werte in Amazon für unsere Terabyte-Datenbank zu prüfen. Und in wenigen Stunden haben Sie das Ergebnis. In der Regel dauert die Bereitstellung einer Terabyte-Datenbank mehrere Stunden. Aber es ist ein Patch in der Entwicklung, wir haben eine Serie möglich, d.h. man kann durchgängig die gleichen pgdata auf dem gleichen Server verwenden und prüfen. Postgres wird neu gestartet und Caches werden zurückgesetzt. Und Sie können die Ladung fahren.

• Es kommt ein Verzeichnis mit einer Reihe verschiedener Dateien an, beginnend mit PG-SnapshotsZustand***. Und das Interessanteste daran ist pg_stat_statements, pg_stat_kcacke. Dies sind zwei Erweiterungen, die Anfragen analysieren. Und pg_stat_bgwriter enthält nicht nur pgwriter-Statistiken, sondern auch über Checkpoints und wie die Backends selbst schmutzige Puffer verdrängen. Und es ist alles interessant zu sehen. Wenn wir beispielsweise shared_buffers einrichten, ist es sehr interessant zu sehen, wie viel jeder ersetzt hat.
• Es kommen auch Postgres-Protokolle an. Zwei Protokolle – ein Vorbereitungsprotokoll und ein Lade-Wiedergabeprotokoll.
• Eine relativ neue Funktion ist FlameGraphs.
• Wenn Sie außerdem die Optionen pgreplay oder pgbench zum Abspielen des Ladevorgangs verwendet haben, ist deren Ausgabe nativ. Und Sie werden Latenz und TPS sehen. Es wird möglich sein zu verstehen, wie sie es sahen.
• System Information.
• Grundlegende CPU- und IO-Prüfungen. Dies gilt eher für EC2-Instanzen in Amazon. Wenn Sie 100 identische Instanzen in einem Thread starten und dort 100 verschiedene Läufe ausführen möchten, haben Sie 10 Experimente. Und Sie müssen sicherstellen, dass Sie nicht auf eine fehlerhafte Instanz stoßen, die bereits von jemandem unterdrückt wird. Andere sind auf dieser Hardware aktiv und Sie haben nur noch wenige Ressourcen übrig. Es ist besser, solche Ergebnisse zu verwerfen. Und mit Hilfe von Sysbench von Alexey Kopytov führen wir einige kurze Tests durch, die mit anderen verglichen werden können, d. h. Sie werden verstehen, wie sich die CPU und wie sich die E/A verhält.

Welche technischen Schwierigkeiten gibt es am Beispiel verschiedener Unternehmen?

Nehmen wir an, wir möchten den tatsächlichen Ladevorgang mithilfe von Protokollen wiederholen. Es ist eine großartige Idee, wenn es auf Open Source pgreplay geschrieben ist. Wir nutzen es. Damit es jedoch gut funktioniert, müssen Sie die vollständige Abfrageprotokollierung mit Parametern und Timing aktivieren.

Es gibt einige Komplikationen hinsichtlich der Dauer und des Zeitstempels. Wir werden die ganze Küche leeren. Die Hauptfrage ist, ob Sie es sich leisten können oder nicht?

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

Das Problem besteht darin, dass es möglicherweise nicht verfügbar ist. Zunächst müssen Sie verstehen, welcher Stream in das Protokoll geschrieben wird. Wenn Sie pg_stat_statements haben, können Sie diese Abfrage verwenden (der Link ist in den Folien verfügbar), um ungefähr zu erfahren, wie viele Bytes pro Sekunde geschrieben werden.

Wir schauen uns die Länge der Anfrage an. Wir vernachlässigen die Tatsache, dass es keine Parameter gibt, aber wir kennen die Länge der Anfrage und wissen, wie oft sie pro Sekunde ausgeführt wurde. Auf diese Weise können wir ungefähr abschätzen, wie viele Bytes pro Sekunde vorhanden sind. Wir machen vielleicht doppelt so viel Fehler, aber wir werden die Reihenfolge auf jeden Fall auf diese Weise verstehen.

Wir können sehen, dass diese Anfrage 802 Mal pro Sekunde ausgeführt wird. Und wir sehen, dass Bytes_pro Sekunde – 300 kB/s plus oder minus geschrieben werden. Und in der Regel können wir uns einen solchen Fluss leisten.

Aber! Tatsache ist, dass es unterschiedliche Protokollierungssysteme gibt. Und die Standardeinstellung der Leute ist normalerweise „Syslog“.

Und wenn Sie Syslog haben, dann haben Sie möglicherweise ein Bild wie dieses. Wir nehmen pgbench, aktivieren die Abfrageprotokollierung und sehen, was passiert.

Ohne Protokollierung – das ist die Spalte links. Wir haben 161 TPS erreicht. Mit Syslog – das ist in Ubuntu 000 auf Amazon – erhalten wir 16.04 TPS. Und wenn wir auf zwei andere Protokollierungsmethoden umsteigen, ist die Situation viel besser. Das heißt, wir haben mit einem Rückgang gerechnet, allerdings nicht im gleichen Ausmaß.

Und unter CentOS 7, an dem auch Journald beteiligt ist, indem es Protokolle in ein Binärformat umwandelt, um die Suche zu erleichtern usw., ist es dort ein Albtraum, wir fallen 44 Mal in TPS aus.

Und damit leben die Menschen. Und oft ist es in Unternehmen, insbesondere in großen Unternehmen, sehr schwierig, dies zu ändern. Wenn Sie sich von Syslog lösen können, dann lassen Sie es bitte.

• Bewerten Sie IOPS und Schreibfluss.
• Überprüfen Sie Ihr Protokollierungssystem.
• Wenn die projizierte Last übermäßig groß ist, sollten Sie eine Probenahme in Betracht ziehen.

Wir haben pg_stat_statements. Wie gesagt, es muss da sein. Und wir können jede Gruppe von Anfragen auf besondere Weise in einer Datei aufnehmen und beschreiben. Und dann können wir eine sehr praktische Funktion in pgbench nutzen – die Möglichkeit, mehrere Dateien mit der Option „-f“ einzufügen.

Es versteht viel „-f“. Und Sie können mit Hilfe von „@“ am Ende erkennen, welchen Anteil jede Datei haben soll. Das heißt, wir können sagen, dass dies in 10 % der Fälle und in 20 % der Fälle der Fall ist. Und das bringt uns näher an das heran, was wir in der Produktion sehen.

Wie werden wir verstehen, was wir in der Produktion haben? Welcher Anteil und wie? Das ist etwas nebenbei. Wir haben noch ein Produkt Postgres-Checkup. Auch eine Basis in Open Source. Und wir entwickeln es jetzt aktiv weiter.

Er wurde aus etwas anderen Gründen geboren. Aus Gründen, aus denen Überwachung nicht ausreicht. Das heißt, Sie kommen, schauen sich die Basis an, schauen Sie sich die bestehenden Probleme an. Und in der Regel machen Sie einen Gesundheitscheck. Wenn Sie ein erfahrener Datenbankadministrator sind, führen Sie einen Health_Check durch. Wir haben uns die Verwendung von Indizes usw. angesehen. Wenn Sie OKmeter haben, dann ist das großartig. Das ist eine coole Überwachung für Postgres. OKmeter.io – bitte installieren, dort ist alles sehr gut gemacht. Es ist bezahlt.

Wenn Sie keins haben, haben Sie normalerweise nicht viel. Bei der Überwachung gibt es normalerweise CPU, IO und dann noch Reservierungen, und das ist alles. Und wir brauchen mehr. Wir müssen sehen, wie das Autovacuum funktioniert, wie der Checkpoint funktioniert, in io müssen wir den Checkpoint vom BGwriter und von den Backends usw. trennen.

Das Problem ist, dass wenn man einem großen Unternehmen hilft, es nicht schnell etwas umsetzen kann. Sie können OKmeter nicht schnell kaufen. Vielleicht kaufen sie es in sechs Monaten. Sie können einige Pakete nicht schnell zustellen.

Und wir kamen auf die Idee, dass wir ein spezielles Tool brauchen, bei dem nichts installiert werden muss, d. h. man muss in der Produktion überhaupt nichts installieren. Installieren Sie es auf Ihrem Laptop oder auf einem Beobachtungsserver, von dem aus Sie es ausführen. Und es wird viele Dinge analysieren: das Betriebssystem, das Dateisystem und Postgres selbst, und einige einfache Abfragen durchführen, die direkt in der Produktion ausgeführt werden können, und nichts wird fehlschlagen.

Wir nannten es Postgres-Checkup. Medizinisch gesehen handelt es sich hierbei um einen regelmäßigen Gesundheitscheck. Wenn es um das Thema Automobil geht, dann ist es wie Wartung. Je nach Marke führen Sie die Wartung Ihres Autos alle sechs Monate oder ein Jahr durch. Führen Sie Wartungsarbeiten an Ihrer Basis durch? Das heißt, führen Sie regelmäßig gründliche Recherchen durch? Es muss getan werden. Wenn Sie Backups erstellen und anschließend eine Überprüfung durchführen, ist dies nicht weniger wichtig.

Und wir haben ein solches Werkzeug. Es begann sich erst vor etwa drei Monaten aktiv zu entwickeln. Er ist noch jung, aber da ist noch einiges drin.

Sammeln der „einflussreichsten“ Gruppen von Abfragen – Bericht K003 im Postgres-Checkup

Und es gibt eine Gruppe von Berichten K. Bisher drei Berichte. Und es gibt einen solchen Bericht K003. Es gibt die Spitze von pg_stat_statements, sortiert nach total_time.

Wenn wir Anforderungsgruppen nach total_time sortieren, sehen wir oben die Gruppe, die unser System am meisten belastet, also mehr Ressourcen verbraucht. Warum benenne ich Abfragegruppen? Weil wir die Parameter verworfen haben. Dabei handelt es sich nicht mehr um Anfragen, sondern um Gruppen von Anfragen, also abstrahiert.

Und wenn wir von oben nach unten optimieren, schonen wir unsere Ressourcen und verzögern den Moment, in dem wir ein Upgrade durchführen müssen. Dies ist eine sehr gute Möglichkeit, Geld zu sparen.

Möglicherweise ist dies keine sehr gute Möglichkeit, sich um Benutzer zu kümmern, da wir möglicherweise keine seltenen, aber sehr ärgerlichen Fälle sehen, in denen eine Person 15 Sekunden gewartet hat. Insgesamt sind sie so selten, dass wir sie nicht sehen, aber wir haben es mit Ressourcen zu tun.

Was ist in dieser Tabelle passiert? Wir haben zwei Schnappschüsse gemacht. Postgres_checkup gibt Ihnen ein Delta für jede Metrik: Gesamtzeit, Aufrufe, Zeilen, shared_blks_read usw. Das war's, das Delta wurde berechnet. Das große Problem mit pg_stat_statements besteht darin, dass es sich nicht daran erinnert, wann es zurückgesetzt wurde. Wenn sich pg_stat_database erinnert, erinnert sich pg_stat_statements nicht. Sie sehen, es gibt eine Zahl von 1, aber wir wissen nicht, von wo aus wir gezählt haben.

Und hier wissen wir es, hier haben wir zwei Schnappschüsse. Wir wissen, dass das Delta in diesem Fall 56 Sekunden betrug. Eine sehr kurze Lücke. Sortiert nach total_time. Und dann können wir differenzieren, d. h. wir teilen alle Kennzahlen nach Dauer. Wenn wir jede Metrik durch die Dauer dividieren, erhalten wir die Anzahl der Anrufe pro Sekunde.

Als nächstes ist total_time pro Sekunde meine Lieblingsmetrik. Sie wird in Sekunden pro Sekunde gemessen, d. h. wie viele Sekunden unser System benötigt hat, um diese Gruppe von Anfragen pro Sekunde auszuführen. Wenn Sie dort mehr als eine Sekunde pro Sekunde sehen, bedeutet das, dass Sie mehr als einen Kern geben mussten. Das ist eine sehr gute Kennzahl. Sie können verstehen, dass dieser Freund beispielsweise mindestens drei Kerne benötigt.

Das ist unser Know-how, so etwas habe ich noch nirgendwo gesehen. Bitte beachten Sie, dass dies eine sehr einfache Sache ist: Sekunde pro Sekunde. Manchmal, wenn Ihre CPU 100 % beträgt, dauert es eine halbe Stunde pro Sekunde, das heißt, Sie haben eine halbe Stunde damit verbracht, genau diese Anforderungen zu erledigen.

Als nächstes sehen wir Zeilen pro Sekunde. Wir wissen, wie viele Zeilen pro Sekunde zurückgegeben wurden.

Und dann gibt es noch etwas Interessantes. Wie viele shared_buffers wir pro Sekunde aus den shared_buffers selbst lesen. Die Treffer waren bereits vorhanden und wir haben die Zeilen aus dem Betriebssystem-Cache oder von der Festplatte entnommen. Die erste Option ist schnell, die zweite kann je nach Situation schnell sein oder auch nicht.

Und die zweite Möglichkeit der Differenzierung besteht darin, die Anzahl der Anfragen in diese Gruppe aufzuteilen. In der zweiten Spalte wird immer eine Abfrage pro Abfrage angezeigt. Und dann ist es interessant – wie viele Millisekunden waren in dieser Anfrage enthalten? Wir wissen, wie sich diese Abfrage im Durchschnitt verhält. Für jede Anfrage wurden 101 Millisekunden benötigt. Dies ist die traditionelle Metrik, die wir verstehen müssen.

Wie viele Zeilen hat jede Abfrage im Durchschnitt zurückgegeben? Wir sehen 8 dieser Gruppe kehrt zurück. Wie viel wurde im Durchschnitt aus dem Cache entnommen und gelesen? Wir sehen, dass alles gut zwischengespeichert ist. Solide Treffer für die erste Gruppe.

Und der vierte Teilstring in jeder Zeile gibt den Prozentsatz der Gesamtsumme an. Wir haben Anrufe. Sagen wir 1. Und wir können verstehen, welchen Beitrag diese Gruppe leistet. Wir sehen, dass in diesem Fall die erste Gruppe weniger als 000 % beiträgt. Das heißt, es ist so langsam, dass wir es im Gesamtbild nicht sehen. Und die zweite Gruppe besteht zu 000 % aus Anrufen. Das heißt, 0,01 % aller Anrufe gehören der zweiten Gruppe an.

Total_time ist auch interessant. Für die erste Gruppe von Anfragen haben wir 14 % unserer gesamten Arbeitszeit aufgewendet. Und zum zweiten – 11 % usw.

Ich werde nicht auf Details eingehen, aber es gibt Feinheiten. Wir zeigen oben einen Fehler an, da beim Vergleich Snapshots schweben können, d. h. einige Anfragen fallen möglicherweise heraus und können im zweiten nicht mehr vorhanden sein, während einige neue möglicherweise auftauchen. Und dort berechnen wir den Fehler. Wenn Sie 0 sehen, ist das gut. Es liegen keine Fehler vor. Wenn die Fehlerquote bis zu 20 % beträgt, ist es in Ordnung.

Dann kehren wir zu unserem Thema zurück. Wir müssen die Arbeitslast bewältigen. Wir gehen von oben nach unten vor und gehen weiter, bis wir 80 % oder 90 % erreichen. Normalerweise sind dies 10-20 Gruppen. Und wir erstellen Dateien für pgbench. Wir verwenden dort Zufall. Manchmal klappt das leider nicht. Und in Postgres 12 wird es mehr Möglichkeiten geben, diesen Ansatz zu nutzen.

Und dann gewinnen wir auf diese Weise 80-90 % an Gesamtzeit. Was soll ich als nächstes nach „@“ einfügen? Wir schauen uns die Anrufe an, schauen, wie groß das Interesse ist und verstehen, dass wir hier so viel Interesse schulden. Anhand dieser Prozentsätze können wir erkennen, wie die einzelnen Dateien ausgeglichen werden. Danach verwenden wir pgbench und machen uns an die Arbeit.

Wir haben auch K001 und K002.

K001 ist eine große Zeichenfolge mit vier Teilzeichenfolgen. Dies ist ein Merkmal unserer gesamten Ladung. Siehe zweite Spalte und zweite Unterzeile. Wir sehen das etwa eineinhalb Sekunden pro Sekunde, d. h. wenn es zwei Kerne gibt, dann ist es gut. Die Kapazität wird etwa 75 % betragen. Und es wird so funktionieren. Wenn wir 10 Kerne haben, sind wir im Allgemeinen ruhig. Auf diese Weise können wir Ressourcen bewerten.

K002 nenne ich Abfrageklassen, also SELECT, INSERT, UPDATE, DELETE. Und separat SELECT FOR UPDATE, da es sich um eine Sperre handelt.

Und hier können wir den Schluss ziehen, dass SELECT bei normalen Lesern 82 % aller Aufrufe, aber gleichzeitig 74 % der Gesamtzeit ausmacht. Das heißt, sie werden oft aufgerufen, verbrauchen aber weniger Ressourcen.

Und wir kehren zu der Frage zurück: „Wie können wir die richtigen Shared_Buffers auswählen?“ Ich beobachte, dass die meisten Benchmarks auf der Idee basieren – mal sehen, wie hoch der Durchsatz sein wird, also wie hoch der Durchsatz sein wird. Sie wird normalerweise in TPS oder QPS gemessen.

Und wir versuchen, mithilfe von Tuning-Parametern so viele Transaktionen pro Sekunde wie möglich aus dem Auto herauszuholen. Hier stehen genau 311 pro Sekunde zur Auswahl.

Aber niemand fährt mit Vollgas zur Arbeit und wieder nach Hause. Das ist dumm. Dasselbe gilt auch für Datenbanken. Wir müssen nicht mit Höchstgeschwindigkeit fahren, und das tut auch niemand. Niemand lebt in der Produktion, die 100 % CPU hat. Obwohl vielleicht jemand lebt, aber das ist nicht gut.

Die Idee ist, dass wir normalerweise mit 20 Prozent der Kapazität fahren, vorzugsweise nicht mehr als 50 Prozent. Und wir versuchen vor allem, die Reaktionszeit für unsere Benutzer zu optimieren. Das heißt, wir müssen unsere Knöpfe so drehen, dass es bedingt eine minimale Latenz bei 20 % Geschwindigkeit gibt. Dies ist eine Idee, die wir auch in unseren Experimenten zu nutzen versuchen.

Und zum Schluss noch Empfehlungen:

• Stellen Sie sicher, dass Sie das Database Lab absolvieren.
• Wenn möglich, tun Sie es bei Bedarf, damit es sich eine Weile entfaltet – spielen Sie es und werfen Sie es weg. Wenn du Wolken hast, dann ist das selbstverständlich, d.h. du musst viel Stehen haben.
• Sei neugierig. Und wenn etwas nicht stimmt, dann überprüfen Sie mit Experimenten, wie es sich verhält. Mit Nancy können Sie trainieren, wie die Basis funktioniert.
• Und streben Sie eine minimale Reaktionszeit an.
• Und haben Sie keine Angst vor Postgres-Quellen. Wenn Sie mit Quellen arbeiten, müssen Sie Englisch beherrschen. Da gibt es viele Kommentare, da ist alles erklärt.
• Und überprüfen Sie den Zustand der Datenbank regelmäßig, mindestens alle drei Monate, manuell oder per Postgres-Check.

Fragen

Vielen Dank! Eine sehr interessante Sache.

Zwei Stück.

Ja, zwei Stück. Nur habe ich es nicht ganz verstanden. Können Nancy und ich bei der Arbeit nur einen Parameter oder eine ganze Gruppe optimieren?

Wir haben einen Delta-Konfigurationsparameter. Sie können dort so viel auf einmal wenden, wie Sie möchten. Aber Sie müssen verstehen, dass Sie falsche Schlussfolgerungen ziehen können, wenn Sie viele Dinge ändern.

Ja. Warum habe ich gefragt? Weil es schwierig ist, Experimente durchzuführen, wenn man nur einen Parameter hat. Ziehen Sie es fest und sehen Sie, wie es funktioniert. Ich habe ihn rausgeworfen. Dann beginnt man mit dem nächsten.

Sie können es gleichzeitig festziehen, aber das hängt natürlich von der Situation ab. Aber es ist besser, eine Idee zu testen. Gestern hatten wir eine Idee. Wir hatten eine sehr enge Situation. Es gab zwei Konfigurationen. Und wir konnten nicht verstehen, warum es einen großen Unterschied gab. Und es entstand die Idee, dass man Dichotomie nutzen muss, um konsequent zu verstehen und herauszufinden, was der Unterschied ist. Sie können sofort die Hälfte der Parameter gleich machen, dann ein Viertel usw. Alles ist flexibel.

Und es gibt noch eine Frage. Das Projekt ist jung und entwickelt sich. Die Dokumentation ist bereits fertig, gibt es eine detaillierte Beschreibung?

Ich habe dort ausdrücklich einen Link zur Beschreibung der Parameter gesetzt. Das ist. Aber vieles ist noch nicht so weit. Ich suche Gleichgesinnte. Und ich finde sie, wenn ich auftrete. Das ist sehr cool. Jemand arbeitet bereits mit mir zusammen, jemand hat dort geholfen und etwas getan. Und wenn Sie sich für dieses Thema interessieren, geben Sie Feedback, was fehlt.

Sobald wir das Labor aufgebaut haben, wird es vielleicht Feedback geben. Mal sehen. Danke!

Guten Tag! Danke für den Bericht! Ich habe gesehen, dass es Amazon-Support gibt. Gibt es Pläne, GSP zu unterstützen?

Gute Frage. Wir haben damit angefangen. Und wir haben es vorerst eingefroren, weil wir Geld sparen wollen. Das heißt, es gibt Unterstützung für die Verwendung von „run on localhost“. Sie können selbst eine Instanz erstellen und lokal arbeiten. Das machen wir übrigens. Ich mache das bei Getlab, dort bei GSP. Aber wir sehen noch keinen Sinn in einer solchen Orchestrierung, da es bei Google keine günstigen Spots gibt. Da gibt es ??? Instanzen, aber sie haben Einschränkungen. Erstens gibt es dort immer nur 70 % Rabatt und man kann dort nicht mit dem Preis spielen. Bei Spots erhöhen wir den Preis um 5-10 %, um die Wahrscheinlichkeit zu verringern, dass Sie rausgeschmissen werden. Das heißt, Sie sichern sich Plätze, diese können Ihnen aber jederzeit wieder entzogen werden. Wenn Sie etwas höher bieten als andere, werden Sie später getötet. Google hat ganz andere Besonderheiten. Und es gibt noch eine weitere sehr schlimme Einschränkung: Sie leben nur 24 Stunden. Und manchmal möchten wir ein Experiment fünf Tage lang durchführen. Aber Sie können dies punktuell tun; Flecken bleiben manchmal Monate lang bestehen.

Guten Tag! Danke für den Bericht! Sie haben die Untersuchung erwähnt. Wie berechnet man stat_statements-Fehler?

Sehr gute Frage. Ich kann es Ihnen sehr detailliert zeigen und erzählen. Kurz gesagt, wir schauen uns an, wie sich die Gruppe der Anforderungsgruppen verändert hat: Wie viele sind abgefallen und wie viele neue sind aufgetaucht. Und dann schauen wir uns zwei Metriken an: total_time und Anrufe, also gibt es zwei Fehler. Und wir schauen uns den Beitrag der schwebenden Gruppen an. Es gibt zwei Untergruppen: diejenigen, die gegangen sind, und diejenigen, die angekommen sind. Mal sehen, welchen Beitrag sie zum Gesamtbild leisten.

Haben Sie keine Angst, dass es in der Zeit zwischen den Schnappschüssen zwei- oder dreimal dorthin wechselt?

Das heißt, haben sie sich erneut registriert oder was?

Diese Anfrage wurde zum Beispiel schon einmal vorweggenommen, dann kam sie und wurde erneut vorweggenommen, dann kam sie noch einmal und wurde erneut vorweggenommen. Und Sie haben hier etwas berechnet, und wo ist das alles?

Gute Frage, wir müssen mal schauen.

Ich habe etwas Ähnliches gemacht. Es war natürlich einfacher, ich habe es alleine gemacht. Aber ich musste zurücksetzen, stat_statements zurücksetzen und zum Zeitpunkt des Snapshots feststellen, dass weniger als ein bestimmter Bruchteil vorhanden war, was immer noch nicht die Obergrenze dessen erreichte, wie viele stat_statements sich dort ansammeln konnten. Und ich verstehe, dass höchstwahrscheinlich nichts verschoben wurde.

Ja Ja.

Aber ich verstehe nicht, wie ich das sonst zuverlässig machen kann.

Leider weiß ich nicht mehr genau, ob wir dort den Abfragetext oder queryid mit pg_stat_statements verwenden und uns darauf konzentrieren. Wenn wir uns auf die Abfrage-ID konzentrieren, vergleichen wir theoretisch vergleichbare Dinge.

Nein, er kann zwischen den Schnappschüssen mehrmals herausgedrängt werden und wiederkommen.

Mit der gleichen ID?

Ja.

Wir werden das studieren. Gute Frage. Wir müssen es studieren. Aber im Moment sehen wir entweder die geschriebene 0...

Dies ist natürlich ein seltener Fall, aber ich war schockiert, als ich herausfand, dass stat_statemetns dort verdrängen kann.

In Pg_stat_statements kann es viele Dinge geben. Wir sind auf die Tatsache gestoßen, dass Ihre Sets auch getrackt werden, wenn Sie track_utility = aktiviert haben.

Ja, natürlich.

Und wenn Sie einen Java-Ruhezustand haben, der zufällig ist, beginnt die Hash-Tabelle dort zu liegen. Und sobald Sie eine sehr ausgelastete Anwendung deaktivieren, stehen am Ende 50–100 Gruppen zur Verfügung. Und dort ist alles mehr oder weniger stabil. Eine Möglichkeit, dem entgegenzuwirken, besteht darin, pg_stat_statements.max zu erhöhen.

Ja, aber Sie müssen wissen, wie viel. Und irgendwie müssen wir ihn im Auge behalten. Das ist, was ich tue. Das heißt, ich habe pg_stat_statements.max. Und ich sehe, dass ich zum Zeitpunkt des Schnappschusses noch nicht 70 % erreicht hatte. Okay, wir haben also nichts verloren. Lassen Sie uns zurücksetzen. Und wir sparen wieder. Wenn der nächste Schnappschuss weniger als 70 beträgt, haben Sie höchstwahrscheinlich nichts mehr verloren.

Ja. Der Standardwert beträgt jetzt 5. Und das ist für viele Menschen ausreichend.

Normalerweise ja.

Video:

PS In meinem eigenen Namen möchte ich hinzufügen, dass Sie Postgres verwenden können, wenn es vertrauliche Daten enthält und diese nicht in die Testumgebung einbezogen werden können PostgreSQL-Anonymisierer. Das Schema sieht ungefähr wie folgt aus:

Source: habr.com