🥇Wann de VACUUM feelt, botze mir den Dësch manuell

VAKUUM kann aus engem Dësch an PostgreSQL nëmmen wat kee kann gesinn - dat heescht, et gëtt keng eenzeg aktiv Ufro déi ugefaang huet ier dës records geännert goufen.

Awer wat wann esou eng désagréabel Zort (laangfristeg OLAP Laascht op eng OLTP Datebank) nach existéiert? Wéi propper aktiv Wiessel Dësch vu laangen Ufroen ëmginn an net op eng Rake trëppelen?

D'Réck ausleeën

Als éischt, loosst eis bestëmmen wat de Problem ass dee mir wëllen léisen a wéi et entstoe kann.

Normalerweis geschitt dës Situatioun op engem relativ klengen Dësch, mä an deem et geschitt vill Ännerungen. Normalerweis dëst oder anescht Meter / Aggregaten / Bewäertungen, op deem UPDATE dacks ausgefouert gëtt, oder bufferschlaang fir e puer stänneg lafende Stroum vun Eventer ze veraarbecht, records vun deenen dauernd INSERT / DELETE sinn.

Loosst eis probéieren d'Optioun mat Bewäertungen ze reproduzéieren:

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;

A parallel, an enger anerer Verbindung, fänkt eng laang, laang Ufro un, e puer komplex Statistiken sammelen, awer keen Afloss op eisen Dësch:

SELECT pg_sleep(10000);

Elo aktualiséieren mir de Wäert vun engem vun de Konter vill, vill Mol. Fir d'Rengheet vum Experiment, loosst eis dat maachen an getrennten Transaktiounen benotzt dblinkwéi et an der Realitéit geschitt:

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

Wat ass geschitt? Firwat souguer fir den einfachsten UPDATE vun engem eenzege Rekord Ausféierung Zäit degradéiert vun 7 Mol - vun 0.524ms op 3.808ms? An eis Bewäertung baut sech ëmmer méi lues op.

Et ass alles dem MVCC seng Schold.

Et ass alles ëm MVCC Mechanismus, wat verursaacht datt d'Ufro duerch all fréier Versioune vum Entrée kuckt. Also loosst eis eisen Dësch aus "dout" Versiounen botzen:

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, et gëtt näischt ze botzen! Parallel Déi lafend Demande stéiert eis - schliisslech wëll hien enges Daags op dës Versiounen wenden (wat wann?), a si sollten him verfügbar sinn. An dofir hëlleft och VACUUM FULL eis net.

"Zesummen" den Dësch

Awer mir wëssen sécher datt dës Ufro eisen Dësch net brauch. Dofir wäerte mir nach ëmmer probéieren d'Systemleistung op adäquat Grenzen zréckzebréngen andeems Dir alles onnéideg aus dem Dësch eliminéiert - op d'mannst "manuell", well VACUUM gitt.

Fir et méi kloer ze maachen, kucke mer d'Beispill vum Fall vun enger Puffertabell. Dat ass, et gëtt e grousse Flux vun INSERT / DELETE, an heiansdo ass den Dësch komplett eidel. Awer wann et net eidel ass, musse mer späichert säin aktuellen Inhalt.

#0: D'Situatioun bewäerten

Et ass kloer datt Dir probéiert eppes mam Dësch ze maachen och no all Operatioun, awer dëst mécht net vill Sënn - d'Ënnerhaltungsoverhead wäert kloer méi grouss sinn wéi den Duerchsatz vun den Zilufroen.

Loosst eis d'Critèrë formuléieren - "et ass Zäit ze handelen" wann:

VACUUM gouf viru laanger Zäit gestart
Mir erwaarden eng schwéier Laascht, also loosst et sinn 60 Sekonnen zënter dem leschte [auto]VACUUM.
kierperlech Dësch Gréisst ass méi grouss wéi Zil
Loosst eis et definéieren als zweemol d'Zuel vun de Säiten (8KB Blocks) relativ zu der Mindestgréisst - 1 blk fir Koup + 1 blk fir all Index - fir eng potenziell eidel Dësch. Wa mir erwaarden datt eng gewësse Quantitéit un Daten ëmmer am Puffer "normalerweis" bleift, ass et raisonnabel dës Formel ze tweaken.

Verifikatioun Ufro

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: Nach ëmmer VAKUUM

Mir kënnen net am Viraus wëssen ob eng parallel Ufro eis bedeitend stéiert - genau wéi vill Opzeechnungen "aus aktuell" ginn zënter et ugefaang huet. Dofir, wa mir décidéieren iergendwéi den Dësch ze verarbeiten, op alle Fall, sollte mir als éischt drop ausféieren VAKUUM - Am Géigesaz zu VACUUM FULL, stéiert et net mat parallele Prozesser déi mat Lies-Schreifdaten schaffen.

Zur selwechter Zäit kann et déi meescht vun deem wat mir gären ewechhuelen direkt botzen. Jo, a spéider Ufroen op dësem Dësch ginn un eis vum "hot cache", déi hir Dauer reduzéieren - an dofir d'Gesamtzäit fir anerer duerch eis Servicetransaktioun ze blockéieren.

#2: Ass iergendeen doheem?

Loosst eis kucken ob et iwwerhaapt eppes an der Tabell ass:

TABLE tbl LIMIT 1;

Wann et keen eenzege Rekord ass, da kënne mir vill op der Veraarbechtung spueren andeems Dir einfach maacht TRUNKATIOUN:

Et handelt d'selwecht wéi en bedingungslos DELETE Kommando fir all Dësch, awer ass vill méi séier well et d'Dëscher net wierklech scannt. Ausserdeem befreit et direkt Disk Space, sou datt et net néideg ass eng VACUUM Operatioun duerno auszeféieren.

Ob Dir musst den Dësch Sequenz Konter zrécksetzen (RESTART IDENTITY) ass un Iech ze entscheeden.

#3: Jiddereen - maacht sech ëm!

Well mir an engem héich kompetitiven Ëmfeld schaffen, wärend mir hei kucken ob et keng Entréen an der Tabell sinn, hätt ee schonn eppes do geschriwwen. Mir sollten dës Informatioun net verléieren, also wat? Dat ass richteg, mir mussen dofir suergen, datt keen et sécher opschreiwe kann.

Fir dëst ze maachen, musse mir aktivéieren SERIALIZABLE-Isolatioun fir eis Transaktioun (jo, hei fänken mir eng Transaktioun un) a gespaart den Dësch "enk":

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

Dëse Blockéierungsniveau gëtt festgeluecht vun den Operatiounen déi mir dorop ausféiere wëllen.

#4: Interessekonflikt

Mir kommen heihinner a wëllen d'Schëld "spären" - wat wann een dee Moment aktiv dru war, zum Beispill dovun ze liesen? Mir wäerten "hänken" a waarden op dëse Block fir fräigelooss ze ginn, an anerer, déi liesen wëllen, lafen an eis ...

Fir dat ze verhënneren, wäerte mir eis "opferen" - wa mir net an enger bestëmmter (akzeptabel kuerzer) Zäit e Spär konnten kréien, da kréie mir eng Ausnahm vun der Basis, awer op d'mannst wäerte mir net ze vill amëschen anerer.

Fir dëst ze maachen, setze d'Sessiounsvariabel lock_timeout (fir Versiounen 9.3+) oder/an statement_timeout. Den Haapt Saach fir ze erënneren ass datt de statement_timeout Wäert nëmme vun der nächster Ausso gëllt. Dat ass, wéi dëst am Kleeblatt - wäert net schaffen:

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

Fir net méi spéit mat der Restauratioun vum "ale" Wäert vun der Variabel ze këmmeren, benotze mir d'Form LOCAL SETZEN, wat den Ëmfang vun der Astellung op déi aktuell Transaktioun limitéiert.

Mir erënneren datt statement_timeout fir all spéider Ufroe gëlt, sou datt d'Transaktioun net op inakzeptabel Wäerter kann ausdehnen wann et vill Daten an der Tabell ass.

# 5: Kopie Daten

Wann den Dësch net komplett eidel ass, mussen d'Donnéeën mat enger temporärer Hëllefstabell nei gespäichert ginn:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Ënnerschrëft OP ENGAGEMENT DROP heescht datt am Moment d'Transaktioun endet, den temporäre Dësch ophält ze existéieren, an et ass net néideg et manuell am Verbindungskontext ze läschen.

Well mir dovun ausgoen datt et net vill "Live" Donnéeën ass, soll dës Operatioun zimlech séier stattfannen.

Gutt, dat ass alles! Vergiesst net nodeems Dir d'Transaktioun ofgeschloss hutt lafen ANALYSE fir Tabellstatistiken ze normaliséieren wann néideg.

D'Finale Skript zesummesetzen

Mir benotzen dëse "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;

Ass et méiglech d'Donnéeën net eng zweete Kéier ze kopéieren?Prinzipiell ass et méiglech wann d'Oid vum Dësch selwer net un all aner Aktivitéite vun der BL Säit oder FK vun der DB Säit gebonnen ass:

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

Loosst eis de Skript op der Quelltabel lafen a kontrolléiert d'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 huet geklappt! Den Dësch ass ëm 50 Mol gekrumpft an all UPDATEn laafen erëm séier.

Source: will.com

Wann VACUUM klappt, propper mir den Dësch manuell