Massenoptimierung von PostgreSQL-Anfragen. Kirill Borovikov (Tensor)

Der Bericht präsentiert einige Ansätze, die es ermöglichen, die Leistung von SQL-Abfragen zu überwachen, wenn es täglich Millionen sind,, und die kontrollierten PostgreSQL-Server – Hunderte.

Welche technischen Lösungen ermöglichen es uns, solche Mengen an Informationen effizient zu verarbeiten, und wie erleichtert dies das Leben eines gewöhnlichen Entwicklers?

Wer interessiert sich für die Analyse spezifischer Probleme und verschiedene Optimierungstechniken von SQL-Abfragen sowie die Lösung typischer DBA-Aufgaben in PostgreSQL – kann sich auch mit einer Artikelreihe vertraut machen. zu diesem Thema.


Mein Name ist Kirill Borovikov, ich vertrete das Unternehmen «Tensor».Konkret spezialisiere ich mich auf die Arbeit mit Datenbanken in unserem Unternehmen.

Heute werde ich Ihnen erzählen, wie wir die Abfragen optimieren, wenn Sie nicht die Leistung einer einzelnen Abfrage verbessern müssen, sondern ein Problem in großem Maßstab lösen möchten. Wenn es Millionen von Abfragen gibt und Sie nach Ansätzen zur Lösung dieses großen Problems suchen.

Im Allgemeinen ist «Tensor» für eine Million unserer Kunden SBIS – unsere Anwendung:ein soziales Unternehmensnetzwerk, Lösungen für Videoanrufe, interne und externe Dokumentenarbeit, Buchhaltungssysteme und Lagerverwaltung,… Das heißt, ein «Mega-Toolkit» für das umfassende Management von Unternehmen, in dem es mehr als 100 verschiedene interne Projekte gibt.

Damit all diese Projekte reibungslos funktionieren und sich entwickeln – haben wir 10 Entwicklungszentren im ganzen Land, in denen mehr als 1000 Entwickler.

arbeiten. Wir arbeiten seit 2008 mit PostgreSQL und haben eine große Menge an Daten gesammelt, die wir verarbeiten – das sind Kundendaten, statistische, analytische und Daten aus externen Informationssystemen – über 400 TB.Allein in «Production» gibt es etwa 250 Server, und insgesamt überwachen wir etwa 1000 DB-Server.

SQL ist eine deklarative Sprache. Sie beschreiben nicht «wie» etwas funktionieren soll, sondern «was» Sie erhalten möchten. Die DBMS kennt sich besser aus, wie man JOIN macht – wie man Ihre Tabellen verbindet, welche Bedingungen anzuwenden sind, was über den Index geht und was nicht,…

Einige DBMS akzeptieren Hinweise: «Nein, diese beiden Tabellen sollen in dieser Reihenfolge verbunden werden», aber PostgreSQL kann das nicht. Das ist eine bewusste Position der Hauptentwickler: «Es ist besser, den Abfrage-Optimierer zu verbessern, als den Entwicklern zu erlauben, irgendwelche Hinweise zu verwenden.»

Aber trotz der Tatsache, dass PostgreSQL sich nicht „von außen“ steuern lässt, ermöglicht es hervorragend, zu sehen, was „innen“ bei ihm passiert,, wenn Sie Ihre Abfrage ausführen, und wo es Probleme gibt.

Welche klassischen Probleme bringt ein Entwickler normalerweise [zum DBA]? „Hier, wir haben die Abfrage ausgeführt, und es ist alles langsam,, alles hängt, irgendetwas passiert… Es ist ein großes Problem!“

Die Gründe sind fast immer die gleichen:

• ein ineffizienter Abfragealgorithmus.
  Entwickler: „Ich habe ihm gerade 10 Tabellen über JOIN in SQL gegeben…“ – und erwartet, dass sich seine Bedingungen auf wunderbare Weise effizient „entwirren“, und er alles schnell erhält. Aber Wunder gibt es nicht, und jedes System gibt bei solcher Variantenvielfalt (10 Tabellen in einem FROM) immer eine gewisse Ungenauigkeit. [Artikel]
• nicht aktuelle Statistiken.
  Dieser Punkt ist besonders relevant für PostgreSQL, wenn Sie einen großen Datensatz auf den Server „geladen“ haben, eine Abfrage machen – und er „Scan“ durch die Tabelle macht. Denn gestern waren dort 10 Datensätze, und heute 10 Millionen, aber PostgreSQL weiß noch nichts davon, und man muss ihm das mitteilen. [Artikel]
• ein Ressourcenengpass.
  Sie haben eine große, schwer belastete Datenbank auf einen schwachen Server gestellt, der nicht über genügend Speicherplatz, RAM oder CPU-Leistung verfügt. Und das war's… Irgendwo gibt es eine Leistungsgrenze, über die Sie nicht hinauskommen können.
• von Sperren
  Ein komplizierter Punkt, aber er ist am relevantesten für verschiedene modifizierende Abfragen (INSERT, UPDATE, DELETE) – das ist ein eigenes großes Thema.

Den Plan abrufen.

… Und für alles andere brauchen wir einen Plan!Wir müssen sehen, was im Server passiert.

Der Ausführungsplan einer Abfrage für PostgreSQL ist ein Baum des Abfrageausführungsalgorithmus in textlicher Darstellung. Es ist genau der Algorithmus, der nach Analyse durch den Planner als der effizienteste erkannt wurde.

Jeder Knoten des Baumes ist eine Operation: Daten aus einer Tabelle oder einem Index abrufen, eine Bitmaske erstellen, zwei Tabellen verknüpfen, vereinigen, schneiden oder Auswahl ausschließen. Die Ausführung der Abfrage erfolgt über die Knoten dieses Baumes.

Um den Abfrageplan zu erhalten, ist der einfachste Weg, den Operator auszuführen EXPLAIN.Um ihn mit allen tatsächlichen Attributen zu erhalten, das heißt, um die Abfrage tatsächlich auf der Datenbank auszuführen – EXPLAIN (ANALYZE, BUFFERS) SELECT ....

Schlechter Moment: Wenn Sie ihn ausführen, geschieht dies „hier und jetzt“, deshalb ist es nur für lokale Debugging geeignet. Wenn Sie jedoch einen stark belasteten Server haben, der starkem Datenänderungsfluss unterliegt, und sehen: „Autsch! Hier kam unsere Anfrage langsam vorbei.sich eine Anfrage.“ Vor eineinhalb Stunden — während Sie herumgerannt sind, diese Anfrage aus den Logs geholt haben, sie erneut zum Server gebracht haben, hat sich Ihr gesamter Datensatz und die Statistik geändert. Sie führen sie aus, um zu debuggen — und sie wird schnell ausgeführt! Und Sie können nicht verstehen, „warum“, warum damit langsam.

Um zu verstehen, was genau in dem Moment passiert ist, als die Anfrage auf dem Server ausgeführt wird, haben kluge Leute das Modul auto_explain. Es ist in praktisch allen gängigen Distributionen von PostgreSQL vorhanden und kann einfach in der Konfigurationsdatei aktiviert werden.

Wenn es versteht, dass eine Anfrage länger als die von Ihnen angegebene Grenze benötigt, macht es einen „Schnappschuss“ des Plans dieser Anfrage und schreibt ihn zusammen in das Log..

Es scheint alles gut zu sein, wir gehen ins Log und sehen dort… [portянка текста]. Aber wir können nichts darüber sagen, außer der Tatsache, dass es ein hervorragender Plan ist, weil er in 11 ms ausgeführt wurde.

Es scheint alles gut zu sein — aber nichts ist klar, was wirklich passiert ist. Abgesehen von der Gesamtzeit sehen wir nicht viel. Denn auf so eine „Latex“-Plain-Text-Darstellung zu schauen, ist ja überhaupt nicht anschaulich.

Aber selbst wenn es nicht anschaulich ist, und unbequem, gibt es bei weitem gravierendere Probleme:

• Im Knoten wird die Summe der Ressourcen des gesamten Unterbaums unter ihm angegeben. Das heißt, es ist einfach unmöglich zu erfahren, wie viel Zeit konkret bei diesem Index-Scan verbraucht wurde — wenn darunter eine Art von verschachtelten Bedingungen steht. Wir müssen dynamisch überprüfen, ob es innen „Kinder“ und bedingte Variablen, CTE — gibt und das alles „im Kopf“ abziehen.
• Zweiter Punkt: Die Zeit, die im Knoten angegeben ist, ist die Zeit der einmaligen Ausführung des Knotens.Wenn dieser Knoten zum Beispiel das Ergebnis eines Schleifen über die Datensätze der Tabelle ist und mehrfach ausgeführt wurde, erhöht sich die Anzahl der Schleifen — Zyklen dieses Knotens im Plan. Aber die eigentliche atomare Ausführungszeit bleibt im Plan unverändert. Das heißt, um zu verstehen, wie lange dieser Knoten insgesamt ausgeführt wurde, muss man das eine mit dem anderen multiplizieren — wiederum „im Kopf“.

Bei solchen Gegebenheiten ist es praktisch unmöglich zu verstehen, „Wer ist die schwächste Glied?“ Deshalb schreiben selbst die Entwickler im „Handbuch“, dass „Das Verständnis des Plans ist eine Kunst, die man erlernen muss, Erfahrung…“.

Aber wir haben 1000 Entwickler, und diese Erfahrung kann man nicht einfach so vermitteln. Ich, du, er — wissen es, aber jemand dort drüben — bereits nicht. Vielleicht lernt er es, vielleicht nicht, aber arbeiten muss er jetzt schon — woher soll er diese Erfahrung nehmen.

Visualisierung des Plans

Deshalb haben wir verstanden — um mit diesen Problemen umzugehen, brauchen wir eine gute Visualisierung des Plans. [статья]

Wir haben zuerst „den Markt“ erkundet — lass uns im Internet suchen, was überhaupt existiert.

Aber es stellte sich heraus, dass es relativ wenige „lebendige“ Lösungen gibt, die mehr oder weniger weiterentwickelt werden — nur eine: explain.depesz.com von Hubert Lubaczewski. Du gibst einen textuellen Plan in das Feld ein, und er zeigt dir eine Tabelle mit den aufgeschlüsselten Daten:

• die eigene Verarbeitungszeit des Knotens
• die gesamte Zeit im gesamten Subbaum
• die Anzahl der extrahierten Datensätze und die statistisch erwartete
• der eigentliche Inhalt des Knotens

Außerdem hat dieser Service die Möglichkeit, einen Link-Archiv zu teilen. Du hast deinen Plan dort hochgeladen und sagst: „Hey, Vasya, hier ist der Link, da stimmt etwas nicht.“

Aber es gibt auch einige kleine Probleme.

Erstens eine riesige Menge an „Copy-Paste“. Du nimmst einen Teil des Logs, steckst ihn rein, und wieder und wieder.

Zweitens, es gibt keine Analyse der gelesenen Datenmengen — genau der Buffers, die ausgegeben werden von EXPLAIN (ANALYZE, BUFFERS), hier sehen wir nichts. Er kann sie einfach nicht analysieren, verstehen und damit arbeiten. Wenn du viele Daten liest und verstehst, dass du es falsch „auf die Festplatte und den Cache im RAM verteilen“ könntest, ist diese Information sehr wichtig.

Ein dritter negativer Punkt — die sehr schwache Entwicklung dieses Projekts. Die Commits sind sehr klein, gut, wenn einmal im halben Jahr, und der Code ist in Perl.

Aber das sind alles „Lyrik“, damit könnte man irgendwie leben, aber es gibt eine Sache, die uns von diesem Dienst stark abgewandt hat. Es sind die Analysefehler der Common Table Expressions (CTE) und verschiedener dynamischer Knoten wie InitPlan/SubPlan.

Wenn man diesem Bild glauben kann, dann ist die Gesamtzeit für die Ausführung jedes einzelnen Knotens höher als die Gesamtzeit für die Ausführung der gesamten Abfrage. Ganz einfach — aus dem Knoten CTE Scan wurde die Zeit für die Generierung dieses CTE nicht abgezogen.. Daher wissen wir mittlerweile nicht mehr, wie lange das CTE-Scannen selbst gedauert hat.

Hier haben wir verstanden, dass es Zeit ist, etwas Eigenes zu schreiben – hurra! Jeder Entwickler sagt: „Jetzt schreiben wir unser eigenes, das wird super einfach!“

Wir haben einen typischen Stack für Web-Services genommen: Kern auf Node.js + Express, Bootstrap integriert und für die schönen Diagramme – D3.js. Unsere Erwartungen wurden vollends erfüllt – den ersten Prototyp haben wir in 2 Wochen erhalten:

• einen eigenen Parser für den Plan
  Das bedeutet, dass wir jetzt jeden Plan analysieren können, den PostgreSQL generiert.
• eine korrekte Analyse der dynamischen Knoten – CTE Scan, InitPlan, SubPlan
• Analyse der Pufferverteilung – wo die Datenseiten aus dem Speicher gelesen werden, wo aus dem lokalen Cache, wo von der Festplatte
• wir haben Anschaulichkeit erhalten
  Um nicht all dies im Log nachzuvollziehen, sondern sofort das „schwächste Glied“ direkt im Bild zu sehen.

Wir haben ungefähr so ein Bild erhalten – direkt mit Syntax-Hervorhebung. Aber normalerweise arbeiten unsere Entwickler nicht mehr mit dem vollständigen Plan, sondern mit einer kürzeren Version. Denn alle Zahlen haben wir bereits geparst und nach links und rechts geschoben, während wir in der Mitte nur die erste Zeile belassen haben, die zeigt, um welchen Knoten es sich handelt: CTE Scan, CTE-Generierung oder Seq Scan für eine bestimmte Tabelle.

Diese verkürzte Darstellung nennen wir Plan-Template.

Was wäre noch nützlich? Es wäre hilfreich zu sehen, welcher Anteil welcher Knoten von der Gesamtzeit abbekommt – und einfach „angeklebt“ an der Seite. Tortendiagramm.

Wir fahren mit dem Mauszeiger über den Knoten und sehen – tatsächlich hat der Seq Scan von der gesamten Zeit weniger als ein Viertel eingenommen, während die restlichen 3/4 durch den CTE Scan beansprucht wurden. Schrecklich! Das ist eine kleine Anmerkung zur „Schnelligkeit“ des CTE Scan, wenn Sie diese in Ihren Abfragen aktiv verwenden. Sie sind nicht sehr schnell – sie verlieren sogar gegen die gewöhnliche Tabellen-Scannierung. [статья] [статья]

Aber normalerweise sind solche Diagramme interessanter und komplexer, wenn wir sofort auf ein Segment zeigen und zum Beispiel sehen, dass mehr als die Hälfte der gesamten Zeit durch einen Seq Scan „gegessen“ wurde. Und dann gab es darin noch einen Filter, zahlreiche Datensätze wurden dadurch verworfen… Man kann dieses Bild direkt an den Entwickler schicken und sagen: „Wanja, hier läuft ganz schön etwas schief! Kümmere dich darum, schau nach – irgendetwas stimmt nicht!“

Natürlich gab es auch einige „Stolpersteine“.

Das erste, worauf wir „gestoßen“ sind, ist das Problem der Rundung. Die Zeit für jeden einzelnen Knoten im Plan wird bis auf 1µs genau angegeben. Und wenn die Anzahl der Knotenzyklen beispielsweise 1000 überschreitet – nach der Ausführung hat PostgreSQL auf „genau bis“ gerechnet, sodass wir beim Rückrechnen eine Gesamtzeit „irgendwo zwischen 0,95ms und 1,05ms“ erhalten. Wenn es um Mikrosekunden geht, ist das noch nichts, aber wenn es bereits um [milli]sekunden geht, müssen wir bei der „Entwirrung“ der Ressourcen nach Knoten im Plan „wer wie viel verbraucht hat“ diese Informationen berücksichtigen.

Der zweite Punkt, der komplexer ist, ist die Verteilung der Ressourcen (dieselben Buffers) auf die dynamischen Knoten. Das hat uns zu den ersten 2 Wochen im Prototyp zusätzlich etwa 4 Wochen gekostet.

Ein solches Problem ist relativ einfach zu erzeugen – wir erstellen ein CTE und lesen darin angeblich etwas. Tatsächlich ist PostgreSQL „intelligent“ und wird dort nichts direkt lesen. Anschließend nehmen wir den ersten Datensatz heraus und zu ihm den einhunderteinundzwanzigsten aus demselben CTE.

Wir schauen uns den Plan an und verstehen – seltsam, wir hatten 3 Buffers (Datenseiten), die im Seq Scan „verbraucht“ wurden, noch 1 im CTE Scan und noch 2 im zweiten CTE Scan. Wenn wir alles einfach addieren, erhalten wir insgesamt 6, aber aus der Tabelle haben wir nur 3 gelesen! Der CTE Scan liest schließlich nichts von irgendwoher, sondern arbeitet direkt im Prozessspeicher. Das heißt, hier stimmt eindeutig etwas nicht!

Tatsächlich stellt sich heraus, dass all die 3 Datenseiten, die im Seq Scan angefordert wurden, zuerst vom 1. CTE Scan angefordert wurden, und dann vom 2. CTE, und noch 2 wurden ihm mitgegeben. Das heißt, es wurden insgesamt 3 Datenseiten gelesen, nicht 6.

Und dieses Bild führte uns zu dem Verständnis, dass die Ausführung des Plans kein Baum mehr ist, sondern einfach ein acyklischer Graph. Und wir erhielten ungefähr ein solches Diagramm, damit wir verstehen, „was woher überhaupt kam“. Hier haben wir ein CTE aus pg_class erstellt, und haben es zweimal angefordert, und die ganze Zeit ging fast auf den Pfad, als wir es ein zweites Mal angefordert haben. Es ist klar, dass das Lesen des 101. Datensatzes viel teurer ist, als einfach den 1. aus der Tabelle zu lesen.

Wir atmeten für eine Weile auf. Wir sagten: „Jetzt, Neo, weißt du Kung-Fu! Jetzt ist unsere Erfahrung direkt auf deinem Bildschirm. Jetzt kannst du sie nutzen.“ [статья]

Konsolidierung von Logs

Unsere 1000 Entwickler atmeten erleichtert auf. Aber wir wussten, dass wir nur hunderte von "Produktiv"-Servern haben und all dieses "Copy-Paste" von Seiten der Entwickler keineswegs bequem ist. Wir erkannten, dass wir das selbst zusammenstellen mussten.

Es gibt tatsächlich ein Standardmodul, das Statistiken sammeln kann, allerdings muss es auch in der Konfiguration aktiviert werden — das ist das Modul pg_stat_statements. Aber es hat uns nicht überzeugt.

Erstens weist es denselben Abfragen in unterschiedlichen Schemata innerhalb einer einzigen Datenbank verschiedene QueryIdzu. Das bedeutet, wenn ich zuerst SET search_path = '01'; SELECT * FROM user LIMIT 1;, und dann SET search_path = '02'; und die gleiche Abfrage mache, zeigen die Statistiken dieses Moduls unterschiedliche Einträge, und ich kann keine umfassende Statistik genau in Bezug auf dieses Abfragemuster ohne Berücksichtigung der Schemata erstellen.

Ein weiterer Punkt, der uns daran hinderte, es zu verwenden — fehlende Pläne. Das heißt, es gibt keinen Plan — nur die Abfrage selbst. Wir sehen, was die Verlangsamung verursacht hat, aber wir verstehen nicht, warum. Und hier kommen wir zurück zum Problem eines sich schnell ändernden Datensatzes.

Und der letzte Punkt — Fehlen von "Fakten". Das heißt, man kann sich nicht auf eine bestimmte Ausführung der Abfrage beziehen — diese gibt es nicht, es gibt nur aggregierte Statistiken. Damit kann man zwar arbeiten, es ist jedoch sehr schwierig.

Deshalb beschlossen wir, gegen das "Copy-Paste" vorzugehen und begannen zu schreiben einen Collector.

Der Collector verbindet sich über SSH, stellt über ein Zertifikat eine gesicherte Verbindung zum Server mit der Datenbank her und tail -F bindet sich an die Log-Datei. So erhalten wir in dieser Sitzung ein vollständiges „Spiegelbild“ der gesamten Log-Datei, die der Server generiert. Die Belastung des Servers selbst ist dabei minimal, da wir nichts parsen, sondern nur den Verkehr spiegeln.

Da wir bereits begonnen hatten, die Schnittstelle in Node.js zu schreiben, setzten wir die Entwicklung des Collectors ebenfalls dort fort. Diese Technologie hat sich bewährt, da es sehr praktisch ist, JavaScript für die Arbeit mit schwach strukturierten Textdaten zu verwenden, wie sie im Log vorkommen. Und die Infrastruktur von Node.js als Backend-Plattform ermöglicht es, bequem mit Netzwerkverbindungen und allgemein mit Datenströmen zu arbeiten.

Dementsprechend "ziehen" wir zwei Verbindungen: die erste, um das Protokoll selbst "zuhören" zu können und es zu uns zu holen, und die zweite – um regelmäßig bei der Datenbank nachzufragen. "Und im Protokoll steht, dass die Tabelle mit oid 123 blockiert ist", aber das sagt dem Entwickler nichts, und es wäre nicht schlecht, die Datenbank zu fragen: „Was ist eigentlich OID = 123?“ So fragen wir regelmäßig bei der Datenbank nach, was wir selbst noch nicht wissen.

"Nur eine Sache hast du nicht bedacht, es gibt eine Art von elefantenähnlichen Bienen!..." Wir haben mit der Entwicklung dieses Systems begonnen, als wir 10 Server überwachen wollten. Die aus unserer Sicht kritischsten, auf denen Probleme auftraten, die schwierig zu handhaben waren. Aber im ersten Quartal erhielten wir bereits eine Hundertschaft zur Überwachung – weil das System "ankam", wollten es alle, es war für alle bequem.

All dies muss zusammengeführt werden, die Datenströme sind groß und aktiv. Im Grunde, was wir überwachen, mit dem wir gut umgehen können – das verwenden wir. Wir nutzen auch PostgreSQL als Datenspeicher. Es gibt nichts Schnelleres, um Daten hinein zu "gießen", als einen Operator. COPY bis jetzt gibt es nichts.

Aber einfach Daten "hinein zu gießen" – das ist nicht ganz unsere Technologie. Denn wenn auf hundert Servern etwa 50k Anfragen pro Sekunde stattfinden, generiert das Ihnen 100-150GB Protokolle pro Tag. Daher mussten wir die Datenbank sorgfältig "bearbeiten".

Erstens haben wir Tagesspartitionierung, denn niemand interessiert sich im Grunde für die Korrelation zwischen den Tagen. Was spielt es für eine Rolle, was du gestern hattest, wenn du heute Nacht eine neue Version der Anwendung ausgegeben hast – und schon gibt es eine neue Statistik.

Zweitens haben wir gelernt (wir mussten) sehr, sehr schnell mit Hilfe von COPYzu schreiben. Also nicht einfach COPY, denn er ist schneller als INSERT, sondern noch schneller.

Drittens mussten wir von Triggern und damit auch von Foreign Keysabsehen. Das bedeutet, dass wir keine referenzielle Integrität mehr haben. Denn wenn Sie eine Tabelle haben, auf der es ein paar FKs gibt, und Sie in der Datenbankstruktur sagen, dass "dieser Logeintrag sich auf eine Gruppe von Einträgen über FK bezieht", dann bleibt PostgreSQL nichts anderes übrig, als es ehrlich auszuführen. SELECT 1 FROM master_fk1_table WHERE ... mit der Identifikationsnummer, die Sie einzufügen versuchen – einfach um zu überprüfen, dass dieser Eintrag dort vorhanden ist, dass Sie diesen Foreign Key mit Ihrem Eintrag nicht "brechen".

Wir erhalten anstelle eines Eintrags in die Zieltabelle und dessen Indizes zusätzlich noch das Lesen aus allen Tabellen, auf die verwiesen wird. Und das brauchen wir überhaupt nicht – unser Ziel ist es, so viel wie möglich und so schnell wie möglich mit der geringsten Belastung zu speichern. Also, FK – adieu!

Der nächste Punkt – Aggregation und Hashing. Ursprünglich hatten wir dies in der DB implementiert – es ist schließlich praktisch, wenn ein Eintrag ankommt, gleich in einer bestimmten Tabelle ein "plus eins" direkt im Trigger. Gut, praktisch, aber schlecht, denn man fügt einen Eintrag hinzu und muss gezwungenermaßen noch etwas aus einer anderen Tabelle lesen und schreiben. Darüber hinaus ist es nicht nur notwendig zu lesen und zu schreiben – man muss es auch jedes Mal tun.

Stellen Sie sich nun vor, Sie haben eine Tabelle, in der Sie einfach die Anzahl der Anfragen zählen, die über einen bestimmten Host gelaufen sind: +1, +1, +1, ..., +1. Und das brauchen Sie prinzipiell nicht – das kann man alles im Speicher auf dem Collector zusammenfassen und alles auf einmal in die Datenbank senden +10.

Ja, im Falle von Problemen kann Ihre logische Integrität "zerfallen", aber das ist praktisch ein unrealistischer Fall – denn Sie haben einen normalen Server, eine Batterie im Controller, ein Transaktionsjournal, ein Journal im Dateisystem… Kurz gesagt, das ist es nicht wert. Der Leistungsverlust, den Sie durch die Verwendung von Triggern/FK erhalten, und die Kosten, die Sie dabei tragen, sind nicht gerechtfertigt.

Das Gleiche gilt für das Hashing. Ein gewisser Request kommt zu Ihnen, Sie berechnen in der DB einen bestimmten Identifikator, schreiben ihn in die Datenbank und geben ihn allen weiter. Alles gut, solange Ihnen nicht während des Schreibens ein weiterer Wunschkandidat begegnet – dann haben Sie eine Blockade, und das ist schon schlecht. Deshalb, wenn Sie die Generierung bestimmter IDs auf den Client (relativ zur Datenbank) auslagern können, ist es besser, dies zu tun.

Es hat sich einfach perfekt ergeben, MD5 vom Text – der Anfrage, dem Plan, der Vorlage,… zu verwenden. Wir berechnen es auf der Seite des Collectors und "gießen" dann die fertige ID in die Datenbank. Die MD5-Länge und die tägliche Partitionierung ermöglichen es uns, uns keine Gedanken über mögliche Kollisionen zu machen.

Um dies alles schnell zu speichern, mussten wir jedoch das eigentliche Schreibverfahren modifizieren.

Wie werden Daten normalerweise geschrieben? Wir haben einen bestimmten Datensatz, den wir auf mehrere Tabellen aufteilen, und dann COPY – zuerst in die erste, dann in die zweite, die dritte... Das ist unpraktisch, weil wir scheinbar einen Datenstrom in drei aufeinanderfolgende Schritte schreiben. Unangenehm. Kann man es schneller machen? Ja!

Dazu reicht es aus, diese Ströme parallel zueinander zu ordnen. Das bedeutet, dass wir Fehler, Anfragen, Vorlagen, Sperren... in separaten Strömen laufen lassen und alles parallel schreiben. Dafür reicht es aus den COPY-Kanal für jede einzelne Ziel-Tabelle ständig offen zu halten.

Das heißt, der Collector hat immer einen Stream, in den ich die benötigten Daten schreiben kann. Aber damit die Datenbank diese Daten sieht und niemand in einer Sperre hängt, während er wartet, dass diese Daten geschrieben werden, muss COPY mit einer bestimmten Häufigkeit unterbrochen werden. Für uns hat sich ein Intervall von etwa 100 ms als am effektivsten erwiesen – wir schließen und öffnen sofort wieder die Verbindung zur gleichen Tabelle. Und wenn wir bei bestimmten Spitzen nicht einen Stream haben, dann machen wir ein Pooling bis zu einer bestimmten Obergrenze.

Zusätzlich haben wir festgestellt, dass jede Aggregation für dieses Lastprofil, wenn Datensätze in Paketen gebündelt werden, schädlich ist. Das klassische Übel ist INSERT ... VALUES und dann 1000 Datensätze. Denn in diesem Moment tritt ein Schreibspitzenreiz auf dem Medium auf, und alle anderen, die versuchen, etwas auf die Festplatte zu schreiben, müssen warten.

Um solche Anomalien zu vermeiden, aggregieren Sie einfach nichts, puffern Sie überhaupt nicht. Und wenn doch eine Pufferung auf die Festplatte auftritt (zum Glück erlaubt das Stream API in Node.js, dies zu erkennen) – trennen Sie diese Verbindung. Sobald Sie das Ereignis erhalten, dass sie wieder frei ist – schreiben Sie daraus aus der angesammelten Warteschlange. Solange sie beschäftigt ist – nehmen Sie den nächsten freien aus dem Pool und schreiben Sie in diesen.

Vor der Implementierung dieses Ansatzes für das Schreiben von Daten hatten wir etwa 4K Schreiboperationen, und auf diese Weise haben wir die Last auf das Vierfache reduziert. Jetzt sind wir noch einmal um das Sechsfache gewachsen, dank neuer beobachtbarer Datenbanken – auf 100MB/s. Und jetzt speichern wir Logs für die letzten 3 Monate mit einem Volumen von etwa 10-15TB in der Hoffnung, dass in drei Monaten jeder Entwickler jedes Problem lösen kann.

Wir verstehen die Probleme

Aber all diese Daten zu sammeln – das ist gut, nützlich, passend, aber wenig – man muss sie verstehen. Denn es handelt sich um Millionen verschiedener Pläne pro Tag.

Aber Millionen sind unüberschaubar, man muss zuerst "weniger" machen. Und zuerst muss man entscheiden, wie man dieses "weniger" organisieren wird.

Wir haben uns drei Schlüsselpunkte herausgearbeitet:

• wer diese Anfrage gesendet hat
  Das heißt, aus welcher Anwendung sie „gekommen“ ist: Web-Oberfläche, Backend, Zahlungssystem oder etwas anderes.
• wo wann es passiert ist
  Auf welchem konkreten Server. Denn wenn Sie mehrere Server für eine Anwendung haben und plötzlich einer „stecken bleibt“ (weil „die Festplatte kaputt ist“, „der Speicher defekt ist“, oder ein anderes Problem), dann muss man sich konkret an den Server richten.
• als in welchem Plan genau das Problem aufgetreten ist

Um zu verstehen, „wer“ uns die Anfrage gesendet hat, verwenden wir das Standardmittel – die Festlegung einer Session-Variablen: SET application_name = '{bl-host}:{bl-method}'; – wir erfassen den Namen des Business-Logic-Hosts, von dem die Anfrage kommt, sowie den Namen der Methode oder Anwendung, die sie initiiert hat.

Nachdem wir den „Eigentümer“ der Anfrage übergeben haben, muss dieser in das Log geschrieben werden – dafür konfigurieren wir die Variable log_line_prefix = ' %m [%p:%v] [%d] %r %a'. Wer interessiert ist, kann im Handbuch nachsehen, was das alles bedeutet. Das bedeutet, dass wir im Log sehen:

• Zeit
• die Prozess- und Transaktions-IDs
• den Datenbanknamen
• die IP-Adresse desjenigen, der diese Anfrage gesendet hat
• und den Namen der Methode

Darüber hinaus haben wir verstanden, dass es nicht sehr interessant ist, die Korrelation einer einzigen Anfrage zwischen verschiedenen Servern zu betrachten. Es kommt nicht oft vor, dass eine Anwendung an beiden Stellen gleich „scheitert“. Aber selbst wenn sie es tut – schauen Sie sich einen dieser Server an.

Also, der Schnitt „ein Server – ein Tag“ war für jede Analyse ausreichend.

Der erste analytische Schnitt – das ist der sogenannte „Schnelltest“ – eine verkürzte Form der Darstellung des Plans, frei von allen quantitativen Angaben. Der zweite Schnitt – die Anwendung oder Methode, und der dritte – das spezifische Plan-Element, das uns Probleme bereitet hat.

Als wir von konkreten Instanzen zu Mustern übergingen, erhielten wir sofort zwei Vorteile:

• eine drastische Reduzierung der Anzahl der Objekte zur Analyse
  Man muss die Probleme nicht mehr anhand von Tausenden Anfragen oder Plänen, sondern durch Dutzende von Mustern untersuchen.
• Zeitachse
  Das heißt, wenn man die "Fakten" in einem bestimmten Kontext zusammenfasst, kann man deren Auftreten im Laufe des Tages darstellen. Und hier können Sie verstehen, dass, wenn Sie ein bestimmtes Muster haben, das zum Beispiel einmal pro Stunde auftritt, obwohl es einmal täglich sein sollte, es sinnvoll ist, darüber nachzudenken, was schiefgelaufen ist – wer und warum hat es ausgelöst, vielleicht sollte es hier gar nicht sein. Dies ist ein weiterer nicht-numerischer, rein visueller Analyseansatz.

Die anderen Methoden basieren auf den Kennzahlen, die wir aus dem Plan extrahieren: wie oft dieses Muster aufgetreten ist, die Gesamt- und Durchschnittszeit, wie viele Daten vom Datenträger gelesen wurden und wie viele aus dem Speicher…

Denn Sie kommen zum Beispiel auf die Analytics-Seite des Hosts, schauen – es scheint, dass zu viele Daten vom Datenträger gelesen wurden. Der Datenträger auf dem Server kann nicht mithalten – aber wer liest von ihm?

Und Sie können nach jeder Spalte sortieren und entscheiden, womit Sie sich gerade beschäftigen möchten – mit der CPU- oder Disklast, oder mit der Gesamtzahl der Anfragen… Sie haben sortiert, die „Top-Punkte“ angeschaut, repariert – eine neue Version der Anwendung ausgerollt.
[видеолекция]

Und sofort können Sie verschiedene Anwendungen sehen, die mit demselben Muster von Anfragen des Typs arbeiten SELECT * FROM users WHERE login = 'Vasya'. Frontend, Backend, Processing… Und Sie fragen sich, warum das Processing den Benutzer lesen sollte, wenn keine Interaktion mit ihm stattfindet.

Der umgekehrte Weg – sofort zu sehen, was die Anwendung macht. Zum Beispiel: Das Frontend – das, das, das hier, und zusätzlich das einmal pro Stunde (genau da hilft der Zeitstrahl). Und sofort taucht die Frage auf – es sollte eigentlich nicht die Aufgabe des Frontends sein, etwas einmal pro Stunde zu tun…

Nach einiger Zeit haben wir festgestellt, dass uns aggregierte Statistiken im Rahmen der Plan-Knoten fehlen.Wir haben aus den Plänen nur diejenigen Knoten herausgefiltert, die mit den Daten der Tabellen etwas tun (ob sie sie nach Index lesen/schreiben oder nicht). Im Wesentlichen wird im Vergleich zum vorherigen Bild nur ein Aspekt hinzugefügt – wie viele Datensätze dieser Knoten uns gebracht hat, und wie viele verworfen wurden (Rows Removed by Filter).

Sie haben keinen geeigneten Index auf der Tabelle, Sie führen eine Anfrage durch, sie ignoriert den Index und fällt in einen Seq Scan… Sie haben alle Datensätze außer einem herausgefiltert. Aber warum benötigen Sie am Tag 100M herausgefilterte Datensätze, ist es nicht besser, einen Index zu erstellen?

Nachdem wir alle Pläne für die Knoten durchgegangen sind, haben wir festgestellt, dass es bestimmte standardisierte Strukturen in den Plänen gibt, die mit sehr hoher Wahrscheinlichkeit verdächtig aussehen. Es wäre nicht schlecht, dem Entwickler zu sagen: „Freund, hier liest du zuerst nach Index, dann sortierst du und schneidest dann ab“ – in der Regel gibt es dort nur einen Eintrag.

Alle, die Anfragen mit diesem Muster gestellt haben, sind sicherlich auf folgendes gestoßen: „Gib mir die letzte Bestellung von Vasja, inklusive seines Datums.“ Und wenn ihr keinen Index nach Datum habt oder in dem verwendeten Index kein Datum vorhanden ist, dann werdet ihr genau auf solche „Schaufeln“ treten.

Aber wir wissen ja, dass das „Schaufeln“ sind – warum also dem Entwickler nicht gleich sagen, was er tun sollte? Dementsprechend sieht unser Entwickler beim Öffnen des Plans sofort ein schönes Bild mit Hinweisen, wo ihm sofort gesagt wird: „Hier und hier hast du Probleme, und sie lösen sich so und so.“

Infolgedessen hat sich das Volumen der Erfahrungen, die erforderlich waren, um Probleme am Anfang und jetzt zu lösen, um ein Vielfaches verringert. So ein Werkzeug haben wir entwickelt.

Quelle: habr.com