Irgendwann in der ferne Zukunft wird die automatische Löschung unnötiger Daten eine der wichtigen Aufgaben von Datenbanksystemen sein [1]. Bis dahin müssen wir uns selbst um das Löschen oder Verschieben von überflüssigen Daten in kostengünstigere Speichersysteme kümmern. Angenommen, Sie möchten mehrere Millionen Zeilen löschen. Eine ziemlich einfache Aufgabe, besonders wenn das Kriterium bekannt ist und ein passender Index vorhanden ist. "DELETE FROM table1 WHERE col1 = :value" – was könnte einfacher sein, oder?
Video:
Ich bin seit dem ersten Jahr im Programmkomitee von Highload, also seit 2007.
Und mit Postgres arbeite ich seit 2005. Ich habe es in vielen Projekten eingesetzt.
Die Gruppe mit RuPostges gibt es ebenfalls seit 2007.
Bei unserem Meetup haben wir über 2100 Teilnehmer erreicht. Das ist der zweite Platz weltweit nach New York, und wir haben San Francisco längst überholt.
Ich lebe seit mehreren Jahren in Kalifornien. Ich arbeite hauptsächlich mit amerikanischen Unternehmen, einschließlich großer Firmen. Sie sind aktive Nutzer von Postgres. Und dort gibt es jede Menge interessante Entwicklungen.
– das ist mein Unternehmen. Wir automatisieren Aufgaben, die Entwicklungsverzögerungen beseitigen.
Wenn Sie etwas tun, gibt es manchmal Engpässe rund um Postgres. Angenommen, Sie müssen warten, bis der Administrator Ihnen eine Testumgebung bereitstellt, oder Sie müssen warten, bis der DBA auf Sie reagiert. Wir identifizieren solche Engpässe im Entwicklungs-, Test- und Administrationsprozess und versuchen, sie durch Automatisierung und neue Ansätze zu beheben.
Ich war kürzlich auf der VLDB in Los Angeles. Das ist die größte Konferenz für Datenbanken. Dort gab es einen Vortrag darüber, dass zukünftige DBMS nicht nur Daten speichern, sondern sie auch automatisch löschen werden. Das ist ein neues Thema.
Die Datenmenge wächst in der Welt auf Zettabyte – das sind 1.000.000 Petabyte. Es wird bereits geschätzt, dass es weltweit mehr als 100 Zettabyte an gespeicherten Daten gibt. Und es wird immer mehr.
Was sollten wir damit machen? Es ist klar, dass wir löschen müssen. Hier ist der Link zu diesem interessanten Vortrag. Aber bisher ist das in DBMS noch nicht umgesetzt.
Diejenigen, die mit Geld umgehen können, wollen zwei Dinge. Sie möchten, dass wir löschen, deshalb müssen wir technisch in der Lage sein, das zu tun.
Was ich gleich erzählen werde, ist eine abstrakte Situation, die viele reale Szenarien umfasst. Es handelt sich um eine Art Kompilation dessen, was mir und den umgebenden Datenbanken im Laufe der Jahre immer wieder passiert ist. Überall gibt es Hürden, und jeder tritt ständig darauf.
Angenommen, wir haben eine oder mehrere Datenbanken, die wachsen. Einige Einträge sind offensichtlicher Müll. Zum Beispiel hat ein Benutzer etwas begonnen, aber nicht abgeschlossen. Nach einer gewissen Zeit wissen wir, dass diese unvollendeten Einträge nicht mehr gespeichert werden müssen. Das heißt, wir möchten einige unerwünschte Daten bereinigen, um Speicherplatz zu sparen, die Leistung zu verbessern usw.
Im Grunde besteht die Aufgabe darin, das Löschen bestimmter Datensätze in einer bestimmten Tabelle zu automatisieren.
Und wir haben eine Abfrage, über die wir heute sprechen werden, nämlich das Löschen von Müll.
Ein erfahrener Entwickler wurde gebeten, das zu erledigen. Er nahm die Abfrage, überprüfte sie – alles funktionierte. Testete sie in der Staging-Umgebung – alles gut. Wir haben sie ausgerollt – alles läuft. Einmal täglich führen wir das aus – alles ist in Ordnung.
Die Datenbank wächst und wächst. Der tägliche DELETE beginnt, ein wenig langsamer zu arbeiten.
Dann merken wir, dass wir gerade eine Marketingkampagne haben und der Verkehr nun mehrfach ansteigt, weshalb wir beschließen, einige unnötige Dinge vorübergehend auf Eis zu legen. Und vergessen, sie zurückzubringen.
Nach ein paar Monaten erinnerten wir uns. Der Entwickler war entweder entlassen worden oder mit etwas anderem beschäftigt, und wir beauftragten jemand anderen, es zurückzubringen.
Er hat es auf dev und staging überprüft – alles in Ordnung. Natürlich muss auch das, was sich angesammelt hat, bereinigt werden. Er hat es geprüft, alles funktioniert.
Was passiert dann? Dann stürzt alles ab. So sehr, dass irgendwann alles zusammenbricht. Alle sind schockiert, niemand versteht, was passiert. Und dann stellt sich heraus, dass es an diesem DELETE lag.
Was lief schief? Hier steht eine Liste dessen, was schiefgelaufen sein könnte. Was davon ist am wichtigsten?
Zum Beispiel gab es keine Überprüfung, das heißt, der DBA-Experte hat nicht geschaut. Mit seinem erfahrenen Auge hätte er das Problem sofort gefunden, zudem hat er Zugriff auf die Prod-Umgebung, in der sich mehrere Millionen Zeilen angesammelt haben.
Vielleicht wurde nicht richtig überprüft.
Vielleicht ist die Hardware veraltet und es ist ein Upgrade für diese Datenbank erforderlich.
Oder es gibt ein Problem mit der Datenbank, und wir müssen von Postgres auf MySQL umziehen.
Oder vielleicht stimmt etwas mit der Operation nicht.
Vielleicht gibt es organisatorische Fehler, und wir sollten jemanden entlassen und bessere Leute einstellen?
Es fand keine DBA-Überprüfung statt. Hätte es einen DBA gegeben, hätte er diese Millionen von Zeilen gesehen und schon ohne Experimente gesagt: „So macht man das nicht“. Angenommen, dieser Code wäre in GitLab oder GitHub gewesen, und es hätte einen Code-Review-Prozess gegeben, der sicherstellt, dass ohne die Genehmigung des DBA diese Operation nicht in der Produktionsumgebung durchgeführt wird, dann hätte der DBA offensichtlich gesagt: „So darf man das nicht machen“.
Er hätte gesagt, dass Sie mit Disk-IO Probleme haben werden und alle Prozesse durcheinander geraten, es könnte zu Sperren kommen, und Sie werden das Autovacuum für eine lange Zeit blockieren, was nicht gut ist.
Der zweite Fehler – wir haben an der falschen Stelle geprüft. Wir haben nachträglich festgestellt, dass sich viele Müll-Daten in der Produktion angesammelt hatten, während der Entwickler in dieser Datenbank keine Daten angesammelt hatte, und auf Staging hat niemand wirklich diesen Müll erzeugt. Dementsprechend gab es dort nur 1.000 Zeilen, die schnell bearbeitet wurden.
Wir erkennen, dass unsere Tests schwach sind, d.h. der etablierte Prozess erkennt die Probleme nicht. Es wurden keine angemessenen DBA-Experimente durchgeführt.
Ein ideales Experiment sollte ideally auf derselben Hardware durchgeführt werden. Es ist nicht immer möglich, dies zu tun, aber es ist sehr wichtig, dass es sich um eine vollständige Kopie der Datenbank handelt. Das propagiere ich seit mehreren Jahren. Und vor einem Jahr habe ich darüber gesprochen, das kann man alles auf YouTube ansehen.
Könnte es sein, dass unsere Hardware schlecht ist? Wenn wir darauf schauen, ist die Latenz gestiegen. Wir haben gesehen, dass die Auslastung bei 100 % lag. Natürlich, wenn wir moderne NVMe-Laufwerke hätten, wäre es wahrscheinlich viel einfacher für uns gewesen. Und vielleicht hätten wir dadurch keinen Ausfall gehabt.
Wenn Sie Cloud-Services nutzen, ist ein Upgrade ganz einfach. Neue Replikate auf neuer Hardware hochgezogen. Switchover. Und alles ist gut. Relativ einfach.
Gibt es eine Möglichkeit, die Festplatten weniger zu belasten? Hier kommen wir mit Hilfe des DBA in ein Thema, das als Checkpoint-Tuning bezeichnet wird. Es stellt sich heraus, dass wir kein Checkpoint-Tuning vorgenommen haben.
Was ist ein Checkpoint? Das gibt es in jedem DBMS. Wenn sich Ihre Daten im Speicher ändern, werden sie nicht sofort auf die Festplatten geschrieben. Die Informationen darüber, dass sich die Daten geändert haben, werden zunächst in ein Protokoll geschrieben, das als Write-Ahead-Log bezeichnet wird. Irgendwann entscheidet das DBMS, dass es Zeit ist, die echten Seiten auf die Festplatte zu schreiben, damit wir im Falle eines Fehlers weniger REDO machen müssen. Das ist wie in einem Spiel. Wenn wir sterben, beginnen wir das Spiel vom letzten Checkpoint. Und jedes DBMS implementiert das.
Die Einstellungen in Postgres sind veraltet. Sie sind auf Daten- und Betriebsvolumina von vor 10-15 Jahren ausgelegt. Und der Checkpoint ist da keine Ausnahme.
Diese Informationen stammen aus unserem Bericht zur Postgres-Überprüfung, d. h. einer automatischen Gesundheitsprüfung. Und hier ist eine Datenbank von mehreren Terabyte. Man sieht gut, dass erzwungene Checkpoints in fast 90 % der Fälle auftreten.
Was bedeutet das? Es gibt zwei Einstellungen. Ein Checkpoint kann beispielsweise alle 10 Minuten aufgrund eines Timeouts eintreten. Oder er tritt ein, wenn eine bestimmte Menge an Daten erreicht ist.
Standardmäßig ist die Einstellung max_wal_size auf 1 Gigabyte festgelegt. Tatsächlich tritt dies in Postgres nach 300-400 Megabyte auf. Wenn Sie so viele Daten geändert haben, erfolgt ein Checkpoint.
Und wenn das niemand optimiert hat, aber der Dienst gewachsen ist und das Unternehmen viel Geld verdient, mit vielen Transaktionen, dann tritt der Checkpoint einmal pro Minute auf, manchmal sogar alle 30 Sekunden, und manchmal überschneiden sie sich. Das ist wirklich schlecht.
Wir müssen dafür sorgen, dass er seltener auftritt. Das heißt, wir können den max_wal_size erhöhen. Dann wird er seltener auftreten.
Wir haben daher eine gesamte Methodik entwickelt, wie man das effektiver angeht, also wie man Entscheidungen über die Auswahl von Einstellungen trifft, indem man sich klar auf konkrete Daten stützt.
Dementsprechend führen wir zwei Serien von Experimenten mit den Datenbanken durch.
In der ersten Serie ändern wir max_wal_size und führen eine Massenoperation durch. Zuerst machen wir das mit den Standardeinstellungen von 1 Gigabyte und führen einen Massen-DELETE von vielen Millionen Zeilen durch.
Es wird deutlich, wie schwierig es für uns ist. Wir sehen, dass das Disk-I/O sehr schlecht ist. Wir überprüfen, wie viel WAL wir generiert haben, denn das ist sehr wichtig. Wir schauen, wie oft ein Checkpoint aufgetreten ist und erkennen, dass die Situation nicht gut ist.
Als nächstes erhöhen wir den max_wal_size. Wir wiederholen das. Wir erhöhen und wiederholen es. Und das viele Male. Grundsätzlich sind 10 Punkte gut, wobei 1, 2, 4, 8 Gigabyte genutzt werden. Dann beobachten wir das Verhalten des spezifischen Systems. Es ist klar, dass die Hardware hier der in der Produktionsumgebung entsprechen sollte. Sie sollten dieselben Festplatten, die gleiche Menge an RAM und dieselben Postgres-Einstellungen haben.
Auf diese Weise tauschen wir unser System aus und wissen, wie sich das DBMS bei schlechten massenhaften DELETE-Operationen verhält und wie es sich checkpointet.
Checkpoint auf Russisch bedeutet – Kontrolle Punkte.
Beispiel: DELETE von mehreren Millionen Zeilen nach Index, die Zeilen sind „verstreut“ über die Seiten.
Hier ist ein Beispiel. Das ist eine bestimmte Datenbank. Mit der standardmäßigen Einstellung von 1 Gigabyte für max_wal_size ist sehr gut sichtbar, dass unsere Festplatten beim Schreiben in die Höhe gehen. Ein solches Bild ist ein typisches Symptom eines sehr kranken Patienten, d.h. es ging ihm tatsächlich schlecht. Und hier gab es nur eine einzige Operation, und zwar das DELETE von mehreren Millionen Zeilen.
Wenn wir eine solche Operation in der Produktionsumgebung durchführen, werden wir tatsächlich ausfallen, denn es ist offensichtlich, dass ein einziges DELETE uns in die Knie zwingt.
Wenn wir weiter zu 16 Gigabyte schauen, sehen wir, dass die Spitzen bereits anfangen. Diese Spitzen sind ein Zeichen dafür, dass es jetzt besser ist. Wir erreichen zwar unser Limit, aber es ist nicht mehr so schlimm. Es gibt ein wenig mehr Spielraum. Auf der rechten Seite sehen wir die Aufzeichnung. Die Anzahl der Operationen ist im zweiten Diagramm dargestellt. Hier wird deutlich, dass wir etwas leichter durchatmen können, wenn wir 16 Gigabyte nutzen.
Bei 64 Gigabyte sieht es bereits viel besser aus. Die Spitzen sind klar ausgeprägt, und es entstehen mehr Möglichkeiten für andere Operationen, um zu bestehen und etwas mit der Festplatte zu machen.
Warum ist das so?
Ich werde ein wenig ins Detail gehen, aber das Thema der Checkpoint-Tuning könnte zu einem ganzen Vortrag führen. Daher möchte ich nicht zu sehr ins Detail gehen, aber einige der Herausforderungen anreißen.
Wenn Checkpoints zu häufig auftreten und wir unsere Zeilen nicht sequenziell aktualisieren, sondern nach Index suchen, was vorteilhaft ist, weil wir nicht die gesamte Tabelle löschen, kann es passieren, dass wir zuerst die erste Seite anfassen, dann die tausendste, und danach zur ersten zurückkehren. Wenn während dieser Zugriffe auf die erste Seite ein Checkpoint diese bereits auf die Festplatte gespeichert hat, wird er sie erneut speichern, weil wir sie ein zweites Mal berührt haben.
Und wir lassen den Checkpoint sie mehrere Male speichern. Dadurch entstehen überflüssige Operationen für ihn.
Aber das ist noch nicht alles. In Postgres wiegt eine Seite 8 Kilobyte, in Linux hingegen 4 Kilobyte. Es gibt die Einstellung full_page_writes. Standardmäßig ist sie aktiviert. Und das ist richtig, denn wenn wir sie deaktivieren, besteht die Gefahr, dass bei einem Ausfall nur eine halbe Seite gespeichert wird.
Das Verhalten beim Schreiben in das WAL (Write-Ahead Log) ist so, dass, wenn der Checkpoint eintritt und wir die Seite zum ersten Mal ändern, die gesamte Seite im Vorausprotokoll landet, d. h. die gesamten 8 Kilobyte, obwohl wir nur eine Zeile geändert haben, die 100 Byte wiegt. Wir sind gezwungen, die gesamte Seite zu speichern.
Bei den folgenden Änderungen wird nur der spezifische Tuple gespeichert, aber beim ersten Mal speichern wir die ganze Seite.
Wenn ein Checkpoint erneut auftritt, müssen wir wieder von vorne anfangen und die gesamte Seite wieder speichern. Bei häufigen Checkpoints, wenn wir uns durch dieselben Seiten bewegen, wird full_page_writes = on mehr Ressourcen beanspruchen, als es nötig wäre, d. h. wir erzeugen mehr WAL. Mehr wird an Replikate, in Archive und auf die Festplatte gesendet.
Somit entstehen uns zwei Überflüssigkeiten.
Wenn wir max_wal_size erhöhen, erleichtern wir sowohl die Arbeit für den Checkpoint als auch für den WAL-Schreiber. Das ist großartig.
Lassen Sie uns ein Terabyte festlegen und einfach damit leben. Was ist daran schlecht? Es ist schlecht, weil wir im Falle eines Ausfalls stundenlang wieder hochfahren müssen, da der Checkpoint schon lange her ist und sich vieles geändert hat. Und wir müssen all das mit REDO wiederherstellen. Deshalb führen wir eine zweite Reihe von Experimenten durch.
Wir führen die Operation durch und schauen, wenn der Checkpoint kurz davor ist, abgeschlossen zu werden, killen wir Postgres absichtlich mit kill -9.
Und danach starten wir ihn neu und beobachten, wie lange er auf dieser Hardware benötigt, d.h. wie viel REDO er in dieser misslichen Lage bewältigen muss.
Ich betone noch einmal, dass die Situation schlecht ist. Erstens, wir sind direkt vor dem Abschluss des Checkpoints abgestürzt, daher müssen wir viel nachholen. Und zweitens hatten wir eine massive Operation. Wenn die Checkpoints nach Zeitüberschreitung wären, würde wahrscheinlich weniger WAL seit dem letzten Checkpoint erzeugt werden. Das heißt, das ist doppelt ungünstig.
Wir messen solche Situationen für unterschiedliche max_wal_size-Parameter und verstehen, dass wir, wenn max_wal_size 64 Gigabyte beträgt, im schlechtesten Fall bis zu 10 Minuten brauchen werden. Und wir fragen uns – ist das für uns akzeptabel oder nicht? Das ist eine unternehmerische Frage. Wir müssen das den Entscheidungsträgern im Geschäftsbereich präsentieren und fragen: "Wie lange können wir im schlimmsten Fall bei einem Problem ausfallen? Können wir im schlechtesten Fall 3-5 Minuten ausfallen?" Dann treffen Sie Ihre Entscheidung.
Hier gibt es einen interessanten Punkt. Auf unserer Konferenz gibt es ein paar Vorträge über Patroni. Möglicherweise nutzen Sie es. Es ist eine Autofailover-Lösung für Postgres. GitLab und Data Egret haben darüber gesprochen.
Und wenn Sie ein Autofailover haben, das innerhalb von 30 Sekunden aktiv wird, dann könnten wir vielleicht auch 10 Minuten ausfallen? Denn bis dahin würden wir auf die Replica umschalten, und alles wäre gut. Das ist eine strittige Frage. Ich habe darauf keine klare Antwort. Ich habe nur das Gefühl, dass es nicht nur um das Wiederherstellen nach einem Ausfall geht.
Wenn wir lange Wiederherstellungszeiten nach einem Ausfall haben, wird es in vielen anderen Situationen unbequem für uns. Zum Beispiel in denselben Experimenten, wenn wir etwas ausprobieren und manchmal bis zu 10 Minuten warten müssen.
Ich würde nicht zu weit gehen, auch wenn wir ein automatisches Failover haben. In der Regel sind Werte wie 64 oder 100 Gigabyte gut. Manchmal kann es sogar sinnvoll sein, weniger auszuwählen. Im Großen und Ganzen ist das eine feine Wissenschaft.
Um Iterationen durchzuführen, zum Beispiel max_wal_size = 1, 8, müssen Sie eine umfangreiche Operation mehrfach wiederholen. Sie haben sie durchgeführt. Und Sie möchten es auf derselben Datenbank erneut tun, aber Sie haben bereits alles gelöscht. Was tun?
Ich werde später über unsere Lösung sprechen, die wir für solche Situationen einsetzen. Das ist der richtige Ansatz.
In diesem Fall hatten wir Glück. Wenn hier steht 'BEGIN, DELETE, ROLLBACK', können wir DELETE wiederholen. Das heißt, wenn wir es selbst zurückgesetzt haben, können wir es wiederholen. Physisch liegen die Daten immer noch dort. Es entsteht sogar kein Bloat. Sie können auf solchen DELETES iterieren.
Ein solcher DELETE mit ROLLBACK ist ideal für die Feinabstimmung von Checkpoints, auch wenn Sie keine ordentlich eingerichteten Datenbank-Labore haben.
Wir haben eine Tabelle mit einer Spalte 'i' erstellt. PostgreSQL hat systemeigene Spalten. Sie sind unsichtbar, es sei denn, man fragt speziell danach. Dazu gehören: ctid, xmid, xmax.
Ctid ist die physische Adresse. Nullseite, erstes Tupel auf der Seite.
Es ist offensichtlich, dass nach dem ROOLBACK der Tupel an derselben Stelle geblieben ist. Das heißt, wir können es noch einmal versuchen, es wird sich genauso verhalten. Das ist das Wesentliche.
Xmax ist die Zeit, zu der der Tupel nicht mehr gültig ist. Er wurde gesetzt, aber Postgres weiß, dass diese Transaktion zurückgesetzt wurde. Daher ist es egal, ob es 0 ist oder die zurückgesetzte Transaktion. Das bedeutet, dass man bei DELETE iterieren und die Verhaltensweise des Systems bei Massenoperationen überprüfen kann. Man könnte Datenbank-Experimente für weniger Begünstigte durchführen.
Das betrifft schon die Programmierer. Auch DBA’s sagen immer zu den Programmierern: 'Warum macht ihr solche langen und schweren Operationen?'. Das ist ein ganz anderes, paralleles Thema. Früher ging es um Administration, jetzt wird es um Entwicklung gehen.
Es ist offensichtlich, dass wir nicht in Teile zerlegt haben. Das ist klar. Man darf einen solchen DELETE für Millionen von Zeilen nicht ungeteilt lassen. Es wird 20 Minuten dauern, und alles wird liegen bleiben. Aber leider machen auch erfahrene Entwickler in sehr großen Unternehmen Fehler.
Warum ist es wichtig, zu fragmentieren?
Wenn wir sehen, dass die Festplatte überlastet ist, sollten wir das Tempo drosseln. Und wenn wir bereits fragmentiert haben, können wir Pausen einfügen oder das Throttling verlangsamen.
Und wir werden andere nicht lange blockieren. In einigen Fällen ist das nicht bedeutend, wenn Sie echten Müll entfernen, mit dem niemand arbeitet. Wahrscheinlich blockieren Sie niemanden, außer der autovacuum-Prozess, da dieser wartet, bis die Transaktion abgeschlossen ist. Wenn Sie jedoch etwas entfernen, das jemand noch anfordern könnte, werden sie blockiert, was eine Kettenreaktion auslösen kann. Auf Websites und in mobilen Anwendungen sollten lange Transaktionen vermieden werden.
Das ist interessant. Es ist häufig der Fall, dass Entwickler fragen: „Welche Batch-Größe sollte ich wählen?“.
Es ist offensichtlich, je größer die Batch-Größe, desto geringer die Transaktionsüberhead, d.h. die zusätzlichen Kosten durch Transaktionen. Aber gleichzeitig erhöht sich die Zeit für diese Transaktion.
Ich habe eine sehr einfache Regel: Nehmen Sie so viel wie möglich, aber überschreiten Sie nicht die Ausführungen pro Sekunde.
Warum eine Sekunde? Die Erklärung ist sehr einfach und für jedermann verständlich, auch für Nicht-Techniker. Wir sehen die Reaktion. Nehmen wir 50 Millisekunden. Wenn sich etwas geändert hat, reagiert unser Auge. Wenn es weniger ist, wird es schwieriger. Wenn etwas nach 100 Millisekunden antwortet, zum Beispiel, wenn Sie mit der Maus klicken und nach 100 Millisekunden eine Antwort erhalten, spüren Sie bereits diese kleine Verzögerung. Eine Sekunde wird bereits als Ruckeln wahrgenommen.
Wenn wir unsere Massenoperationen in 10-Sekunden-Pakete aufteilen, besteht das Risiko, dass wir jemanden blockieren. Und er wird einige Sekunden lang beschäftigt sein, was die Leute bemerken werden. Deshalb bevorzuge ich es, nicht mehr als eine Sekunde zu warten. Gleichzeitig möchte ich aber auch nicht zu klein aufteilen, da die Transaktionsüberhang nun deutlich zu spüren sein wird. Die Datenbank wird stärker belastet, und es könnten noch andere verschiedene Probleme auftreten.
Wir wählen die Größe des Pakets. In jedem Fall können wir dies unterschiedlich tun. Wir können es automatisieren. Und wir stellen die Effizienz der Verarbeitung eines Pakets sicher. Das heißt, wir führen ein DELETE oder UPDATE eines Pakets durch.
Übrigens, alles, was ich beschreibe, betrifft nicht nur DELETE. Wie Sie wahrscheinlich erraten haben, handelt es sich um alle Arten von Massenoperationen mit Daten.
Und wir stellen fest, dass der Plan ausgezeichnet ist. Ein Index-Scan ist sichtbar, sogar noch besser ein Index-Only-Scan. Und wir haben nur eine geringe Menge an Daten, die beteiligt sind. Und es dauert immer weniger als eine Sekunde. Super.
Und wir müssen auch sicherstellen, dass keine Verschlechterung auftritt. Manchmal arbeiten die ersten Chargen schnell und dann wird es immer schlechter. Der Prozess erfordert viel Testen. Genau dafür sind Datenbank-Labs notwendig.
Außerdem sollten wir etwas vorbereiten, mit dem wir im Produktionsumfeld die Situation richtig überwachen können. Zum Beispiel können wir im Log die Zeiten aufzeichnen, wir können festhalten, wo wir uns gerade befinden und wen wir gerade entfernt haben. Das wird uns helfen, zu verstehen, was vor sich geht. Und falls etwas schiefgeht, können wir das Problem schnell finden.
Wenn wir die Effizienz von Anfragen überprüfen und häufig iterieren müssen, gibt es dafür dieses praktische Tool namens "Bot". Es ist bereits einsatzbereit und wird täglich von vielen Entwicklern genutzt. Damit können Sie innerhalb von 30 Sekunden eine große TB-Datenbank abrufen – Ihre eigene Kopie. Sie können Einträge löschen und mit "RESET" zurücksetzen, um erneut zu löschen. So können Sie mit ihm experimentieren. Ich sehe eine Zukunft für dieses Tool. Und wir nutzen es bereits.
Welche Strategien zur Aufteilung gibt es? Ich sehe drei verschiedene Strategien, die Entwickler in der Software verwenden.
Die erste ist sehr einfach. Wir haben eine numerische ID und lassen uns in verschiedene Intervalle aufteilen, mit denen wir arbeiten. Der Nachteil ist klar: Im ersten Abschnitt könnten wir 100 irrelevante Zeilen haben, im zweiten Abschnitt 5 oder sogar gar keine, oder alle 1000 Zeilen könnten irrelevant sein. Die Verteilung ist sehr ungleichmäßig, aber die Aufteilung ist einfach. Wir nehmen die maximale ID und teilen sie auf. Das ist ein rudimentärer Ansatz.
Die zweite Strategie ist ein ausgewogener Ansatz. Sie wird in GitLab verwendet. Dabei wurde die Tabelle gescannt und die Grenzen der ID-Batches festgelegt, sodass jede Charge genau 10.000 Datensätze umfasst. Diese wurden in eine Warteschlange eingereiht und anschließend weiter verarbeitet. Dies kann in mehreren Threads erfolgen.
In der ersten Strategie ist es übrigens auch möglich, dies in mehreren Threads zu tun. Es ist nicht kompliziert.
Es gibt jedoch einen besseren und optimaleren Ansatz. Dies ist die dritte Strategie. Wenn möglich, sollte man diese bevorzugen. Wir basieren dies auf einem speziellen Index. In diesem Fall handelt es sich wahrscheinlich um einen Index gemäß unserem Müll- und ID-Kriterium. Wir fügen die ID hinzu, damit es ein Index-Only-Scan wird, sodass wir den Heap nicht aufsuchen müssen.
In der Regel ist ein Index-Only-Scan schneller als ein Index-Scan.
Und wir finden schnell die IDs, die wir löschen möchten. Die BATCH_SIZE legen wir im Voraus fest. Und wir erhalten sie nicht nur, wir holen sie auf spezielle Weise und sperren sie sofort. Doch so, dass wir, falls sie bereits gesperrt sind, diese nicht erneut sperren, sondern weitermachen und die nächsten nehmen. Das ist "for update skip locked". Dieses Superfeature von Postgres ermöglicht es uns, wenn wir wollen, mehrere Threads zu verwenden. Man kann auch einen Thread verwenden. Und hier gibt es CTE – das ist eine Abfrage. Im zweiten Teil dieser CTE erfolgt die eigentliche Löschung – returning *. Man könnte "returning id" verwenden, aber besser *, wenn Sie nicht viele Daten in jeder Zeile haben.
Warum brauchen wir das? Das benötigen wir, um Berichte zu erstellen. Wir haben jetzt so viele Zeilen gelöscht. Und unsere Grenzen nach ID oder nach created_at sehen so aus. Man kann min, max machen. Man kann noch vieles andere tun. Hier kann man viel unterbringen. Und das ist sehr praktisch für das Monitoring.
Zum Thema Index gibt es noch einen wichtigen Punkt. Wenn wir entschieden haben, dass wir für diese Aufgabe einen speziellen Index benötigen, sollten wir sicherstellen, dass er die Updates von Heap-Only-Tuples nicht beeinträchtigt. In PostgreSQL gibt es Statistiken dazu. Dies kann in pg_stat_user_tables für Ihre Tabelle überprüft werden. Sie können sehen, ob Hot-Updates verwendet werden oder nicht.
Es gibt Situationen, in denen Ihr neuer Index einfach andere Vorgänge blockieren kann. Dadurch verlangsamen sich alle anderen Updates, die bereits laufen. Das nicht nur, weil der Index hinzugefügt wurde (jeder Index verlangsamt Updates ein wenig), sondern weil der neue Index auch andere Vorgänge stört. Eine spezielle Optimierung für diese Tabelle ist dann nicht möglich. Das passiert gelegentlich und ist eine Feinheit, die nur wenigen bewusst ist. Man kann leicht auf diese Falle hereinfallen. Manchmal ist es notwendig, einen anderen Ansatz zu finden und ohne diesen neuen Index auszukommen, entweder einen anderen Index zu erstellen oder auch auf andere Weise, beispielsweise eine zweite Methode zu verwenden.
Aber das ist die optimalste Strategie, um in Batches aufzuteilen und mit einer Anfrage Gruppen zu verarbeiten, Stück für Stück zu löschen usw.
Lange Transaktionen —
Blocked autovacuum —
Blocking issue —
Fehler Nr. 5 ist gravierend. Nikolai von Okmeter hat über die Überwachung von Postgres gesprochen. Leider gibt es kein perfektes Monitoring für Postgres. Einige sind näher dran, andere weiter entfernt. Okmeter ist relativ nah dran, um ideal zu sein, aber es fehlt noch viel und muss hinzugefügt werden. Darauf sollte man vorbereitet sein.
Zum Beispiel sollten Tote Tuples besser überwacht werden. Wenn Sie viel 'tote' Daten in Ihrer Tabelle haben, stimmt etwas nicht. Es ist besser, jetzt zu reagieren, denn es könnte zu einer Degradation kommen, und wir könnten ausfallen. So etwas passiert.
Bei hohem IO ist klar, dass dies nicht gut ist.
Lange Transaktionen ebenfalls. Bei OLTP sollten keine langen Transaktionen gestartet werden. Hier ist ein Link zu einem Snippet, das es ermöglicht, dieses Snippet zu verwenden und eine gewisse Überwachung für lange Transaktionen einzurichten.
Warum sind lange Transaktionen schlecht? Weil alle Locks erst am Ende gelöst werden. Und wir sperren alle. Außerdem blockieren wir die Arbeit von autovacuum für alle Tabellen. Das ist überhaupt nicht gut. Selbst wenn Sie auf Ihrer Replica Hot Standby eingeschaltet haben, bleibt es problematisch. Im Allgemeinen sollten lange Transaktionen nirgendwo zugelassen werden.
Wenn wir viele Tabellen haben, die nicht im Autovacuum sind, sollten wir einen Alert haben. Genau hier könnte eine solche Situation auftreten. Wir können indirekt die Arbeit des Autovacuum beeinflussen. Das ist ein Snippet von Avito, das ich ein wenig verbessert habe. So entstand ein interessantes Tool, um zu sehen, wie es um unser Autovacuum steht. Zum Beispiel warten einige Tabellen auf ihre Bearbeitung und erhalten nicht ihre Chance. Auch das sollte in das Monitoring aufgenommen werden, und ein Alert ist erforderlich.
Und die Blocks der Issues. Ein Wald von Sperr-Bäumen. Ich mag es, bei anderen etwas zu nehmen und es zu verbessern. Hier habe ich von Data Egret einen tollen rekursiven CTE übernommen, der den Wald der Sperr-Bäume anzeigt. Das ist ein ausgezeichnetes Tool für die Diagnose. Auch darauf basierend kann man ein Monitoring aufbauen, aber das muss vorsichtig gemacht werden. Man sollte sich selbst eine kleine statement_timeout setzen. Und es wäre gut, einen lock_timeout zu haben.
Manchmal treten all diese Fehler zusammen auf.
Meiner Meinung nach ist der Hauptfehler hier organisatorischer Natur. Er ist organisatorisch, weil die Technik nicht funktioniert. Nummer 2 – wir haben nicht am richtigen Ort geprüft.
Wir haben nicht am richtigen Ort geprüft, weil wir keinen Klon der Produktion hatten, auf dem man leicht testen kann. Der Entwickler hat möglicherweise überhaupt keinen Zugriff auf die Produktion.
Und wir haben nicht dort überprüft. Wenn wir dort überprüft hätten, hätten wir das selbst gesehen. Der Entwickler hätte das auch ohne DBA bemerkt, wenn er in einer geeigneten Umgebung gearbeitet hätte, in der die Datenmenge und die Anordnung identisch sind. Er hätte die gesamte Degradation bemerkt, und es wäre ihm peinlich gewesen.
Noch etwas zum automatischen Vakuum. Nachdem wir eine umfangreiche Bereinigung von mehreren Millionen Zeilen durchgeführt haben, ist es notwendig, ein REPACK durchzuführen. Das ist besonders wichtig für Indizes. Diese werden darunter leiden, nachdem wir alles dort bereinigt haben.
Wenn Sie die tägliche Bereinigungsarbeit zurückbringen möchten, würde ich empfehlen, dies häufiger, jedoch in kleineren Mengen zu tun. Man könnte es einmal pro Minute oder sogar noch öfter in kleinen Portionen tun. Außerdem sollte man die Überwachung von zwei Dingen einrichten: dass es keine Fehler bei diesem Vorgang gibt und dass er nicht hinterherhinkt. Der Trick, den ich gezeigt habe, wird Ihnen dabei helfen.
Was wir tun, ist Open Source. Es ist auf GitLab veröffentlicht. Wir ermöglichen es den Menschen, dies selbst zu überprüfen, auch ohne DBA. Wir erstellen ein Database Lab, das ist der grundlegende Baustein, auf dem Joe derzeit arbeitet. Sie können eine Produktionskopie nehmen. Momentan gibt es eine Implementierung von Joe für Slack, wo Sie sagen können: „Erkläre diesen bestimmten Query“ und sofort das Ergebnis für Ihre Datenbankkopie erhalten. Sie können sogar DELETE ausführen, und niemand wird es bemerken.
Angenommen, Sie haben 10 Terabyte, wir erstellen auch ein Database Lab von 10 Terabyte. Und mit gleichzeitig 10 Terabyte großen Datenbanken können gleichzeitig 10 Entwickler arbeiten. Jeder kann tun, was er möchte. Löschen, Drop und so weiter. Solche Möglichkeiten bieten wir. Darüber werden wir morgen sprechen.
Das nennt man Thin Provisioning. Das ist die Nutzung von Thin Provisioning. Es ist eine Art von Technologie, die erheblich Verzögerungen in der Entwicklung und im Testen reduziert und die Welt in dieser Hinsicht verbessert. Das heißt, es ermöglicht Ihnen, Probleme mit massenhaften Operationen zu vermeiden.
Beispiel: Eine Datenbank von 5 Terabyte, das Erstellen einer Kopie in weniger als 30 Sekunden. Und es hängt nicht von der Größe ab, das heißt, es ist egal, wie viele Terabyte.
Bereits heute können Sie und in unseren Tools stöbern. Sie können sich registrieren, um zu sehen, was wir anbieten. Sie können auch diesen Bot installieren. Er ist kostenlos. Schreiben Sie uns.
Fragen
In der Praxis kommt es oft vor, dass die Daten, die in der Tabelle bleiben sollen, viel weniger sind als die, die entfernt werden müssen. In solchen Fällen ist es oft einfacher, einen neuen Datensatz zu erstellen, die benötigten Daten dorthin zu kopieren und die alte Tabelle zu truncatieren. Es ist klar, dass ein programmatischer Ansatz für diesen Schritt erforderlich ist, während Sie den Wechsel vornehmen. Wie könnte ein solcher Ansatz aussehen?
Das ist ein sehr guter Ansatz und eine interessante Aufgabe. Er ähnelt stark dem, was pg_repack tut, und ist vergleichbar mit den Herausforderungen, die auftreten, wenn Sie IDs auf 4 Byte ändern müssen. Viele Frameworks haben dies vor einigen Jahren getan, und jetzt müssen die Tabellen, die gewachsen sind, auf 8 Byte konvertiert werden.
Diese Aufgabe ist ziemlich anspruchsvoll. Wir haben das bereits gemacht. Und Sie müssen dabei sehr vorsichtig sein. Es gibt Locks und so weiter. Aber es ist machbar. Das heißt, der Standardansatz ähnelt pg_repack. Sie deklarieren eine solche Tabelle. Und bevor Sie beginnen, Daten mit einem Snapshot hineinzuspielen, deklarieren Sie noch eine zusätzliche Tabelle, die alle Änderungen überwacht. Es gibt einen Trick dabei: Einige Änderungen müssen Sie möglicherweise gar nicht verfolgen. Es gibt Feinheiten. Und dann wechseln Sie, nachdem Sie die Änderungen angewendet haben. Es wird eine kurze Pause geben, in der wir alle sperren, aber im Großen und Ganzen ist das machbar.
Wenn Sie sich pg_repack auf GitHub anschauen, dann finden Sie die Idee, bei der Aufgabe die ID von int 4 auf int 8 umzuwandeln, dazu pg_repack selbst zu verwenden. Das ist auch möglich, aber es ist eine etwas hacke Methode, die trotzdem dafür geeignet ist. Sie können sich in den Trigger einmischen, den pg_repack verwendet, und dort angeben: „Wir benötigen diese Daten nicht“, d.h. wir übertragen nur das, was wir brauchen. Und danach wechselt es einfach und das war's.
Bei diesem Ansatz erhalten wir zusätzlich eine zweite Kopie der Tabelle, in der die Daten bereits indiziert und sehr gleichmäßig mit schönen Indizes angeordnet sind.
Bloat gibt es nicht, das ist ein guter Ansatz. Aber ich weiß, dass es Versuche gibt, eine Automatisierung dafür zu entwickeln, also eine universelle Lösung zu schaffen. Ich kann Sie mit dieser Automatisierung in Verbindung bringen. Sie ist in Python geschrieben und ist eine gute Sache.
Ich komme ein wenig aus der MySQL-Welt, deshalb bin ich hier, um zuzuhören. Und wir nutzen einen solchen Ansatz.
Aber das gilt nur, wenn wir 90 % haben. Wenn wir nur 5 % haben, ist es nicht ideal, ihn anzuwenden.
Danke für den Vortrag! Wenn es keine Ressourcen gibt, um eine vollständige Kopie der Produktionsumgebung zu erstellen, gibt es einen Algorithmus oder eine Formel, um die Last oder die Größe zu berechnen?
Das ist eine gute Frage. Bis jetzt finden wir multimillionen Terabyte große Datenbanken. Selbst wenn die Hardware nicht genau die gleiche ist, zum Beispiel weniger RAM, weniger CPU und nicht ganz die gleichen Festplatten, machen wir es trotzdem. Wenn es wirklich keinen Platz gibt, müssen wir darüber nachdenken. Lassen Sie mich bis morgen darüber nachdenken, Sie waren hier, wir können darüber sprechen, das ist eine gute Frage.
Vielen Dank für den Bericht! Sie haben zu Beginn angesprochen, dass es eine großartige Postgres-Version gibt, die bestimmte Einschränkungen hat, aber sich weiterentwickelt. Letztendlich sind das alles nur Behelfslösungen. Steht das nicht im Widerspruch zur Entwicklung von Postgres, wenn irgendwann ein DELETE-Feature eingeführt wird oder etwas anderes, das auf niedrigem Niveau unterstützen soll, was wir hier mit unseren seltsamen Mitteln versuchen zu lösen?
Wenn wir in SQL viele Datensätze in einer Transaktion löschen oder aktualisieren, wie kann Postgres das dann verteilen? Wir sind physisch bei den Operationen limitiert. Es wird dennoch lange dauern, und wir werden in dieser Zeit sperren müssen usw.
Das haben sie mit den Indizes gemacht.
Ich könnte mir vorstellen, dass man das Tuning der Checkpoints automatisieren könnte. Irgendwann wird das vielleicht Realität sein. Aber ich verstehe die Frage nicht ganz.
Die Frage ist, ob es einen Entwicklungsvektor gibt, der dort verläuft und hier parallel zu Ihrem? Denken sie dort derzeit noch nicht darüber nach?
Ich habe die Prinzipien erläutert, die man jetzt verwenden kann. Es gibt einen anderen Bot. , damit kann eine automatisierte Checkpoint-Tuning durchgeführt werden. Wird es das irgendwann in Postgres geben? Ich weiß es nicht, das wird momentan nicht einmal diskutiert. Wir sind noch weit davon entfernt. Aber es gibt Wissenschaftler, die neue Systeme entwickeln. Und sie treiben uns in automatische Indizes. Es gibt Entwicklungen. Zum Beispiel können Sie sich das Auto-Tuning ansehen. Es passt die Parameter automatisch an. Aber es kann Ihnen vorerst kein Checkpoint-Tuning durchführen. Das heißt, es wird die Parameter für Performance, Shell-Puffer usw. auswählen.
Für das Checkpoint-Tuning können Sie etwas tun: Wenn Sie tausend Cluster und verschiedene Hardware, verschiedene virtuelle Maschinen in der Cloud haben, können Sie mit unserem Bot eine Automatisierung durchführen. Und die max_wal_size wird automatisch basierend auf Ihren Zielvorgaben angepasst. Aber leider gibt es das bislang nicht einmal annähernd im Kernel.
Guten Tag! Sie haben über die Nachteile langer Transaktionen gesprochen. Sie haben gesagt, dass autovacuum bei Löschvorgängen blockiert wird. Was schadet uns sonst noch? Denn wir sprechen mehr darüber, wie wir Platz freigeben und ihn nutzen können. Was verlieren wir noch?
Autovacuum mag hier nicht das größte Problem sein. Das eigentliche Risiko liegt darin, dass eine lange Transaktion andere Transaktionen blockieren kann, was deutlich gefährlicher ist. Es kann dazu kommen, oder auch nicht. Wenn es auftritt, kann es sehr problematisch werden. Auch das Autovacuum ist eine Herausforderung. Es gibt zwei Hauptprobleme mit langen Transaktionen in OLTP: Locks und Autovacuum. Wenn Sie das Hot Standby-Feedback auf dem Replica aktiviert haben, kann es zu einer Autovacuum-Sperre auf dem Master kommen, die vom Replica ausgeht. Aber immerhin wird es dort keine Locks geben. Hier hingegen wird es Locks geben. Da wir über Datenänderungen sprechen, sind Locks hier ein entscheidender Faktor. Und wenn das alles lange dauert, werden immer mehr Transaktionen blockiert. Diese können auch andere blockieren. Es entstehen Lock-Bäume. Ich hatte auf einen Snippet verwiesen. Dieses Problem wird schneller sichtbar als das mit dem Autovacuum, das sich nur ansammeln kann.
Vielen Dank für den Vortrag! Sie haben Ihren Vortrag damit begonnen, dass die Tests nicht richtig durchgeführt wurden. Sie haben Ihre Idee fortgesetzt, dass man gleichwertige Hardware verwenden sollte, mit einer identischen Datenbasis. Angenommen, wir haben dem Entwickler die Datenbasis gegeben. Und er hat die Abfrage erfolgreich ausgeführt. Das scheint alles in Ordnung zu sein. Aber er testet ja nicht in der Live-Umgebung, wo wir beispielsweise eine Auslastung von 60-70 % haben. Und selbst wenn wir dieses Tuning anwenden, ist das Ergebnis nicht optimal.
Es ist wichtig, einen Experten im Team zu haben und sich auf DBAs zu stützen, die eine Prognose darüber abgeben können, was bei tatsächlicher Hintergrundbelastung passieren wird. Wenn wir nur unsere Änderungen isoliert testen, sehen wir ein gewisses Bild. Ein fortgeschrittenerer Ansatz besteht darin, das Gleiche noch einmal zu tun, aber zusätzlich mit simulierter Produktionsbelastung. Das ist wirklich beeindruckend. Aber darauf müssen wir erst hinkommen. Das ist schon professionell. Wir haben auch klar bewertet, was vorhanden ist und analysiert, ob uns die Ressourcen ausreichen. Das ist eine wichtige Frage.
Wenn wir bereits eine Garbage-Abfrage durchführen und beispielsweise ein Deleted-Flag haben.
Das ist das, was Autovacuum automatisch in Postgres macht.
Oh, macht es das?
Autovacuum ist der Garbage Collector.
Danke!
Vielen Dank für den Vortrag! Gibt es eine Möglichkeit, die Datenbank so zu entwerfen, dass alle Überreste von der Haupttabelle in eine andere Richtung abgetrennt werden?
Ja, das gibt es.
Können wir uns dann absichern, wenn wir die Tabelle, die nicht verwendet werden sollte, gesperrt haben?
Ja, das gibt es. Aber das ist so eine Frage wie bei Huhn und Ei. Wenn wir alle wüssten, was in der Zukunft passiert, würden wir alles großartig umsetzen. Aber das Geschäft ändert sich, es kommen neue Spalten und neue Abfragen dazu. Und dann – oh, wir möchten das löschen. Aber das ist die ideale Situation, die man im Leben manchmal antrifft, aber nicht immer. Aber insgesamt ist das eine gute Idee. Einfach truncate und fertig.
Quelle: habr.com
