🥇När VACUUM sveper bort — städar vi tabellen manuellt

VACUUM kan "städa" från tabellen i PostgreSQL endast det som ingen kan se — det vill säga att det inte finns någon aktiv förfrågan som startade innan dessa poster ändrades.

Och om det finns en obehaglig typ (långvarig OLAP-belastning på en OLTP-databas) ändå? Hur städa en aktivt förändrad tabell i en miljö med långa frågor utan att trampa på en rake?

Låt oss analysera rake-en

Först ska vi definiera vad problemet är och hur det överhuvudtaget kan uppstå, som vi vill lösa.

Vanligtvis inträffar en sådan situation på en relativt liten tabell, men där det sker många förändringar. Vanligtvis handlar det om antingen olika räknare/aggregat/betyg, där UPDATE körs väldigt ofta, eller en buffertkö för att hantera en konstant ström av händelser, vars poster hela tiden INSERT/DELETE.

Låt oss försöka reproducera ett scenario med betyg:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- vi bygger betyget på denna index

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

Och parallellt startar en långvarig fråga i en annan anslutning som samlar någon komplex statistik, men inte påverkar vår tabell:

SELECT pg_sleep(10000);

Nu uppdaterar vi många många gånger värdet av en av räknarna. För att hålla experimentet rent ska vi göra detta i separata transaktioner med hjälp av dblink, som det kommer att gå till 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 har till och med för den enklaste UPDATE av den enda posten exekveringstiden försämrats i 7 gånger — från 0.524ms till 3.808ms? Och vårt betyg byggs allt långsammare.

Det är allt MVCC:s fel

Allt handlar om MVCC-mekanismen, som tvingar förfrågan att granska alla tidigare versioner av posten. Så låt oss rensa vår tabell från "döda" versioner:

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": hittade 0 borttagbara, 10026 icke-borttagbara radversioner i 45 av 45 sidor
DETAIL:  10000 döda radversioner kan inte tas bort ännu, äldsta xmin: 597439602

Oj, det är ingenting att städa! Parallellt den körande förfrågan stör oss — för den kan komma att behöva hänvisa till dessa versioner (vad vet vi?), och de måste vara tillgängliga. Och därför hjälper inte ens VACUUM FULL oss.

Vi "sammanfogar" tabellen

Men vi vet ju precis att den förfrågan inte behöver vår tabell. Så vi ska ändå försöka återfå systemets prestanda till rimliga nivåer genom att ta bort allt onödigt från tabellen — åtminstone "manuellt", eftersom VACUUM misslyckas.

För att vara tydliga, låt oss överväga ett exempel med en buffertabell. Det vill säga, det finns ett stort flöde av INSERT/DELETE, och ibland är tabellen helt tom. Men om den inte är tom, måste vi bevara dess aktuella innehåll.

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

Det är uppenbart att man kan försöka göra något med tabellen efter varje operation, men det har inte mycket mening - overheadkostnaderna för underhåll kommer tydligt att vara större än genomströmningen av målförfrågningarna.

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

VACUUM har körts för ganska länge sedan
Vi väntar oss hög belastning, så låt oss sätta det till 60 sekunder sedan [auto]VACUUM.
den fysiska storleken av tabellen är större än målet
Vi definierar den som dubbla antalet sidor (block på 8KB) i förhållande till minimi storleken — 1 blk på heap + 1 blk på varje av indexen — för en potentiellt tom tabell. Om vi däremot förväntar oss att det kommer finnas en viss mängd data i bufferten "normalt", är det rimligt att justera denna formel.

Kontrollförfrågan

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- det skulle vara mer korrekt att göra * current_setting('block_size')::bigint, men vem ändrar blockstorlek?..
, 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 inte veta i förväg hur mycket en parallell fråga påverkar oss – hur många poster som faktiskt har "åldrats" sedan den startades. Därför, när vi väl beslutar att behandla tabellen på något sätt, bör vi först alltid utföra på den VACUUM – den, till skillnad från VACUUM FULL, hindrar parallella processer från att arbeta med data för läsning och skrivning.

Samtidigt kan den direkt rensa bort en stor del av det vi skulle vilja ta bort. Dessutom kommer de efterföljande frågorna om denna tabell att gå genom den "heta cachen", vilket minskar deras varaktighet – och därmed den totala tiden för att blockera andra transaktioner som vår hanterar.

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

Låt oss kontrollera – finns det överhuvudtaget något i tabellen:

TABLE tbl LIMIT 1;

Om det inte finns någon post kvar kan vi spara betydligt på behandlingen – helt enkelt genom att utföra TRUNCATE:

Det fungerar på samma sätt som ett ovillkorligt DELETE-kommando för varje tabell, men mycket snabbare, eftersom det faktiskt inte skannar tabellerna. Dessutom frigör det omedelbart diskutrymme, så en VACUUM-operation efter det behövs inte.

Om ni också vill återställa sekvensräknaren för tabellen (RESTART IDENTITY) – bestäm själva.

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

Eftersom vi arbetar under hög konkurrens, medan vi kontrollerar att det inte finns några poster i tabellen, kan någon redan ha skrivit något där. Vi får inte förlora denna information, vilket innebär – vad? Rätt, vi måste se till att ingen kan skriva.

För att göra detta behöver vi aktivera SERIALIZABLE-isoleringen för vår transaktion (ja, här startar vi en transaktion) och låsa tabellen "hårt":

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

Just denna låsnivå beror på de operationer vi vill utföra på den.

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

Här kommer vi och vill "låsa" tabellen – men om någon var aktiv på den vid det tillfället, till exempel läste från den? Vi "hänger" i väntan på att den här låsningen ska släppas, och andra som vill läsa kommer att fastna på oss...

För att undvika detta "offrar vi oss" – om vi inte lyckas få låsningen inom en viss (väldigt liten) tid, får vi ett undantag från databasen, men åtminstone stör vi inte mycket med andra.

För detta sätter vi en sessionvariabel lock_timeout (för versioner 9.3+) eller/och statement_timeout. Det viktigaste att komma ihåg är att värdet för statement_timeout endast används med nästa statement. Så här i kedjan — kommer det inte att fungera:

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

För att inte behöva återställa det "gamla" värdet på variabeln använder vi formen SET LOCAL, som begränsar inställningens giltighet till den aktuella transaktionen.

Vi kommer ihåg att statement_timeout gäller för alla följande förfrågningar, så att transaktionen inte kan sträcka sig till oacceptabla nivåer, om det faktiskt finns många data i tabellen.

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

Om tabellen inte är helt tom – måste data sparas igen via en hjälptabell:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Signatur ON COMMIT DROP betyder att vid slutet av transaktionen kommer den temporära tabellen att sluta existera, och det behövs ingen manuell borttagning i samband med anslutningen.

Eftersom vi förutsätter att det inte finns så många "levande" data, borde denna operation gå ganska snabbt.

Så, det var allt! Tänk på att efter att transaktionen är avslutad köra ANALYZE för att normalisera statistik i tabellen, om det behövs.

Sammanfattar det slutgiltiga skriptet

Vi använder en sådan "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 vi undvika att kopiera data en andra gång?I teorin kan vi det, om ingen annan aktivitet som BL eller FK från databasen är kopplad till oid för den aktuella tabellen:

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ör skriptet på den ursprungliga tabellen och kontrollera metrikerna:

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 fungerade! Tabellen reducerades med 50 gånger, och alla UPDATE-satser fungerar snabbt igen.

Källa: habr.com

När VACUUM misslyckas - rensar vi tabellen manuellt