🥇 Kui VACUUM ei toimi — puhastame tabelit käsitsi

VACUUM võib PostgreSQL tabelist „kustutada” ainult seda, mida keegi ei saa näha — st ühtegi aktiivset päringut pole, mis oleks alanud enne nende rekordite muutmist.

Aga kui selline ebameeldiv tüüp (pikaajaline OLAP-koormus OLTP-andmebaasis) siiski on? Kuidas puhastada aktiivselt muutuvaid tabelit piklike päringute keskkonnas ja mitte nahka panna?

Asetame naerukorke

Esiteks määratleme, mis see probleem on ja kuidas see üldse võib tekkida, mida soovime lahendada.

Tavaliselt juhtub selline olukord suhteliselt väikese tabeli puhul, kuid kus toimub väga palju muudatusi. Tavaliselt on need kas erinevad loendurid/ägereetid/hinnangud, millele tehakse tihti UPDATE, või vahevõrk kuna töötlemiseks on pidev voog sündmusi, mille kohta salvestatakse pidevalt INSERT/DELETE.

Proovime recreateerida varianti hinnangutest:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- selle indeksi alusel koostame hinnangu

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

Ja samal ajal, teises ühenduses, algab pikk-pikk päring, mis kogub mingit keerulist statistikat, kuid meie tabelit mitte puudutav:

SELECT pg_sleep(10000);

Nüüd värskendame korduvalt ühe loendi väärtust. Eksperimendi puhtuse huvides teeme seda eraldi tehingutes dblink'i abil, nagu see tegelikult toimub:

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;

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

Mis juhtus? Miks isegi kõige lihtsama UPDATE ühe kirje jaoks täitmise aeg halvenes 7 korda — 0.524 ms kuni 3.808 ms? Ja meie reiting suureneb aina aeglasemalt.

Süüdi on MVCC

Probleem on MVCC mehhanismis, mis paneb päringud vaatama kõiki eelnevaid kirjeversioone. Nii et puhastame meie tabelis «surnud» versioonid:

VACUUM VERBOSE tbl;

INFO:  vaakumeerimine "public.tbl"
INFO:  "tbl": leiti 0 eemaldatavat, 10026 eemaldamatut ridade versiooni 45-st 45-st leheküljest
DETAIL:  10000 surnud ridade versiooni ei saa veel eemaldada, vanim xmin: 597439602

Oi, aga puhastada pole midagi! Samal ajal töötav päring takistab meid — sest see võib kunagi soovida nendele versioonidele viidata (äkki?), ja need peavad olema sellele kergesti kättesaadavad. Seega ei aita isegi VACUUM FULL.

«Kokku tõmbame» tabeli

Aga me teame kindlalt, et sellele päringule meie tabel ei ole vajalik. Seetõttu proovime siiski süsteemi jõudlust normaalsesse raamistikku viia, eemaldades tabelist kõik tarbetu — vähemalt «käsitsi», kuna VACUUM allub.

Selleks, et oleks selgem, vaatame juba näidet puhvritabeli puhul. See tähendab, et toimub suur INSERT/DELETE voog, ja mõnikord on tabelis üldse tühi. Aga kui seal pole tühi, peame säästma tema praegust sisu.

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

On selge, et pärast iga toimingut tabeliga midagi teha on võimalik, kuid see ei oma suurt mõtet — hoolduskulud on selgelt suuremad kui sihtpäringute läbilaskevõime.

Vormistame kriteeriumid — "on juba aeg tegutseda", kui:

VACUUM käivitati piisavalt kaua tagasi
Ootame suurt koormust, nii et see olgu 60 sekundit viimasest [auto]VACUUM-ist.
tabeli füüsiline suurus on suurem kui siht
Määratleme selle kui kahekordse lehtede (8KB plokkide) arvu minimaalse suuruse suhtes — 1 blk heap-i kohta + 1 blk iga indeksi jaoks — potentsiaalselt tühi tabel. Kui me ootame, et puhveris oleks alati mingi andmemahu hulk, on mõistlik seda valemit kohandada.

Kontrollküsitlus

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- siin on õigem teha * current_setting('block_size')::bigint, aga kes muudab ploki suurust?..
, 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

Me ei saa ette teada, kui palju paralleelsed päringud meid segavad — kui palju täpselt on andmeid, mis on „aegunud” alates selle käivitamisest. Seetõttu, kui me otsustame mingil viisil tabelit töödelda, tuleks kõigepealt selle peal teha VACUUM — erinevalt VACUUM FULL'ist ei sega see paralleelseid protsesse andmete lugemise ja kirjutamisega.

Lisaks võib see korraga eemaldada suure osa sellest, mida me tahaksime kõrvaldada. Ja järgmised päringud sellele tabelile tulevad meil „kuumast vahemälust”,mis vähendab nende kestust — ja seetõttu ka teenindava tehingu teiste päringute koguaega.

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

Katsume üle — kas tabelis on üldse midagi:

TABLE tbl LIMIT 1;

Kui andmeid pole jäänud, saame me töötluses märkimisväärselt kokku hoida — lihtsalt täites TRUNCATE:

See toimib nagu tingimusteta DELETE käsk iga tabeli jaoks, kuid palju kiiremini, kuna see ei skaneeri tegelikult tabeleid. Veelgi enam, see vabastab kohe kettaruumid, nii et pärast seda VACUUM'i teostamine ei ole vajalik.

Kas teil on sel juhul vaja tabeli järjestuse loenduri lähtestamist (RESTART IDENTITY) — otsustage ise.

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

Kuna töötame kõrge konkurentsi tingimustes, siis seni, kui kontrollime, et tabelis pole kirjeid, on keegi juba midagi sinna võinud kirjutada. Me ei tohi seda teavet kaotada, seega — mis? Õige, peame tagama, et keegi ei saaks midagi kirjutada.

Selleks peame sisse lülitama SERIALIZABLE-isolatsiooni meie tehingule (jah, siin me alustame tehingut) ja lukustama tabeli „igaveseks“:

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

Just see lukustusaste on tingitud neist toimingutest, mida me soovime selle üle teha.

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

Me tuleme siia ja tahame tabelit „lukustada“ — aga kui keegi on sel hetkel juba aktiivne, näiteks loeb seda? Me „hangume“ oodates selle lukustuse vabastamist, ja teised soovijad jäävad meisse kinni…

Kuna seda ei juhtuks, „ohverdame end“ — kui me ei suuda lühikese (lubatud väikese) ajavahemiku jooksul lukustust siiski saada, saame andmebaasilt erandi, kuid vähemalt ei sega me teisi liialt.

Selleks seame sessioonimuutuja lock_timeout (versioonide 9.3+ jaoks) või/ja statement_timeout. Peamine on meeles pidada, et statement_timeout väärtus kehtib ainult järgnevate käskude puhul. See tähendab, et niimoodi koos — ei tööta:

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

Kuna me ei soovi hiljem "vana" väärtuse taastamisega tegelda, kasutame vormi SET LOCAL, mis piirab seadistuse ulatust jooksva tehingu raames.

Peame meeles, et statement_timeout kehtib kõigi edasiste päringute kohta, et tehing ei saaks venida vastuvõetamatuteks suurusteks, kui tabelis on palju andmeid.

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

Kui tabel ei ole täiesti tühi — andmed tuleb salvestada abiliste ajutise tabeli kaudu:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatuur ON COMMIT DROP tähendab, et tehingu lõppemisel lakkab ajutine tabel olemast, ja seda ei pea käsitsi kustutama seose kontekstis.

Kuna eeldame, et "elavaid" andmeid ei ole palju, peaks see operatsioon läbima piisavalt kiiresti.

Noh, tundub, et see on kõik! Ärge unustage pärast tehingu lõpetamist käivitada ANALYZE statistika tabeli normaliseerimiseks, kui see on vajalik.

Kogume lõppscripti

Kasutame sellist 'vale-Pythonit':

# собираем статистику с таблицы
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;

Kas andmeid võib teist korda mitte kopeerida?Põhimõtteliselt jah, kui tabeli oid ei ole seotud teiste BL või DB poolelt FK tegevustega:

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

Käivitame skripti algse tabeli peal ja kontrollime meetrikaid:

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

Kõik läks hästi! Tabel vähenes 50 korda, ja kõik UPDATE'id töötavad taas kiiresti.

Allikas: habr.com

Kui VACUUM ei tööta — puhastame tabeli käsitsi