🥇Kui VACUUM ebaõnnestub, puhastame laua käsitsi

VAKUUM saab PostgreSQL-i tabelist "koristada" ainult mida keegi ei näe - see tähendab, et pole ühtegi aktiivset päringut, mis oleks alanud enne nende kirjete muutmist.

Aga mis siis, kui selline ebameeldiv tüüp (pikaajaline OLAP-i koormus OLTP-andmebaasis) siiski eksisteerib? Kuidas puhastage aktiivselt mähkimislaud ümbritsetud pikkadest päringutest ega astu reha otsa?

Reha välja panemine

Esiteks teeme kindlaks, mis on probleem, mida tahame lahendada ja kuidas see võib tekkida.

Tavaliselt selline olukord juhtub suhteliselt väikesel laual, kuid milles see esineb palju muutusi. Tavaliselt see või erinev meetrid/agregaadid/reitingud, millel sageli käivitatakse UPDATE või puhver-järjekord töödelda mõnda pidevalt käimasolevat sündmuste voogu, mille kirjeid pidevalt INSERT/DELETE toimub.

Proovime reprodutseerida valikut koos hinnangutega:

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;

Ja paralleelselt ühes teises seoses algab pikk-pikk päring, mis kogub mingit keerulist statistikat, aga ei mõjuta meie lauda:

SELECT pg_sleep(10000);

Nüüd värskendame ühe loenduri väärtust mitu korda. Katse puhtuse huvides teeme seda eraldi tehingutes dblinki abilkuidas see tegelikkuses juhtub:

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 juhtus? Miks isegi ühe kirje kõige lihtsama värskenduse jaoks täitmisaeg vähenes 7 korda - 0.524 ms kuni 3.808 ms? Ja meie reiting kujuneb üha aeglasemalt.

See kõik on MVCC süü.

See kõik on umbes MVCC mehhanism, mis paneb päringu läbi vaatama kõik kirje varasemad versioonid. Nii et puhastame oma laua "surnud" versioonidest:

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, pole midagi puhastada! Paralleelselt Töötav päring segab meid - lõppude lõpuks võib ta kunagi soovida nende versioonide poole pöörduda (mis siis, kui?) ja need peaksid olema talle kättesaadavad. Ja seetõttu ei aita meid isegi VACUUM FULL.

Tabeli "kokkuvarisemine".

Kuid me teame kindlalt, et see päring ei vaja meie tabelit. Seetõttu proovime ikkagi süsteemi jõudlust piisavatele piiridele viia, eemaldades tabelist kõik ebavajaliku - vähemalt "käsitsi", kuna VACUUM annab järele.

Et see oleks selgem, vaatame näidet puhvertabeli juhtumist. See tähendab, et INSERT/DELETE on palju ja mõnikord on tabel täiesti tühi. Aga kui see pole tühi, peame seda tegema salvestage selle praegune sisu.

#0: olukorra hindamine

On selge, et võite proovida tabeliga midagi teha ka pärast iga toimingut, kuid sellel pole erilist mõtet - hoolduse üldkulud on selgelt suuremad kui sihtpäringute läbilaskevõime.

Sõnastame kriteeriumid – “on aeg tegutseda”, kui:

VACUUM käivitati üsna kaua aega tagasi
Ootame suurt koormust, las siis olla 60 sekundit alates viimasest [auto]VAKUUMIst.
füüsilise tabeli suurus on sihtmärgist suurem
Määratleme selle kahekordse lehekülgede arvuna (8KB plokid) võrreldes minimaalse suurusega - 1 tuhk hunniku jaoks + 1 tuhk iga indeksi eest - potentsiaalselt tühja laua jaoks. Kui eeldame, et teatud hulk andmeid jääb puhvrisse alati "tavaliselt", on mõistlik seda valemit muuta.

Kinnitustaotlus

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: Ikka VAKUUM

Me ei saa ette teada, kas paralleelpäring meid oluliselt segab – kui palju kirjeid on selle algusest saati "aegunud". Seega, kui me otsustame tabelit kuidagi töödelda, peaksime igal juhul esmalt sellel käivitama VAKUUM - erinevalt VACUUM FULL-ist ei sega see paralleelsete protsesside tööd, mis töötavad lugemis-kirjutamisandmetega.

Samal ajal saab see kohe eemaldada suurema osa sellest, mida sooviksime eemaldada. Jah, ja edasised päringud selle tabeli kohta lähevad meile "kuum vahemälu" poolt, mis vähendab nende kestust – ja seega ka teiste meie teenindustehingu kaudu blokeerimise koguaega.

#2: Kas keegi on kodus?

Kontrollime, kas tabelis on üldse midagi:

TABLE tbl LIMIT 1;

Kui alles pole ühtegi kirjet, saame töötlemise pealt palju kokku hoida, lihtsalt tehes KÕRBI:

See toimib iga tabeli jaoks samamoodi nagu tingimusteta DELETE käsk, kuid on palju kiirem, kuna ta tegelikult tabeleid ei kontrolli. Pealegi vabastab see koheselt kettaruumi, mistõttu pole vaja pärast VACUUM toimingut teha.

See, kas peate tabelijärjestuse loenduri (RESTART IDENTITY) lähtestama, on teie otsustada.

#3: Kõik – kordamööda!

Kuna töötame tiheda konkurentsiga keskkonnas ja kontrollime siin, et tabelis ei oleks kirjeid, võis keegi sinna juba midagi kirjutada. Me ei tohiks seda teavet kaotada, mis siis? Täpselt nii, me peame tagama, et keegi ei saaks seda kindlalt kirja panna.

Selleks peame lubama SERIALISEERITUD- meie tehingu isoleerimine (jah, siin alustame tehingut) ja lukusta tabel "tihedalt":

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

Selle blokeerimise taseme määravad toimingud, mida me sellega teha tahame.

#4: Huvide konflikt

Me tuleme siia ja tahame sildi “lukku panna” – mis siis, kui keegi oleks sellel hetkel aktiivne, näiteks luges sealt? Jääme selle ploki vabastamist ootama “ripuma” ja teised, kes tahavad lugeda, jooksevad meile vastu...

Et seda ei juhtuks, "ohverdame ennast" - kui meil ei õnnestunud teatud (aktsepteeritavalt lühikese) aja jooksul lukku saada, siis saame baasilt erandi, kuid vähemalt ei sega me liiga palju. teised.

Selleks määrake seansi muutuja lock_timeout (versioonidele 9.3+) või/ja avaldus_ajalõpp. Peamine asi, mida meeles pidada, on see, et lause_timeout väärtus rakendub ainult järgmisest lausest. See tähendab, et liimimisel - ei tööta:

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

Selleks, et mitte hiljem tegelda muutuja “vana” väärtuse taastamisega, kasutame vormi MÄÄRA KOHALIK, mis piirab sätte ulatust praeguse tehinguga.

Peame meeles, et lause_timeout kehtib kõikidele järgnevatele päringutele, et tehing ei saaks venida vastuvõetamatute väärtusteni, kui tabelis on palju andmeid.

#5: andmete kopeerimine

Kui tabel pole täiesti tühi, tuleb andmed täiendava ajutise tabeli abil uuesti salvestada:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Allkiri KOHTUMISEL tähendab, et tehingu lõppemise hetkel ajutine tabel lakkab olemast ja seda pole vaja ühenduse kontekstis käsitsi kustutada.

Kuna eeldame, et "reaalajas" andmeid pole palju, peaks see toiming toimuma üsna kiiresti.

Noh, see on kõik! Ärge unustage pärast tehingu sooritamist käivitage ANALÜÜS et vajadusel normaliseerida tabelistatistikat.

Lõpliku stsenaariumi kokkupanek

Me kasutame seda "pseudo-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 on võimalik andmeid teist korda mitte kopeerida?Põhimõtteliselt on see võimalik, kui tabeli oid ei ole seotud ühegi teise tegevusega BL-i poolelt või FK-ga DB-poolselt:

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 lähtetabelis ja kontrollime mõõdikuid:

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 õnnestus! Tabel on kahanenud 50 korda ja kõik VÄRSKENDUSED käivad taas kiiresti.

Allikas: www.habr.com

Kui VACUUM ebaõnnestub, puhastame laua käsitsi