🥇Yen VACUUM gagal, kita ngresiki meja kanthi manual

VACUUM bisa "ngresiki" saka meja ing PostgreSQL mung apa ora ana sing weruh - yaiku, ora ana panjaluk aktif sing diwiwiti sadurunge rekaman kasebut diganti.

Nanging yen ana jinis sing ora nyenengake (muat OLAP jangka panjang ing basis data OLTP) isih ana? Carane resik aktif ganti meja diubengi dening pitakonan dawa lan ora langkah ing rake?

Nyetak rake

Pisanan, ayo nemtokake apa masalah sing arep dirampungake lan kepiye kedadeyane.

Biasane kahanan iki kedadeyan ing meja relatif cilik, nanging ing ngendi iku dumadi akèh owah-owahan. Biasane iki utawa beda meter / agregat / ratings, sing UPDATE asring dieksekusi, utawa antrian buffer kanggo ngolah sawetara acara sing terus-terusan, cathetan sing terus-terusan INSERT / DELETE.

Ayo nyoba ngasilake pilihan kanthi 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;

Lan kanthi podo karo, ing sambungan liyane, panjaluk sing dawa lan dawa diwiwiti, ngumpulake sawetara statistik rumit, nanging ora mengaruhi meja kita:

SELECT pg_sleep(10000);

Saiki kita nganyari Nilai siji saka counters akeh, kakehan. Kanggo kemurnian eksperimen, ayo nindakake iki ing transaksi kapisah nggunakake dblinkcarane iku bakal kelakon ing kasunyatan:

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

Ana apa? Apa malah kanggo UPDATE paling gampang saka rekaman siji wektu eksekusi degradasi dening 7 kaping - saka 0.524ms kanggo 3.808ms? Lan rating kita saya suwe saya suwe saya suwe.

Iki kabeh salah MVCC.

Iku kabeh babagan mekanisme MVCC, sing njalari pitakon kanggo ndeleng kabeh versi entri sadurunge. Dadi ayo ngresiki meja saka versi "mati":

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, ora ana sing kudu diresiki! Paralel Panjaluk sing mlaku ngganggu kita - sawise kabeh, dheweke bisa uga pengin ngowahi versi kasebut (apa yen?), Lan kudu kasedhiya kanggo dheweke. Lan mulane malah VACUUM FULL ora bakal mbantu kita.

"Ambruk" meja

Nanging kita ngerti manawa pitakon kasebut ora mbutuhake tabel kita. Mulane, kita isih bakal nyoba kanggo bali kinerja sistem kanggo watesan nyukupi dening mbusak kabeh rasah saka meja - paling ora "kanthi manual", wiwit VACUUM menehi ing.

Kanggo nggawe luwih cetha, ayo kang katon ing conto cilik saka Tabel buffer. Sing, ana aliran gedhe INSERT / DELETE, lan kadhangkala meja wis rampung kosong. Nanging yen ora kosong, kita kudu nyimpen isi saiki.

# 0: Netepake kahanan

Cetha sing bisa nyoba kanggo nindakake soko karo meja malah sawise saben operasi, nanging iki ora nggawe akeh pangertèn - nduwur sirah pangopènan cetha bakal luwih saka throughput saka pitakonan target.

Ayo ngrumusake kritéria - "wis wayahe tumindak" yen:

VACUUM diluncurake cukup suwe
Kita ngarepake beban sing abot, mula ayo 60 detik wiwit pungkasan [otomatis] VACUUM.
ukuran meja fisik luwih gedhe tinimbang target
Ayo ditetepake minangka kaping pindho jumlah kaca (blok 8KB) relatif marang ukuran minimal - 1 blk kanggo numpuk + 1 blk kanggo saben indeks - kanggo meja potensial kosong. Yen kita nyana yen jumlah tartamtu saka data bakal tansah tetep ing buffer "biasane", iku cukup kanggo ngapiki rumus iki.

Panjaluk verifikasi

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

Kita ora bisa ngerti luwih dhisik apa pitakon paralel ngganggu kita - persis pira rekaman sing wis "kedaluwarsa" wiwit diwiwiti. Mulane, nalika kita mutusake kanggo ngolah tabel, ing kasus apa wae, kita kudu ngetrapake dhisik VACUUM - ora kaya VACUUM FULL, ora ngganggu proses paralel sing bisa digunakake karo data maca-tulis.

Ing wektu sing padha, bisa langsung ngresiki kabeh sing pengin dibusak. Ya, lan pitakonan sakteruse ing meja iki bakal menyang kita dening "cache panas", sing bakal nyuda durasi - lan, mulane, total wektu mblokir wong liya kanthi transaksi layanan kita.

#2: Apa ana wong ing omah?

Ayo priksa manawa ana apa-apa ing meja:

TABLE tbl LIMIT 1;

Yen ora ana rekaman siji-sijine, mula kita bisa ngirit akeh ing proses kanthi mung nindakake TRUNCATE:

Tumindak sing padha karo prentah DELETE tanpa syarat kanggo saben meja, nanging luwih cepet amarga ora mindai tabel. Kajaba iku, iku langsung mbebasake spasi disk, supaya ora perlu kanggo nindakake operasi VACUUM sakwise.

Apa sampeyan kudu ngreset counter urutan meja (RESTART IDENTITAS) iku nganti sampeyan arep.

#3: Saben uwong - giliran!

Awit kita bisa ing lingkungan Highly competitive, nalika kita kene mriksa sing ora ana entri ing meja, wong bisa wis ditulis soko ana. Kita ora bakal kelangan informasi iki, dadi apa? Bener, kita kudu nggawe manawa ora ana sing bisa nulis kanthi pasti.

Kanggo nindakake iki, kita kudu ngaktifake SERIALIZED-isolasi kanggo transaksi kita (ya, ing kene kita miwiti transaksi) lan ngunci meja "kenceng":

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

Tingkat pamblokiran iki ditemtokake dening operasi sing pengin ditindakake.

# 4: Konflik kapentingan

Kita teka ing kene lan pengin "ngunci" tandha - apa yen ana wong sing aktif ing wektu kasebut, umpamane, maca saka iku? Kita bakal "nyumerepi" ngenteni blok iki dirilis, lan wong liya sing pengin maca bakal nemoni kita ...

Kanggo nyegah kedadeyan kasebut, kita bakal "ngorbanake awake dhewe" - yen ora bisa entuk kunci sajrone wektu tartamtu (bisa ditrima cendhak), mula kita bakal nampa pangecualian saka pangkalan, nanging paling ora bakal ngganggu banget. liyane.

Kanggo nindakake iki, setel variabel sesi lock_timeout (kanggo versi 9.3+) utawa / lan statement_timeout. Wangsulan: Bab ingkang utama kanggo elinga iku statement_timeout Nilai mung ditrapake saka statement sabanjuré. Yaiku, kaya iki ing gluing - ora bakal bisa:

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

Supaya ora kudu menehi hasil karo mulihake nilai "lawas" saka variabel mengko, kita nggunakake formulir SET LOKAL, kang matesi orane katrangan saka setelan kanggo transaksi saiki.

Kita elinga yen statement_timeout ditrapake kanggo kabeh panjalukan sakteruse supaya transaksi ora bisa ngluwihi nilai sing ora bisa ditampa yen ana akeh data ing tabel.

#5: Nyalin data

Yen tabel ora kosong, data kasebut kudu disimpen maneh nggunakake tabel sementara tambahan:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Tandatangan ON COMMIT DROP tegese ing wayahe transaksi rampung, tabel sauntara bakal mandheg ana, lan ora perlu mbusak kanthi manual ing konteks sambungan.

Awit kita nganggep yen ora akeh data "urip", operasi iki kudu ditindakake kanthi cepet.

Inggih, iku kabeh! Aja lali sawise ngrampungake transaksi mbukak ANALYZE kanggo normalake statistik tabel yen perlu.

Nglumpukake skrip pungkasan

Kita nggunakake "pseudo-python" iki:

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

Apa bisa ora nyalin data kaping pindho?Ing asas, iku bisa yen oid saka Tabel dhewe ora disambungake menyang sembarang aktivitas liyane saka sisih BL utawa FK saka sisih 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;

Ayo mbukak skrip ing tabel sumber lan mriksa 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

Kabeh wis rampung! Tabel wis shrunk dening 50 kaping lan kabeh UPDATEs mlaku cepet maneh.

Source: www.habr.com

Nalika VACUUM gagal, kita ngresiki meja kanthi manual