🥇Kung mapakyas ang VACUUM, limpyohan namon ang lamesa nga mano-mano

VACUUM mahimo "paglimpyo" gikan sa usa ka lamesa sa PostgreSQL lamang kung unsa walay makakita - sa ato pa, walay bisan usa ka aktibong hangyo nga nagsugod sa wala pa kini nga mga rekord giusab.

Apan unsa man kung ang ingon nga dili maayo nga tipo (dugay nga pagkarga sa OLAP sa usa ka database sa OLTP) naglungtad pa? Giunsa limpyo nga aktibo nga nag-ilis sa lamesa gilibutan sa taas nga mga pangutana ug wala magtunob sa usa ka rake?

Pagbukhad sa rake

Una, atong tinoon kon unsa ang problema nga gusto natong sulbaron ug sa unsang paagi kini motungha.

Kasagaran kini nga sitwasyon mahitabo sa medyo gamay nga lamesa, apan diin kini mahitabo daghang kausaban. Kasagaran kini o lahi metro/aggregate/rating, diin ang UPDATE kanunay nga gipatuman, o buffer-pila aron maproseso ang pipila nga nagpadayon nga pag-agay sa mga panghitabo, ang mga rekord niini kanunay nga INSERT/DELETE.

Atong sulayan nga kopyahon ang kapilian nga adunay mga rating:

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;

Ug sa susama, sa lain nga koneksyon, ang usa ka taas, taas nga hangyo magsugod, pagkolekta sa pipila ka komplikado nga estadistika, apan dili makaapekto sa among lamesa:

SELECT pg_sleep(10000);

Karon among gi-update ang kantidad sa usa sa mga counter sa makadaghan, daghang beses. Alang sa kaputli sa eksperimento, buhaton nato kini sa lahi nga mga transaksyon gamit ang dblinkunsaon kini mahitabo sa tinuod:

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

Unsay nahitabo? Ngano nga bisan sa pinakasimple nga UPDATE sa usa ka rekord Ang oras sa pagpatuman gipaubos sa 7 ka beses - gikan sa 0.524ms ngadto sa 3.808ms? Ug ang among rating nagkahinay ug nagkahinay.

Sala man ni sa MVCC.

Kini mahitungod sa tanan Mekanismo sa MVCC, nga maoy hinungdan nga tan-awon sa pangutana ang tanang naunang bersyon sa entry. Busa atong limpyohan ang atong lamesa gikan sa "patay" nga mga bersyon:

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

Oh, walay bisan unsa nga limpyohan! Parallel Ang nagdagan nga hangyo nakabalda kanamo - human sa tanan, tingali sa umaabot gusto niya nga mobalik sa kini nga mga bersyon (unsaon kung?), ug kinahanglan nga magamit kini niya. Ug busa bisan ang VACUUM FULL dili makatabang kanamo.

"Nahugno" ang lamesa

Apan nahibal-an namon nga sigurado nga kana nga pangutana wala magkinahanglan sa among lamesa. Busa, sulayan gihapon namo nga ibalik ang pasundayag sa sistema sa igo nga mga limitasyon pinaagi sa pagwagtang sa tanan nga wala kinahanglana gikan sa lamesa - labing menos "manual", tungod kay ang VACUUM naghatag.

Aron mas klaro, atong tan-awon ang pananglitan sa kaso sa buffer table. Sa ato pa, adunay dako nga dagan sa INSERT/DELETE, ug usahay ang lamesa hingpit nga walay sulod. Apan kung dili kini haw-ang, kinahanglan naton i-save ang kasamtangang sulod niini.

#0: Pagtimbang-timbang sa sitwasyon

Klaro nga mahimo nimong sulayan ang pagbuhat sa usa ka butang sa lamesa bisan pagkahuman sa matag operasyon, apan dili kini hinungdanon - ang pagmentinar sa overhead tin-aw nga labi ka dako kaysa sa throughput sa target nga mga pangutana.

Himoon nato ang mga sukdanan - "panahon na sa paglihok" kung:

Ang VACUUM dugay nang gilusad
Nagdahom kami nga bug-at nga karga, busa pasagdi lang 60 segundo sukad sa katapusang [auto]VACUUM.
Ang pisikal nga gidak-on sa lamesa mas dako kay sa target
Atong ipasabot nga doble ang gidaghanon sa mga panid (8KB blocks) kalabot sa minimum nga gidak-on - 1 blk alang sa heap + 1 blk alang sa matag index - para sa posibleng walay sulod nga lamesa. Kung gipaabut namon nga ang usa ka piho nga kantidad sa datos kanunay nga magpabilin sa buffer nga "normal", makatarunganon nga i-tweak kini nga pormula.

Pangayo sa pag-verify

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: VACUUM gihapon

Dili nato mahibal-an daan kung ang usa ka parallel nga pangutana dako nga makabalda kanato - eksakto kung pila ang mga rekord nga nahimong "napaspas na" sukad kini nagsugod. Busa, sa diha nga kita modesisyon sa usa ka paagi sa pagproseso sa lamesa, sa bisan unsa nga kaso, kita kinahanglan una nga ipatuman niini VACUUM - dili sama sa VACUUM FULL, dili kini makabalda sa mga parallel nga proseso nga nagtrabaho sa read-write data.

Sa samang higayon, malimpyohan dayon niini ang kadaghanan sa gusto natong tangtangon. Oo, ug ang sunod nga mga pangutana sa kini nga lamesa moadto kanamo pinaagi sa "hot cache", nga makapakunhod sa ilang gidugayon - ug, busa, ang kinatibuk-ang panahon sa pagbabag sa uban pinaagi sa among transaksyon sa pagserbisyo.

#2: Naa bay tawo sa balay?

Atong susihon kon aduna bay bisan unsa sa lamesa:

TABLE tbl LIMIT 1;

Kung wala nay bisan usa ka rekord nga nahabilin, nan makadaginot kita og daghan sa pagproseso pinaagi lamang sa pagbuhat PUTOL:

Kini naglihok sama sa usa ka walay kondisyon nga DELETE nga sugo alang sa matag lamesa, apan mas paspas tungod kay dili kini aktwal nga mag-scan sa mga lamesa. Dugang pa, kini diha-diha dayon nagpagawas sa wanang sa disk, mao nga dili na kinahanglan nga maghimo usa ka operasyon sa VACUUM pagkahuman.

Kung kinahanglan nimo nga i-reset ang sequence counter sa lamesa (RESTART IDENTITY) naa kanimo ang pagdesisyon.

#3: Tanan - magpuli-puli!

Tungod kay nagtrabaho kami sa usa ka labi ka kompetisyon nga palibot, samtang ania kami dinhi nagsusi nga wala’y mga entri sa lamesa, adunay usa nga nagsulat na didto. Kinahanglan nga dili naton mawala kini nga kasayuran, busa unsa man? Husto, kinahanglan natong sigurohon nga walay makasulat niini nga sigurado.

Sa pagbuhat niini kita kinahanglan nga makahimo SERIALIZABLE-pag-inusara alang sa among transaksyon (oo, dinhi magsugod kami sa usa ka transaksyon) ug i-lock ang lamesa nga "hugot":

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

Kini nga lebel sa pag-block gitino sa mga operasyon nga gusto namon buhaton niini.

#4: Panagbangi sa interes

Mianhi kami dinhi ug gusto nga "i-lock" ang karatula - unsa man kung adunay usa nga aktibo niini nianang higayona, pananglitan, nagbasa gikan niini? Kami "magbitay" nga maghulat alang sa kini nga block nga ipagawas, ug ang uban nga gusto magbasa modagan kanamo ...

Aron mapugngan kini nga mahitabo, kita "isakripisyo ang atong kaugalingon" - kung dili kita makakuha og kandado sulod sa usa ka piho nga (madawat nga mubo) nga panahon, nan makadawat kita og eksepsiyon gikan sa base, apan labing menos dili kita makabalda pag-ayo sa uban.

Aron mahimo kini, itakda ang variable nga sesyon lock_timeout (alang sa mga bersyon 9.3+) o/ug statement_timeout. Ang nag-unang butang nga hinumduman mao nga ang statement_timeout nga bili magamit lamang gikan sa sunod nga pahayag. Sa ato pa, sama niini sa gluing - dili molihok:

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

Aron dili kinahanglan nga atubangon ang pagpahiuli sa "daan" nga kantidad sa variable sa ulahi, gigamit namon ang porma I-SET LOKAL, nga naglimite sa kasangkaran sa setting sa kasamtangan nga transaksyon.

Nahinumdom kami nga ang statement_timeout magamit sa tanan nga nagsunod nga mga hangyo aron ang transaksyon dili maabut sa dili madawat nga mga kantidad kung adunay daghang datos sa lamesa.

#5: Kopyaha ang datos

Kung ang lamesa dili hingpit nga walay sulod, ang datos kinahanglan nga i-save pag-usab gamit ang usa ka auxiliary temporaryo nga lamesa:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Pirma SA COMMIT DROP nagpasabot nga sa higayon nga matapos ang transaksyon, ang temporaryo nga lamesa mohunong sa paglungtad, ug dili kinahanglan nga mano-mano nga papason kini sa konteksto sa koneksyon.

Tungod kay nagtuo kami nga wala'y daghang "live" nga datos, kini nga operasyon kinahanglan nga mahitabo dayon.

Aw, kana lang! Ayaw kalimti human makompleto ang transaksyon dagan ANALYZE aron ma-normalize ang mga istatistika sa lamesa kung kinahanglan.

Paghiusa sa katapusang script

Gigamit namo kini nga "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;

Posible ba nga dili kopyahon ang datos sa ikaduhang higayon?Sa prinsipyo, posible kung ang oid sa lamesa mismo dili nahigot sa bisan unsang ubang mga kalihokan gikan sa BL nga bahin o FK gikan sa kilid sa 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;

Atong ipadagan ang script sa source table ug susihon ang 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

Nahuman ang tanan! Ang lamesa mikunhod sa 50 ka beses ug ang tanan nga mga UPDATE paspas nga nagdagan.

Source: www.habr.com

Kung mapakyas ang VACUUM, limpyohan namon ang lamesa nga mano-mano