🥇Kui VACUUM ei jõua — puhastame tabelit käsitsi

VACUUM võib PostgreSQL-ist tabelist "puhastada" ainult need, mis keegi ei näe — see tähendab, et ei ole ühtegi aktiivset päringut, mis oleks alustatud enne, kui need kirjed muudeti.

Aga mis juhtub, kui selline ebameeldiv tüüp (kestuseks OLAP-koormus OLTP-andmebaasis) on olemas? Kuidas puhastada aktiivselt muudetavat tabelit pikade päringute keskkonnas ja mitte trapida?

Lahendame probleemid

Esmalt määratleme, mis probleem meil on ja kuidas see võib üldse tekkida, mida me tahame lahendada.

Tavaliselt toimub selline olukord suhteliselt väikeses tabelis, kuid kus toimub väga palju muudatusi. Tavaliselt on need kas erinevad loendurid/aggregaadid/hinnangud, mille kohta tehakse pidevalt UPDATE, või puhvri-queue kuskilt pideva sündmuste voogude töötlemiseks, kus salvestused on pidevalt INSERT/DELETE.

Proovime luua olukorra koos hinnangutega:

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, käivitub pikk-pikk päring, mis kogub mingit keerulist statistikat, kuid ei puuduta meie tabelit:

SELECT pg_sleep(10000);

Nüüd uuendame korduvalt ühe loenduri väärtust. Katse puhtuse nimel teeme seda erinevates tehingutes dblink'i kaudu, 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;

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

Mis aga juhtus? Miks isegi kõige lihtsama UPDATE’i puhul ainukese kirje jaoks täitminete aeg halvenes 7 korda — 0.524ms-lt 3.808ms-ni? Ja meie hinnang muutub aina aeglasemaks.

Süüdistada tuleb MVCC-d

Küsimus on MVCC mehhanismis, mis paneb päringu vaatama kõiki varasemaid kirje versioone. Nii et puhastame meie tabelist "surnud" versioonid:

VACUUM VERBOSE tbl;

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

Oi, ja puhastamiseks ei olegi midagi! Samal ajal töötav päring segab meid — kuna see võib kunagi soovida neid versioone kasutada (igal juhul?), peavad nad olema talle kättesaadavad. Ja seetõttu isegi VACUUM FULL ei aita meid.

„Koonime” tabeli kokku

Aga me teame, et see päring ei vaja meie tabelit. Seetõttu proovime ikkagi taastada süsteemi jõudlust, visates tabelist kõik tarbetu välja — isegi "käsitsi", kuna VACUUM loobub.

Selle paremaks visualiseerimiseks vaatame näidet puhvertabelist. St toimub suur INSERT/DELETE voog ja vahel on tabel üldse tühi. Kuid kui seal ei ole tühi, peame s Säilitama selle praeguse sisu.

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

On selge, et saab proovida midagi tabeliga teha isegi pärast iga toimingut, kuid see ei ole suure ülejäänud mõttega — hoolduskulud on selgelt suuremad kui sihtpäringute läbilaskevõime.

Formuleerime kriteeriumid — „on juba aeg tegutseda”, kui:

VACUUM käivitatud piisavalt kaua tagasi
Ootame suurt koormust, seega olgu see 60 sekundit alates [auto]VACUUM.
tabeli füüsiline suurus on suurem kui siht
Määratleme selle kui kahekordse lehekülgede (8KB plokkide) arvu minimaalsest suurusest — 1 blk heap'is + 1 blk iga indeksi kohta — potentsiaalselt-tühjale tabelile. Kui aga ootame, et puhver "tavaliselt" sisaldab alati teatud mahus andmeid, on mõistlik seda valemit veel timmida.

Kontrolli päring

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- siin oleks õ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 paralleelne päring meid takistab - kui palju andmeid on alates selle algusest "vananenud". Seega, kui me siiski otsustame tabelit kuidagi töödelda, siis tasub kõigepealt sellel VACUUM — erinevalt VACUUM FULL-st, ei takista see paralleelseid protsesse andmete lugemist ja kirjutamist.

Ühtlasi suudab see kohe eemaldada suure osa nendest, mida me tahaksime eemaldada. Ja edasised päringud selle tabeli osas toimuvad meil "kuumalt vahemälust", mis lühendab nende kestust - ja seega ka koguaegset blokeerimise aega teiste meie teenindavate tehingute jaoks.

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

Kontrollime, kas tabelis on üldse midagi:

TABLE tbl LIMIT 1;

Kui ühtegi kirjet ei ole alles, saame töötlusel palju kokku hoida - lihtsalt tehes TRUNCATE:

See toimib nagu tingimusteta DELETE käsk iga tabeli jaoks, kuid palju kiiremini, kuna see ei skaneeri tabeleid. Veelgi enam, see vabastab kohe kettaruumi, seega ei ole pärast selle teostamist VACUUM'i tegemine vajalik.

Kas peate selle juures tabeleid järjestamise loenduri (RESTART IDENTITY) nullima - otsustage ise.

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

Kuna töötame kõrge konkurentsikeskkonna tingimustes, siis samas kui me kontrollime tabelis kirjete puudumist, on keegi juba midagi sinna kirjutanud. Me ei tohi seda teavet kaotada, seega - mis? Õige, peame tegema nii, et keegi ei saaks kirjutada.

Selleks peame me sisse lülitama SERIALIZABLE-isolatsiooni meie tehingule (jah, siin me alustame tehingut) ja blokeerima tabeli "läbi viimseni":

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

Sellise blokeeringu tase on tingitud neist toimingutest, mida me tahame selle üle teha.

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

Me tuleme siia ja tahame tabelit "luku panna" - aga kui sel hetkel keegi selles juba aktiivne oli, näiteks luges seda? Me "ripume" oodates selle blokeeringu vabastamist, samas kui teised, kes soovivad lugeda, takerduvad meie juurde...

Kuna seda ei juhtuks, "toome ohvriks" - kui me ei saa teatud (lubatavalt väikese) aja jooksul blokeeringut kätte, saame andmebaasilt erandi, kuid vähemalt ei takista me liiga palju teisi.

Selleks seadistame sessiooni muutuja lock_timeout (versioonides 9.3+) või / ja statement_timeoutPeaegu meeles pidada, et statement_timeout väärtus kehtib ainult järgmise lause puhul. See tähendab, et nii ei tööta liites — see ei tööta:

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

Kuna ei ole vaja hiljem taastada vana väärtust, kasutame vormi SET LOCAL, mis piirab seadistamise ulatust praeguse tehinguga.

Peame meeles, et statement_timeout hõlmab kõiki järgnevaid päringuid, et tehing ei veniks vastuvõetamatutes suurustes, kui tabelis osutub palju andmeid.

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

Kui tabel osutub mitte täiesti tühjaks — andmed tuleb salvestada abistava ajutise tabelina:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatuur ON COMMIT DROP tähendab, et tehingu lõppedes ajutine tabel lakkab olemast, ja ei ole vaja seda käsitsi kustutada seoses ühendusega.

Kuna eeldame, et elusate andmete ei ole väga palju, siis peaks see operatsioon toimuma piisavalt kiiresti.

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

Kogume lõplikku skripti

Kasutame sellist „pseudopythoni“:

# собираем статистику с таблицы
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 on võimalik andmeid teist korda mitte kopeerida?Põhimõtteliselt on see võimalik, kui tabeli oid ei sõltu muudest tegevustest BLa või FK poolelt DB-st:

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 algses tabelis 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 korda! Tabel vähenes 50 korda ja kõik UPDATE-d töötavad taas kiiresti.

Allikas: habr.com

Kui VACUUM kõrvaldab — puhastame tabeli käsitsi