🥇Kun VACUUM epäonnistuu, puhdistamme pöydän manuaalisesti

VACUUM voi "siivota" PostgreSQL:n taulukosta vain mitä kukaan ei näe - eli ei ole yhtään aktiivista pyyntöä, joka olisi alkanut ennen näiden tietueiden muuttamista.

Mutta entä jos tällainen epämiellyttävä tyyppi (pitkäaikainen OLAP-kuorma OLTP-tietokannassa) on edelleen olemassa? Miten puhdista aktiivisesti hoitopöytä pitkien kyselyjen ympäröimänä etkä astu haravan päälle?

Haravan laskeminen

Ensin selvitetään, mikä ongelma haluamme ratkaista ja miten se voi syntyä.

Yleensä tämä tilanne tapahtuu suhteellisen pienellä pöydällä, mutta jossa se tapahtuu paljon muutoksia. Yleensä tämä tai erilainen metriä/aggregaatit/luokitukset, jossa UPDATE suoritetaan usein, tai puskuri-jono prosessoida jatkuvasti käynnissä olevia tapahtumia, joiden tietueita INSERT/DELETE jatkuvasti.

Yritetään toistaa vaihtoehto luokituksilla:

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 samanaikaisesti, toisessa yhteydessä, alkaa pitkä, pitkä pyyntö, joka kerää monimutkaisia tilastoja, mutta ei vaikuta pöytäämme:

SELECT pg_sleep(10000);

Nyt päivitämme yhden laskurin arvon monta, monta kertaa. Tehdään tämä kokeen puhtauden vuoksi erillisissä tapahtumissa dblinkin avullamiten se tapahtuu todellisuudessa:

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

Mitä tapahtui? Miksi edes yksittäisen tietueen yksinkertaisin PÄIVITYS suoritusaika heikkeni 7 kertaa - 0.524 ms - 3.808 ms? Ja arvosanamme muodostuu yhä hitaammin.

Kaikki on MVCC:n syytä.

Kyse on kaikesta MVCC mekanismi, mikä saa kyselyn tarkistamaan merkinnän kaikki aiemmat versiot. Joten siivotaan pöytämme "kuolleista" versioista:

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

Voi, ei ole mitään siivottavaa! Rinnakkainen Käynnissä oleva pyyntö häiritsee meitä - loppujen lopuksi hän saattaa joskus haluta kääntyä näiden versioiden puoleen (entä jos?), ja niiden pitäisi olla hänen käytettävissään. Ja siksi edes VACUUM FULL ei auta meitä.

Pöydän "luottaminen".

Mutta tiedämme varmasti, että tämä kysely ei tarvitse taulukkoamme. Siksi yritämme silti palauttaa järjestelmän suorituskyvyn riittäviin rajoihin poistamalla taulukosta kaiken tarpeettoman - ainakin "manuaalisesti", koska VACUUM luovuttaa.

Selvyyden vuoksi katsotaanpa esimerkkiä puskuritaulukon tapauksesta. Toisin sanoen INSERT/DELETE-toimintoja on paljon, ja joskus taulukko on täysin tyhjä. Mutta jos se ei ole tyhjä, meidän täytyy tallentaa sen nykyisen sisällön.

#0: Arvioi tilannetta

On selvää, että voit yrittää tehdä jotain taulukon kanssa myös jokaisen toimenpiteen jälkeen, mutta tässä ei ole paljon järkeä - ylläpitokustannukset ovat selvästi suurempia kuin kohdekyselyiden läpimenokyky.

Muotoillaan kriteerit - "on aika toimia", jos:

VACUUM lanseerattiin jo kauan sitten
Odotamme raskasta kuormaa, joten anna sen olla 60 sekuntia edellisen [auto]VACUUMin jälkeen.
fyysinen taulukon koko on suurempi kuin tavoite
Määritetään se kaksinkertaiseksi sivumääräksi (8 kt:n lohkot) suhteessa vähimmäiskokoon - 1 blk kasasta + 1 blk jokaisesta indeksistä - mahdollisesti tyhjälle pöydälle. Jos oletamme, että tietty määrä dataa jää aina puskuriin "normaalisti", on järkevää säätää tätä kaavaa.

Vahvistuspyyntö

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: Edelleen tyhjiö

Emme voi tietää etukäteen, häiritseekö rinnakkaiskysely meitä merkittävästi - kuinka monta tietuetta on "vanhentunut" sen alkamisen jälkeen. Siksi, kun päätämme jotenkin käsitellä taulukkoa, meidän tulee joka tapauksessa ensin suorittaa se VACUUM - toisin kuin VACUUM FULL, se ei häiritse rinnakkaisia prosesseja, jotka työskentelevät luku-kirjoitusdatan kanssa.

Samalla se voi välittömästi puhdistaa suurimman osan siitä, mitä haluaisimme poistaa. Kyllä, ja tämän taulukon myöhemmät kyselyt menevät meille "hot cache", mikä lyhentää niiden kestoa - ja siten kokonaisaikaa, jonka aikana huoltotapahtumamme estää muita.

#2: Onko kukaan kotona?

Katsotaan, onko taulukossa ylipäänsä mitään:

TABLE tbl LIMIT 1;

Jos yhtään tietuetta ei ole jäljellä, voimme säästää paljon käsittelyssä yksinkertaisesti tekemällä KATKAISTA:

Se toimii samalla tavalla kuin ehdoton DELETE-komento jokaiselle taulukolle, mutta on paljon nopeampi, koska se ei itse tarkista taulukoita. Lisäksi se vapauttaa välittömästi levytilaa, joten tyhjiötoimintoa ei tarvitse suorittaa jälkeenpäin.

Päätät, tarvitseeko sinun nollata taulukon sekvenssilaskuri (RESTART IDENTITY).

#3: Kaikki - vuorotellen!

Koska työskentelemme erittäin kilpaillussa ympäristössä, kun olemme täällä tarkistamassa, ettei taulukossa ole merkintöjä, joku olisi voinut jo kirjoittaa jotain sinne. Meidän ei pitäisi hukata tätä tietoa, entä sitten? Aivan oikein, meidän on varmistettava, ettei kukaan voi kirjoittaa sitä varmasti muistiin.

Tätä varten meidän on otettava käyttöön SARJOITTAVA-tapahtumamme eristäminen (kyllä, tässä aloitamme tapahtuman) ja lukitse taulukko "tiukasti":

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

Tämän eston tason määräävät toiminnot, jotka haluamme suorittaa sille.

#4: Eturistiriita

Tulemme tänne ja haluamme "lukita" kyltin - entä jos joku olisi sillä hetkellä aktiivinen, esimerkiksi lukemassa sitä? "Joudumme" odottamaan tämän lohkon julkaisua, ja muut, jotka haluavat lukea, törmäävät meihin...

Tämän estämiseksi "uhraamme itsemme" - jos emme saaneet lukkoa tietyssä (hyväksyttävässä) ajassa, saamme tukikohdan poikkeuksen, mutta emme ainakaan häiritse liikaa muut.

Voit tehdä tämän asettamalla istuntomuuttujan lock_timeout (versioille 9.3+) tai/ja lausunto_aikakatkaisu. Tärkeintä on muistaa, että lauseke_aikakatkaisu-arvo on voimassa vain seuraavasta käskystä alkaen. Eli näin liimauksessa - ei toimi:

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

Jotta muuttujan "vanhan" arvon palauttamista ei tarvitse tehdä myöhemmin, käytämme muotoa SET PAIKALLINEN, joka rajoittaa asetuksen laajuuden nykyiseen tapahtumaan.

Muistamme, että lauseke_aikakatkaisu koskee kaikkia myöhempiä pyyntöjä, jotta tapahtuma ei voi venyä kelpaamattomiin arvoihin, jos taulukossa on paljon dataa.

#5: Kopioi tiedot

Jos taulukko ei ole täysin tyhjä, tiedot on tallennettava uudelleen väliaikaisen aputaulukon avulla:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Allekirjoitus SITOUMUKSESTA tarkoittaa, että tapahtuman päättyessä väliaikainen taulukko lakkaa olemasta, eikä sitä tarvitse manuaalisesti poistaa yhteyskontekstissa.

Koska oletamme, että "elävää" dataa ei ole paljon, tämän toimenpiteen pitäisi tapahtua melko nopeasti.

No, siinä kaikki! Älä unohda tapahtuman suorittamisen jälkeen suorita ANALYSE taulukkotilastojen normalisoimiseksi tarvittaessa.

Lopullisen käsikirjoituksen kokoaminen

Käytämme tätä "pseudo-pythonia":

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

Onko mahdollista olla kopioimatta tietoja toisen kerran?Periaatteessa se on mahdollista, jos itse taulukon oidi ei ole sidottu muihin aktiviteetteihin BL-puolelta tai FK:hen DB-puolelta:

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

Suoritetaan komentosarja lähdetaulukossa ja tarkistetaan mittarit:

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

Kaikki sujui! Taulukko on kutistunut 50 kertaa ja kaikki PÄIVITYKSET toimivat taas nopeasti.

Lähde: will.com

Kun VACUUM epäonnistuu, puhdistamme pöydän manuaalisesti