PostgreSQL Antipatterns: Aktualisierung einer großen Tabelle unter Last

Was soll ich tun (und definitiv nicht), wenn Sie eine große Anzahl von Datensätzen in der aktiv genutzten PostgreSQL-Tabelle "multi-million" aktualisieren müssen - den Wert des neuen Felds initialisieren oder Fehler in vorhandenen Datensätzen korrigieren? Gleichzeitig sparen Sie Zeit und verlieren kein Firmengeld durch Ausfallzeiten.



Bereiten Sie die Testdaten vor:

CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i; --  ,   ! CREATE INDEX ON tbl(k, v); 

Angenommen, wir möchten nur den Wert von v für alle Datensätze mit k im Bereich 'q' .. 'z' um 1 erhöhen.

Bevor Sie jedoch mit den Experimenten beginnen, speichern Sie den Originaldatensatz, um jedes Mal "saubere" Ergebnisse zu erzielen:

 CREATE TABLE _tbl AS TABLE tbl; 

UPDATE: Einer für alle und alle für einen

Die einfachste Option, die einem sofort in den Sinn kommt, ist, alles in einem UPDATE zu erledigen:

 UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z'; 

[siehe EXPLAIN.TENSOR.RU]

Ein recht einfacher Vorgang auf ganz „kurzen“ Leitungen dauerte anscheinend länger als 2,5 Sekunden. Und wenn Ihr Ausdruck komplizierter ist, ist die Linie authentischer, es gibt mehr Aufzeichnungen und sogar einige Auslöser greifen ein - die Zeit kann sogar bis zu Minuten, aber bis zu Stunden ansteigen. Angenommen, Sie sind bereit zu warten, und der Rest Ihres Systems ist an diese Basis gebunden, wenn eine aktive OLTP-Last vorliegt.

Das Problem ist, dass UPDATE, sobald es zu einem bestimmten Datensatz gelangt, diesen bis zum Ende der Ausführung blockiert . Wenn er gleichzeitig mit demselben Datensatz an einem parallel gestarteten „Spot“ -UPDATE arbeiten möchte, wartet er nach wie vor auf einen Block für die Aktualisierungsanforderung und setzt sich bis zum Ende seiner Arbeit durch.


© wumo.com/wumo

Das schlimmste Szenario sind Web-Systeme, bei denen Verbindungen zur Datenbank nach Bedarf hergestellt werden. Schließlich sammeln sich solche "baumelnden" Verbindungen an und verschlingen die Ressourcen sowohl der Datenbank als auch des Clients, wenn Sie keinen davon getrennten Schutzmechanismus einrichten.

Transaktionen aufteilen

Im Allgemeinen ist nicht alles gut, wenn alles auf einmal erledigt wird. Ja, und selbst wenn wir ein großes UPDATE in mehrere kleine UPDATE aufteilen, aber alles in einer Transaktion funktionieren lassen, bleibt das Problem beim Sperren gleich, da veränderbare Datensätze bis zum Ende der gesamten Transaktion gesperrt sind.

Wir müssen also eine große Transaktion in mehrere aufteilen. Dazu können wir entweder externe Mittel verwenden und eine Art Skript schreiben, das separate Transaktionen generiert, oder die Möglichkeiten nutzen, die uns die Datenbank selbst bietet.

CALL und Transaktionsmanagement

Ab PostgreSQL 11 können Transaktionen direkt im prozeduralen Code verwaltet werden:

In Prozeduren, die vom CALL-Befehl aufgerufen werden, sowie in anonymen Codeblöcken (im DO-Befehl) können Sie Transaktionen abschließen, indem Sie COMMIT und ROLLBACK ausführen. Nachdem die Transaktion mit diesen Befehlen abgeschlossen ist, wird automatisch eine neue gestartet.

Aber diese Version ist bei weitem nicht jedermanns Sache und die Arbeit mit CALL hat ihre Grenzen. Deshalb werden wir versuchen, unser Problem ohne externe Mittel zu lösen, damit es auf allen aktuellen Versionen und auch mit minimalen Änderungen auf dem Server selbst funktioniert - so dass es nicht notwendig wäre, irgendetwas zu kompilieren und neu zu starten.

Aus dem gleichen Grund werden wir die Möglichkeit, autonome Transaktionen über pg_background zu organisieren, nicht in Betracht ziehen .

Verwalten von Verbindungen "innerhalb" der Basis

PostgreSQL hat in der Vergangenheit verschiedene Methoden verwendet, um autonome Transaktionen zu emulieren und separate zusätzliche Verbindungen zu generieren - über zusätzliche prozedurale Sprachen oder das Standard- Dblink-Modul . Letzteres hat den Vorteil, dass es standardmäßig in den meisten Distributionen enthalten ist und nur ein Befehl erforderlich ist, um es in der Datenbank zu aktivieren:

 CREATE EXTENSION dblink; 

"... und viele, viele ekelhafte Kinder mitgebracht"

Bevor Sie jedoch eine dblink-Bindung erstellen, sollten Sie zunächst herausfinden, wie ein „normaler Entwickler“ einen großen Datensatz, den er aktualisieren muss, in kleine aufteilt.

Naive LIMIT ... OFFSET

Die erste Idee besteht darin, eine "Paginierungs" -Suche durchzuführen: " Wählen wir jedes Mal die nächsten tausend Datensätze aus ", indem Sie den OFFSET-Wert in jeder neuen Anforderung erhöhen:

 UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY --       k, v --     ! LIMIT $1 OFFSET $2 * $1 ) S WHERE (Tk, Tv) = (Sk, Sv); 

Vor dem Testen der Leistung dieser Lösung stellen wir den Datensatz wieder her:

 TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl; 

Wie wir im obigen Plan gesehen haben, müssen wir ungefähr 384.000 Datensätze aktualisieren. Lassen Sie uns daher gleich sehen, wie die Aktualisierungen näher am Ende durchgeführt werden - im Bereich der 300. Iteration von 1000 Einträgen :


[siehe EXPLAIN.TENSOR.RU]

Oh ... Die Aktualisierung des Samples am Ende der gesamten 1K-Datensätze kostet uns fast so viel Zeit wie die gesamte Originalversion !

Dies ist nicht unsere Wahl. Es kann trotzdem verwendet werden, wenn Sie nur wenige Iterationen und kleine OFFSET-Werte erhalten. Weil das LIMIT X OFFSET Y für die Datenbank gleichbedeutend ist mit " subtrahieren / auswählen / bilden von ersten X + Y-Datensätzen und dann werfen Sie das erste Y in den Papierkorb ", was für große Werte von Y tragisch aussieht.

Tatsächlich kann diese Methode überhaupt nicht angewendet werden ! Wir verlassen uns nicht nur auf aktualisierte Werte für die Auswahl, sondern riskieren auch, einen Teil der Datensätze zu überspringen und den anderen Teil zweimal zu aktualisieren, wenn Blöcke mit denselben Schlüsseln an den Seitenrand gelangen:


In diesem Beispiel haben wir den grünen Datensatz zweimal und den roten Datensatz nie aktualisiert. Nur weil bei gleichen Werten der Sortierschlüssel die Reihenfolge der Datensätze selbst in einem solchen Block nicht festgelegt ist.

Traurig BESTELLEN BEI ... LIMIT

Lassen Sie uns die Aufgabe etwas modifizieren - fügen Sie ein neues Feld hinzu, in das wir unseren Wert v + 1 schreiben:

 ALTER TABLE tbl ADD COLUMN x integer; 

Bitte beachten Sie, dass dieses Design fast sofort funktioniert, ohne die gesamte Tabelle neu zu schreiben. Wenn Sie jedoch einen DEFAULT-Wert hinzufügen, gilt dies - erst ab der 11. Version .

Lassen Sie uns sofort einen Index erstellen, in dem nur nicht initialisierte Einträge verbleiben:

 CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL; 

Der CONCURRENTLY-Index blockiert nicht die Lese- und Schreibarbeit mit der Tabelle, während er sich langsam sogar auf einen riesigen Datensatz überträgt.

Nun lautet die Idee: "Wählen wir jedes Mal nur die ersten tausend Datensätze aus diesem Index aus."

 UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000 --   OFFSET! ) S WHERE (Tk, Tv) = (Sk, Sv) AND Tx IS NULL; 

[siehe EXPLAIN.TENSOR.RU]

Schon viel besser - die Dauer jeder einzelnen Transaktion verkürzt sich jetzt um das Sechsfache.

Aber lassen Sie uns noch einmal sehen, wie der Plan für die 200. Iteration aussehen wird:

 Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1 

Die Zeit verschlechterte sich erneut (allerdings nur um 25%), und die Puffer nahmen zu - aber warum?
Tatsache ist, dass MVCC in PostgreSQL "tote Seelen" im Index hinterlässt - Versionen bereits aktualisierter Datensätze, die jetzt nicht mehr für den Index geeignet sind. Das heißt, wir scannen immer noch die ersten 1000 Datensätze bei der 200. Iteration, obwohl wir später die vorherigen 199K-Versionen von Tupeln verwerfen, die bereits geändert wurden.

Wenn bei uns nicht mehrere Hundert, sondern mehrere Hunderttausend Iterationen erforderlich sind, macht sich die Verschlechterung bei jeder nachfolgenden Abfrageausführung stärker bemerkbar.

UPDATE nach Segment

Warum hängen wir eigentlich so an diesem Wert von „1000 Datensätzen“? Schließlich haben wir keinen Grund, genau 1000 oder eine andere bestimmte Zahl zu wählen . Wir wollten lediglich den gesamten Datensatz in einige, nicht unbedingt gleich große, nicht zusammenhängende Segmente "schneiden". Verwenden wir also unseren vorhandenen Index für den beabsichtigten Zweck.

Ein indiziertes Paar (k, v) eignet sich hervorragend für unsere Aufgabe. Lassen Sie uns eine Abfrage erstellen, damit sie auf dem zuletzt verarbeiteten Paar aufbauen kann:

 WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1; 

Bei der ersten Iteration genügt es, die Abfrageparameter auf den Wert "Null" ('', 0) zu setzen , und bei jeder nächsten Iteration wird das Ergebnis der vorherigen Abfrage abgerufen .


[siehe EXPLAIN.TENSOR.RU]

Die Transaktions- / Sperrzeit beträgt weniger als eine Millisekunde. Die Anzahl der Iterationen wird nicht beeinträchtigt. Ein vollständiger vorläufiger Scan aller Daten in der Tabelle ist nicht erforderlich. Großartig!


Ein zusätzlicher Vorteil dieser Methode ist die Möglichkeit, die Ausführung dieses Skripts jederzeit zu unterbrechen und dann an der gewünschten Stelle fortzusetzen.

Komplexe Berechnungen in UPDATE

Ich werde die Situation mit der schwierigen Berechnung des zugewiesenen Werts separat erwähnen - wenn Sie etwas aus den verknüpften Tabellen berechnen müssen.

Die für die Berechnung aufgewendete Zeit erhöht auch die Dauer der Transaktion. Daher ist es am besten , diese Werte nach UPDATE zu berechnen .

Zum Beispiel möchten wir unser neues Feld x mit der Anzahl der Datensätze füllen, die denselben Wert haben (k, v). Erstellen wir eine "temporäre" Tabelle, deren Generierung keine zusätzlichen Sperren erfordert:

 CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v); 

Nun können wir gemäß dem oben beschriebenen Modell gemäß dieser Tabelle iterieren und das Ziel aktualisieren:

 UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2); 

Wie Sie sehen, sind keine komplizierten Berechnungen erforderlich.

Denken Sie daran, die Hilfstabelle später zu löschen.