🥇Wenn VACUUM nicht mehr ausreicht – reinigen wir die Tabelle manuell

VACUUM kann man aus einer PostgreSQL-Tabelle nur das "bereinigen", was niemand sehen kann — das heißt, es gibt keine aktiven Abfragen, die vor der Änderung dieser Datensätze gestartet wurden.

Und was ist, wenn es so einen unangenehmen Fall gibt (langanhaltende OLAP-Last in einer OLTP-Datenbank)? Wie kann man eine aktiv veränderliche Tabelle in einem Umfeld mit langen Abfragen bereinigen und nicht über die eigenen Füße stolpern?

Schauen wir uns das genauer an

Zuerst sollten wir bestimmen, worin genau das Problem besteht und wie es überhaupt entstehen kann.

Solche Situationen treten normalerweise auf bei relativ kleinen Tabellen, aber bei denen sehr viele Änderungen stattfinden. Normalerweise handelt es sich entweder um verschiedeneZähler/Aggregate/Rankings , bei denen häufig UPDATEs ausgeführt werden, odereinen Puffer-Warteschlange zur Verarbeitung eines stetigen Ereignisstroms, bei dem ständig INSERTs/DELETEs stattfinden. Versuchen wir, ein Beispiel mit Rankings nachzustellen:

CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC); -- nach diesem Index werden wir das Ranking aufbauenINSERT INTO tbl SELECT chr(ascii('a'::text) + i) k , 0 v FROM generate_series(0, 25) i;

Währenddessen wird in einer anderen Verbindung eine langwierige Anfrage gestartet, die komplexe Statistiken sammelt, aber

Gleichzeitig startet in einer anderen Verbindung eine langwierige Anfrage, die komplizierte Statistiken sammelt, aber ohne unsere Tabelle zu beeinträchtigen:

SELECT pg_sleep(10000);

Jetzt aktualisieren wir die Werte eines der Zähler ganz, ganz oft. Um die Experimente sauber zu halten, machen wir das in separaten Transaktionen mit Hilfe von dblink, so wie es in der Realität geschehen würde:

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;

HINWEIS:  i =  1000, Ausführungszeit = 0.524
HINWEIS:  i =  2000, Ausführungszeit = 0.739
HINWEIS:  i =  3000, Ausführungszeit = 1.188
HINWEIS:  i =  4000, Ausführungszeit = 2.508
HINWEIS:  i =  5000, Ausführungszeit = 1.791
HINWEIS:  i =  6000, Ausführungszeit = 2.658
HINWEIS:  i =  7000, Ausführungszeit = 2.318
HINWEIS:  i =  8000, Ausführungszeit = 2.572
HINWEIS:  i =  9000, Ausführungszeit = 2.929
HINWEIS:  i = 10000, Ausführungszeit = 3.808

Was ist hier passiert? Warum hat selbst die einfachste UPDATE-Anweisung für einen einzigen Datensatz die Ausführungszeit sich um das 7-fache verschlechtert — von 0,524 ms auf 3,808 ms? Und unser Rating wird auch immer langsamer.

Dafür ist MVCC verantwortlich

Es liegt an dem MVCC-Mechanismus, der alle vorherigen Versionen des Datensatzes durchsehen lässt. Lassen Sie uns also unsere Tabelle von „toten“ Versionen bereinigen:

VACUUM VERBOSE tbl;

INFO:  Bereinigung von "public.tbl"
INFO:  "tbl": gefunden 0 entfernbarer, 10026 nicht entfernbarer Zeilenversionen in 45 von 45 Seiten
DETAIL:  10000 tote Zeilenversionen können noch nicht entfernt werden, ältestes xmin: 597439602

Oh, da gibt’s nichts zu bereinigen! Parallel läuft eine Abfrage, die uns stört — schließlich könnte sie irgendwann auf diese Versionen zugreifen wollen (wer weiß?), und sie müssen für sie verfügbar sein. Daher hilft uns sogar VACUUM FULL nicht.

Wir „verdichten“ die Tabelle

Aber wir wissen ganz genau, dass diese Abfrage unsere Tabelle nicht benötigt. Lassen Sie uns daher versuchen, die Systemleistung wieder in gesunde Bahnen zu lenken, indem wir alles Überflüssige aus der Tabelle entfernen — zumindest „manuell“, da VACUUM nicht greift.

Um es anschaulicher zu machen, betrachten wir ein Beispiel mit einer Puffer-Tabelle. Das heißt, es gibt einen großen Fluss an INSERT/DELETE-Vorgängen, und manchmal ist die Tabelle ganz leer. Wenn sie jedoch nicht leer ist, müssen wir ihren aktuellen Inhalt speichern.

#0: Оцениваем ситуацию

Es ist klar, dass man versuchen kann, mit der Tabelle nach jeder Operation etwas zu machen, aber es macht wenig Sinn - die Betriebskosten werden offensichtlich höher sein als die Durchsatzrate der Zielanfragen.

Lassen Sie uns die Kriterien festlegen: „Es ist an der Zeit zu handeln“, wenn:

VACUUM schon lange genug ausgeführt wurde
Wir erwarten eine hohe Last, daher setzen wir 60 Sekunden seit dem letzten [auto]VACUUM.
Die physische Größe der Tabelle ist größer als die Zielgröße
Definieren wir sie als das Doppelte der Seitenanzahl (Blöcke von 8 KB) im Verhältnis zur minimalen Größe - 1 blk auf dem Heap + 1 blk für jeden der Indizes — für eine potenziell leere Tabelle. Wenn wir jedoch erwarten, dass immer eine gewisse Menge an Daten im Puffer bleibt, sollte diese Formel sinnvoll angepasst werden.

Überprüfungsanfrage

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- hier wäre es korrekter, * current_setting('block_size')::bigint zu verwenden, aber wer ändert die Blockgröße?..
, 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: Все равно VACUUM

Wir können im Voraus nicht wissen, ob eine parallele Anfrage uns in starkem Maße stört – wie viele Einträge seit ihrem Beginn "veraltet" sind. Daher sollten wir, wenn wir uns entscheiden, die Tabelle irgendwie zu verarbeiten, zunächst unbedingt auf ihr VACUUM — im Gegensatz zu VACUUM FULL stören parallele Prozesse nicht beim Lesen und Schreiben von Daten.

Er kann gleichzeitig den Großteil der Daten bereinigen, den wir gerne entfernen möchten. Zudem werden die nachfolgenden Anfragen zur Tabelle aus dem "Hot Cache", was die Dauer verringert – und damit die gesamte Blockierungszeit anderer Transaktionen, die von uns bearbeitet werden.

#2: Есть кто-нибудь дома?

Lassen Sie uns überprüfen, ob in der Tabelle überhaupt noch etwas vorhanden ist:

TABLE tbl LIMIT 1;

Wenn keine Einträge mehr vorhanden sind, können wir bei der Verarbeitung erheblich sparen – indem wir einfach TRUNCATE:

Es funktioniert wie ein bedingungsloser DELETE-Befehl für jede Tabelle, jedoch viel schneller, da es die Tabellen tatsächlich nicht scannt. Darüber hinaus wird sofort Speicherplatz freigegeben, sodass ein VACUUM nach ihr nicht erforderlich ist.

Ob Sie dabei den Sequenzzähler der Tabelle zurücksetzen (RESTART IDENTITY) sollten, entscheiden Sie selbst.

#3: Все — по-очереди!

Da wir uns in einem hochgradig wettbewerbsintensiven Umfeld bewegen, könnte es sein, dass während wir auf die Abwesenheit von Einträgen in der Tabelle prüfen, bereits jemand etwas hineingeschrieben hat. Diese Informationen dürfen wir nicht verlieren, also was müssen wir tun? Richtig, wir müssen sicherstellen, dass niemand schreiben kann.

Dafür müssen wir SERIALIZABLE- die Isolation für unsere Transaktion aktivieren (ja, hier starten wir die Transaktion) und die Tabelle 'komplett sperren':

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

Gerade dieses Sperrlevel ist durch die Operationen bedingt, die wir damit durchführen möchten.

#4: Конфликт интересов

Wir kommen hierher und wollen die Tabelle 'sperren' — und wenn gerade jemand aktiv auf dieser Tabelle war, beispielsweise etwas daraus gelesen hat? Dann 'hängen' wir auf das Ende dieser Sperrung und andere, die lesen möchten, stoßen auf uns …

Damit das nicht passiert, 'opfern wir uns' — wenn wir innerhalb einer bestimmten (geringfügigen) Zeit immer noch keine Sperrung erhalten haben, werden wir von der Datenbank eine Ausnahme erhalten, aber wir stören nicht allzu sehr die anderen.

Für dieses Ziel setzen wir eine Sitzungsvariable. lock_timeout (für Versionen 9.3+) oder/und statement_timeout. Wichtig ist zu beachten, dass der Wert von statement_timeout nur für das folgende Statement gilt. Das bedeutet, so in der Verknüpfung — wird nicht funktionieren.:

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

Um später nicht das "alte" Werten der Variable wiederherzustellen, verwenden wir die Form SET LOCAL, die den Geltungsbereich der Einstellung auf die aktuelle Transaktion beschränkt.

Wir dürfen nicht vergessen, dass statement_timeout auf alle nachfolgenden Anfragen angewendet wird, damit die Transaktion nicht auf unzumutbare Werte ausgedehnt werden kann, falls in der Tabelle viele Daten vorhanden sind.

#5: Копируем данные

Falls die Tabelle nicht ganz leer ist, müssen die Daten über eine unterstützende temporäre Tabelle erneut gespeichert werden:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatur ON COMMIT DROP bedeutet, dass die temporäre Tabelle am Ende der Transaktion nicht mehr existieren wird, und es ist nicht nötig, sie manuell im Verbindungskontext zu löschen.

Da wir davon ausgehen, dass es nicht viele "lebende" Daten gibt, sollte dieser Vorgang relativ schnell ablaufen.

Nun, das wäre alles! Vergessen Sie nicht, nach Abschluss der Transaktion ANALYZE auszuführen. zur Normalisierung der Statistiken der Tabelle, falls erforderlich.

Wir erstellen das endgültige Skript

Wir verwenden eine solche "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;

Kann man die Daten nicht ein zweites Mal kopieren?Im Prinzip ja, wenn an der oid der Tabelle keine anderen Aktivitäten von der BL oder FK von der DB gebunden sind:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Wir führen das Skript auf der ursprünglichen Tabelle aus und überprüfen die Metriken:

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! Die Tabelle wurde um das 50-fache verkleinert und alle UPDATEs laufen jetzt schnell.

Quelle: habr.com

Wenn VACUUM versagt – reinigen Sie die Tabelle manuell