ProHoster > Blog > Administrasi > Nalika VACUUM gagal, urang ngabersihan tabel sacara manual

Nalika VACUUM gagal, urang ngabersihan tabel sacara manual

vakum tiasa "ngabersihan" tina méja di PostgreSQL ngan naon teu saurang ogé bisa ningali - nyaeta, teu aya hiji pamundut aktif nu dimimitian saméméh rékaman ieu dirobah.

Tapi kumaha upami jinis anu teu pikaresepeun sapertos (beban OLAP jangka panjang dina database OLTP) masih aya? Kumaha bersih aktip ngarobah tabel dikurilingan ku queries panjang tur teu lengkah dina rake a?

Nalika VACUUM gagal, urang ngabersihan tabel sacara manual

Ngabelesatkeun garu

Kahiji, hayu urang nangtukeun naon masalah urang rék ngajawab téh jeung kumaha bisa timbul.

Biasana kaayaan ieu lumangsung dina méja rélatif leutik, tapi di mana eta lumangsung loba parobahan. Biasana ieu atanapi béda méter / agrégat / ratings, anu UPDATE sering dieksekusi, atanapi panyangga-antrean pikeun ngolah sababaraha aliran acara anu terus-terusan, rékaman anu terus-terusan INSERT / DELETE.

Hayu urang cobian baranahan pilihan kalayan 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;

Sareng paralel, dina sambungan anu sanés, pamundut anu panjang sareng panjang dimimitian, ngumpulkeun sababaraha statistik kompleks, tapi teu mangaruhan tabel kami:

SELECT pg_sleep(10000);

Ayeuna urang ngamutahirkeun nilai salah sahiji counters loba, sababaraha kali. Pikeun kamurnian percobaan, hayu urang ngalakukeun ieu dina transaksi misah ngagunakeun dblinkkumaha eta bakal kajadian dina kanyataanana:

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

Aya naon? Naha malah pikeun UPDATE pangbasajanna tina rékaman tunggal waktos palaksanaan didegradasi ku 7 kali - ti 0.524ms ka 3.808ms? Jeung rating urang ngawangun beuki lalaunan.

Éta sadayana lepat MVCC.

Éta sadayana ngeunaan mékanisme MVCC, nu ngabalukarkeun query kasampak ngaliwatan sagala versi saméméhna tina entri. Janten hayu urang ngabersihan méja urang tina versi "maot":

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, teu aya anu ngabersihan! Sajajar Paménta ngajalankeun ngaganggu kami - Barina ogé, anjeunna bisa someday hoyong giliran versi ieu (kumaha lamun?), Jeung maranéhna kudu sadia pikeun anjeunna. Ku kituna sanajan VACUUM FULL moal mantuan kami.

"Rubuh" méja

Tapi urang terang pasti yén pamundut éta henteu peryogi méja kami. Kituna, urang masih bakal coba balik kinerja sistem ka wates nyukupan ku ngaleungitkeun sagalana nu teu perlu ti tabél - sahenteuna "sacara manual", saprak VACUUM masihan.

Sangkan leuwih jelas, hayu urang nempo conto kasus tabel panyangga. Hartina, aya aliran badag INSERT / DELETE, sarta kadangkala tabel sagemblengna kosong. Tapi lamun teu kosong, urang kudu simpen eusina ayeuna.

# 0: Assessing kaayaan

Ieu jelas yén anjeun bisa nyoba ngalakukeun hiji hal kalawan tabél malah sanggeus unggal operasi, tapi ieu teu make akal pikiran - overhead pangropéa jelas bakal leuwih gede dibandingkeun throughput tina queries target.

Hayu urang ngarumuskeun kriteria - "éta waktuna pikeun meta" lamun:

  • VACUUM diluncurkeun rada lami
    Urang ngaharepkeun beban beurat, jadi hayu eta jadi detik 60 saprak panungtungan [otomatis] VACUUM.
  • ukuran tabel fisik leuwih badag batan target
    Hayu urang ngartikeun salaku dua kali jumlah kaca (8KB blok) relatif ka ukuran minimum - 1 blk pikeun numpuk + 1 blk pikeun tiap indéks - pikeun tabel berpotensi kosong. Lamun urang nyangka yén jumlah nu tangtu data bakal salawasna tetep dina panyangga "normal", éta lumrah mun tweak rumus ieu.

pamundut 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: Masih VACUUM

Kami henteu tiasa terang sateuacanna naha pamundut paralel sacara signifikan ngaganggu kami - persis sabaraha rékaman anu janten "katinggaleun jaman" ti saprak mimiti. Ku alatan éta, nalika urang mutuskeun kumaha bae ngolah tabél, dina sagala hal, urang kedah ngajalankeun eta vakum - Beda sareng VACUUM FULL, éta henteu ngaganggu prosés paralel anu dianggo sareng data baca-tulis.

Dina waktos anu sami, éta tiasa langsung ngabersihkeun seueur anu urang hoyong hapus. Leres, sareng patarosan salajengna dina tabel ieu bakal ka urang ku "cache panas", anu bakal ngirangan durasina - sareng, ku kituna, total waktos ngahalangan batur ku transaksi jasa kami.

#2: Aya saha di imah?

Hayu urang pariksa naha aya nanaon dina tabél:

TABLE tbl LIMIT 1;

Upami teu aya hiji catetan anu tinggaleun, maka urang tiasa ngahémat seueur pikeun ngolah ku ngan saukur ngalakukeun POTONGAN:

Éta tindakan anu sami sareng paréntah DELETE saratna pikeun unggal méja, tapi langkung gancang sabab éta henteu leres-leres nyeken tabél. Sumawona, éta langsung ngabébaskeun rohangan disk, janten henteu kedah ngalakukeun operasi VACUUM saatosna.

Naha anjeun kedah ngareset counter sekuen méja (Ngamimitian deui Idéntitas) terserah anjeun mutuskeun.

#3: Dulur - giliran!

Kusabab urang damel di lingkungan anu kompetitif pisan, nalika kami di dieu mariksa yén teu aya éntri dina tabél, batur tiasa parantos nyerat anu aya. Urang teu kudu leungit informasi ieu, jadi naon? Leres, urang kedah mastikeun yén teu aya anu tiasa nyerat éta pasti.

Jang ngalampahkeun ieu urang kudu ngaktipkeun SERIALIZED-isolasi pikeun transaksi urang (enya, di dieu urang ngamimitian transaksi) sareng konci méja "kedap":

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

Tingkat blocking ieu ditangtukeun ku operasi anu urang hoyong laksanakeun.

#4: Konflik kapentingan

Kami sumping ka dieu sareng hoyong "konci" tanda - kumaha upami aya anu aktip dina waktos éta, contona, maca tina éta? Kami bakal "ngagantung" ngantosan blok ieu dileupaskeun, sareng batur anu hoyong maca bakal nabrak kami ...

Pikeun nyegah ieu kajantenan, urang bakal "ngorbankeun diri" - upami urang henteu tiasa kéngingkeun konci dina waktos anu tangtu (ditampi pondok), maka urang bakal nampi pengecualian tina dasarna, tapi sahenteuna urang moal ngaganggu teuing. batur.

Jang ngalampahkeun ieu, setel variabel sési lock_timeout (pikeun versi 9.3+) atawa / jeung statement_timeout. Hal utama anu kudu diinget nyaéta yén nilai statement_timeout ngan lumaku tina pernyataan salajengna. Maksudna, sapertos kieu dina gluing - moal jalan: 

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

Dina raraga teu kudu nungkulan malikkeun "heubeul" nilai variabel engké, kami nganggo formulir SET LOKAL, nu ngawatesan lingkup setelan ka transaksi ayeuna.

Urang émut yén statement_timeout manglaku ka sadaya pamundut anu salajengna supados urus henteu tiasa manteng ka nilai anu teu katampi upami aya seueur data dina tabél.

#5: Nyalin data

Upami tabél henteu lengkep kosong, data kedah disimpen deui nganggo méja samentawis bantu:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Tanda tangan ON COMMIT DROP hartina di momen urus ends, tabel samentara bakal cease mun aya, sarta teu perlu sacara manual ngahapus eta dina konteks sambungan.

Kusabab urang nganggap yén teu aya loba data "hirup", operasi ieu kudu lumangsung rada gancang.

Muhun, éta sadayana! Tong hilap saatos réngsé transaksi ngajalankeun ANALISIS pikeun normalize statistik tabel lamun perlu.

Nyusun naskah ahir

Kami nganggo "pseudo-python" ieu:

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

Naha mungkin henteu nyalin data kadua kalina?Sacara prinsip, mungkin lamun oid tabel sorangan teu dihijikeun ka sagala kagiatan séjén ti sisi BL atanapi FK ti sisi 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;

Hayu urang ngajalankeun skrip dina tabel sumber sareng pariksa métrik:

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

Sagalana digawé kaluar! tabél geus shrunk ku 50 kali tur sagala UPDATEs ngajalankeun gancang deui.

sumber: www.habr.com

Tambahkeun komentar