🥇Als VACUUM uitvalt, maken we de tafel handmatig schoon

VACUÜM kan alleen wat uit een tabel in PostgreSQL "opruimen". niemand kan zien - dat wil zeggen dat er geen enkel actief verzoek is gestart voordat deze records werden gewijzigd.

Maar wat als een dergelijk onaangenaam type (OLAP-belasting op lange termijn op een OLTP-database) nog steeds bestaat? Hoe maak actief de commode schoon omringd door lange vragen en niet op de hark trappen?

Het uitzetten van de hark

Laten we eerst bepalen wat het probleem is dat we willen oplossen en hoe het kan ontstaan.

Meestal gebeurt deze situatie op een relatief kleine tafel, maar waarin het voorkomt veel veranderingen. Meestal dit of anders meters/aggregaten/beoordelingen, waarop UPDATE vaak wordt uitgevoerd, of buffer-wachtrij om een voortdurend voortdurende stroom van gebeurtenissen te verwerken, waarvan de registraties voortdurend INSERT/DELETE zijn.

Laten we proberen de optie met beoordelingen te reproduceren:

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;

En parallel, in een ander verband, begint een lang, lang verzoek, waarbij enkele complexe statistieken worden verzameld, maar heeft geen invloed op onze tafel:

SELECT pg_sleep(10000);

Nu werken we de waarde van een van de tellers vele malen bij. Laten we dit doen voor de zuiverheid van het experiment in afzonderlijke transacties met behulp van dblinkhoe het in werkelijkheid zal gebeuren:

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 zelfs voor de eenvoudigste UPDATE van een enkel record uitvoeringstijd verminderd met 7 keer - van 0.524 ms naar 3.808 ms? En onze rating groeit steeds langzamer.

Het is allemaal de schuld van MVCC.

Het draait allemaal om MVCC-mechanisme, waardoor de query alle eerdere versies van het item doorzoekt. Dus laten we onze tafel opruimen van "dode" versies:

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

O, er valt niets schoon te maken! Parallel Het lopende verzoek hindert ons - hij zou tenslotte op een dag deze versies willen gebruiken (wat als?), en ze zouden voor hem beschikbaar moeten zijn. En daarom helpt zelfs VACUÜM VOL ons niet.

De tafel ‘inklappen’

Maar we weten zeker dat die zoekopdracht onze tabel niet nodig heeft. Daarom zullen we nog steeds proberen de systeemprestaties terug te brengen naar adequate limieten door alles wat onnodig is uit de tabel te verwijderen - tenminste "handmatig", aangezien VACUUM het opgeeft.

Laten we, om het duidelijker te maken, eens kijken naar het voorbeeld van een buffertabel. Dat wil zeggen, er is een grote stroom INSERT/DELETE en soms is de tabel helemaal leeg. Maar als het niet leeg is, moeten we wel de huidige inhoud opslaan.

#0: De situatie beoordelen

Het is duidelijk dat u zelfs na elke bewerking kunt proberen iets met de tabel te doen, maar dit heeft niet zoveel zin: de onderhoudsoverhead zal duidelijk groter zijn dan de doorvoer van de doelquery's.

Laten we de criteria formuleren: “het is tijd om te handelen” als:

VACUUM is al een hele tijd geleden gelanceerd
We verwachten een zware last, dus laat dat zo zijn 60 seconden sinds het laatste [auto]VACUUM.
fysieke tafelgrootte is groter dan de doelstelling
Laten we het definiëren als tweemaal het aantal pagina's (blokken van 8 KB) ten opzichte van de minimumgrootte - 1 blk voor heap + 1 blk voor elke index - voor een mogelijk lege tafel. Als we verwachten dat een bepaalde hoeveelheid gegevens ‘normaal gesproken’ altijd in de buffer zal blijven, is het redelijk om deze formule aan te passen.

Verificatieverzoek

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: Nog steeds VACUÜM

We kunnen niet van tevoren weten of een parallelle zoekopdracht ons aanzienlijk hindert - hoeveel records er precies "verouderd" zijn geworden sinds het begin ervan. Daarom moeten we, als we besluiten om de tabel op de een of andere manier te verwerken, deze eerst uitvoeren VACUÜM - In tegenstelling tot VACUUM FULL interfereert het niet met parallelle processen die werken met lees-schrijfgegevens.

Tegelijkertijd kan het het meeste van wat we graag willen verwijderen onmiddellijk opruimen. Ja, en daaropvolgende vragen in deze tabel gaan naar ons door "hot cache", wat de duur ervan zal verkorten - en dus de totale tijd van het blokkeren van anderen door onze onderhoudstransactie.

#2: Is er iemand thuis?

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

TABLE tbl LIMIT 1;

Als er geen enkel record meer over is, kunnen we door simpelweg te doen veel besparen op de verwerking AFKNOPEN:

Het werkt hetzelfde als een onvoorwaardelijke DELETE-opdracht voor elke tabel, maar is veel sneller omdat de tabellen niet daadwerkelijk worden gescand. Bovendien maakt het onmiddellijk schijfruimte vrij, zodat u achteraf geen VACUUM-bewerking hoeft uit te voeren.

Of u de tabelvolgordeteller (RESTART IDENTITY) moet resetten, kunt u zelf beslissen.

#3: Iedereen - om de beurt!

Omdat we in een zeer competitieve omgeving werken, kan het zijn dat iemand daar al iets heeft geschreven terwijl we hier controleren of er geen vermeldingen in de tabel staan. We mogen deze informatie niet verliezen, dus wat? Dat klopt, we moeten ervoor zorgen dat niemand het met zekerheid kan opschrijven.

Om dit te doen moeten we inschakelen SERIALISEERBAAR-isolatie voor onze transactie (ja, hier starten we een transactie) en sluiten de tafel “stevig” af:

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

Dit blokkeringsniveau wordt bepaald door de bewerkingen die we erop willen uitvoeren.

#4: Belangenverstrengeling

We komen hier en willen het bord 'vergrendelen' - wat als iemand er op dat moment actief mee bezig was, er bijvoorbeeld van las? We zullen blijven wachten tot dit blok wordt vrijgegeven, en anderen die willen lezen zullen ons tegenkomen...

Om dit te voorkomen zullen we “onszelf opofferen” - als we er niet in slagen om binnen een bepaalde (aanvaardbaar korte) tijd een slot te bemachtigen, dan krijgen we een uitzondering van de basis, maar we zullen ons er in ieder geval niet te veel mee bemoeien anderen.

Om dit te doen, stelt u de sessievariabele in lock_time-out (voor versies 9.3+) of/en statement_time-out. Het belangrijkste om te onthouden is dat de waarde statement_timeout alleen van toepassing is vanaf de volgende instructie. Dat wil zeggen, zoals dit bij het lijmen - zal niet werken:

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

Om later niet te hoeven omgaan met het herstellen van de “oude” waarde van de variabele, gebruiken we het formulier STEL LOKAAL IN, waardoor het bereik van de instelling wordt beperkt tot de huidige transactie.

We onthouden dat statement_timeout van toepassing is op alle volgende verzoeken, zodat de transactie zich niet kan uitstrekken tot onaanvaardbare waarden als er veel gegevens in de tabel staan.

#5: Gegevens kopiëren

Als de tabel niet helemaal leeg is, moeten de gegevens opnieuw worden opgeslagen met behulp van een tijdelijke hulptabel:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Handtekening OP COMMIT DALING betekent dat op het moment dat de transactie eindigt, de tijdelijke tabel ophoudt te bestaan en dat het niet nodig is om deze handmatig te verwijderen in de verbindingscontext.

Omdat we ervan uitgaan dat er niet veel ‘live’ gegevens zijn, zou deze operatie vrij snel moeten plaatsvinden.

Nou dat is alles! Vergeet dit niet na het voltooien van de transactie voer ANALYSEREN uit om tabelstatistieken indien nodig te normaliseren.

Het samenstellen van het definitieve script

Wij gebruiken deze “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;

Is het mogelijk om de gegevens niet een tweede keer te kopiëren?In principe is het mogelijk als de oid van de tabel zelf niet gekoppeld is aan andere activiteiten van de BL-kant of FK van de DB-kant:

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

Laten we het script uitvoeren op de brontabel en de statistieken controleren:

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 kleiner geworden en alle UPDATEs lopen weer snel.

Bron: www.habr.com

Wanneer VACUUM uitvalt, maken wij de tafel handmatig schoon