VACUUM kann aus einer Tabelle in PostgreSQL nur das "aufräumen", was
niemand sehen kann - das heißt, es gibt keine einzige aktive Abfrage, die früher gestartet wurde, als diese Datensätze geändert wurden.
Aber wenn es so einen unangenehmen Typ gibt (Langzeit-OLAP-Last auf der OLTP-Basis)? Wie
reinige ich einen aktiv wechselnden Tisch, der von langen Abfragen umgeben ist und nicht auf einen Rechen tritt?
Wir haben einen Rechen verteilt
Zunächst legen wir fest, was es ist und wie das Problem entstehen kann, das wir lösen möchten.
Normalerweise geschieht dies
auf einem relativ kleinen Tisch , an dem sich
jedoch viele Änderungen ergeben . In der Regel sind dies entweder verschiedene
Zähler / Aggregate / Bewertungen , auf denen UPDATE häufig ausgeführt wird, oder eine
Pufferwarteschlange für die Verarbeitung eines ständig laufenden Ereignisstroms, dessen Datensätze immer INSERT / DELETE sind.
Versuchen wir, die Option mit Bewertungen zu reproduzieren:
CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC);
Parallel dazu wird in einem anderen Zusammenhang eine lange und lange Abfrage gestartet, bei der einige komplexe Statistiken erfasst werden, die sich jedoch
nicht auf unsere Tabelle auswirken :
SELECT pg_sleep(10000);
Jetzt aktualisieren wir den Wert eines der Zähler viele Male. Für die Reinheit des Experiments werden wir dies
in separaten Transaktionen mit dblink tun , da dies in der Realität passieren wird:
DO $$ DECLARE i integer; tsb timestamp; tse timestamp; d double precision; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); FOR i IN 1..10000 LOOP tsb = clock_timestamp(); PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$); tse = clock_timestamp(); IF i % 1000 = 0 THEN d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000; RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5); END IF; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql;
NOTICE: i = 1000, exectime = 0.524 NOTICE: i = 2000, exectime = 0.739 NOTICE: i = 3000, exectime = 1.188 NOTICE: i = 4000, exectime = 2.508 NOTICE: i = 5000, exectime = 1.791 NOTICE: i = 6000, exectime = 2.658 NOTICE: i = 7000, exectime = 2.318 NOTICE: i = 8000, exectime = 2.572 NOTICE: i = 9000, exectime = 2.929 NOTICE: i = 10000, exectime = 3.808
Was ist passiert? Warum hat sich
die Laufzeit selbst für das einfachste UPDATE eines einzelnen Datensatzes um
das Siebenfache verkürzt - von 0,524 ms auf 3,808 ms? Und unser Rating wird immer langsamer und langsamer aufgebaut.
MVCC ist schuld
Es geht um
den MVCC-Mechanismus , der die Anforderung zwingt, alle vorherigen Versionen des Datensatzes zu betrachten. Also lasst uns unseren Tisch von den "toten" Versionen reinigen:
VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl" INFO: "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages DETAIL: 10000 dead row versions cannot be removed yet, oldest xmin: 597439602
Oh, es gibt nichts zu reinigen! Eine parallele
Abfrage stört uns - schließlich möchte er eines Tages auf diese Versionen verweisen (was wäre wenn?), Und sie sollten ihm zur Verfügung stehen. Und so hilft uns auch VACUUM FULL nicht weiter.
Den Tisch festklemmen
Wir wissen jedoch mit Sicherheit, dass für unsere Tabelle keine Abfrage erforderlich ist. Versuchen wir daher, die Systemleistung auf ein adäquates Framework zurückzusetzen, indem wir alles Überflüssige aus der Tabelle gestrichen haben - zumindest "manuell", da VACUUM bestanden hat.
Betrachten wir zur Verdeutlichung ein Beispiel für eine Puffertabelle. Das heißt, es gibt einen großen INSERT / DELETE-Stream, und manchmal ist die Tabelle vollständig leer. Wenn es dort nicht leer ist, müssen wir
den aktuellen Inhalt speichern .
# 0: bewerte die Situation
Es ist klar, dass Sie auch nach jedem Vorgang versuchen können, etwas mit der Tabelle zu tun, aber dies macht wenig Sinn - der Wartungsaufwand ist deutlich höher als der Durchsatz der Zielanforderungen.
Wir formulieren die Kriterien - "es ist Zeit zu handeln", wenn:
- VACUUM läuft schon lange
Wir erwarten eine große Last, also lassen Sie es 60 Sekunden vom letzten [auto] VACUUM sein. - physische Tischgröße größer als das Ziel
Wir definieren es als die doppelte Anzahl von Seiten (8 KB-Blöcke) im Verhältnis zur Mindestgröße - 1 Block pro Heap + 1 Block für jeden der Indizes - für eine möglicherweise leere Tabelle. Wenn wir davon ausgehen, dass eine bestimmte Datenmenge immer „normal“ im Puffer verbleibt, ist es sinnvoll, diese Formel zu verschärfen.
Überprüfungsanforderung SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 1105920 | 3392.484835
# 1: Trotzdem VAKUUM
Wir können nicht im Voraus wissen, ob die parallele Abfrage uns wirklich behindert - genau wie viele Datensätze seit ihrer Einführung "veraltet" waren. Wenn wir uns dennoch dazu entschließen, die Tabelle irgendwie zu verarbeiten, sollten Sie auf jeden Fall zuerst
VACUUM darauf
ausführen - es beeinträchtigt im Gegensatz zu VACUUM FULL nicht die parallelen Prozesse mit Lese- / Schreibdaten.
Gleichzeitig kann er sofort das meiste aufräumen, was wir entfernen möchten. Ja, und nachfolgende Anfragen für diese Tabelle werden
in einem "Hot Cache" an uns
gesendet , wodurch sich ihre Dauer verringert - und daher die Gesamtzeit, um andere mit unserer Serving-Transaktion zu blockieren.
# 2: Ist jemand zu Hause?
Lassen Sie uns nachsehen - ist überhaupt etwas in der Tabelle:
TABLE tbl LIMIT 1;
Wenn kein einziger Datensatz mehr vorhanden ist, können wir viel bei der Verarbeitung einsparen - indem wir
einfach TRUNCATE ausführen :
Es verhält sich wie der unbedingte DELETE-Befehl für jede Tabelle, ist jedoch viel schneller, da keine Tabellen gescannt werden. Darüber hinaus wird sofort Speicherplatz freigegeben, sodass keine VACUUM-Operation mehr ausgeführt werden muss.
Ob Sie den Zähler der Tabellensequenz (RESTART IDENTITY) gleichzeitig zurücksetzen müssen - entscheiden Sie selbst.
# 3: Alles - der Reihe nach!
Da wir unter Bedingungen hoher Wettbewerbsfähigkeit arbeiten und hier prüfen, ob Einträge in der Tabelle fehlen, könnte dort bereits jemand etwas schreiben. Wir sollten diese Informationen nicht verlieren, na und? Das ist richtig, es muss gemacht werden, damit niemand sicher aufnehmen kann.
Dazu müssen wir die
SERIALIZABLE- Isolation für unsere Transaktion aktivieren (ja, hier starten wir die Transaktion) und die Tabelle "fest" sperren:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
Diese Blockierungsstufe ist auf die Vorgänge zurückzuführen, die mit ihr ausgeführt werden sollen.
# 4: Interessenkonflikt
Wir kommen hierher und möchten das Tablet „sperren“ - und wenn zu diesem Zeitpunkt jemand aktiv war, z. B. daraus lesen? Wir werden im Vorgriff auf die Freigabe dieses Blocks "hängen", während andere, die lesen möchten, bereits in uns begraben sind ...
Um dies zu verhindern, "opfern Sie sich selbst" - wenn wir das Schloss für eine bestimmte (zulässige kleine) Zeit immer noch nicht erhalten konnten, erhalten wir eine Ausnahme aus der Datenbank, aber zumindest kümmern wir uns nicht um den Rest.
Setzen Sie dazu die Sitzungsvariable
lock_timeout (ab Version 9.3) oder / und
statement_timeout . Wichtig ist, dass der Wert von statement_timeout erst ab der nächsten Anweisung gültig ist. Das heißt, so wie beim Kleben
funktioniert es nicht :
SET statement_timeout = ...;LOCK TABLE ...;
Um den „alten“ Wert der Variablen später nicht wieder herzustellen, verwenden wir das Formular
SET LOCAL , das den Umfang der Einstellungen auf die aktuelle Transaktion begrenzt.
Denken Sie daran, dass statement_timeout für alle nachfolgenden Anforderungen gilt, sodass die Transaktion nicht auf inakzeptable Werte ausgedehnt werden kann, wenn sich viele Daten in der Tabelle befinden.
# 5: Daten kopieren
Wenn sich herausstellt, dass die Tabelle nicht vollständig leer ist, müssen die Daten über das temporäre Zusatzetikett erneut gespeichert werden:
CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
Die
ON COMMIT DROP- Signatur bedeutet, dass zum Zeitpunkt des Transaktionsendes die temporäre Tabelle nicht mehr vorhanden ist und Sie sie im Kontext der Verbindung nicht manuell löschen müssen.
Da wir davon ausgehen, dass es nicht viele "Live" -Daten gibt, sollte dieser Vorgang schnell genug verlaufen.
Nun, das ist alles! Denken Sie
daran, ANALYZE nach Abschluss der Transaktion
auszuführen , um die Statistiken der Tabelle bei Bedarf zu normalisieren.
Wir sammeln das endgültige Drehbuch
Wir verwenden so ein "Pseudopython":
Und können Sie die Daten nicht ein zweites Mal kopieren?Grundsätzlich ist es möglich, wenn die OID der Tabelle selbst nicht an andere Aktivitäten von BL-Seite oder FK von DB-Seite gebunden ist:
CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL); INSERT INTO _swap_%table TABLE %table; DROP TABLE %table; ALTER TABLE _swap_%table RENAME TO %table;
Lassen Sie uns das Skript in der Quelltabelle ausführen und die Metriken überprüfen:
VACUUM tbl; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tmp_swap; COMMIT;
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 49152 | 32.705771
Alles hat geklappt! Der Tisch ist um das 50-fache geschrumpft und alle UPDATE-Vorgänge laufen wieder schnell.