ProHoster > Blog > Administration > Wenn VACUUM pausiert – reinigen wir die Tabelle manuell

Wenn VACUUM pausiert – reinigen wir die Tabelle manuell

VACUUM kann nur das aus der Tabelle in PostgreSQL "bereinigen", was niemand sehen kann – das heißt, es gibt keine aktiven Anfragen, die gestartet wurden, bevor diese Datensätze geändert wurden.

Und was, wenn es so einen unangenehmen Typen (lange OLAP-Last auf einer OLTP-Datenbank) doch gibt? Wie eine aktiv veränderliche Tabelle in einem Umfeld mit langen Anfragen zu reinigen, ohne auf die gleichen Schwierigkeiten zu stoßen?

Wenn VACUUM pausiert – reinigen wir die Tabelle manuell

Legen wir die Schwierigkeiten dar

Zuerst bestimmen wir, worin das Problem besteht und wie es überhaupt entstehen kann, das wir lösen wollen.

Normalerweise tritt eine solche Situation auf bei einer relativ kleinen Tabelle, in der jedoch sehr viele Änderungenvorkommen. Normalerweise handelt es sich dabei um verschiedene Zähler/Aggregate/Rankings, die häufig und ständig aktualisiert werden, oder eine Warteschlange zur Bearbeitung eines ständig ablaufenden Ereignisstroms, dessen Aufzeichnungen ständig INSERT/DELETE unterzogen werden.

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 aufbauen

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

Und parallel dazu startet in einer anderen Verbindung eine lange Anfrage, die komplizierte Statistiken zusammenträgt, aber unsere Tabelle nicht berührt:

SELECT pg_sleep(10000);

Jetzt aktualisieren wir viele Male den Wert eines der Zähler. Zur Klarheit des Experiments werden wir dies in einzelnen Transaktionen mit Hilfe von dblink, wie es in der Realität geschehen 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 selbst für das einfachste UPDATE eines einzelnen Datensatzes die Ausführungszeit um das Siebenfache abgenommen – von 0.524ms auf 3.808ms? Und unser Ranking wird immer langsamer.

Das ist alles wegen MVCC

Es liegt am MVCC-Mechanismus, der den Befehl zwingt, alle vorherigen Versionen des Eintrags zu durchsuchen. Lass uns also unsere Tabelle von den "toten" Versionen bereinigen:

VACUUM VERBOSE tbl;

INFO: Durchführung von VACUUM "public.tbl"
INFO: "tbl": 0 entfernbare, 10026 nicht entfernbare Zeilenversionen in 45 von 45 Seiten gefunden
DETAIL: 10000 tote Zeilenversionen können noch nicht entfernt werden, ältester xmin: 597439602

Oh, und es gibt nichts zu bereinigen! Parallel läuft der andere Befehl uns im Weg — denn er könnte irgendwann auf diese Versionen zugreifen wollen (was wäre, wenn?), und sie müssen für ihn verfügbar sein. Und daher hilft uns sogar VACUUM FULL nicht.

Wir "kleben" die Tabelle zusammen

Aber wir wissen genau, dass diese Tabelle für den Befehl nicht notwendig ist. Lassen Sie uns also versuchen, die Systemleistung wieder in angemessene Grenzen zu bringen, indem wir alles Überflüssige aus der Tabelle entfernen - wenn schon "manuell", da VACUUM versagt.

Um es anschaulicher zu machen, betrachten wir den Fall einer Puffertabelle. Das heißt, es gibt einen großen Strom von INSERT/DELETE, und manchmal ist die Tabelle komplett leer. Aber wenn sie nicht leer ist, müssen wir ihren aktuellen Inhalt speichern.

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

Es ist klar, dass man versuchen kann, etwas mit der Tabelle nach jeder Operation zu tun, aber das hat wenig Sinn - die Kosten für die Wartung werden eindeutig höher sein als die Durchsatzrate der Zielabfragen.

Formulieren wir die Kriterien - "es ist Zeit zu handeln", wenn:

  • VACUUM schon lange nicht mehr durchgeführt wurde
    Wir erwarten eine hohe Last, also soll dies 60 Sekunden seit dem letzten [auto]VACUUM sein.
  • die physische Größe der Tabelle größer ist als das Ziel
    Definieren wir es als die doppelte Anzahl der Seiten (Blöcke von 8KB) im Verhältnis zur minimalen Größe - 1 blk auf Heap + 1 blk für jeden der Indizes — für eine potenziell leere Tabelle. Wenn wir jedoch erwarten, dass im Puffer "normalerweise" immer ein gewisser Datenanteil bleibt, ist es sinnvoll, diese Formel anzupassen.

Überprüfungsanfrage

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- hier wäre es richtiger, * current_setting('block_size')::bigint zu machen, 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, wie sehr uns die parallele Anfrage stört – wie viele Datensätze seit ihrem Beginn "veraltet" sind. Daher sollten wir, wenn wir uns entscheiden, die Tabelle zu verarbeiten, zuerst darauf VACUUM – im Gegensatz zu VACUUM FULL stört sie parallele Prozesse nicht beim Lesen und Schreiben von Daten.

Darüber hinaus kann sie sofort den Großteil dessen bereinigen, was wir entfernen möchten. Und die nachfolgenden Anfragen an diese Tabelle laufen dann über den "heißen Cache", was ihre Dauer verkürzt – und damit auch die Gesamtzeit der Sperrung anderer, die von unserer Transaktion bearbeitet werden.

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

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

TABLE tbl LIMIT 1;

Wenn kein einziger Datensatz mehr vorhanden ist, können wir bei der Verarbeitung viel sparen – indem wir einfach TRUNCATE:

verwenden. Es funktioniert wie ein bedingungsloser DELETE-Befehl für jede Tabelle, ist jedoch viel schneller, da es die Tabellen eigentlich nicht scannt. Darüber hinaus gibt es sofort freien Speicherplatz frei, sodass es nicht erforderlich ist, nach dieser Operation ein VACUUM durchzuführen.

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

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

Da wir unter Bedingungen hoher Konkurrenz arbeiten, könnte während wir hier überprüfen, ob keine Datensätze in der Tabelle vorhanden sind, bereits jemand etwas hineingeschrieben haben. Wir dürfen diese Informationen nicht verlieren, also was? Richtig, wir müssen sicherstellen, dass niemand gleichzeitig Schreiben kann.

Zu diesem Zweck 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;

Dieses Sperrniveau wird durch die Operationen bestimmt, die wir mit ihr durchführen möchten.

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

Wir kommen hierher und wollen die Tabelle "sperren" – aber was ist, wenn zu diesem Zeitpunkt jemand aktiv war, beispielsweise etwas aus ihr gelesen hat? Wir würden "stocken" in Erwartung der Freigabe dieser Sperre, während andere, die lesen möchten, bereits auf uns stoßen würden...

Damit das nicht passiert, "opfern wir uns" – falls wir es innerhalb einer bestimmten (angemessen kurzen) Zeit nicht schaffen, die Sperre zu erhalten, erhalten wir eine Ausnahme von der Datenbank, aber zumindest stören wir nicht allzu sehr die anderen.

Zu diesem Zweck setzen wir die Sitzungseinstellung lock_timeout für Versionen 9.3+ und/oder statement_timeout. Es ist wichtig zu beachten, dass der Wert von statement_timeout nur auf das nächste Statement angewendet wird. Also so in der Kombination — funktioniert nicht:

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

Um später nicht den alten Wert der Variable wiederherstellen zu müssen, verwenden wir die Form SET LOCAL, die den Anwendungsbereich der Einstellung auf die aktuelle Transaktion beschränkt.

Denken Sie daran, dass statement_timeout auf alle folgenden Abfragen angewendet wird, damit die Transaktion nicht auf unzulässige Werte ausgedehnt werden kann, falls viele Daten in der Tabelle vorhanden sein sollten.

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

Wenn die Tabelle nicht ganz leer ist, müssen die Daten durch eine temporäre Hilfstabelle 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 existiert und kein manuelles Löschen im Kontext der Verbindung erforderlich ist.

Da wir davon ausgehen, dass nicht viele "lebendige" Daten vorhanden sind, sollte dieser Vorgang relativ schnell ablaufen.

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

Zusammenstellung des endgültigen Skripts

Wir verwenden so etwas wie ein "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 seitens der DB hängen:

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

Es hat alles geklappt! Die Tabelle wurde um den Faktor 50 verkleinert, und alle UPDATEs laufen wieder schnell.

Quelle: habr.com

60GB SSD 8Gb DDR4