🥇VACUUM inaposhindikana, tunasafisha meza kwa mikono

VACUUM inaweza "kusafisha" kutoka kwa jedwali katika PostgreSQL tu nini hakuna anayeweza kuona - yaani, hakuna ombi moja amilifu lililoanza kabla ya rekodi hizi kubadilishwa.

Lakini vipi ikiwa aina hiyo isiyopendeza (mzigo wa muda mrefu wa OLAP kwenye hifadhidata ya OLTP) bado ipo? Vipi safi kikamilifu kubadilisha meza kuzungukwa na maswali marefu na sio kukanyaga reki?

Kuweka nje reki

Kwanza, hebu tuone ni tatizo gani tunataka kutatua na jinsi gani linaweza kutokea.

Kawaida hali hii hutokea kwenye meza ndogo kiasi, lakini ambayo hutokea mabadiliko mengi. Kawaida hii au tofauti mita/jumla/ukadiriaji, ambayo UPDATE mara nyingi hutekelezwa, au foleni ya bafa kuchakata baadhi ya mfululizo unaoendelea wa matukio, rekodi zake INGIZA/FUTA kila mara.

Wacha tujaribu kuzaliana chaguo na makadirio:

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 kwa sambamba, katika uhusiano mwingine, ombi la muda mrefu, la muda mrefu huanza, kukusanya takwimu fulani ngumu, lakini haiathiri meza yetu:

SELECT pg_sleep(10000);

Sasa tunasasisha thamani ya moja ya vihesabio mara nyingi. Kwa usafi wa jaribio, hebu tufanye hivi katika shughuli tofauti kwa kutumia dblinkjinsi itatokea katika ukweli:

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

Nini kimetokea? Kwa nini hata kwa USASISHAJI rahisi zaidi wa rekodi moja muda wa utekelezaji umepungua kwa mara 7 - kutoka 0.524ms hadi 3.808ms? Na ukadiriaji wetu unaongezeka polepole zaidi na zaidi.

Yote ni makosa ya MVCC.

Yote ni kuhusu Utaratibu wa MVCC, ambayo husababisha hoja kutazama matoleo yote ya awali ya ingizo. Kwa hivyo, wacha tusafishe meza yetu kutoka kwa matoleo "yaliyokufa":

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

Lo, hakuna kitu cha kusafisha! Sambamba Ombi linaloendeshwa linatuingilia - baada ya yote, siku moja anaweza kutaka kurejea matoleo haya (vipi ikiwa?), Na yanapaswa kupatikana kwake. Na kwa hivyo hata VACUUM FULL haitatusaidia.

"Kukunja" meza

Lakini tunajua kwa hakika kuwa swala hilo halihitaji meza yetu. Kwa hivyo, bado tutajaribu kurudisha utendaji wa mfumo kwa mipaka ya kutosha kwa kuondoa kila kitu kisichohitajika kwenye jedwali - angalau "kwa mikono", kwani VACUUM inajitolea.

Ili kuifanya iwe wazi zaidi, hebu tuangalie mfano wa kesi ya meza ya buffer. Hiyo ni, kuna mtiririko mkubwa wa INSERT/DELETE, na wakati mwingine meza ni tupu kabisa. Lakini ikiwa sio tupu, lazima hifadhi maudhui yake ya sasa.

#0: Kutathmini hali

Ni wazi kwamba unaweza kujaribu kufanya kitu na meza hata baada ya kila operesheni, lakini hii haina maana sana - uendeshaji wa matengenezo utakuwa wazi zaidi kuliko upitishaji wa maswali ya lengo.

Wacha tuunde vigezo - "ni wakati wa kuchukua hatua" ikiwa:

VACUUM ilizinduliwa muda mrefu uliopita
Tunatarajia mzigo mzito, basi iwe hivyo Sekunde za 60 tangu [auto]VACUUM ya mwisho.
saizi halisi ya jedwali ni kubwa kuliko lengo
Wacha tufafanue kama mara mbili ya idadi ya kurasa (vitalu vya KB 8) ikilinganishwa na saizi ya chini - Blk 1 kwa lundo + blk 1 kwa kila faharasa - kwa meza inayoweza kuwa tupu. Ikiwa tunatarajia kwamba kiasi fulani cha data kitasalia kwenye bafa "kawaida", ni jambo la busara kubadili fomula hii.

Ombi la uthibitishaji

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: Bado Utupu

Hatuwezi kujua mapema ikiwa swali sambamba linatuingilia kwa kiasi kikubwa - ni rekodi ngapi hasa ambazo "zimepitwa na wakati" tangu zilipoanza. Kwa hiyo, tunapoamua kwa namna fulani kusindika meza, kwa hali yoyote, tunapaswa kwanza kutekeleza juu yake VACUUM - tofauti na VACUUM FULL, haiingilii na michakato sambamba inayofanya kazi na data ya kusoma-kuandika.

Wakati huo huo, inaweza kusafisha mara moja zaidi ya kile tungependa kuondoa. Ndio, na maswali yanayofuata kwenye jedwali hili yatatuendea na "cache moto", ambayo itapunguza muda wao - na, kwa hiyo, muda wa jumla wa kuzuia wengine kwa shughuli yetu ya huduma.

#2: Kuna mtu yuko nyumbani?

Wacha tuangalie ikiwa kuna kitu kwenye meza kabisa:

TABLE tbl LIMIT 1;

Ikiwa hakuna rekodi moja iliyobaki, basi tunaweza kuokoa mengi kwenye usindikaji kwa kufanya tu KUNYANYA:

Inafanya kazi sawa na amri ya DELETE isiyo na masharti kwa kila jedwali, lakini ni haraka sana kwani haichanganui majedwali. Zaidi ya hayo, mara moja hufungua nafasi ya disk, kwa hiyo hakuna haja ya kufanya operesheni ya VACUUM baadaye.

Iwapo unahitaji kuweka upya kihesabu cha mfuatano wa jedwali (ANZA UPYA IDENTITY) ni juu yako kuamua.

#3: Kila mtu - chukua zamu!

Kwa kuwa tunafanya kazi katika mazingira yenye ushindani mkubwa, tukiwa hapa kuangalia kwamba hakuna maingizo kwenye jedwali, mtu anaweza kuwa tayari ameandika kitu hapo. Hatupaswi kupoteza habari hii, kwa nini? Hiyo ni kweli, tunahitaji kuhakikisha kuwa hakuna mtu anayeweza kuiandika kwa uhakika.

Ili kufanya hivyo tunahitaji kuwezesha SERIALIZABLE-kutengwa kwa shughuli yetu (ndio, hapa tunaanza shughuli) na funga meza "kwa ukali":

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

Kiwango hiki cha kuzuia kinatambuliwa na shughuli ambazo tunataka kufanya juu yake.

#4: Mgongano wa maslahi

Tunakuja hapa na tunataka "kufunga" ishara - vipi ikiwa mtu alikuwa akifanya kazi juu yake wakati huo, kwa mfano, akisoma kutoka kwake? "Tutanyongwa" tukingojea kizuizi hiki, na wengine wanaotaka kusoma watatukimbilia ...

Ili kuzuia hili kutokea, "tutajitolea" - ikiwa hatukuweza kupata kufuli ndani ya muda fulani (unaokubalika mfupi), basi tutapokea ubaguzi kutoka kwa msingi, lakini angalau hatutaingilia sana. wengine.

Ili kufanya hivyo, weka tofauti ya kikao lock_timeout (kwa matoleo 9.3+) au/na statement_timeout. Jambo kuu la kukumbuka ni kwamba thamani ya statement_timeout inatumika tu kutoka kwa taarifa inayofuata. Hiyo ni, kama hii katika gluing - haitafanya kazi:

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

Ili si lazima kukabiliana na kurejesha thamani ya "zamani" ya kutofautiana baadaye, tunatumia fomu WEKA MTAA, ambayo inaweka mipaka ya wigo wa mpangilio kwa shughuli ya sasa.

Tunakumbuka kuwa statement_timeout inatumika kwa maombi yote yanayofuata ili muamala usifikie thamani zisizokubalika ikiwa kuna data nyingi kwenye jedwali.

#5: Nakili data

Ikiwa jedwali sio tupu kabisa, data italazimika kuhifadhiwa tena kwa kutumia jedwali la muda la msaidizi:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Sahihi ON COMIT DROP inamaanisha kuwa wakati shughuli inaisha, meza ya muda itakoma kuwapo, na hakuna haja ya kuifuta kwa mikono katika muktadha wa unganisho.

Kwa kuwa tunadhani kwamba hakuna data nyingi "moja kwa moja", operesheni hii inapaswa kufanyika haraka sana.

Naam, hiyo ndiyo yote! Usisahau baada ya kukamilisha muamala kimbia CHAMBUA kurekebisha takwimu za jedwali ikiwa ni lazima.

Kuweka pamoja hati ya mwisho

Tunatumia "pseudo-python" hii:

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

Je, inawezekana kutonakili data mara ya pili?Kimsingi, inawezekana ikiwa oid ya jedwali yenyewe haijaunganishwa na shughuli zingine zozote kutoka upande wa BL au FK kutoka upande wa 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;

Wacha tuendeshe hati kwenye jedwali la chanzo na angalia metriki:

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

Kila kitu kilifanyika! Jedwali limepungua kwa mara 50 na UPDATE zote zinaendelea haraka tena.

Chanzo: mapenzi.com

Wakati VACUUM inashindwa, tunasafisha meza kwa mikono