🥇Kapag nabigo ang VACUUM, nililinis namin nang manu-mano ang mesa

VACUUM maaaring "maglinis" mula sa isang talahanayan sa PostgreSQL lamang kung ano walang makakakita - iyon ay, walang isang aktibong kahilingan na nagsimula bago ang mga talaang ito ay binago.

Ngunit paano kung ang gayong hindi kanais-nais na uri (pangmatagalang pag-load ng OLAP sa isang database ng OLTP) ay umiiral pa rin? Paano linisin ang aktibong pagpapalit ng mesa napapaligiran ng mahabang tanong at hindi nakatapak sa kalaykay?

Paglalatag ng kalaykay

Una, alamin natin kung ano ang problemang nais nating lutasin at kung paano ito maaaring lumabas.

Kadalasan nangyayari ang ganitong sitwasyon sa medyo maliit na mesa, ngunit kung saan ito nangyayari maraming pagbabago. Kadalasan ito o naiiba metro/aggregate/rating, kung saan madalas na isinasagawa ang UPDATE, o buffer-queue upang iproseso ang ilang patuloy na patuloy na pag-stream ng mga kaganapan, ang mga talaan kung saan ay patuloy na INSERT/DELETE.

Subukan nating kopyahin ang opsyon na may 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;

At kahanay, sa isa pang koneksyon, magsisimula ang isang mahaba, mahabang kahilingan, nangongolekta ng ilang kumplikadong istatistika, ngunit hindi naaapektuhan ang table namin:

SELECT pg_sleep(10000);

Ngayon ay ina-update namin ang halaga ng isa sa mga counter nang maraming, maraming beses. Para sa kadalisayan ng eksperimento, gawin natin ito sa magkakahiwalay na transaksyon gamit ang dblinkkung paano ito mangyayari sa katotohanan:

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

Anong nangyari? Bakit kahit para sa pinakasimpleng UPDATE ng isang tala nabawasan ng 7 beses ang oras ng pagpapatupad — mula 0.524ms hanggang 3.808ms? At ang aming rating ay mas mabagal.

Kasalanan ng MVCC ang lahat.

Tungkol ito sa lahat Mekanismo ng MVCC, na nagiging sanhi ng query upang tingnan ang lahat ng nakaraang bersyon ng entry. Kaya't linisin natin ang ating mesa mula sa mga "patay" na 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

Naku, walang malilinis! Parallel Nakakasagabal sa amin ang tumatakbong kahilingan - pagkatapos ng lahat, maaaring balang araw ay gusto niyang bumaling sa mga bersyong ito (paano kung?), at dapat na magagamit niya ang mga ito. At samakatuwid kahit na ang VACUUM FULL ay hindi makakatulong sa amin.

"Pagbagsak" ng mesa

Ngunit alam naming sigurado na hindi kailangan ng query na iyon ang aming talahanayan. Samakatuwid, susubukan pa rin naming ibalik ang pagganap ng system sa sapat na mga limitasyon sa pamamagitan ng pag-aalis ng lahat ng hindi kailangan mula sa talahanayan - kahit man lang "mano-mano", dahil sumusuko ang VACUUM.

Upang gawin itong mas malinaw, tingnan natin ang halimbawa ng kaso ng isang buffer table. Iyon ay, mayroong isang malaking daloy ng INSERT/DELETE, at kung minsan ang talahanayan ay ganap na walang laman. Ngunit kung hindi ito walang laman, kailangan natin i-save ang kasalukuyang nilalaman nito.

#0: Pagsusuri sa sitwasyon

Malinaw na maaari mong subukang gumawa ng isang bagay sa talahanayan kahit na pagkatapos ng bawat operasyon, ngunit hindi ito gaanong makabuluhan - ang overhead ng pagpapanatili ay malinaw na mas malaki kaysa sa throughput ng mga target na query.

Bumuo tayo ng pamantayan - "oras na para kumilos" kung:

Ang VACUUM ay inilunsad medyo matagal na ang nakalipas
Inaasahan namin ang isang mabigat na karga, kaya hayaan ito 60 segundo mula noong huling [auto]VACUUM.
mas malaki ang pisikal na sukat ng talahanayan kaysa sa target
Tukuyin natin ito bilang dalawang beses sa bilang ng mga pahina (8KB block) na may kaugnayan sa pinakamababang laki - 1 blk para sa heap + 1 blk para sa bawat index - para sa isang posibleng walang laman na mesa. Kung inaasahan namin na ang isang tiyak na halaga ng data ay palaging mananatili sa buffer "normal", makatwirang i-tweak ang formula na ito.

Kahilingan sa pagpapatunay

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 pa rin

Hindi namin malalaman nang maaga kung ang isang parallel na query ay makabuluhang nakakasagabal sa amin - kung gaano karaming mga tala ang naging "luma na" mula noong nagsimula ito. Samakatuwid, kapag nagpasya kaming iproseso ang talahanayan, sa anumang kaso, dapat muna naming isagawa ito VACUUM - hindi tulad ng VACUUM FULL, hindi ito nakakasagabal sa mga parallel na proseso na nagtatrabaho sa read-write na data.

Kasabay nito, maaari nitong linisin kaagad ang karamihan sa mga gusto nating alisin. Oo, at mapupunta sa amin ang mga kasunod na query sa talahanayang ito sa pamamagitan ng "hot cache", na magbabawas sa kanilang tagal - at, samakatuwid, ang kabuuang oras ng pagharang sa iba sa pamamagitan ng aming transaksyon sa pagseserbisyo.

#2: May tao ba sa bahay?

Tingnan natin kung mayroong anumang bagay sa talahanayan:

TABLE tbl LIMIT 1;

Kung wala nang isang tala na natitira, maaari tayong makatipid ng malaki sa pagproseso sa pamamagitan lamang ng paggawa TRUNCATE:

Ito ay gumaganap ng parehong bilang isang walang kundisyong DELETE na utos para sa bawat talahanayan, ngunit mas mabilis dahil hindi nito aktuwal na sinusuri ang mga talahanayan. Bukod dito, agad itong naglalabas ng espasyo sa disk, kaya hindi na kailangang magsagawa ng operasyong VACUUM pagkatapos.

Kung kailangan mong i-reset ang table sequence counter (RESTART IDENTITY) ay nasa iyo ang pagpapasya.

#3: Lahat - magpalitan!

Dahil nagtatrabaho kami sa isang mataas na mapagkumpitensyang kapaligiran, habang tinitingnan namin na walang mga entry sa talahanayan, maaaring may nakasulat na doon. Hindi natin dapat mawala ang impormasyong ito, kaya ano? Tama, kailangan nating siguraduhin na walang makakasulat nito para sigurado.

Upang gawin ito kailangan nating paganahin SERIALIZABLE-paghihiwalay para sa aming transaksyon (oo, dito kami magsisimula ng isang transaksyon) at i-lock ang talahanayan "mahigpit":

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

Ang antas ng pagharang na ito ay tinutukoy ng mga operasyong gusto naming gawin dito.

#4: Conflict of interest

Pumunta kami dito at nais na "i-lock" ang karatula - paano kung may isang taong aktibo dito sa sandaling iyon, halimbawa, nagbabasa mula dito? Kami ay "magbibitin" sa paghihintay na mailabas ang bloke na ito, at ang iba na gustong magbasa ay tatakbo sa amin...

Upang maiwasang mangyari ito, "isasakripisyo namin ang aming sarili" - kung hindi kami nakakuha ng lock sa loob ng isang tiyak (katanggap-tanggap na maikli) na oras, pagkatapos ay makakatanggap kami ng isang pagbubukod mula sa base, ngunit hindi bababa sa hindi kami masyadong makagambala sa iba pa.

Upang gawin ito, itakda ang variable ng session lock_timeout (para sa mga bersyon 9.3+) o/at statement_timeout. Ang pangunahing bagay na dapat tandaan ay ang statement_timeout na halaga ay nalalapat lamang mula sa susunod na pahayag. Iyon ay, tulad nito sa gluing - hindi gagana:

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

Upang hindi na makitungo sa pagpapanumbalik ng "lumang" halaga ng variable sa ibang pagkakataon, ginagamit namin ang form Itakda ang LOKAL, na naglilimita sa saklaw ng setting sa kasalukuyang transaksyon.

Naaalala namin na ang statement_timeout ay nalalapat sa lahat ng kasunod na kahilingan upang ang transaksyon ay hindi maabot sa mga hindi katanggap-tanggap na halaga kung mayroong maraming data sa talahanayan.

#5: Kopyahin ang data

Kung ang talahanayan ay hindi ganap na walang laman, ang data ay kailangang muling i-save gamit ang isang pantulong na pansamantalang talahanayan:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Lagda ON COMMIT DROP nangangahulugan na sa sandaling magtatapos ang transaksyon, ang pansamantalang talahanayan ay titigil sa pag-iral, at hindi na kailangang manu-manong tanggalin ito sa konteksto ng koneksyon.

Dahil ipinapalagay namin na walang maraming "live" na data, ang operasyong ito ay dapat maganap nang mabilis.

Well, iyon lang! Huwag kalimutan pagkatapos makumpleto ang transaksyon tumakbo ANALYZE upang gawing normal ang mga istatistika ng talahanayan kung kinakailangan.

Pagsasama-sama ng huling script

Ginagamit namin itong "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 bang hindi kopyahin ang data sa pangalawang pagkakataon?Sa prinsipyo, posible kung ang oid ng talahanayan mismo ay hindi nakatali sa anumang iba pang mga aktibidad mula sa BL side o FK mula sa DB side:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Patakbuhin natin ang script sa source table at suriin ang mga sukatan:

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

Lahat ay nagtagumpay! Ang talahanayan ay lumiit ng 50 beses at lahat ng UPDATE ay tumatakbo muli nang mabilis.

Pinagmulan: www.habr.com

Kapag nabigo ang VACUUM, nililinis namin nang manu-mano ang mesa