ProHoster > Blog > Bestjoer > As VACUUM mislearret, skjinje wy de tafel mei de hân

As VACUUM mislearret, skjinje wy de tafel mei de hân

VACUUM kin "opromje" fan in tabel yn PostgreSQL allinnich wat nimmen kin sjen - dat is, d'r is net ien aktyf fersyk dat begon foardat dizze records waarden feroare.

Mar wat as sa'n onaangenaam type (lange termyn OLAP-load op in OLTP-database) noch bestiet? Hoe skjin aktyf wikseljende tafel omjûn troch lange fragen en net op in rake stappe?

As VACUUM mislearret, skjinje wy de tafel mei de hân

It útlizzen fan de hark

Litte wy earst bepale wat it probleem is dat wy wolle oplosse en hoe't it kin ûntstean.

Meastentiids bart dizze situaasje op in relatyf lytse tafel, mar wêryn it foarkomt in protte feroarings. Meastal dit of oars meters/aggregaten/wurdearrings, wêrop UPDATE faak útfierd wurdt, of buffer-wachtrige te ferwurkjen wat oanhâldend oanhâldende stream fan eveneminten, records wêrfan binne hieltyd YNFOEGJE / DELETE.

Litte wy besykje de opsje te reprodusearjen mei wurdearrings:

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, yn in oare ferbining, begjint in lange, lange fersyk, it sammeljen fan wat komplekse statistiken, mar gjin ynfloed op ús tafel:

SELECT pg_sleep(10000);

No aktualisearje wy de wearde fan ien fan 'e tellers in protte, in protte kearen. Foar de suverens fan it eksperimint litte wy dit dwaan yn aparte transaksjes mei help fan dblinkhoe't it yn werklikheid barre sil:

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 bard? Wêrom sels foar de ienfâldichste UPDATE fan ien record útfiering tiid degradearre troch 7 kear - fan 0.524ms oant 3.808ms? En ús wurdearring bout hieltyd stadiger op.

It is allegear de skuld fan MVCC.

It hat alles te krijen mei MVCC meganisme, wêrtroch't de query troch alle eardere ferzjes fan 'e yngong sjocht. Dat litte wy ús tabel skjinmeitsje fan "deade" ferzjes:

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

Och, der is neat te skjin! Parallel It rinnende fersyk bemuoit ús - hy kin ommers wol ris nei dizze ferzjes (wat as?), en dy moatte foar him beskikber wêze. En dêrom sil sels VACUUM FULL ús net helpe.

"Tafel ynstoarten".

Mar wy witte wis dat dy query ús tabel net nedich hat. Dêrom sille wy noch besykje om de systeemprestaasjes werom te bringen nei adekwate grinzen troch alles wat net nedich is fan 'e tafel te eliminearjen - op syn minst "hânmjittich", om't VACUUM opjout.

Om it dúdliker te meitsjen, litte wy nei it foarbyld sjen fan it gefal fan in buffertabel. Dat is, der is in grutte stream fan INSERT / DELETE, en soms de tafel is hielendal leech. Mar as it net leech is, moatte wy bewarje de hjoeddeistige ynhâld.

#0: De situaasje beoardielje

It is dúdlik dat jo sels nei elke operaasje besykje kinne wat mei de tafel te dwaan, mar dit makket net folle sin - de ûnderhâldsoverhead sil dúdlik grutter wêze as de trochfier fan 'e doelfragen.

Litte wy de kritearia formulearje - "it is tiid om te hanneljen" as:

  • VACUUM waard frij lang lyn lansearre
    Wy ferwachtsje in swiere lading, dus lit it mar 60 sekonden sûnt de lêste [auto]VACUUM.
  • fysike tabel grutte is grutter as doel
    Litte wy it definiearje as twa kear it oantal siden (8KB-blokken) relatyf oan de minimale grutte - 1 blk foar heap + 1 blk foar elke yndeks - foar in potinsjeel lege tafel. As wy ferwachtsje dat in bepaalde hoemannichte gegevens altyd yn 'e buffer "normaal" bliuwt, is it ridlik om dizze formule oan te passen.

Ferifikaasje fersyk

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: Noch fakuüm

Wy kinne net fan tefoaren witte oft in parallelle query ús signifikant ynterferearret - krekt hoefolle records binne "ferâldere" wurden sûnt it begon. Dêrom, as wy beslute om ien of oare manier te ferwurkjen de tafel, yn alle gefallen, wy moatte earst útfiere op it VACUUM - yn tsjinstelling ta VACUUM FULL, bemuoit it net mei parallelle prosessen dy't wurkje mei lêzen-skriuwgegevens.

Tagelyk kin it it measte fan wat wy fuorthelje wolle fuortendaliks skjinmeitsje. Ja, en folgjende fragen oer dizze tabel sille nei ús gean troch "hot cache", wat har doer sil ferminderje - en dus de totale tiid fan blokkearjen fan oaren troch ús tsjinsttransaksje.

#2: Is immen thús?

Lit ús kontrolearje oft der überhaupt wat yn 'e tabel stiet:

TABLE tbl LIMIT 1;

As d'r gjin inkeld record oer is, dan kinne wy ​​​​in protte besparje op ferwurking troch gewoan te dwaan TRUNCATE:

It docht itselde as in betingstleaze DELETE-kommando foar elke tabel, mar is folle flugger, om't it de tabellen net eins scant. Boppedat makket it skiifromte daliks frij, dus it is net nedich om dêrnei in VACUUM-operaasje út te fieren.

Oft jo de teller fan 'e tabelsekwinsje weromsette moatte (RESTART IDENTITY) is oan jo om te besluten.

#3: Elkenien - om beurten!

Sûnt wy wurkje yn in tige kompetitive omjouwing, wylst wy hjir kontrolearje dat der gjin ynstjoerings yn 'e tabel binne, koe immen dêr al wat skreaun hawwe. Wy moatte dizze ynformaasje net ferlieze, dus wat? Dat kloppet, wy moatte der foar soargje dat nimmen it wis opskriuwe kin.

Om dit te dwaan moatte wy ynskeakelje SERIALIZABLE-isolaasje foar ús transaksje (ja, hjir begjinne wy ​​in transaksje) en slute de tabel "strak":

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

Dit nivo fan blokkearjen wurdt bepaald troch de operaasjes dy't wy derop wolle útfiere.

# 4: Konflikt fan belang

Wy komme hjir en wolle it teken "beskoattelje" - wat as immen der op dat stuit aktyf wie, bygelyks it lêzen fan it? Wy sille "hingje" wachtsjen op dit blok om te wurde frijlitten, en oaren dy't wolle lêze sille ús tsjinkomme ...

Om foar te kommen dat dit bart, sille wy "ússels opofferje" - as wy binnen in bepaalde (akseptabel koarte) tiid gjin slot kinne krije, dan krije wy in útsûndering fan 'e basis, mar wy sille teminsten net tefolle bemuoie mei oaren.

Om dit te dwaan, set de sesjefariabele yn lock_timeout (foar ferzjes 9.3+) of/en statement_timeout. It wichtichste ding om te ûnthâlden is dat de statement_timeout-wearde allinich jildt fan 'e folgjende ferklearring. Dat is, sa by it lijmen - sil net wurkje: 

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

Om net te krijen hawwe mei it werstellen fan de "âlde" wearde fan 'e fariabele letter, brûke wy it formulier SET LOKAL, dy't de omfang fan 'e ynstelling beheint ta de aktuele transaksje.

Wy ûnthâlde dat statement_timeout jildt foar alle folgjende oanfragen, sadat de transaksje net kin útwreidzje nei ûnakseptabele wearden as d'r in protte gegevens yn 'e tabel binne.

#5: Kopiearje gegevens

As de tabel net folslein leech is, moatte de gegevens opnij bewarre wurde mei in tydlike helptabel:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Hantekening ON COMMIT DROP betsjut dat op it stuit dat de transaksje einiget, sil de tydlike tabel ophâlde te bestean, en it is net nedich om it manuell te wiskjen yn 'e ferbiningskontekst.

Om't wy oannimme dat d'r net in protte "live" gegevens binne, moat dizze operaasje frij fluch plakfine.

No, dat is alles! Ferjit net nei it foltôgjen fan de transaksje rinne ANALYSE om tabelstatistiken as nedich te normalisearjen.

It gearstallen fan it definitive skript

Wy brûke dizze "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 it mooglik om de gegevens net in twadde kear te kopiearjen?Yn prinsipe is it mooglik as de oid fan 'e tafel sels net bûn is oan oare aktiviteiten fan' e BL-kant of FK fan 'e 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;

Litte wy it skript op 'e boarnetabel útfiere en de metriken kontrolearje:

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 slagge! De tabel is krimp mei 50 kear en alle UPDATEs rinne fluch wer.

Boarne: www.habr.com

Add a comment