Wenn VACUUM fehlschlägt, reinigen wir den Tisch manuell

VACUUM Von einer Tabelle kann in PostgreSQL nur was „aufgeräumt“ werden niemand kann sehen - Das heißt, es gibt keine einzige aktive Anfrage, die gestartet wurde, bevor diese Datensätze geändert wurden.

Was aber, wenn solch ein unangenehmer Typ (langfristige OLAP-Belastung einer OLTP-Datenbank) immer noch existiert? Wie Reinigen Sie aktiv den Wickeltisch von langen Fragen umgeben und nicht auf einen Rechen treten?

Den Rechen auslegen

Lassen Sie uns zunächst feststellen, welches Problem wir lösen möchten und wie es entstehen kann.

Normalerweise passiert diese Situation auf einem relativ kleinen Tisch, sondern in dem es vorkommt viele Veränderungen. Normalerweise dies oder anders Zähler/Aggregate/Nennwerte, auf dem UPDATE häufig ausgeführt wird, oder Pufferwarteschlange um einen ständig andauernden Strom von Ereignissen zu verarbeiten, deren Aufzeichnungen ständig EINFÜGEN/LÖSCHEN sind.

Versuchen wir, die Option mit Bewertungen zu reproduzieren:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- по этому индексу будем строить рейтинг

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

Und parallel dazu startet in einem anderen Zusammenhang eine lange, lange Anfrage, die einige komplexe Statistiken sammelt, aber hat keinen Einfluss auf unseren Tisch:

SELECT pg_sleep(10000);

Jetzt aktualisieren wir den Wert eines der Zähler viele, viele Male. Um die Reinheit des Experiments zu gewährleisten, machen wir Folgendes in separaten Transaktionen mit dblinkwie es 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 auch nur für das einfachste UPDATE eines einzelnen Datensatzes? Ausführungszeit um das Siebenfache verkürzt — von 0.524 ms auf 3.808 ms? Und unser Rating baut sich immer langsamer auf.

Es ist alles die Schuld von MVCC.

Es geht alles um MVCC-Mechanismus, wodurch die Abfrage alle vorherigen Versionen des Eintrags durchsucht. Säubern wir also unsere Tabelle von „toten“ Versionen:

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! Parallel Die laufende Anfrage stört uns – schließlich möchte er vielleicht eines Tages auf diese Versionen zurückgreifen (was wäre, wenn?), und sie sollten ihm zur Verfügung stehen. Und deshalb hilft uns auch VAKUUM VOLL nichts.

Den Tisch „zusammenklappen“.

Aber wir wissen mit Sicherheit, dass diese Abfrage unsere Tabelle nicht benötigt. Daher werden wir weiterhin versuchen, die Systemleistung wieder auf angemessene Grenzen zu bringen, indem wir alles Unnötige aus der Tabelle streichen – zumindest „manuell“, da VACUUM aufgibt.

Schauen wir uns zur Verdeutlichung das Beispiel einer Puffertabelle an. Das heißt, es gibt einen großen INSERT/DELETE-Fluss und manchmal ist die Tabelle völlig leer. Aber wenn es nicht leer ist, müssen wir es tun Speichern Sie den aktuellen Inhalt.

#0: Die Situation einschätzen

Es ist klar, dass Sie auch nach jedem Vorgang versuchen können, etwas mit der Tabelle zu tun, aber das macht wenig Sinn – der Wartungsaufwand wird eindeutig größer sein als der Durchsatz der Zielabfragen.

Lassen Sie uns die Kriterien formulieren: „Es ist Zeit zu handeln“, wenn:

• VACUUM wurde schon vor langer Zeit auf den Markt gebracht
  Wir erwarten eine schwere Belastung, also lassen Sie es sein 60 Sekunden seit dem letzten [auto]VACUUM.
• Die physische Tabellengröße ist größer als das Ziel
  Definieren wir es als die doppelte Anzahl an Seiten (8-KB-Blöcke) im Verhältnis zur Mindestgröße – 1 Block für den Heap + 1 Block für jeden Index - für eine möglicherweise leere Tabelle. Wenn wir davon ausgehen, dass eine bestimmte Datenmenge „normalerweise“ immer im Puffer verbleibt, ist es sinnvoll, diese Formel zu optimieren.

Verifizierungsanfrage

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: Immer noch VAKUUM

Wir können nicht im Voraus wissen, ob uns eine parallele Abfrage erheblich stört – wie viele Datensätze seit Beginn genau „veraltet“ sind. Wenn wir uns daher dazu entschließen, die Tabelle irgendwie zu verarbeiten, sollten wir sie auf jeden Fall zuerst ausführen VACUUM - Im Gegensatz zu VACUUM FULL beeinträchtigt es nicht parallele Prozesse, die mit Lese-/Schreibdaten arbeiten.

Gleichzeitig kann das meiste, was wir entfernen möchten, sofort entfernt werden. Ja, und spätere Anfragen zu dieser Tabelle gehen an uns von „Hot Cache“, was ihre Dauer verkürzt – und damit die Gesamtzeit der Blockierung anderer durch unsere Wartungstransaktion.

#2: Ist jemand zu Hause?

Schauen wir mal, ob überhaupt etwas in der Tabelle steht:

TABLE tbl LIMIT 1;

Wenn kein einziger Datensatz mehr vorhanden ist, können wir durch einfaches Ausführen viel Bearbeitung einsparen KÜRZEN:

Er verhält sich wie ein bedingungsloser DELETE-Befehl für jede Tabelle, ist jedoch viel schneller, da er die Tabellen nicht tatsächlich durchsucht. Darüber hinaus wird sofort Speicherplatz freigegeben, sodass anschließend kein VACUUM-Vorgang ausgeführt werden muss.

Ob Sie den Tabellensequenzzähler zurücksetzen müssen (RESTART IDENTITY), bleibt Ihnen überlassen.

#3: Alle – abwechselnd!

Da wir in einem hart umkämpften Umfeld arbeiten, könnte es sein, dass jemand dort schon etwas geschrieben hat, während wir hier prüfen, ob keine Einträge in der Tabelle vorhanden sind. Wir sollten diese Informationen nicht verlieren, na und? Das ist richtig, wir müssen sicherstellen, dass niemand es sicher aufschreiben kann.

Dazu müssen wir aktivieren SERIALISIERBAR-Isolation für unsere Transaktion (ja, hier starten wir eine Transaktion) und sperren die Tabelle „fest“:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Dieser Grad der Blockierung wird durch die Vorgänge bestimmt, die wir darauf ausführen möchten.

#4: Interessenkonflikt

Wir kommen hierher und wollen das Schild „sperren“ – was wäre, wenn jemand in diesem Moment aktiv darauf wäre, zum Beispiel daraus lesen würde? Wir werden „hängen“ und darauf warten, dass dieser Block aufgehoben wird, und andere, die lesen möchten, werden uns begegnen ...

Um dies zu verhindern, werden wir „uns selbst opfern“ – wenn wir innerhalb einer bestimmten (akzeptabel kurzen) Zeit keine Sperre erhalten konnten, erhalten wir eine Ausnahme von der Basis, aber wir werden uns zumindest nicht zu sehr einmischen Andere.

Legen Sie dazu die Sitzungsvariable fest lock_timeout (für Versionen 9.3+) oder/und Anweisung_Timeout. Beachten Sie vor allem, dass der Wert „statement_timeout“ erst ab der nächsten Anweisung gilt. Das heißt, so beim Kleben - wird nicht funktionieren:

SET statement_timeout = ...;LOCK TABLE ...;

Um uns später nicht mit der Wiederherstellung des „alten“ Werts der Variablen befassen zu müssen, verwenden wir das Formular LOKAL EINSTELLEN, wodurch der Umfang der Einstellung auf die aktuelle Transaktion beschränkt wird.

Wir erinnern uns, dass „statement_timeout“ für alle nachfolgenden Anforderungen gilt, sodass sich die Transaktion nicht auf inakzeptable Werte ausdehnen kann, wenn die Tabelle viele Daten enthält.

#5: Daten kopieren

Wenn die Tabelle nicht vollständig leer ist, müssen die Daten mithilfe einer temporären Hilfstabelle erneut gespeichert werden:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Unterschrift ON COMMIT DROP Dies bedeutet, dass die temporäre Tabelle in dem Moment, in dem die Transaktion endet, nicht mehr existiert und im Verbindungskontext nicht manuell gelöscht werden muss.

Da wir davon ausgehen, dass nicht viele „Live“-Daten vorliegen, sollte dieser Vorgang recht schnell erfolgen.

Nun, das ist alles! Nach Abschluss der Transaktion nicht vergessen Führen Sie ANALYSE aus um Tabellenstatistiken bei Bedarf zu normalisieren.

Zusammenstellen des endgültigen Drehbuchs

Wir verwenden dieses „Pseudo-Python“:

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

Ist es möglich, die Daten nicht ein zweites Mal zu kopieren?Grundsätzlich ist es möglich, wenn das OID der Tabelle selbst nicht an andere Aktivitäten von der BL-Seite oder FK von der 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 für die 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

Es hat alles geklappt! Die Tabelle ist um das 50-fache geschrumpft und alle UPDATEs laufen wieder schnell.

Source: habr.com