🥇När VAKUUM misslyckas rengör vi bordet manuellt

VAKUUM kan "städa upp" från en tabell i PostgreSQL bara vad ingen kan se - det vill säga det finns inte en enda aktiv begäran som startade innan dessa poster ändrades.

Men vad händer om en sådan obehaglig typ (långvarig OLAP-belastning på en OLTP-databas) fortfarande existerar? Hur rent aktivt skötbord omgiven av långa frågor och inte trampa på en rake?

Lägger ut rakan

Låt oss först avgöra vad problemet vi vill lösa är och hur det kan uppstå.

Vanligtvis inträffar denna situation på ett relativt litet bord, men där det förekommer många förändringar. Vanligtvis detta eller annorlunda mätare/aggregat/klasser, där UPDATE ofta körs, eller buffertkö att bearbeta någon ständigt pågående ström av händelser, vars register ständigt INSERT/DELETE.

Låt oss försöka återskapa alternativet med betyg:

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;

Och parallellt, i ett annat sammanhang, startar en lång, lång förfrågan, som samlar in lite komplex statistik, men påverkar inte vårt bord:

SELECT pg_sleep(10000);

Nu uppdaterar vi värdet på en av räknarna många, många gånger. För experimentets renhet, låt oss göra detta i separata transaktioner med dblinkhur det kommer att hända i verkligheten:

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

Vad hände? Varför ens för den enklaste UPPDATERING av en enda post exekveringstiden minskade med 7 gånger — från 0.524ms till 3.808ms? Och vårt betyg bygger allt långsammare.

Allt är MVCC:s fel.

Allt handlar om MVCC-mekanism, vilket gör att frågan går igenom alla tidigare versioner av posten. Så låt oss rensa vårt bord från "döda" 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, det finns inget att rengöra! Parallell Den löpande begäran stör oss – trots allt kanske han någon gång vill vända sig till dessa versioner (tänk om?), och de borde vara tillgängliga för honom. Och därför kommer inte ens VAKUUM FULL hjälpa oss.

"Komprimerar" bordet

Men vi vet med säkerhet att den frågan inte behöver vår tabell. Därför kommer vi fortfarande att försöka återställa systemets prestanda till adekvata gränser genom att eliminera allt onödigt från tabellen - åtminstone "manuellt", eftersom VACUUM ger efter.

För att göra det mer tydligt, låt oss titta på exemplet med fallet med en bufferttabell. Det vill säga att det är ett stort flöde av INSERT/DELETE, och ibland är tabellen helt tom. Men om det inte är tomt så måste vi spara dess nuvarande innehåll.

#0: Bedömer situationen

Det är uppenbart att du kan försöka göra något med tabellen även efter varje operation, men detta är inte så vettigt - underhållskostnaderna kommer helt klart att vara större än genomströmningen av målfrågorna.

Låt oss formulera kriterierna - "det är dags att agera" om:

VACUUM lanserades för ganska länge sedan
Vi förväntar oss en tung belastning, så låt det vara 60 sekunder sedan senaste [auto]VAKUUM.
fysisk tabellstorlek är större än målet
Låt oss definiera det som dubbelt så många sidor (8KB block) i förhållande till minimistorleken - 1 blk för hög + 1 blk för varje index - för ett potentiellt tomt bord. Om vi förväntar oss att en viss mängd data alltid kommer att finnas kvar i bufferten "normalt", är det rimligt att justera denna formel.

Begäran om verifiering

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

Vi kan inte i förväg veta om en parallell fråga stör oss avsevärt - exakt hur många poster som har blivit "inaktuella" sedan den började. Därför, när vi bestämmer oss för att på något sätt bearbeta tabellen, bör vi i alla fall först köra på den VAKUUM - till skillnad från VACUUM FULL stör den inte parallella processer som arbetar med läs-skrivdata.

Samtidigt kan den direkt rensa bort det mesta vi skulle vilja ta bort. Ja, och efterföljande frågor i den här tabellen kommer att gå till oss av "hot cache", vilket kommer att minska deras varaktighet - och därmed den totala tiden för att blockera andra av vår servicetransaktion.

#2: Är någon hemma?

Låt oss kolla om det överhuvudtaget finns något i tabellen:

TABLE tbl LIMIT 1;

Om det inte finns en enda post kvar kan vi spara mycket på bearbetningen genom att helt enkelt göra STYMPA:

Det fungerar på samma sätt som ett ovillkorligt DELETE-kommando för varje tabell, men är mycket snabbare eftersom det faktiskt inte skannar tabellerna. Dessutom frigör det omedelbart diskutrymme, så det finns inget behov av att utföra en VAKUUM-operation efteråt.

Om du behöver återställa tabellsekvensräknaren (RESTART IDENTITY) är upp till dig att bestämma.

#3: Alla – turas om!

Eftersom vi arbetar i en mycket konkurrensutsatt miljö, medan vi är här och kontrollerar att det inte finns några poster i tabellen, kan någon redan ha skrivit något där. Vi bör inte förlora denna information, så vad? Det stämmer, vi måste se till att ingen säkert kan skriva ner det.

För att göra detta måste vi aktivera SERIALISERBAR-isolering för vår transaktion (ja, här startar vi en transaktion) och lås bordet "tätt":

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

Denna nivå av blockering bestäms av de operationer som vi vill utföra på den.

#4: Intressekonflikt

Vi kommer hit och vill "låsa" skylten - tänk om någon var aktiv på den i det ögonblicket, till exempel läste från den? Vi kommer att "hänga" och vänta på att detta block ska släppas, och andra som vill läsa kommer att stöta på oss...

För att förhindra att detta händer kommer vi att "offra oss själva" - om vi inte kunde få ett lås inom en viss (acceptabelt kort) tid, kommer vi att få ett undantag från basen, men vi kommer åtminstone inte att störa för mycket med andra.

För att göra detta, ställ in sessionsvariabeln lock_timeout (för version 9.3+) eller/och statement_timeout. Det viktigaste att komma ihåg är att värdet statement_timeout endast gäller från nästa sats. Det vill säga så här i limning - kommer inte att fungera:

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

För att inte behöva ta itu med att återställa det "gamla" värdet på variabeln senare använder vi formuläret STÄLL LOKALT, vilket begränsar omfattningen av inställningen till den aktuella transaktionen.

Vi kommer ihåg att statement_timeout gäller för alla efterföljande förfrågningar så att transaktionen inte kan sträcka sig till oacceptabla värden om det finns mycket data i tabellen.

#5: Kopiera data

Om tabellen inte är helt tom måste data sparas på nytt med hjälp av en tillfällig extra tabell:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatur PÅ ÅTAGANDE DROP betyder att i det ögonblick som transaktionen avslutas kommer den temporära tabellen att upphöra att existera, och det finns inget behov av att manuellt radera den i anslutningssammanhanget.

Eftersom vi antar att det inte finns mycket "live" data, bör denna operation ske ganska snabbt.

Tja, det är allt! Glöm inte efter att transaktionen har slutförts kör ANALYSE för att normalisera tabellstatistik vid behov.

Att sätta ihop det sista manuset

Vi använder denna "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;

Är det möjligt att inte kopiera uppgifterna en andra gång?I princip är det möjligt om själva bordets oid inte är bunden till några andra aktiviteter från BL-sidan eller FK från DB-sidan:

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

Låt oss köra skriptet på källtabellen och kontrollera mätvärdena:

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

Allt löste sig! Tabellen har krympt med 50 gånger och alla UPPDATERINGAR går snabbt igen.

Källa: will.com

När VAKUUM misslyckas rengör vi bordet manuellt