ProHoster > Pūnaewele > Nā Administration > Ke hāʻule ka VACUUM, hoʻomaʻemaʻe mākou i ka papaʻaina me ka lima

Ke hāʻule ka VACUUM, hoʻomaʻemaʻe mākou i ka papaʻaina me ka lima

MAHELE hiki ke "hoʻomaʻemaʻe" mai kahi papa ma PostgreSQL wale nō ʻaʻohe mea e ʻike - ʻo ia hoʻi, ʻaʻohe noi ikaika i hoʻomaka ma mua o ka hoʻololi ʻia ʻana o kēia mau moʻolelo.

Akā, pehea inā he ʻano ʻoluʻolu ʻole (hoʻouka OLAP lōʻihi ma kahi waihona OLTP) mau? Pehea hoʻomaʻemaʻe hoʻololi papa hoʻopuni ʻia e nā nīnau lōʻihi a ʻaʻole hehi i ka rake?

Ke hāʻule ka VACUUM, hoʻomaʻemaʻe mākou i ka papaʻaina me ka lima

Ka wehe ʻana i ka rake

ʻO ka mea mua, e hoʻoholo kākou i ka pilikia a mākou e makemake ai e hoʻoponopono a pehea e ala mai ai.

ʻO ka maʻamau kēia kūlana ma kahi papaʻaina liʻiliʻi, aka, ma kahi e puka mai ai nui nā hoʻololi. ʻO ka maʻamau kēia a ʻokoʻa paha mika/hui/helu, kahi e hoʻokō pinepine ʻia ai ʻo UPDATE, a i ʻole pale-queue e hoʻoponopono i kekahi kahawai mau o nā hanana, nā moʻolelo o ia mau mea e INSERT/DELETE mau.

E ho'āʻo kākou e hana hou i ke koho me nā loina:

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;

A ma ke ʻano like, ma kahi pili ʻē aʻe, hoʻomaka kahi noi lōʻihi lōʻihi, e hōʻiliʻili i kekahi mau helu paʻakikī, akā. ʻaʻole pili i kā mākou papaʻaina:

SELECT pg_sleep(10000);

I kēia manawa, hoʻonui mākou i ka waiwai o kekahi o nā helu helu i nā manawa he nui. No ka maʻemaʻe o ka hoʻokolohua, e hana kāua i kēia i nā hana kaʻawale me ka hoʻohana ʻana i dblinkpehea e hiki ai i ka mea maoli:

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

He aha i hana ai? No ke aha no ka UPDATE maʻalahi o kahi moʻolelo hoʻokahi hoʻohaʻahaʻa ʻia ka manawa hoʻokō e 7 mau manawa - mai 0.524ms a i 3.808ms? A ke kūkulu lohi nei kā mākou helu helu.

No MVCC ka hewa.

Pili wale ia MVCC mīkini, ka mea e nānā ai ka nīnau ma nā mana mua o ke komo. No laila e hoʻomaʻemaʻe mākou i kā mākou papaʻaina mai nā mana "make":

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

ʻAe, ʻaʻohe mea e hoʻomaʻemaʻe! Kaulike Ke keakea nei ka noi holo ia makou - ma hope o nā mea a pau, makemake paha ʻo ia e huli i kēia mau mana (pehea inā?), A pono e loaʻa iā ia. A no laila ʻaʻole kōkua ʻo VACUUM FULL iā mākou.

"Ke hāʻule nei" i ka papaʻaina

Akā ʻike maopopo mākou ʻaʻole pono kēlā nīnau i kā mākou papaʻaina. No laila, e hoʻāʻo mākou e hoʻihoʻi i ka hana ʻōnaehana i nā palena kūpono ma ka hoʻopau ʻana i nā mea āpau ʻole mai ka papaʻaina - ma ka liʻiliʻi loa "ma ka lima", no ka mea hāʻawi ʻo VACUUM.

No ka hoʻomaʻamaʻa ʻana, e nānā kākou i ke ʻano o ka hihia o kahi papa ʻaina. ʻO ia hoʻi, he kahe nui o INSERT/DELETE, a i kekahi manawa ua nele loa ka papaʻaina. Akā inā ʻaʻole kaʻawale, pono mākou mālama i kāna mau mea i kēia manawa.

#0: Ka loiloi i ke kūlana

Ua maopopo hiki iā ʻoe ke hoʻāʻo e hana i kekahi mea me ka papaʻaina ma hope o kēlā me kēia hana, akā ʻaʻole i manaʻo nui kēia - ʻoi aku ka nui o ka mālama ʻana ma mua o ka throughput o nā nīnau i makemake ʻia.

E hoʻolālā i nā pae hoʻohālike - "ua hiki i ka manawa e hana" inā:

  • Ua hoʻomaka ʻia ʻo VACUUM i kahi manawa lōʻihi i hala
    Manaʻo mākou i kahi haʻahaʻa kaumaha, no laila e ʻae 60 kekona mai ka [auto]VACUUM hope loa.
  • ʻoi aku ka nui o ka pākaukau kino ma mua o ka pahuhopu
    E wehewehe i ʻelua ka helu o nā ʻaoʻao (8KB poloka) pili i ka liʻiliʻi liʻiliʻi - 1 blk no ka puʻu + 1 blk no kēlā me kēia papa kuhikuhi - no ka papa ʻaina ʻole. Inā manaʻo mākou e noho mau ka nui o ka ʻikepili i ka buffer "maʻamau", kūpono ke hoʻololi i kēia ʻano.

Noi hōʻoia

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 mau

ʻAʻole hiki iā mākou ke ʻike ma mua inā ke keʻakeʻa nei kahi nīnau like iā mākou - ʻehia mau moʻolelo i lilo i "kahiko" mai ka wā i hoʻomaka ai. No laila, ke hoʻoholo mākou e hana i ka papaʻaina, i kekahi hihia, pono mākou e hoʻokō mua iā ia MAHELE - ʻaʻole like me VACUUM FULL, ʻaʻole ia e hoʻopilikia i nā kaʻina hana like e hana ana me ka ʻikepili heluhelu-kākau.

I ka manawa like, hiki iā ia ke hoʻomaʻemaʻe koke i ka hapa nui o nā mea a mākou e makemake ai e wehe. ʻAe, a e hele mai nā nīnau ma kēia pākaukau iā mākou na "hot cache", ka mea e hōʻemi i ko lākou lōʻihi - a, no laila, ka manawa holoʻokoʻa o ka pale ʻana i nā poʻe ʻē aʻe e kā mākou lawelawe lawelawe.

#2: Aia kekahi i ka home?

E nānā inā loaʻa kekahi mea i ka papaʻaina:

TABLE tbl LIMIT 1;

Inā ʻaʻohe moʻolelo hoʻokahi i koe, a laila hiki iā mākou ke mālama nui i ka hana ʻana ma ka hana wale ʻana OKI:

Hana like ia me ke kauoha DELETE ʻole no kēlā me kēia pākaukau, akā ʻoi aku ka wikiwiki no ka mea ʻaʻole ia e nānā pono i nā papa. Eia kekahi, hoʻokuʻu koke ia i kahi disk, no laila ʻaʻohe pono e hana i kahi hana VACUUM ma hope.

Inā pono ʻoe e hoʻihoʻi i ka helu helu papa (RESTART IDENTITY) iā ʻoe ke hoʻoholo.

#3: ʻO kēlā me kēia kanaka - e hoʻololi!

No ka mea, hana mākou ma kahi ʻano hoʻokūkū koʻikoʻi, ʻoiai mākou e ʻike nei ʻaʻohe mea komo i loko o ka papaʻaina, hiki i kekahi ke kākau i kekahi mea ma laila. ʻAʻole pono mākou e nalowale i kēia ʻike, no laila pehea? Pololei, pono mākou e hōʻoia ʻaʻole hiki i kekahi ke kākau i lalo me ka maopopo.

No ka hana ʻana i kēia pono mākou e hiki ai SERIALIZBLE- hoʻokaʻawale no kā mākou kālepa (ʻae, eia mākou e hoʻomaka ai i kahi kālepa) a laka i ka papa "paʻa":

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

Hoʻoholo ʻia kēia pae o ka pale ʻana e nā hana a mākou e makemake ai e hana ma luna.

#4: Ka paio o ka hoihoi

Hele mai mākou i ʻaneʻi a makemake mākou e "laka" i ka hōʻailona - pehea inā e hana ana kekahi ma ia manawa, no ka laʻana, heluhelu mai ia? E "kau" mākou e kali ana i ka hoʻokuʻu ʻia ʻana o kēia poloka, a ʻo nā mea ʻē aʻe e makemake e heluhelu e holo i loko o mākou ...

No ka pale ʻana i kēia, e "kaumaha mākou iā mākou iho" - inā ʻaʻole hiki iā mākou ke loaʻa i kahi laka i loko o kekahi manawa (pono ​​pōkole), a laila e loaʻa iā mākou kahi ʻokoʻa mai ka waihona, akā ma ka liʻiliʻi ʻaʻole mākou e hoʻopilikia nui loa. nā mea ʻē aʻe.

No ka hana ʻana i kēia, hoʻonohonoho i ka hoʻololi session lock_timeout (no nā mana 9.3+) a i ʻole/a ʻōlelo_manawa. ʻO ka mea nui e hoʻomanaʻo, ʻo ka waiwai ʻōlelo_timeout e pili wale ana mai ka ʻōlelo aʻe. ʻO ia hoʻi, e like me kēia i ka gluing - ʻaʻole e hana: 

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

I mea e pono ʻole ai ka hoʻihoʻi ʻana i ka waiwai "kahiko" o ka loli ma hope, hoʻohana mākou i ke ʻano HOOLAHA KUI, ka mea e kaupalena ana i ka laulā o ka hoʻonohonoho ʻana i ke kālepa o kēia manawa.

Hoʻomanaʻo mākou e pili ana ka statement_timeout i nā noi hope aʻe i hiki ʻole i ke kālepa ke hele i nā waiwai ʻae ʻole inā he nui nā ʻikepili i ka papaʻaina.

#5: Kope i ka ʻikepili

Inā ʻaʻole piha ka papaʻaina, pono e mālama hou ʻia ka ʻikepili me ka hoʻohana ʻana i kahi papaʻaina kōkua:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

kakauinoa ON COMMIT DROP 'o ia ho'i, i ka manawa e pau ai ke kālepa, e pau ka papa'aina i ka noho 'ana, a 'a'ole pono e holoi lima 'ia ma ka pō'aiapili pili.

Ma muli o ko mākou manaʻo ʻaʻole nui ka ʻikepili "ola", pono e hana koke kēia hana.

ʻAe, ʻo ia wale nō! Mai poina ma hope o ka pau ʻana o ke kālepa holo ANALYZE e hoʻoponopono i nā helu helu papa inā pono.

Hoʻohui i ka palapala hope loa

Hoʻohana mākou i kēia "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;

ʻAʻole hiki ke kope i ka ʻikepili i ka lua o ka manawa?Ma ke kumu, hiki ke hoʻopaʻa ʻia ka oid o ka papaʻaina ponoʻī i nā hana ʻē aʻe mai ka ʻaoʻao BL a i ʻole FK mai ka ʻaoʻao 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;

E holo kāua i ka palapala ma ka papa kumu a nānā i nā ana:

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

Ua holo pono nā mea a pau! Ua emi ka papaaina ma 50 manawa a ke holo hou nei na UPDATE a pau.

Source: www.habr.com

Pākuʻi i ka manaʻo hoʻopuka