ProHoster > Blog > administration > Når VACUUM fejler, renser vi bordet manuelt

Når VACUUM fejler, renser vi bordet manuelt

VACUUM kan "rydde op" fra en tabel i PostgreSQL kun hvad ingen kan se - det vil sige, at der ikke er en eneste aktiv anmodning, der startede før disse poster blev ændret.

Men hvad hvis en sådan ubehagelig type (langvarig OLAP-belastning på en OLTP-database) stadig eksisterer? Hvordan rent aktivt puslebord omgivet af lange forespørgsler og ikke træde på en rake?

Når VACUUM fejler, renser vi bordet manuelt

Udfoldning af riven

Lad os først finde ud af, hvad problemet er, vi ønsker at løse, og hvordan det kan opstå.

Normalt sker denne situation på et relativt lille bord, men hvor det forekommer en masse ændringer. Normalt dette eller anderledes målere/aggregater/vurderinger, hvor UPDATE ofte udføres, eller buffer-kø at behandle en eller anden konstant igangværende strøm af hændelser, hvoraf optegnelser konstant er INSERT/DELETE.

Lad os prøve at gengive muligheden med vurderinger:

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;

Og sideløbende, i en anden forbindelse, starter en lang, lang anmodning, der samler nogle komplekse statistikker, men ikke påvirker vores bord:

SELECT pg_sleep(10000);

Nu opdaterer vi værdien af ​​en af ​​tællerne mange, mange gange. For renheden af ​​eksperimentet, lad os gøre dette i separate transaktioner ved hjælp af dblinkhvordan det vil ske 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 endda for den enkleste OPDATERING af en enkelt post udførelsestiden forringet med 7 gange — fra 0.524 ms til 3.808 ms? Og vores rating bygger langsommere og langsommere.

Det hele er MVCC's skyld.

Det handler om MVCC mekanisme, hvilket får forespørgslen til at gennemse alle tidligere versioner af posten. Så lad os rense vores bord fra "døde" versioner:

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

Åh, der er ikke noget at gøre rent! Parallel Den kørende anmodning forstyrrer os - han kan jo en dag have lyst til at vende sig til disse versioner (hvad nu hvis?), og de burde være tilgængelige for ham. Og derfor vil selv VACUUM FULL ikke hjælpe os.

"Skjuler sammen" bordet

Men vi ved med sikkerhed, at den forespørgsel ikke har brug for vores tabel. Derfor vil vi stadig forsøge at bringe systemets ydeevne tilbage til passende grænser ved at fjerne alt unødvendigt fra tabellen - i det mindste "manuelt", da VACUUM giver efter.

For at gøre det mere klart, lad os se på eksemplet med tilfældet med en buffertabel. Det vil sige, at der er et stort flow af INSERT/DELETE, og nogle gange er bordet helt tomt. Men hvis det ikke er tomt, må vi det gemme dets nuværende indhold.

#0: Vurderer situationen

Det er klart, at du kan prøve at gøre noget med tabellen selv efter hver operation, men det giver ikke meget mening - vedligeholdelsesomkostningerne vil klart være større end gennemløbet af målforespørgslerne.

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

  • VACUUM blev lanceret for ganske lang tid siden
    Vi forventer et tungt læs, så lad det være 60 sekunder siden sidste [auto]VAKUUM.
  • fysisk bordstørrelse er større end målet
    Lad os definere det som dobbelt så mange sider (8KB blokke) i forhold til minimumsstørrelsen - 1 blk for bunke + 1 blk for hvert indeks - for et potentielt tomt bord. Hvis vi forventer, at en vis mængde data altid vil forblive i bufferen "normalt", er det rimeligt at justere denne formel.

Bekræftelsesanmodning

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

Vi kan ikke på forhånd vide, om en parallel forespørgsel i væsentlig grad forstyrrer os - præcis hvor mange poster, der er blevet "forældede", siden den begyndte. Derfor, når vi beslutter os for at behandle tabellen på en eller anden måde, skal vi under alle omstændigheder først udføre på den VACUUM - i modsætning til VACUUM FULL, forstyrrer det ikke parallelle processer, der arbejder med læse-skrive-data.

Samtidig kan den med det samme rense det meste af det, vi gerne vil fjerne. Ja, og efterfølgende forespørgsler på denne tabel vil gå til os af "hot cache", hvilket vil reducere deres varighed - og dermed den samlede tid for blokering af andre ved vores servicetransaktion.

#2: Er der nogen hjemme?

Lad os tjekke, om der overhovedet er noget i tabellen:

TABLE tbl LIMIT 1;

Hvis der ikke er en eneste post tilbage, så kan vi spare meget på behandlingen ved blot at gøre det afkorte:

Den fungerer på samme måde som en ubetinget DELETE-kommando for hver tabel, men er meget hurtigere, da den faktisk ikke scanner tabellerne. Desuden frigør det øjeblikkeligt diskplads, så der er ingen grund til at udføre en VACUUM operation bagefter.

Om du skal nulstille tabelsekvenstælleren (GENSTART IDENTITET) er op til dig at afgøre.

#3: Alle – skiftes til!

Da vi arbejder i et stærkt konkurrencepræget miljø, mens vi er her og tjekker, at der ikke er nogen poster i tabellen, kunne nogen allerede have skrevet noget der. Vi bør ikke miste disse oplysninger, hvad så? Det er rigtigt, vi skal sørge for, at ingen kan skrive det ned med sikkerhed.

For at gøre dette skal vi aktivere SERIALISERbar-isolation for vores transaktion (ja, her starter vi en transaktion) og lås bordet "tæt":

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

Dette blokeringsniveau bestemmes af de operationer, vi ønsker at udføre på det.

#4: Interessekonflikt

Vi kommer her og vil "låse" skiltet - hvad nu hvis nogen var aktive på det i det øjeblik, for eksempel læste fra det? Vi vil "hænge" og vente på, at denne blok bliver frigivet, og andre, der vil læse, vil løbe ind i os...

For at forhindre dette i at ske, vil vi "ofre os selv" - hvis vi ikke var i stand til at opnå en lås inden for en vis (acceptabel kort) tid, så vil vi modtage en undtagelse fra basen, men vi vil i det mindste ikke blande os for meget med andre.

For at gøre dette skal du indstille sessionsvariablen lock_timeout (for version 9.3+) eller/og statement_timeout. Det vigtigste at huske er, at statement_timeout-værdien kun gælder fra den næste sætning. Det vil sige sådan i limning - vil ikke virke: 

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

For ikke at skulle beskæftige os med at genoprette den "gamle" værdi af variablen senere, bruger vi formularen SÆT LOKAL, hvilket begrænser omfanget af indstillingen til den aktuelle transaktion.

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

#5: Kopier data

Hvis tabellen ikke er helt tom, skal dataene gemmes igen ved hjælp af en midlertidig hjælpetabel:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Underskrift PÅ KOMMIT DROP betyder, at i det øjeblik transaktionen slutter, vil den midlertidige tabel ophøre med at eksistere, og der er ingen grund til manuelt at slette den i forbindelseskonteksten.

Da vi antager, at der ikke er mange "live" data, bør denne operation foregå ret hurtigt.

Nå, det er alt! Glem ikke efter at have gennemført transaktionen køre ANALYSE at normalisere tabelstatistikker, hvis det er nødvendigt.

Sammensætning af det endelige manuskript

Vi bruger denne "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;

Er det muligt ikke at kopiere dataene en anden gang?I princippet er det muligt, hvis selve bordets oid ikke er bundet til andre aktiviteter fra BL-siden eller FK fra DB-siden:

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

Lad os køre scriptet på kildetabellen og tjekke 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

Alt fungerede! Tabellen er skrumpet med 50 gange, og alle OPDATERINGER kører hurtigt igen.

Kilde: www.habr.com

Tilføj en kommentar