ProHoster > Блог > Nchịkwa > Mgbe VACUUM dara, anyị na-eji aka na-ehicha tebụl

Mgbe VACUUM dara, anyị na-eji aka na-ehicha tebụl

AHỤ nwere ike "ihichapụ" site na tebụl dị na PostgreSQL naanị gịnị ọ dịghị onye pụrụ ịhụ ya - ya bụ, ọ dịghị otu arịrịọ na-arụsi ọrụ ike malitere tupu agbanwee ndekọ ndị a.

Ma gịnị ma ọ bụrụ na ụdị adịghị mma dị otú ahụ (ibu OLAP ogologo oge na nchekwa data OLTP) ka dị? Kedu dị ọcha na-agbanwe agbanwe tebụl gbara ogologo ajụjụ gbara ya gburugburu ma ghara ịzọ ụkwụ n'ịke?

Mgbe VACUUM dara, anyị na-eji aka na-ehicha tebụl

Ịtọpụta rake

Nke mbụ, ka anyị chọpụta ihe nsogbu anyị chọrọ idozi bụ na otú o nwere ike isi malite.

Ọtụtụ mgbe ọnọdụ a na-eme na tebụl dịtụ ntakịrị, ma nke ọ na-eme otutu mgbanwe. Na-emekarị nke a ma ọ bụ dị iche iche mita / mkpokọta / ọkwa, nke a na-egbukarị mmelite, ma ọ bụ kwụ n'ahịrị iji hazie ụfọdụ ihe omume na-aga n'ihu na-aga n'ihu, ndekọ nke a na-etinye oge niile / ehichapụ.

Ka anyị gbalịa iwepụtaghachi nhọrọ ahụ na ọkwa:

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;

Na n'otu aka ahụ, na njikọ ọzọ, ogologo arịrịọ ogologo na-amalite, na-anakọta ụfọdụ ọnụ ọgụgụ dị mgbagwoju anya, mana adịghị emetụta tebụl anyị:

SELECT pg_sleep(10000);

Ugbu a, anyị na-emelite uru nke otu n'ime counters ọtụtụ, ọtụtụ ugboro. Maka ịdị ọcha nke nnwale, ka anyị mee nke a na azụmahịa dị iche iche site na iji dblinkka ọ ga-esi mee n'eziokwu:

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

Kedu ihe mere? Gịnị kpatara ọbụna maka mmelite kachasị mfe nke otu ndekọ oge ogbugbu wedara site na ugboro 7 - site na 0.524ms ruo 3.808ms? Na ọkwa anyị na-ewuwanye nke ọma.

Ọ bụ MVCC kpatara ya.

Ọ bụ ihe niile MVCC usoro, nke na-eme ka ajụjụ ahụ lelee ụdị ntinye niile gara aga. Yabụ ka anyị hichaa tebụl anyị site na ụdị “nwụrụ anwụ”:

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, ọ nweghị ihe dị ọcha! Yiri Arịrịọ ịgba ọsọ na-egbochi anyị - E kwuwerị, ọ nwere ike otu ụbọchị ịchọrọ ịtụgharị na nsụgharị ndị a (gịnị ma ọ bụrụ?), Ha kwesịrị ịdị ya. Ya mere ọbụna VACUUM FULL agaghị enyere anyị aka.

"Na-ada" tebụl

Mana anyị maara nke ọma na ajụjụ ahụ adịghị mkpa tebụl anyị. Ya mere, anyị ka ga-anwa iweghachi arụmọrụ sistemu na oke zuru oke site na iwepu ihe niile na-adịghị mkpa na tebụl - ọbụlagodi “aka”, ebe VACUUM na-enye.

Iji mee ka o doo anya karị, ka anyị leba anya n'ihe atụ nke tebụl nchekwa. Ya bụ, enwere nnukwu mmiri nke INSERT/HIchapụ, na mgbe ụfọdụ tebụl na-atọgbọ chakoo. Ma ọ bụrụ na ọ bụghị efu, anyị ga- chekwaa ọdịnaya ya ugbu a.

#0: Nyochaa ọnọdụ

O doro anya na ị nwere ike ịgbalị ime ihe na tebụl ọbụna mgbe ọrụ ọ bụla gasịrị, ma nke a adịghị eme ka uche dị ukwuu - nlekọta nlekọta ga-abụ nke ọma karịa ntinye nke ajụjụ ndị a chọrọ.

Ka anyị chepụta njirisi - "oge eruola ime ihe" ma ọ bụrụ:

  • Emepụtara VACUUM ogologo oge gara aga
    Anyị na-atụ anya ibu dị arọ, yabụ ka ọ dị 60 sekọnd kemgbe ikpeazụ [auto] VACUUM.
  • Nha tebụl anụ ahụ buru ibu karịa ebumnuche
    Ka anyị kọwapụta ya dị ka ọnụọgụ peeji okpukpu abụọ (block 8KB) n'ihe gbasara nha kacha nta - 1 blk maka obo + 1 blk maka ndeksi ọ bụla - maka tebụl nwere ike ị tọgbọ chakoo. Ọ bụrụ na anyị na-atụ anya na ụfọdụ data ga-anọgide na-echekwa "na-emekarị", ọ bụ ihe ezi uche dị na tweak usoro a.

Arịrịọ nkwenye

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

Anyị enweghị ike ịma tupu oge a ma ajụjụ ndị yiri ya ọ na-etinye aka na anyị - kpọmkwem ndekọ ole aghọọla “ihe ochie” kemgbe ọ malitere. Ya mere, mgbe anyị kpebiri n'ụzọ ụfọdụ hazie tebụl, n'ọnọdụ ọ bụla, anyị kwesịrị ibu ụzọ gbuo na ya AHỤ - N'adịghị ka VACUUM FULL, ọ naghị egbochi usoro ndị yiri ya na-arụ ọrụ na data-agụ.

N'otu oge ahụ, ọ nwere ike kpochapụ ọtụtụ ihe anyị ga-achọ iwepụ ozugbo. Ee, na ajụjụ ndị na-esote na tebụl a ga-agara anyị site na "hot cache", nke ga-ebelata oge ha - yana, ya mere, ngụkọta oge nke igbochi ndị ọzọ site na azụmahịa anyị na-eje ozi.

#2: Ọ nwere onye nọ n'ụlọ?

Ka anyị lelee ma ọ dị ihe ọ bụla na tebụl ma ọlị:

TABLE tbl LIMIT 1;

Ọ bụrụ na enweghị otu ndekọ fọdụrụ, mgbe ahụ, anyị nwere ike ịchekwa ọtụtụ ihe na nhazi site na ime naanị Gbanwee:

Ọ na-eme otu ihe ahụ dị ka iwu ihichapụ enweghị ọnọdụ maka tebụl ọ bụla, mana ọ na-adị ngwa ngwa ebe ọ bụ na ọ naghị enyocha tebụl n'ezie. Ọzọkwa, ọ na-ahapụ ohere diski ozugbo, yabụ na ọ dịghị mkpa ịme ọrụ VACUUM ma emesịa.

Ọ dị gị n'aka ikpebi ma ị ga-eme ka tebulu sequence counter (malitegharịa ekwentị).

#3: Onye ọ bụla - tụgharịa!

Ebe ọ bụ na anyị na-arụ ọrụ na gburugburu ebe asọmpi siri ike, ka anyị nọ ebe a na-achọpụta na ọ dịghị ihe ndenye na tebụl, mmadụ nwere ike dee ihe ọ bụla n'ebe ahụ. Anyị ekwesịghị ịtụfu ozi a, yabụ gịnị? Nke ahụ ziri ezi, anyị kwesịrị ijide n'aka na ọ dịghị onye nwere ike ide ya n'ezie.

Iji mee nke a anyị kwesịrị ime SERIALIZABLE-kewapụ maka azụmahịa anyị (ee, ebe a ka anyị na-amalite azụmahịa) wee kpọchie tebụl "nke ọma":

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

A na-ekpebi ọkwa mgbochi a site na arụmọrụ anyị chọrọ ịrụ na ya.

#4: Esemokwu nke mmasị

Anyị na-abịa ebe a ma chọọ "ịkpọchi" akara - gịnị ma ọ bụrụ na mmadụ na-arụsi ọrụ ike na ya n'oge ahụ, dịka ọmụmaatụ, na-agụ ya? Anyị ga-akpọgidere ka a tọhapụrụ ngọngọ a, ndị ọzọ chọrọ ịgụ ga-agbaba na anyị...

Iji gbochie nke a ime, anyị "ga-achụ onwe anyị n'àjà" - ọ bụrụ na anyị enweghị ike ịnweta mkpọchi n'ime oge ụfọdụ (nke a na-anabata nke ọma), mgbe ahụ, anyị ga-enweta ihe dịpụrụ adịpụ site na isi, ma ọ dịkarịa ala, anyị agaghị etinye aka na ya. ndị ọzọ.

Iji mee nke a, tọọ mgbanwe oge oge mkpochi (maka ụdị 9.3+) ma ọ bụ/na nkwupụta_oge agwụla. Isi ihe ị ga-echeta bụ na ọnụ ahịa nkwupụta_timeout na-emetụta naanị site na nkwupụta na-esote. Ya bụ, dị ka nke a na gluing - agaghị arụ ọrụ: 

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

Ka ị ghara inwe nsogbu maka iweghachi uru "ochie" nke mgbanwe mgbe e mesịrị, anyị na-eji ụdị ahụ TỤLA MGBE, nke na-egbochi oke ntọala na azụmahịa dị ugbu a.

Anyị na-echeta na nkwupụta_timeout na-emetụta arịrịọ niile na-esote ka azụmahịa ahụ ghara ịgbatị ụkpụrụ na-anabataghị ma ọ bụrụ na enwere ọtụtụ data na tebụl.

#5: Detuo data

Ọ bụrụ na tebụl ezughị ezu, a ga-echekwa data ahụ site na iji tebụl nwa oge inyeaka:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

mbinye aka NA COMMIT DROP pụtara na n'oge azụmahịa ahụ kwụsịrị, tebụl nwa oge ga-akwụsị ịdị adị, ọ dịghịkwa mkpa iji aka ihichapụ ya na njikọ njikọ.

Ebe anyị na-eche na ọ dịghị ọtụtụ data "dị ndụ", ọrụ a kwesịrị ime ngwa ngwa.

Ọfọn, nke ahụ bụ ihe niile! Echefula mgbe ịmechara azụmahịa ahụ gbaa ANYA iji normalize ọnụ ọgụgụ tebụl ma ọ bụrụ na ọ dị mkpa.

Na-etinye ọnụ na edemede ikpeazụ

Anyị na-eji "pseudo-python" a:

# собираем статистику с таблицы
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;

Ọ ga-ekwe omume ịghara iṅomi data nke ugboro abụọ?Na ụkpụrụ, ọ ga-ekwe omume ma ọ bụrụ na mmanụ nke tebụl n'onwe ya ejikọtaghị ya na ọrụ ọ bụla ọzọ site na BL ma ọ bụ FK si n'akụkụ 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;

Ka anyị mee edemede ahụ na tebụl isi iyi wee lelee metrik:

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

Ihe niile mere! Tebụl ahụ adaala ugboro 50 na mmelite niile na-agba ọsọ ọsọ ọzọ.

isi: www.habr.com

Tinye a comment