Kürzlich habe ich erzählt, wie man mit Standardrezepten aus der PostgreSQL-Datenbank steigern kann. Heute wird es darum gehen, wie man das Schreiben in die Datenbank effizienter gestalten kann, ohne irgendwelche "Regler" in der Konfiguration zu verwenden – einfach durch die richtige Organisation der Datenströme.
#1. Секционирование
Der Artikel behandelt, wie und warum man die organisieren sollte, hier geht es jedoch um die praktische Anwendung einiger Ansätze in unserem .
"Die Taten längst vergangener Tage..."
Ursprünglich, wie es bei jedem MVP der Fall ist, startete unser Projekt unter einer recht geringen Last – die Überwachung fand nur für eine Handvoll der kritischsten Server statt, alle Tabellen waren relativ kompakt... Aber die Zeit verging, die überwachten Hosts wurden immer mehr, und als wir erneut versuchten, etwas mit einer der Tabellen mit einer Größe von 1,5 TB, stellten wir fest, dass es zwar möglich war, so weiterzumachen, aber es war sehr umständlich.
Die Zeiten waren fast legendär, verschiedene Versionen von PostgreSQL 9.x waren aktuell, daher musste die ganze Partitionierung "manuell" erfolgen – durch Tabellenvererbung und Trigger Routing mit dynamischen EXECUTE.
Die entstandene Lösung erwies sich als ziemlich universell, sodass sie auf alle Tabellen anwendbar war:
- Es wurde eine leere "Header"-Elterntabelle deklariert, in der alle notwendigen Indizes und Trigger beschrieben wurden..
- Die Aufnahme aus Sicht des Clients erfolgte in die "Wurzel"-Tabelle, und innerhalb davon mit dem Routing-Trigger
BEFORE INSERTwurde der Datensatz "physisch" in den benötigten Abschnitt eingefügt. Sollte ein solcher noch nicht existieren, fingen wir die Ausnahme und... - ... erstellten mit nach dem Muster der Elterntabelle einen Abschnitt mit einer Einschränkung auf das benötigte Datum, damit beim Abrufen von Daten nur in diesem gelesen wurde.
PG10: der erste Versuch
Aber die Partitionierung über Vererbung war historisch gesehen nicht gut geeignet für die Arbeit mit aktivem Schreibstrom oder einer großen Anzahl von Nachkommentabellen. Zum Beispiel kann man sich erinnern, dass der Algorithmus zur Auswahl des erforderlichen Abschnitts eine quadratische Komplexitäthatte, was bei 100+ Abschnitten, wie Sie sich denken können, nicht optimal funktionierte...
In PG10 wurde diese Situation erheblich optimiert, indem die Unterstützung für . Daher haben wir versucht, es sofort nach der Migration des Speichers anzuwenden, aber…
Wie sich nach eingehendem Lesen des Handbuchs herausstellte, unterstützt die nativ partitionierte Tabelle in dieser Version:
- nicht die Beschreibung von Indizes
- unterstützt keine Trigger
- kann selbst kein "Nachkomme" sein
- unterstützt
INSERT ... ON CONFLICT - kann keine Sektion automatisch erzeugen
Nach einem schmerzhaften Schlag mit dem Rechen auf die Stirn haben wir verstanden, dass wir nicht ohne eine Modifikation der Anwendung auskommen werden, und haben die weiteren Forschungen um ein halbes Jahr verschoben.
PG10: Eine zweite Chance
Also begannen wir, die aufgetretenen Probleme nacheinander zu lösen:
- Da Trigger und
ON CONFLICTuns an manchen Stellen doch erforderlich waren, haben wir eine Zwischen- Proxy-Tabelle. - Den "Routing"-Prozess entfernt in Triggern — also von
EXECUTE. - Wir haben separat eine Vorlage-Tabelle mit allen Indizes erstellt, sodass sie nicht einmal in der Proxy-Tabelle vorhanden waren.
Schließlich, nach alledem, haben wir die Haupttabelle nativ partitioniert. Die Erstellung einer neuen Sektion blieb vorerst in der Verantwortung der Anwendung.
"Wir bearbeiten" Wörterbücher
Wie in jedem analytischen System hatten wir auch "Fakten" und "Schnitte" (Wörterbücher). In unserem Fall fungierten zum Beispiel einheitlicher langsamer Abfragen oder der Text der Anfrage selbst.
"Fakten" waren bei uns seit langem nach Tagen partitioniert, daher konnten wir veraltete Sektionen problemlos löschen, und sie störten uns nicht (Logs!). Aber mit den Wörterbüchern gab es Probleme…
Es kann nicht gesagt werden, dass es sehr viele waren, aber ungefähr auf 100TB "Fakten" erhielten wir ein Wörterbuch von 2.5TB. Aus einer solchen Tabelle kann man nichts bequem löschen, nicht komprimieren in angemessener Zeit, und das Schreiben darin wurde allmählich immer langsamer.
Einen Wörterbuch… in dem jeder Eintrag genau einmal vertreten sein sollte… und das ist richtig, aber!.. Niemand hindert uns daran, ein separates Wörterbuch für jeden Tag! Ja, das bringt eine gewisse Redundanz mit sich, ermöglicht aber:
- schneller zu schreiben/zu lesen durch die kleinere Größe der Sektionen
- weniger Speicher zu verbrauchen durch die Arbeit mit kompakteren Indizes
- weniger Daten zu speichern durch die Möglichkeit, schnell veraltete Daten zu löschen
Infolge des gesamten Maßnahmenpakets sank die CPU-Auslastung um ~30%, und die Festplattennutzung um ~50%:
Dabei haben wir weiterhin genau das gleiche in die Datenbank geschrieben, einfach mit einer geringeren Belastung.
#2. Эволюция и рефакторинг БД
Also, we stopped at the point where we have a section for each day with data. Essentially, CHECK (dt = '2018-10-12'::date) — is the partitioning key and the condition for a record to fit into a specific section.
Since all reports in our service are built over a specific date, the indexes from the 'non-partitioned times' were all of the type (Server, Datum, Plan Template), (Server, Datum, Plan Node), (Datum, Error Class, Server),…
But now each section contains its own instances of each such index… And within each section the date is a constant… It turns out that now we simply write a constant as one of the fields into each such index, which increases both its size and the search time across it, but yields no results. We’ve created a stumbling block for ourselves, oops… The direction for optimization is obvious — we simply
remove the date field from all indexes on partitioned tables. Given our volumes, the gain is about 1TB/week And now let’s note that this terabyte still had to be recorded somehow. This means we also need to!
load the disk less now ! This picture clearly shows the effect achieved from the cleaning we dedicated a week to:One of the major troubles of overloaded systems is
#3. «Размазываем» пиковую нагрузку
excessive synchronization of operations that do not require it. Sometimes it's 'because we didn't notice,' sometimes 'it was easier that way,' but sooner or later, you have to get rid of it. If we zoom in on the previous picture, we can see that the disk is
loading with twice the amplitude between adjacent readings, which clearly should not happen statistically with such a number of operations:
Achieving this is quite simple. We had already monitored nearly 1000 servers,each processed by a separate logical thread, and each thread dumps the accumulated information for sending to the database at a certain regularity, approximately like this:
setInterval(sendToDB, interval)The problem lies exactly in the fact that all threads start at roughly the same time,so their sending moments almost always coincide 'down to the dot.' Oops #2…
Fortunately, this can be fixed quite easily by adding a 'random' offset in time:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Кэшируем, что нужно можно
The third traditional problem of high load is the lack of cache where it könnte should be.
Zum Beispiel haben wir die Möglichkeit geschaffen, Analysen nach Plan-Knoten durchzuführen (all das Seq Scan auf Benutzer), aber sofort zu denken, dass sie im Großen und Ganzen gleich sind — das haben wir vergessen.
Natürlich wird in die Datenbank nichts erneut geschrieben, das schneidet den Trigger mit INSERT ... ON CONFLICT DO NOTHING. Aber die Daten gelangen trotzdem zur Datenbank, und zusätzlich ist es unnötig Lesen zur Überprüfung von Konflikten muss gemacht werden. Ups Nr. 3…
Der Unterschied in der Anzahl der in die Datenbank gesendeten Datensätze vor/nach Aktivierung des Caching ist offensichtlich:
Und dies führt zu einer gleichzeitig sinkenden Last auf dem Speicher:
Insgesamt
„Terabyte-pro-Tag“ klingt nur erschreckend. Wenn Sie alles richtig machen, sind das nur 2^40 Byte / 86400 Sekunden = ~12.5MB/s, was sogar von Desktop-IDE-Festplatten gehalten wurde. 🙂
Und mal ernsthaft, selbst bei einem zehnfachen „Ungleichgewicht“ der Last über einen Tag können Sie problemlos innerhalb der Möglichkeiten moderner SSDs bleiben.
Quelle: habr.com
