🥇Når VACUUM synger på sidste vers — rydder vi tabellen manuelt

VACUUM kan "rydde" fra tabellen i PostgreSQL kun det, som ingen kan se — det vil sige der ikke er nogen aktive forespørgsler, der startede før disse poster blev ændret.

Og hvis der alligevel er en ubehagelig type (langvarig OLAP-belastning på OLTP-databasen)? Hvordan rydde en aktivt ændrende tabel i en situation med langvarige forespørgsler og ikke træde i strå.

Lad os lægge stråene ud

Først definerer vi, hvad problemet består i, og hvordan det overhovedet kan opstå, som vi ønsker at løse.

Sådanne situationer opstår normalt på en relativt lille tabel, men hvor der sker meget mange ændringer. Dette er normalt enten forskellige tællere/aggregater/rangeringer, på hvilke der ofte foretages UPDATE, eller en buffer-kø til behandling af en konstant strøm af begivenheder, hvis poster hele tiden INSERT/DELETE.

Lad os forsøge at genskabe et scenarie med rangeringen:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- vi bygger rangeringen på denne indeks

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

Og parallelt, i en anden forbindelse, starter en langvarig forespørgsel, der indsamler en eller anden kompleks statistik, men berører ikke vores tabel:

SELECT pg_sleep(10000);

Nu opdaterer vi rigtig mange gange værdien af en af tællerne. For eksperiments renhed gør vi dette i separate transaktioner ved hjælp af dblink, som det ville foregå i virkeligheden:

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

Hvad skete der? Hvorfor er tiden for selv den simpleste UPDATE af en eneste post forringet syv gange — fra 0.524 ms til 3.808 ms? Og vores rangering opbygges stadig langsommere.

Alt skyldes MVCC.

Det handler om MVCC-mekanismen., der får forespørgslen til at gennemgå alle tidligere versioner af posten. Så lad os rydde op i vores tabel for "døde" versioner:

VACUUM VERBIS tbl;

INFO:  vakuumerer "public.tbl"
INFO:  "tbl": fandt 0 fjernbare, 10026 ikke-fjernbare rækkeversioner i 45 ud af 45 sider
DETAIL:  10000 døde rækkeversioner kan endnu ikke fjernes, ældste xmin: 597439602

Åh, der er ikke noget at rydde op i! Samtidig forstyrrer den kørende forespørgsel os — for den kan en dag have brug for at henvise til disse versioner (hvad nu hvis?), og de skal være tilgængelige for den. Og derfor hjælper selv VACUUM FULL os ikke.

«Vi presser sammen» tabellen

Men vi ved bestemt, at denne forespørgsel ikke har brug for vores tabel. Derfor vil vi alligevel forsøge at genoprette systemets ydeevne til acceptable niveauer, ved at fjerne alt unødvendigt fra tabellen — i det mindste "manuelt", da VACUUM opgiver.

For at det skal være tydeligt, lad os betragte et eksempel med en buffer-tabel. Det vil sige, der er en stor strøm af INSERT/DELETE, og nogle gange er tabellen helt tom. Men hvis den ikke er tom, skal vi bevare dens nuværende indhold.

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

Det er klart, at man kan forsøge at gøre noget med tabellen efter hver operation, men det giver ikke meget mening — omkostningerne ved vedligeholdelse vil klart være større end kapaciteten for målrettede forespørgsler.

Lad os formulere kriterierne — "det er tid til at handle", hvis:

VACUUM blev startet for længe siden
Vi forventer høj belastning, så lad det være 60 sekunder siden den sidste [auto]VACUUM.
den fysiske størrelse af tabellen er større end målet
Definer det som det dobbelte antal sider (blokke på 8KB) i forhold til den minimale størrelse — 1 blk på heap + 1 blk for hver af indeksene — for en potentielt tom tabel. Hvis vi forventer, at der i bufferområdet altid vil være et vist volumen data, er det fornuftigt at justere denne formel.

Kontrolforespørgsel

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- her ville det være mere korrekt at gøre * current_setting('block_size')::bigint, men hvem ændrer blokstørrelsen?..
, 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

Vi kan ikke på forhånd vide, hvor meget vores parallelle forespørgsel forstyrrer os - hvor mange plader der er 'forældede' siden dens start. Derfor, når vi endelig beslutter at bearbejde tabellen på en eller anden måde, er det altid bedst først at udføre VACUUM — i modsætning til VACUUM FULL, forstyrrer den ikke de parallelle processer, når de arbejder med data til læsning og skrivning.

Samtidig kan den straks rense det meste af det, vi gerne vil fjerne. Og de efterfølgende forespørgsler til denne tabel vil være i 'den varme cache', hvilket vil forkorte deres varighed - og dermed også den samlede tid til at blokere andre transaktioner, der understøttes af os.

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

Lad os tjekke - er der overhovedet noget i tabellen:

TABLE tbl LIMIT 1;

Hvis der ikke er nogen poster tilbage, kan vi spare betydeligt på behandlingen - blot ved at udføre TRUNCATE:

Den fungerer på samme måde som en ubetinget DELETE-kommando for hver tabel, men meget hurtigere, da den faktisk ikke scanner tabellerne. Derudover frigiver den straks diskplads, så det er ikke nødvendigt at udføre VACUUM efter den.

Om du skal nulstille sekvens tælleren for tabellen (RESTART IDENTITY) - beslut selv.

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

Da vi arbejder under forhold med høj konkurrence, kan nogen have skrevet noget i tabellen, mens vi tjekker for mangel på poster. Vi må ikke miste denne information, så hvad? Rigtigt, vi skal sørge for, at ingen kan skrive.

For dette skal vi aktivere SERIALIZABLE-isolering til vores transaktion (ja, her starter vi transaktionen) og låse tabellen 'fast':

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

Netop det niveau af låsninger skyldes de operationer, vi ønsker at udføre.

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

Vi kommer her og ønsker at 'låse' tabellen - men hvad hvis nogen var aktiv på den i mellemtiden, for eksempel læste fra den? Vi vil 'hænge' og vente på, at låsningen bliver frigivet, mens andre, der ønsker at læse, vil støde ind i os...

For at undgå dette vil vi 'ofre os' - hvis vi ikke har været i stand til at få låsningen inden for en rimelig (også gerne lille) tid, vil vi få en undtagelse fra databasen, men vi vil i det mindste ikke forstyrre de andre for meget.

Til dette skal vi sætte session variablen lock_timeout (til versioner 9.3+) eller/og statement_timeoutDet vigtigste at huske er, at værdien af statement_timeout kun gælder for den følgende statement. Sådan her i sammenkædningen — virker ikke:

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

For ikke at skulle genoprette den " gamle" værdi af variablen, bruger vi formen SET LOCAL, som begrænser indvirkningen af indstillingen til den aktuelle transaktion.

Husk, at statement_timeout gælder for alle efterfølgende forespørgsler, så transaktionen ikke kan strække sig til uacceptable værdier, hvis der er mange data i tabellen.

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

Hvis tabellen viser sig ikke at være helt tom, skal dataene gemmes gennem en midlertidig hjælpetabel:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatur ON COMMIT DROP betyder, at når transaktionen afsluttes, vil den midlertidige tabel ophøre med at eksistere, og der er ikke behov for manuel sletning af den i forbindelse med forbindelsen.

Da vi forventer, at der ikke er mange "levende" data, bør denne operation forløbe relativt hurtigt.

Og det er det! Glem ikke at køre ANALYZE for at normalisere tabelstatistikken, hvis det er nødvendigt.

Samler det afsluttende script

Vi bruger sådan et "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;

Kan man undgå at kopiere data en anden gang?I princippet ja, hvis der ikke er nogen andre aktiviteter knyttet til oid for selve tabellen fra BL eller FK fra databasen:

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ører scriptet på den oprindelige tabel og tjekker metrics:

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

Det lykkedes! Tabellen blev reduceret med 50 gange, og alle UPDATE-funktioner kører igen hurtigt.

Kilde: habr.com

Når VACUUM giver op — rengør tabel manuelt