Wanneer VACUUM niet helpt — tafel handmatig opschonen

VACUUM kan alleen 'schoonmaken' uit de tabel in PostgreSQL wat niemand kan zien — dat wil zeggen, er is geen enkele actieve query die is gestart voordat deze records zijn gewijzigd.

En als er zo'n vervelende situatie is (een langdurige OLAP-belasting op een OLTP-database)? Hoe een actief wijzigende tabel schonen in een omgeving met lange queries en niet in de problemen komen?

Laten we de problemen in kaart brengen

Eerst bepalen we wat het probleem precies inhoudt en hoe het überhaupt kan ontstaan.

Normaal gesproken gebeurt zo'n situatie op een relatief kleine tabel, maar waarbij er heel veel wijzigingen plaatsvinden. Dit is meestal of verschillende tellers / aggregaten / beoordelingen, waarvoor vaak UPDATE-commando's worden uitgevoerd, of een bufferwachtrij voor het verwerken van een constante stroom van gebeurtenissen, waarvan de records voortdurend INSERT / DELETE.

Laten we een scenario met beoordelingen reproduceren:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- we zullen deze index gebruiken voor de beoordeling

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

En parallel, in een andere verbinding, start een langdurige query, die enige complexe statistieken verzamelt, maar geen invloed heeft op onze tabel:

SELECT pg_sleep(10000);

Nu werken we heel vaak de waarde van een van de tellers bij. Voor de zuiverheid van het experiment zullen we dit doen in afzonderlijke transacties met behulp van dblink, zoals in de praktijk het geval zou zijn:

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

Wat is er gebeurd? Waarom is de uitvoeringstijd, zelfs voor de eenvoudigste UPDATE van de enige record gedegadeerd naar 7 keer — van 0.524ms naar 3.808ms? En onze beoordeling wordt steeds trager.

Alles is de schuld van MVCC

Het probleem ligt bij het MVCC-mechanisme, die het verzoek dwingt om alle eerdere versies van de record te bekijken. Laten we onze tabel ontdoen van 'dode' versies:

VACUUM VERBOSE tbl;

INFO:  aan het vegen "public.tbl"
INFO:  "tbl": gevonden 0 verwijderbare, 10026 niet-verwijderbare rijversies in 45 van de 45 pagina's
DETAIL:  10000 dode rijversies kunnen nog niet worden verwijderd, oudste xmin: 597439602

Oeps, er is niets om schoon te maken! Tegelijkertijd verstoort het actieve verzoek ons — want het kan ooit nodig zijn om deze versies aan te roepen (wie weet?), en ze moeten beschikbaar zijn. En daarom helpt zelfs VACUUM FULL ons niet.

Laten we de tabel 'samenvouwen'

Maar we weten zeker dat die verzoek onze tabel niet nodig heeft. Dus laten we proberen de systeemprestaties weer in redelijke grenzen te brengen door alles overbodige uit de tabel te verwijderen — al is het 'met de hand', aangezien VACUUM ons in de steek laat.

Om het duidelijker te maken, laten we het voorbeeld bekijken van een buffer-tabel. Dat betekent dat er een grote stroom van INSERT/DELETE is, en soms is de tabel helemaal leeg. Maar als die niet leeg is, moeten we de huidige inhoud ervan behouden.

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

Het is duidelijk dat het mogelijk is om iets met de tabel te doen na elke operatie, maar dat heeft weinig zin — de overhead voor onderhoud zal aanzienlijk groter zijn dan de capaciteit van de doelverzoeken.

Laten we de criteria opstellen — “het is tijd om te handelen”, als:

• VACUUM al een tijd geleden is uitgevoerd
  We verwachten een grote belasting, dus laten we dit instellen op 60 seconden sinds de laatste [auto]VACUUM.
• de fysieke grootte van de tabel is groter dan het doel
  We definiëren dit als het dubbele aantal pagina's (blocks van 8KB) ten opzichte van de minimale grootte — 1 blk op heap + 1 blk op elk van de indexen — voor een potentiële lege tabel. Maar als we verwachten dat er altijd een bepaald volume aan gegevens in de buffer blijft, is het verstandig deze formule aan te passen.

Controle verzoek

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- hier is het verstandiger om * current_setting('block_size')::bigint te doen, maar wie verandert de blokgrootte?..
, 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

We kunnen van tevoren niet weten hoezeer een parallelle query ons stoort - hoeveel records er "verouderd" zijn sinds het begin ervan. Daarom is het, wanneer we besluiten om de tabel op de een of andere manier te verwerken, hoe dan ook het beste om eerst VACUUM aus te voeren, aangezien deze, in tegenstelling tot VACUUM FULL, de parallelle processen niet hindert bij het lezen en schrijven van gegevens.

Daarnaast kan het meteen een groot deel schoonmaken van wat we willen verwijderen. En de volgende queries op deze tabel zullen gaan via de "hot cache", wat hun duur zal verkorten - en dat betekent ook de totale tijd van blokkering door onze dienstverlenende transactie.

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

Laten we controleren of er in de tabel überhaupt iets staat:

TABLE tbl LIMIT 1;

Als er geen enkele record meer over is, kunnen we veel besparen op de verwerking - door simpelweg TRUNCATE:

Het werkt als een onvoorwaardelijke DELETE-commando voor elke tabel, maar veel sneller, omdat het de tabellen feitelijk niet scant. Bovendien maakt het onmiddellijk schijfruimte vrij, zodat het uitvoeren van een VACUUM daarna niet nodig is.

Of je de volgordeteller van de tabel moet resetten (RESTART IDENTITY) - dat moet je zelf beslissen.

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

Aangezien we in een omgeving met hoge concurrentie werken, zou er ondertussen iemand iets in de tabel kunnen hebben geschreven terwijl we hier controleren op afwezigheid van records. We mogen deze informatie niet verliezen, wat betekent - wat? Juist, we moeten zorgen dat niemand iets kan schrijven.

Hiervoor moeten we SERIALIZABLE-isolatie inschakelen voor onze transactie (ja, hier starten we de transactie) en de tabel "permanent" vergrendelen:

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

Dit niveau van vergrendeling is precies wat we nodig hebben voor de operaties die we op de tabel willen uitvoeren.

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

Hier willen we de tabel "vergrendelen" - maar als er op dat moment iemand actief is op de tabel, bijvoorbeeld deze aan het lezen is? We zullen "vastlopen" in afwachting van het vrijgeven van deze vergrendeling, terwijl andere die willen lezen al bij ons vastlopen...

Om dat te voorkomen, "offer we onszelf op" - als we binnen een bepaalde (toegestaan kleine) tijd de vergrendeling nog steeds niet hebben kunnen krijgen, krijgen we een uitzondering van de database, maar we hinderen in ieder geval de anderen niet te veel.

Hiervoor stellen we de sessievariabele lock_timeout (voor versies 9.3+) of/en statement_timeoutHet belangrijkste om te onthouden is dat de waarde van statement_timeout alleen geldt voor de volgende statement. Dus zo in de samensmelting — werkt niet:

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

Om later niet weer de 'oude' waarde van de variabele te moeten herstellen, gebruiken we de vorm SET LOCAL, die het bereik van de instellingen tot de huidige transactie beperkt.

Vergeet niet dat statement_timeout van toepassing is op alle volgende verzoeken, zodat de transactie niet uitstrekt tot onacceptabele lengte, als er veel gegevens in de tabel zijn.

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

Als de tabel niet helemaal leeg blijkt te zijn, moeten de gegevens opnieuw worden opgeslagen via een tijdelijke hulptabel:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Handtekening ON COMMIT DROP betekent dat op het moment van de beëindiging van de transactie de tijdelijke tabel niet meer zal bestaan, en dat handmatig verwijderen in de context van de verbinding niet nodig is.

Aangezien we aannemen dat er niet veel 'levende' gegevens zijn, zou deze operatie vrij snel moeten verlopen.

Nou, dat is het eigenlijk! Vergeet niet om na het beëindigen van de transactie ANALYZE uit te voeren voor normalisatie van de tabelstatistieken, indien nodig.

Samenstellen van het definitieve script

We gebruiken zo'n '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;

Kunnen we de gegevens niet een tweede keer kopiëren?In principe kan dat, als er geen andere activiteiten aan de oid van de tabel zijn gekoppeld vanuit de BL of FK vanuit de DB:

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

We draaien het script op de oorspronkelijke tabel en controleren de 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

Alles is gelukt! De tabel is 50 keer verkleind, en alle UPDATEs draaien weer snel.

Bron: habr.com