Industrieller Ansatz zur Optimierung von PostgreSQL: Experimente mit Datenbanken." Nikolai Samokhalov

Ich empfehle, den Bericht von Nikolai Samokhvalov über "Industrieansätze zur Optimierung von PostgreSQL: Experimente mit Datenbanken" zu lesen.

Shared_buffers = 25 % – ist das viel oder wenig? Oder genau richtig? Wie erkennt man, ob diese – ziemlich veraltete – Empfehlung für Ihren speziellen Fall geeignet ist?

Es ist an der Zeit, das Thema der Parameteranpassung in der postgresql.conf "ernsthaft" zu betrachten. Nicht mit blindem "Autotuning" oder veralteten Ratschlägen aus Artikeln und Blogs, sondern basierend auf:

1. streng durchgeführten Experimenten an Datenbanken, die automatisiert, in großer Anzahl und unter Bedingungen, die den "Echtbetrieb" möglichst nahekommen, durchgeführt werden,
2. einem tiefen Verständnis der Besonderheiten von DBMS und Betriebssystemen.

Mit Nancy CLI (https://gitlab.com/postgres.ai/nancy), werden wir ein konkretes Beispiel – die berüchtigten shared_buffers – in verschiedenen Situationen und Projekten betrachten und versuchen herauszufinden, wie man die optimale Einstellung für unsere Infrastruktur, Datenbank und Last findet.

Es wird um Experimente mit Datenbanken gehen. Diese Geschichte dauert etwas mehr als ein halbes Jahr.

Ein wenig über mich. Ich habe über 14 Jahre Erfahrung mit Postgres. Ich habe mehrere sozialnetzwerkorientierte Unternehmen gegründet. Überall wurde Postgres eingesetzt und wird weiterhin verwendet.

Außerdem die Gruppe RuPostgres auf Meetup, Platz 2 weltweit. Wir nähern uns langsam 2.000 Mitgliedern. RuPostgres.org.

Und auf verschiedenen Konferenzen, einschließlich Highload, bin ich seit der Gründung für die Datenbanken, insbesondere Postgres, verantwortlich.

In den letzten Jahren habe ich meine Praxis im Postgres-Consulting in 11 Zeitzonen von hier aus neu gestartet.

Als ich das vor einigen Jahren tat, hatte ich eine gewisse Pause bei der aktiven manuellen Arbeit mit Postgres, wahrscheinlich seit 2010. Ich war überrascht, wie wenig sich der Arbeitsalltag von DBAs verändert hatte und wie viel man immer noch manuelle Arbeit einsetzen musste. Und ich dachte sofort, dass hier etwas nicht stimmt, es muss mehr automatisiert werden.

Da dies alles remote stattfand, waren die meisten Kunden in der Cloud. Und vieles war bereits offensichtlich automatisiert. Darüber später mehr. Das heißt, es kam zur Idee, dass es eine Reihe von Werkzeugen geben sollte, also eine Art Plattform, die nahezu alle DBA-Aktivitäten automatisiert, um eine große Anzahl von Datenbanken verwalten zu können.

In diesem Bericht wird es nicht geben:

• „Silberne Kugeln“ und Aussagen wie – setzen Sie 8 GB oder 25 % shared_buffers ein und alles wird gut. Es wird nicht so viel über shared_buffers gesprochen.
• Hardcore „Innereien“.

Was wird passieren?

• Es wird Optimierungsprinzipien geben, die wir anwenden und weiterentwickeln. Es werden verschiedene Ideen entstehen, die uns auf unserem Weg kommen, sowie verschiedene Werkzeuge, die wir größtenteils in Open Source erstellen, d.h. das Grundgerüst erstellen wir in Open Source. Darüber hinaus haben wir Tickets, die gesamte Kommunikation findet praktisch in Open Source statt. Sie können sehen, was wir gerade tun, was im nächsten Release kommen wird, usw.
• Es wird auch einige Erfahrungen mit der Anwendung dieser Prinzipien und Werkzeuge in mehreren Unternehmen geben: von kleinen Startups bis hin zu großen Firmen.

Wie entwickelt sich das alles?

Zunächst einmal ist die Hauptaufgabe eines DBAs neben der Sicherstellung der Erstellung von Instanzen, der Bereitstellung von Backups usw. die Identifizierung von Engpässen und die Optimierung der Leistung.

Aktuell erfolgt das folgendermaßen. Wir schauen uns das Monitoring an, sehen etwas und es fehlen uns einige Details. Wir fangen an, genauer nachzuforschen, normalerweise händisch, und verstehen, was wir damit machen können.

Es gibt zwei Ansätze. Pg_stat_statements – eine standardmäßige Lösung zur Identifizierung langsamer Abfragen. Und die Analyse der Postgres-Logs mit Hilfe von pgBadger.

Jeder der Ansätze hat erhebliche Nachteile. Im ersten Ansatz werden alle Parameter ignoriert. Wenn wir Gruppen von SELECT * FROM table where column gleich „?“ oder „$“ ab Version Postgres 10 sehen, wissen wir nicht, ob es sich um einen Index-Scan oder einen Seq-Scan handelt. Es hängt sehr vom Parameter ab. Wenn man einen seltenen Wert einfügt, wird es ein Index-Scan. Wenn man einen Wert einfügt, der 90 % der Tabelle ausmacht, wird es offensichtlich ein Seq-Scan, da Postgres die Statistiken kennt. Und das ist ein großes Manko von pg_stat_statements, obwohl hier an einigen Verbesserungen gearbeitet wird.

Die größte Schwäche bei der Log-Analyse ist, dass man sich in der Regel „log_min_duration_statement = 0“ nicht leisten kann. Darüber werden wir ebenfalls sprechen. Folglich sieht man nicht das ganze Bild. Eine sehr schnelle Abfrage kann enorm viele Ressourcen verbrauchen, aber man wird sie nicht sehen, weil sie unterhalb des Schwellenwerts liegt.

Wie lösen DBAs die gefundenen Probleme?

Zum Beispiel haben wir ein Problem gefunden. Was wird normalerweise getan? Wenn Sie Entwickler sind, werden Sie etwas auf einem bestimmten Instance tun, der nicht so groß ist. Wenn Sie DBA sind, haben Sie eine Staging-Umgebung. Und es kann nur eine sein. Und diese ist seit einem halben Jahr veraltet. Und Sie denken, dass Sie in die Produktion gehen werden. Und sogar erfahrene DBAs überprüfen später in der Produktion, auf der Replik. Manchmal erstellen sie einen temporären Index, stellen sicher, dass er hilft, löschen ihn und geben ihn an die Entwickler zurück, damit sie ihn in die Migrationsdateien einfügen. So ein Unsinn passiert gerade. Und das ist ein Problem.

• Konfigurationen optimieren.
• Den Index-Satz optimieren.
• Den SQL-Befehl selbst ändern (das ist der komplizierteste Weg).
• Kapazitäten hinzufügen (der einfachste Weg in den meisten Fällen).

Mit diesen Dingen gibt es sehr viel. Es gibt viele Möglichkeiten in Postgres. Man muss viel wissen. Viele Indizes in Postgres, auch dank der Organisatoren dieser Konferenz. Und all das muss man wissen, und genau das lässt bei Nicht-DBAs den Eindruck entstehen, dass DBAs mit schwarzer Magie arbeiten. Das heißt, man muss etwa 10 Jahre damit verbringen, um alles richtig zu verstehen.

Und ich bin der Kämpfer gegen diese schwarze Magie. Ich möchte alles so gestalten, dass es Technologie gibt und keine Intuition dabei.

Beispiele aus dem Leben

Das habe ich mindestens in zwei Projekten beobachtet, einschließlich meinem eigenen. Ein weiterer Blog-Beitrag informiert uns, dass der Wert von 1.000 für default_statistic_target gut ist. Gut, lassen Sie es uns in der Produktion versuchen.

Und hier können wir, zwei Jahre später mit unserem Tool und through experimentation with the databases that we are talking about today, vergleichen, was war und was geworden ist.

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

• Der erste Teil ist die Umgebung. Wir benötigen Hardware. Und wenn ich in ein Unternehmen komme und einen Vertrag abschließe, sage ich, dass ich die gleiche Hardware wie in der Produktion haben möchte. Für jeden Ihrer Master brauche ich mindestens eine Hardware, die dieselbe ist. Entweder ist es eine virtuelle Maschine in Amazon oder Google oder ich brauche genau die gleiche Hardware. Das heißt, ich möchte die Umgebung nachbilden. Und unter Umfeld verstehen wir die Hauptversion von Postgres.
• Der zweite Teil ist das Objekt unserer Forschung. Das ist die Datenbank. Sie kann auf verschiedene Arten erstellt werden. Ich werde Ihnen zeigen, wie.
• Der dritte Teil ist die Last. Das ist der komplizierteste Moment.
• Und der vierte Teil ist das, was wir überprüfen, d. h. womit wir vergleichen werden. Angenommen, wir können in der Konfiguration einen oder mehrere Parameter ändern oder wir können einen Index erstellen usw.

Wir starten das Experiment. Hier ist pg_stat_statements. Links – was war. Rechts – was geworden ist.

Links steht default_statistics_target = 100, rechts = 1 000. Wir sehen, dass uns das geholfen hat. Insgesamt hat es um 8 % verbessert.

Wenn wir jedoch nach unten scrollen, sehen wir Gruppen von Anfragen aus pgBadger oder pg_stat_statements. Hier gibt es zwei Möglichkeiten. Wir werden sehen, dass eine bestimmte Anfrage um 88 % gefallen ist. Und hier ist der technische Ansatz. Wir können weiter in die Tiefe gehen, weil es interessant ist, warum sie gefallen ist. Man muss verstehen, was in den Statistiken war. Warum führen mehr Buckets in der Statistik zu einem schlechteren Ergebnis.

Oder wir müssen nicht weiter forschen, sondern können „ALTER TABLE … ALTER COLUMN“ durchführen und die 100 Buckets zurück in die Statistik dieser Spalte geben. Und dann können wir durch ein weiteres Experiment überprüfen, dass dieser Patch geholfen hat. Das ist es. Das ist der technische Ansatz, der uns hilft, das Ganze zu sehen und Entscheidungen auf Basis von Daten und nicht auf Basis von Intuition zu treffen.

Ein paar Beispiele aus anderen Bereichen. In den Tests gibt es seit vielen Jahren CI-Tests. Und kein Projekt in gesundem Verstand würde ohne automatisierte Tests leben.

In anderen Branchen: in der Luftfahrt, im Automobilbau, wenn wir die Aerodynamik testen, haben wir ebenfalls die Möglichkeit, Experimente durchzuführen. Wir werden nicht sofort etwas nach einem Plan ins All schicken oder ein Auto sofort auf die Straße bringen. Zum Beispiel gibt es einen Windkanal.

Aus Beobachtungen in anderen Branchen können wir Schlussfolgerungen ziehen.

Erstens haben wir eine spezielle Umgebung. Sie ist nah am Produktionsbetrieb, aber nicht zu nah. Ihr Hauptmerkmal ist, dass sie kostengünstig, reproduzierbar und maximal automatisiert sein sollte. Zudem sollten spezielle Mittel für die Durchführung einer detaillierten Analyse vorhanden sein.

Wahrscheinlich haben wir, als wir das Flugzeug gestartet und geflogen sind, weniger Möglichkeiten, jeden Millimeter der Tragflächenoberfläche zu untersuchen, als in einem Windkanal. Wir haben mehr Mittel zur Diagnose. Wir können es uns erlauben, mehr von allem schweren anzuhängen, was wir uns im Flugzeug nicht erlauben können. Das gilt auch für Postgres. In einigen Fällen können wir das vollständige Logging der Anfragen während der Experimente aktivieren. Und das wollen wir in der Produktion nicht tun. Vielleicht schalten wir es in Zukunft mit auto_explain ein.

Wie ich bereits gesagt habe, bedeutet ein hohes Maß an Automatisierung, dass wir auf eine Taste gedrückt haben und es wiederholt haben. So sollte es laufen, damit es viele Experimente gibt, damit es im Fluss ist.

Nancy CLI – das Fundament des „Datenbanklabors“

Und so haben wir so etwas gemacht. Das heißt, ich habe im Juni, also vor fast einem Jahr, von diesen Ideen gesprochen. Und wir haben bereits in Open Source die sogenannte Nancy CLI. Dies ist die Grundlage, um ein Datenbanklabor aufzubauen.

Nancy — Es ist Open Source auf Gitlab. Sie können darauf zugreifen, Sie können es ausprobieren. Ich habe in den Folien einen Link hinzugefügt. Auf den kann man klicken und dort ist es help für alle Parameter.

Natürlich ist dort vieles noch in der Entwicklung. Es gibt viele Ideen. Aber das ist bereits das, was wir praktisch täglich anwenden. Wenn wir die Idee haben – was passiert, wenn wir 40.000.000 Zeilen löschen und alles auf IO stößt, dann können wir ein Experiment durchführen und genauer hinsehen, um zu verstehen, was passiert, und versuchen, das unterwegs zu beheben. Das heißt, wir führen ein Experiment durch. Zum Beispiel ändern wir etwas und schauen, was am Ende herauskommt. Und wir machen das nicht in der Produktion. Das ist der Kern der Idee.

Wo kann das funktionieren? Das kann lokal funktionieren, das heißt, man kann das überall machen, man kann es sogar auf einem MacBook starten. Man braucht Docker, legen wir los. Und das war's. Man kann es auf irgendeinem Server-Instance oder in einer VM, wo auch immer, starten.

Es gibt auch die Möglichkeit, remote in Amazon EC2-Instanzen in Spot-Preisen zu starten. Das ist eine sehr coole Möglichkeit. Zum Beispiel haben wir gestern über 500 Experimente auf einer i3-Instanz durchgeführt, beginnend mit der kleinsten und endend mit i3-16-xlarge. Und 500 Experimente haben uns 64 Dollar gekostet. Jedes dauerte 15 Minuten. Das heißt, durch die Nutzung von Spots ist das sehr günstig – ein Rabatt von 70 %, abgerechnet nach Sekunden von Amazon. Man kann also sehr viel machen. Man kann echte Forschung durchführen.

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

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

• Dump/sql-Datei.
• Der Hauptweg ist, das PGDATA-Verzeichnis zu klonen. Normalerweise wird es von einem Backup-Server genommen. Wenn Sie vernünftige binäre Backups haben, können Sie von dort Klone erstellen. Wenn Sie eine Cloud haben, wird das für Sie von einem Cloud-Anbieter wie Amazon oder Google erledigt. Das ist der wichtigste Weg für Klone aus einer echten Produktionsumgebung. So setzen wir es genau um.
• Und die letzte Methode eignet sich für Forschungszwecke, wenn man herausfinden möchte, wie eine bestimmte Funktion in Postgres funktioniert. Das ist pgbench. Sie können mit pgbench generieren. Das ist einfach eine Option "db-pgbench". Sie sagen ihm, welchen Scale. Und alles wird in der Cloud generiert, wie gesagt.

Und die Last:

• Die Last können wir in einem einzelnen SQL-Thread ausführen. Das ist die einfachste Methode.
• Oder wir können die Last emulieren. Und wir können die Last in erster Linie folgendermaßen emulieren. Wir müssen alle Logs sammeln. Und das ist schmerzhaft. Ich werde zeigen, warum. Und wir spielen es mit pgreplay ab, das in Nancy integriert ist.
• Oder eine andere Variante. Die sogenannte Craft-Last, die wir mit etwas Mühe erzeugen. Indem wir unsere aktuelle Last auf dem Live-System analysieren, ziehen wir die wichtigsten Gruppen von Anfragen heraus. Und mit pgbench können wir diese Last im Labor emulieren.

• Oder wir müssen irgendein SQL ausführen, d. h. wir überprüfen eine Migration, erstellen einen Index, führen ANALYZE durch. Und wir schauen uns an, was vor dem Vacuum und nach dem Vacuum war. Im Allgemeinen ist es jedes SQL.
• Entweder ändern wir in der Konfiguration einen oder mehrere Parameter. Wir können sagen, dass wir zum Beispiel 100 Werte auf Amazon für unsere Terabyte-Datenbank prüfen lassen möchten. Und nach ein paar Stunden haben Sie das Ergebnis. In der Regel wird sich die Terabyte-Datenbank mehrere Stunden entwickeln. Aber in der Entwicklung gibt es einen Patch, wir können eine Serie nutzen, d.h. Sie können auf demselben Server die gleiche pgdata nacheinander verwenden und prüfen. Postgres wird neu gestartet, Caches werden zurückgesetzt. Und Sie können die Last testen.

• Es kommt ein Verzeichnis an, in dem sich eine Menge verschiedener Dateien befinden, angefangen von Snapshots von pgstat***. Und das Interessanteste sind pg_stat_statements, pg_stat_kcacke. Dies sind zwei Erweiterungen, die Anfragen analysieren. Und pg_stat_bgwriter enthält nicht nur statistische Informationen über den pgwriter, sondern auch über Checkpoint und darüber, wie die Backends schmutzige Puffer verdrängen. Das ist alles interessant zu betrachten. Zum Beispiel ist es sehr interessant zu sehen, wie viel dort verdrängt wurde, wenn wir shared_buffers konfigurieren.
• Außerdem kommen die Protokolle von Postgres. Zwei Protokolle – das Vorbereitungsprotokoll und das Protokoll zur Ausführung der Last.
• Eine relativ neue Funktion sind die FlameGraphs.
• Wenn Sie pgreplay oder pgbench-Varianten zur Lastwiederholung verwendet haben, wird deren nativer Output bereitgestellt. Und Sie werden Latenz und TPS sehen können. Man kann verstehen, wie sie das gesehen haben.
• Informationen über das System.
• Grundlegende Überprüfungen von CPU und IO. Das ist mehr für EC2-Instanzen in Amazon, wenn Sie 100 identische Instanzen in einem Stream starten und dort jeweils 100 unterschiedliche Läufe machen möchten, haben Sie 10.000 Experimente. Und Sie müssen sicherstellen, dass Sie nicht auf eine fehlerhafte Instanz stoßen, die bereits von jemandem beeinträchtigt wird. Auf dieser Hardware werden andere aktiv, und Ihnen bleibt wenig Ressourcen. Solche Ergebnisse sollten besser verworfen werden. Genau mit sysbench von Alexey Kopytov führen wir einige kurze Überprüfungen durch, die ankommen und mit anderen verglichen werden können, d.h. Sie werden verstehen, wie sich CPU und IO verhalten.

Welche technischen Schwierigkeiten gibt es am Beispiel verschiedener Unternehmen?

Angenommen, wir möchten eine echte Last anhand von Protokollen wiederholen. Eine großartige Idee, wenn dies auf Open Source pgreplay geschrieben ist. Wir verwenden es. Aber damit es gut funktioniert, müssen Sie das vollständige Logging der Abfragen mit Parametern und Timing aktivieren.

Es gibt einige Schwierigkeiten bei der Dauer und dem Zeitstempel. Diese ganzen Komplikationen lassen wir beiseite. Die Hauptfrage ist, können Sie sich das leisten oder nicht?

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

Das Problem ist, dass dies möglicherweise nicht verfügbar ist. Sie müssen zunächst verstehen, welcher Datenstrom ins Protokoll geschrieben wird. Wenn Sie pg_stat_statements haben, können Sie mit dieser Anfrage (der Link wird in den Folien zur Verfügung stehen) schätzen, wie viele Bytes pro Sekunde geschrieben werden.

Wir betrachten die Länge der Anfrage. Wir ignorieren, dass dort keine Parameter vorhanden sind, aber wir kennen die Länge der Anfrage und wissen, wie oft sie pro Sekunde ausgeführt wurde. So können wir abschätzen, wie viele Bytes pro Sekunde ungefähr generiert werden. Wir können uns um den Faktor zwei irren, aber die Größenordnung werden wir auf diese Weise definitiv verstehen.

Wir können sehen, dass diese Anfrage 802 Mal pro Sekunde ausgeführt wird. Und wir sehen, dass bytes_per_sec – 300 kB/s ungefähr geschrieben werden. Und im Allgemeinen können wir uns einen solchen Datenstrom leisten.

Aber! Das Ding ist, dass es verschiedene Protokollierungssysteme gibt. Und normalerweise haben die Leute standardmäßig „syslog“ eingestellt.

Und wenn Sie syslog haben, könnte es so aussehen. Wir nehmen pgbench, aktivieren die Protokollierung der Anfragen und schauen, was passiert.

Ohne Protokollierung – das ist die Spalte links. Wir hatten 161.000 TPS. Mit syslog – das ist in Ubuntu 16.04 in Amazon, und wir erreichen 37.000 TPS. Wenn wir auf zwei andere Protokollierungsarten umschalten, ist die Situation viel besser. Das heißt, wir haben erwartet, dass es schlechter wird, aber nicht so sehr.

Und auf CentOS 7, wo auch journald beteiligt ist, der Logs in ein binäres Format für eine bessere Suche umwandelt, ist es ein völliger Albtraum, wir sinken um das 44-fache bei TPS.

Und das ist etwas, mit dem die Menschen leben müssen. Und oft in Unternehmen, besonders in großen, ist es sehr schwierig zu ändern. Wenn Sie von syslog wegkommen können, dann tun Sie es bitte.

• Bewerten Sie IOPS und den Schreibstrom.
• Überprüfen Sie Ihr Protokollierungssystem.
• Wenn die vorhergesagte Last übermäßig hoch ist, ziehen Sie eine Sampling-Option in Betracht.

Wir haben pg_stat_statements. Wie gesagt, er muss unbedingt vorhanden sein. Und wir können jede Anfragegruppe auf eine spezielle Art und Weise in einer Datei beschreiben. Und dann können wir eine sehr nützliche Funktion in pgbench nutzen – die Möglichkeit, mehrere Dateien mit der Option „-f“ einzufügen.

Er versteht viel von „-f“. Und man kann am Ende mit „@“ sagen, welcher Anteil für jede Datei gelten soll. Das heißt, wir können sagen, dass dies in 10 % der Fälle ausgeführt werden soll, und dieses in 20 %. Und das wird uns dem näher bringen, was wir in der Produktion sehen.

Wie erkennen wir, was wir in der Produktion haben? Welcher Anteil und was genau? Hier gehen wir ein wenig vom Thema ab. Wir haben ein weiteres Produkt. postgres-checkup. Es ist auch eine Basis in Open Source. Und wir entwickeln es jetzt aktiv weiter.

Es entstand aus einem anderen Grund. Aufgrund der unzureichenden Überwachung. Das heißt, Sie kommen, schauen auf die Basis, betrachten die Probleme, die vorhanden sind. Und normalerweise führen Sie einen health_check durch. Wenn Sie ein erfahrener DBA sind, führen Sie einen health_check durch. Sie betrachten die Nutzung der Indizes usw. Wenn Sie OKmeter haben, ist es großartig. Es ist eine tolle Überwachung für Postgres. OKmeter.io – bitte installieren Sie es, alles ist dort sehr gut gemacht. Es ist kostenpflichtig.

Wenn Sie es nicht haben, haben Sie normalerweise wenig. In der Überwachung gibt es normalerweise CPU, IO und das mit Vorbehalten, und das war’s. Aber wir brauchen mehr. Wir müssen sehen, wie der Autovacuum funktioniert, wie der Checkpoint funktioniert, im IO müssen wir den Checkpoint vom bgwriter und von den Backends trennen usw.

Das Problem ist, wenn Sie einem großen Unternehmen helfen, können sie etwas nicht schnell implementieren. Sie können OKmeter nicht schnell kaufen. Vielleicht kaufen sie es in sechs Monaten. Sie können keine Pakete schnell installieren.

Wir hatten die Idee, dass wir ein spezielles Tool benötigen, das keine Installation erfordert, das heißt, Sie müssen auf der Produktion überhaupt nichts installieren. Sie installieren es auf Ihrem Laptop oder auf einem Observationsserver, von wo aus Sie starten werden. Und es wird viele Dinge analysieren: das Betriebssystem, das Dateisystem und Postgres selbst, und dabei einige leichte Abfragen durchführen, die Sie direkt in der Produktion ausführen können, ohne dass etwas ausfällt.

Wir haben es Postgres-checkup genannt. Wenn es medizinisch betrachtet wird, ist es eine regelmäßige Gesundheitsprüfung. Im automobilen Bereich entspricht es der Inspektion. Sie machen alle sechs Monate oder jährlich eine Inspektion, je nach Marke. Machen Sie auch eine Inspektion für Ihre Datenbank? Das heißt, führen Sie regelmäßig eine tiefgehende Untersuchung durch? Das sollte man machen. Wenn Sie Backups machen, dann machen Sie auch das Checkup, das ist nicht weniger wichtig.

Und wir haben ein solches Tool. Es begann vor etwa drei Monaten aktiv zu entstehen. Es ist noch jung, aber es gibt schon viele Funktionen.

Wir sammeln die „einflussreichsten“ Abfragegruppen – Bericht K003 in Postgres-checkup

Und dort gibt es eine Gruppe von Berichten K. Bis jetzt gibt es drei Berichte. Und es gibt diesen Bericht K003. Dort ist die Spitze von pg_stat_statements, sortiert nach total_time.

Wenn wir die Abfragegruppen nach total_time sortieren, sehen wir an der Spitze eine Gruppe, die unser System am meisten belastet, d. h. die am meisten Ressourcen verbraucht. Warum nenne ich sie Abfragegruppen? Weil wir die Parameter weggelassen haben. Das sind keine Abfragen mehr, sondern Gruppen von Abfragen, d. h. sie sind abstrahiert.

Und wenn wir von oben nach unten optimieren, werden wir unsere Ressourcen entlasten und den Moment aufschieben, an dem ein Upgrade notwendig wird. Das ist eine sehr gute Möglichkeit, Geld zu sparen.

Vielleicht ist es nicht die beste Methode, um sich um die Nutzer zu kümmern, da wir möglicherweise seltene, aber sehr ärgerliche Fälle übersehen, in denen jemand 15 Sekunden gewartet hat. Insgesamt sind die so selten, dass wir sie nicht sehen, aber wir kümmern uns um die Ressourcen.

Was ist in dieser Tabelle passiert? Wir haben zwei Snapshots gemacht. Postgres_checkup zeigt Ihnen die Delta-Werte für jede Metrik: total-time, calls, rows, shared_blks_read usw. Alles, Delta wurde berechnet. Ein großes Problem bei pg_stat_statements ist, dass es sich nicht erinnert, wann es zurückgesetzt wurde. Während pg_stat_database sich erinnert, tut pg_stat_statements dies nicht. Sie sehen dort die Zahl 1.000.000, aber wir wissen nicht, woher wir gezählt haben.

Hier wissen wir, dass wir zwei Snapshots haben. Wir wissen, dass das Delta in diesem Fall 56 Sekunden betrug. Ein sehr kurzer Zeitraum. Nach total_time sortiert. Und dann können wir differenzieren, das heißt, wir teilen alle Metriken durch die Dauer. Wenn wir jede Metrik durch die Dauer teilen, haben wir die Anzahl der Aufrufe pro Sekunde.

Weiter geht’s mit total_time pro Sekunde – das ist meine Lieblingsmetrik. Sie wird in Sekunden gemessen, pro Sekunde, d. h. wie viele Sekunden unser System benötigt hat, um diese Gruppe von Abfragen pro Sekunde auszuführen. Wenn Sie dort mehr als eine Sekunde pro Sekunde sehen, bedeutet das, dass mehr als einen Kern hätten bereitgestellt werden müssen. Das ist eine sehr gute Metrik. Sie können verstehen, dass dieser Kollege zum Beispiel mindestens drei Kerne benötigt.

Das ist unser Know-how, so etwas habe ich nirgendwo gesehen. Beachten Sie – das ist eine sehr einfache Sache – Sekunde für Sekunde. Manchmal, wenn Ihre CPU 100 % erreicht, sind das eine halbe Stunde pro Sekunde, d. h. Sie haben eine halbe Stunde nur mit diesen Abfragen verbracht.

Weiter sehen wir die Zeilen pro Sekunde. Wir wissen, wie viele Zeilen pro Sekunde zurückgegeben wurden.

Und auch das ist interessant. Wie viele shared_buffers wir pro Sekunde aus den shared_buffers gelesen haben. Hits waren bereits dort, und die Reihen haben wir aus dem Cache des Betriebssystems oder von der Festplatte genommen. Die erste Option ist schnell, die zweite könnte schnell sein, muss es aber nicht, das hängt von der Situation ab.

Die zweite Differenzierungsmethode – wir teilen die Anzahl der Anfragen in dieser Gruppe. In der zweiten Spalte haben Sie immer eine Anfrage geteilt durch die Anfrage. Und dann wird es interessant – wie viele Millisekunden in dieser Anfrage waren. Wir wissen, wie sich diese Anfrage im Durchschnitt verhält. 101 Millisekunden benötigten wir für jede Anfrage. Das ist eine traditionelle Metrik, die wir für unser Verständnis benötigen.

Wie viele Zeilen jede Anfrage im Durchschnitt zurückgab. Wir sehen, dass diese Gruppe 8 zurückgibt. Wie viele im Durchschnitt aus dem Cache abgefragt und gelesen wurden. Wir sehen, dass alles großartig im Cache gespeichert ist. Nur Hits für die erste Gruppe.

Die vierte Zeile in jeder Zeile – das sind wie viele Prozent der Gesamtanzahl. Wir haben insgesamt Aufrufe. Nehmen wir an, 1.000.000. Und wir können verstehen, welchen Beitrag diese Gruppe leistet. Wir sehen, dass in diesem Fall die erste Gruppe weniger als 0,01 % beiträgt. Das bedeutet, sie ist so langsam, dass wir sie im Gesamtbild nicht sehen. Die zweite Gruppe hingegen – 5 % der Aufrufe. Das bedeutet, 5 % aller Aufrufe kommen von der zweiten Gruppe.

Bei total_time ist es auch interessant. Für die erste Gruppe von Anfragen haben wir 14 % der gesamten Arbeitszeit aufgewendet. Für die zweite Gruppe 11 % usw.

Ich werde nicht ins Detail gehen, aber es gibt Feinheiten. Wir zeigen oben einen Fehler an, weil, wenn wir vergleichen, die Snapshots abweichen können, d. h. einige Anfragen können ausfallen und im zweiten Snapshot nicht vorhanden sein, während einige neue auftauchen können. Und wir berechnen dort den Fehler. Wenn Sie 0 sehen, ist das gut. Das bedeutet, es gibt keine Fehler. Wenn der Fehlerindikator bis zu 20 % beträgt, ist das in Ordnung.

Dann kehren wir zu unserem Thema zurück. Wir müssen die Arbeitslast zusammenschnüren. Wir gehen von oben nach unten, bis wir 80 % oder 90 % erreicht haben. Normalerweise sind das 10-20 Gruppen. Und wir erstellen Dateien für pgbench. Dort verwenden wir random. Manchmal klappt das leider nicht. Und in Postgres 12 wird es mehr Möglichkeiten geben, diesen Ansatz zu nutzen.

Und so erreichen wir 80-90 % der gesamten Zeit. Was sollten wir nach dem @ einsetzen? Wir schauen uns die Anrufe an, sehen uns an, wie viele Prozentsätze es sind und verstehen, dass wir hier einen bestimmten Prozentsatz erreichen müssen. Aus diesen Prozentsätzen können wir verstehen, wie wir jede einzelne Datei ausbalancieren. Danach verwenden wir pgbench und fangen an zu arbeiten.

Wir haben auch K001 und K002.

K001 ist eine große Zeile mit vier Unterzeilen. Dies beschreibt die gesamte Last. Schaut euch die zweite Spalte und die zweite Unterzeile an. Wir sehen, dass es etwa anderthalb Sekunden pro Sekunde sind, d. h. wenn es zwei Kerne gibt, wäre das gut. Die Auslastung läge dann bei etwa 75 %. Und so wird es funktionieren. Wenn wir 10 Kerne haben, sind wir vollkommen in Ordnung. So können wir die Ressourcen einschätzen.

K002 bezeichne ich als Klassen von Anfragen, d. h. SELECT, INSERT, UPDATE, DELETE. Und separat SELECT FOR UPDATE, weil dieser sperrt.

Hier können wir feststellen, dass die gewöhnlichen SELECT-Abfragen – 82 % aller Aufrufe ausmachen, aber dabei – 74 % der Gesamtzeit. D. h. sie werden häufig aufgerufen, verbrauchen aber weniger Ressourcen.

Und zurück zu der Frage: „Wie wählen wir die richtigen shared_buffers aus?“. Ich stelle fest, dass die meisten Benchmarks auf der Idee basieren – lasst uns schauen, was die Durchsatzrate sein wird, also welche Bandbreite wir erreichen können. Diese wird üblicherweise in TPS oder QPS gemessen.

Und wir versuchen, mit den Parametern des Tuning so viele Transaktionen pro Sekunde wie möglich aus der Maschine herauszuholen. Hier sind es 311 pro Sekunde für SELECT.

Aber niemand fährt mit dem Auto mit Vollgas zur Arbeit und zurück nach Hause. Das ist dumm. Ebenso ist es mit Datenbanken. Wir sollten nicht mit Vollgas fahren, und das tut auch niemand. Niemand lebt in einer Produktion, die 100 % CPU-Auslastung hat. Obwohl vielleicht jemand das tut, ist das nicht gut.

Die Idee ist, dass wir normalerweise bei etwa 20 % unserer Möglichkeiten fahren, und es ist wünschenswert, dass wir nicht über 50 % hinausgehen. Und wir versuchen, die Antwortzeiten vor allem für unsere Nutzer zu optimieren. D. h., wir müssen unsere Handlungen so gestalten, dass die Latenz bei hypothetisch 20 % Geschwindigkeit minimal ist. Das ist die Idee, die wir auch in unseren Experimenten zu nutzen versuchen.

Und schließlich die Empfehlungen:

• Stellt unbedingt eine Datenbankumgebung bereit.
• Wenn möglich, macht es on demand, sodass es für eine gewisse Zeit bereitgestellt wird – gespielt und dann wieder entfernt. Wenn ihr Clouds habt, ist das selbstverständlich, d. h. handelt damit.
• Seien Sie neugierig. Und wenn etwas nicht stimmt, testen Sie durch Experimente, wie es sich verhält. Nancy kann verwendet werden, um sich selbst zu schulen, um zu überprüfen, wie die Datenbank funktioniert.
• Und zielen Sie auf die minimale Reaktionszeit.
• Haben Sie keine Angst vor den Postgres-Quellcodes. Wenn Sie mit den Quellcodes arbeiten, sollten Sie Englisch kennen. Da gibt es sehr viele Kommentare, die alles erklären.
• Und überprüfen Sie regelmäßig die Gesundheit der Datenbank, mindestens einmal alle drei Monate von Hand oder mit Postgres-checkup.

Fragen

Vielen Dank! Sehr interessantes Thema.

Zwei Sachen.

Ja, zwei Sachen. Nur ich habe das nicht ganz verstanden. Wenn wir mit Nancy arbeiten, können wir nur einen Parameter anpassen oder eine ganze Gruppe?

Wir haben einen Delta-Config-Parameter. Sie können dort beliebig viele gleichzeitig anpassen. Aber Sie müssen verstehen, dass wenn Sie vieles ändern, Sie falsche Schlussfolgerungen ziehen könnten.

Ja. Warum habe ich gefragt? Weil es schwierig ist, Experimente durchzuführen, wenn man nur einen Parameter hat. Man stellt ihn ein, sieht, wie er funktioniert. Dann optimalisiert man den nächsten.

Man kann mehrere gleichzeitig anpassen, aber das hängt natürlich von der Situation ab. Aber es ist besser, eine Idee zu testen. Wir hatten gestern eine Idee. Wir hatten eine sehr ähnliche Situation. Es gab zwei Konfigurationen. Und wir konnten nicht verstehen, warum es so große Unterschiede gab. Und die Idee entstand, dass wir Dichotomie verwenden müssen, um schrittweise zu verstehen und herauszufinden, wo der Unterschied liegt. Man kann sofort die Hälfte der Parameter gleich machen, dann ein Viertel usw. Alles flexibel.

Und ich habe noch eine Frage. Das Projekt ist jung, es entwickelt sich. Ist die Dokumentation schon fertig, gibt es eine ausführliche Beschreibung?

Ich habe dort absichtlich einen Link zur Beschreibung der Parameter gesetzt. Das gibt es. Aber vieles gibt es noch nicht. Ich suche Gleichgesinnte. Und ich finde sie, wenn ich spreche. Das ist echt klasse. Jemand arbeitet bereits mit mir, jemand hat geholfen und etwas gemacht. Und wenn Ihnen dieses Thema interessiert, geben Sie bitte Feedback – was fehlt.

Wenn wir das Labor machen, könnte es Rückmeldungen geben. Mal schauen. Danke!

Hallo! Vielen Dank für den Bericht! Ich habe gesehen, dass es Unterstützung für Amazon gibt. Ist Unterstützung für GSP geplant?

Gute Frage. Wir haben damit begonnen. Und vorerst eingefroren, weil wir sparen wollen. D. h. es gibt Unterstützung durch Run on localhost. Sie können selbst ein Instance erstellen und lokal arbeiten. Übrigens, so machen wir es. In Getlab mache ich das so, dort auf GSP. Aber genau diese Orchestrierung sehen wir derzeit keinen Sinn, da Google keine günstigen Spot-Instances hat. Es gibt ??? Instances, aber die haben Einschränkungen. Erstens gibt es immer nur 70 % Rabatt und man kann den Preis nicht anpassen. Bei Spot-Instances erhöhen wir den Preis um 5-10 %, um die Wahrscheinlichkeit zu verringern, dass sie gekillt werden. D. h. bei Spot-Instances sparen Sie, aber sie können jederzeit weggenommen werden. Wenn Sie den Preis etwas höher ansetzen als bei anderen, werden Sie später gekillt. Google hat eine ganz andere Spezifik. Und es gibt noch eine sehr unangenehme Einschränkung – sie leben nur 24 Stunden. Manchmal wollen wir jedoch ein Experiment über 5 Tage laufen lassen. Bei Spot-Instances kann man das machen, diese leben manchmal monatelang.

Hallo! Vielen Dank für den Vortrag! Sie haben den Checkup erwähnt. Wie berechnen Sie die Fehler in stat_statements?

Sehr gute Frage. Ich kann das sehr detailliert zeigen und erklären. Kurz gesagt - wir schauen, wie sich eine Gruppe von Anfragen verhalten hat: wie viele weggefallen sind und wie viele neu hinzugekommen sind. Und dann betrachten wir zwei Metriken: total_time und calls, daher gibt es zwei Fehler. Und wir sehen uns an, welchen Beitrag die betroffenen Gruppen geleistet haben. Es gibt zwei Untergruppen: die weggefahrenen und die neu angekommenen. Wir schauen, wie viel Einfluss sie auf das Gesamtbild haben.

Haben Sie keine Angst, dass es zwischen den Snapshots zwei- oder dreimal durchgeführt wird?

D. h. haben sie sich neu registriert oder wie?

Zum Beispiel wurde diese Anfrage einmal bereits verdrängt, dann kam sie wieder und wurde erneut verdrängt, dann kam sie noch einmal und wurde wieder verdrängt. Und was haben Sie hier gezählt, wo ist das alles?

Gute Frage, das müssen wir uns anschauen.

Ich habe eine ähnliche Sache gemacht. Natürlich einfacher, ich habe es alleine gemacht. Aber ich musste zurücksetzen, stat_statements zurücksetzen und mich im Moment des Snapshots orientieren, dass dort weniger als ein bestimmter Anteil ist, dass es trotzdem nicht an die Obergrenze von stat_statements gekommen ist. Und ich orientiere mich daran, dass wahrscheinlich nichts verdrängt wurde.

Ja, ja.

Aber ich verstehe nicht, wie man es anders verlässlich machen kann.

Leider erinnere ich mich nicht genau – verwenden wir dort den Anfrage-Text oder den queryid aus pg_stat_statements und orientieren uns daran. Wenn wir uns auf queryid orientieren, vergleichen wir beispielsweise vergleichbare Dinge.

Nein, er kann sich zwischen den Snapshots mehrmals überschreiben und wieder kommen.

Mit dieser ID?

Ja.

Wir werden das untersuchen. Gute Frage. Wir müssen es studieren. Aber bisher sehen wir nur, dass bei uns entweder 0 angezeigt wird...

Das ist natürlich ein seltener Fall, aber ich war erschrocken, als ich erfuhr, dass stat_statements dort überschrieben werden kann.

In Pg_stat_statements kann es viel geben. Wir haben festgestellt, dass, wenn Sie track_utility = on, auch Ihre Sets verfolgt werden.

Ja, natürlich.

Und wenn Sie Java Hibernate haben, das zufällig ist, dann beginnt die Hash-Tabelle zu blockieren. Und sobald Sie eine stark belastete Anwendung ausschalten, haben Sie 50-100 Gruppen. Und dort ist alles mehr oder weniger stabil. Ein Weg, dem entgegenzuwirken, ist, pg_stat_statements.max zu erhöhen.

Ja, aber man muss wissen, wie viel. Und man muss es irgendwie im Auge behalten. So mache ich es. Das heißt, ich habe pg_stat_statements.max. Und ich schaue, dass ich zum Zeitpunkt des Snapshots nicht 70 % erreicht habe. Gut, das heißt, wir haben nichts verloren. Wir machen einen Reset. Und sammeln es erneut. Wenn es im nächsten Snapshot weniger als 70 ist, dann haben wir wahrscheinlich wieder nichts verloren.

Ja. Standardmäßig sind es jetzt 5.000. Und vielen reicht das aus.

Normalerweise – ja.

Video:

P.S. Ich füge hinzu, dass, wenn sich vertrauliche Daten in Postgres befinden und diese nicht in die Testumgebung gelangen dürfen, man Folgendes verwenden kann: PostgreSQL Anonymizer. Das Schema ist ungefähr wie folgt:

Quelle: habr.com