🥇Ka rahua te VACUUM, ka horoi a ringa matou i te ripanga

VACUUM Ka taea te "horoi" mai i te tepu i PostgreSQL anake te aha kahore he tangata e kite - ara, karekau he tono kaha i timata i mua i te whakarereketanga o enei rekoata.

Engari ka pewhea mena kei te noho tonu tetahi momo kino (te uta OLAP mo te wa roa i runga i te papaa raraunga OLTP)? Pehea ma te huri i te tepu e karapotia ana e nga patai roa me te kore e takahi i te rake?

Te whakatakoto i te rake

Tuatahi, me whakatau he aha te raruraru e hiahia ana tatou ki te whakaoti me te pehea e puta ai.

I te nuinga o te wa ka tupu tenei ahuatanga i runga i tetahi tepu iti, engari i roto i te puta he maha nga huringa. I te nuinga o te waa he rereke tenei mita / whakatōpū / whakatauranga, i runga i te WHAKAMAHI he maha nga mahi, ranei tūtira-buffer ki te tukatuka i etahi rerenga o nga huihuinga e haere tonu ana, ko nga rekoata e mau tonu ana KOKU/MUKU.

Me ngana ki te whakaputa i te whiringa me nga whakatauranga:

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, i roto i te whakarara, i tetahi atu hononga, ka timata te tono roa, roa, kohikohi etahi tatauranga uaua, engari kaore e pa ki ta maatau tepu:

SELECT pg_sleep(10000);

Inaianei ka whakahouhia e matou te uara o tetahi o nga porotiti he maha, he maha nga wa. Mo te ma o te whakamatautau, me mahi tenei i roto i nga whakawhitinga motuhake ma te whakamahi i te dblinkme pehea e tupu ai i roto i te mooni:

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 te mea i tupu? He aha te mea mo te WHAKAHOU ngawari o te rekoata kotahi ka whakahekehia te wa mahi e 7 nga wa — mai i te 0.524ms ki te 3.808ms? A kei te piki haere to maatau whakatauranga.

Na MVCC katoa te he.

He korero katoa MVCC tikanga, ka puta te uiui ki nga putanga katoa o mua o te urunga. Na me horoi ta tatou tepu mai i nga putanga "mate":

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

Aue, kaore he mea hei horoi! Whakarara Kei te pokanoa te tono rere ki a matou - i muri i nga mea katoa, tera pea ka hiahia ia ki te huri ki enei putanga (mehemea?), a me waatea ki a ia. Na reira ahakoa ko te VACUUM FULL e kore e awhina i a maatau.

"Te tiango" te tepu

Engari e tino mohio ana matou kaore e hiahiatia e taua patai ta maatau ripanga. Na reira, ka ngana tonu taatau ki te whakahoki i te mahinga o te punaha ki nga rohe e tika ana ma te whakakore i nga mea katoa kaore e tika mai i te tepu - he iti rawa "ma te ringa", mai i te mea ka tukuna e te VACUUM.

Kia marama ake ai, me titiro ki te tauira o te keehi o te tepu papaa. Arā, he nui te rere o te INSERT/DELETE, a, i etahi wa ka noho kau te teepu. Engari ki te kore e kau, me maatau tiakina ona ihirangi o naianei.

#0: Te aromatawai i te ahuatanga

E marama ana ka taea e koe te ngana ki te mahi i tetahi mea ki te teepu ahakoa i muri i ia mahi, engari kaore i te tino mohio - ka tino nui ake te utu o te tiaki i te urunga o nga patai kua whakaritea.

Me whakatakoto nga paearu - "kua tae ki te wa ki te mahi" mena:

I whakarewahia te VACUUM no mua noa atu
Kei te tumanako tatou he kawenga taumaha, na, waiho 60 hēkona mai i te [aunoa] VACUUM whakamutunga.
he nui ake te rahi o te ripanga tinana i te whaainga
Me tautuhi kia rua te maha o nga wharangi (8KB poraka) e pa ana ki te rahinga iti - 1 blk mo te puranga + 1 blk mo ia taurangi - mo te tepu karekau pea. Mena kei te tumanako tatou ka noho tonu etahi o nga raraunga i roto i te parapara "te tikanga", he mea tika ki te takawiri i tenei tikanga.

Tono manatoko

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: WAEKE tonu

Kaore e taea e matou te mohio i mua mena kei te tino whakararu te patai whakarara ki a matou - e hia nga rekoata kua "taamata" mai i te timatanga. Na reira, ka whakatau tatou ki te whakahaere i te tepu, ahakoa, me mahi tuatahi ki runga VACUUM - kaore i rite ki te VACUUM FULL, kaore e pokanoa ki nga mahi whakarara e mahi ana me nga raraunga panui-tuhi.

I te wa ano, ka taea e ia te horoi i te nuinga o nga mea e hiahia ana matou ki te tango. Ae, ka haere mai nga patai i runga i tenei tepu ki a matou na "keteroki wera", ka iti ake te roanga - a, no reira, te tapeke o te wa aukati i etahi atu na roto i ta maatau mahi mahi.

#2: He tangata kei te kainga?

Me titiro mena he mea kei te ripanga:

TABLE tbl LIMIT 1;

Mena karekau he rekoata e toe ana, katahi ka taea e tatou te penapena nui mo te tukatuka ma te mahi noa KAUPAPA:

He rite tonu te mahi ki te whakahau DELETE herekore mo ia tepu, engari he tere ake i te mea kaore e tino karapahia nga ripanga. I tua atu, ka waatea tonu te mokowā kōpae, na reira kaore he take ki te mahi i tetahi mahi VACUUM i muri mai.

Mena ka hiahia koe ki te tautuhi ano i te kaute raupapa ripanga (RESTART IDENTITY) kei a koe te whakatau.

#3: Katoa - tahuri mai!

I te mea e mahi ana matou i roto i te taiao tino whakataetae, i a matou i konei e tirotiro ana karekau he whakaurunga ki te ripanga, kua tuhia e te tangata tetahi mea ki reira. Kaua e ngaro enei korero, he aha? E tika ana, me whakarite kia kore e taea e te tangata te tuhi.

Ki te mahi i tenei me taea e tatou SERIALIZBLE-whakawehe mo a maatau tauwhitinga (ae, i konei ka tiimata he tauwhitinga) ka maukatia te ripanga "kia kaha":

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

Ko tenei taumata aukati ka whakatauhia e nga mahi e hiahia ana matou ki te mahi ki runga.

#4: Taupatupatu o nga paanga

Ka haere mai matou ki konei ka hiahia ki te "kati" te tohu - ka pehea mena kei te kaha te tangata ki runga i taua waa, hei tauira, te panui mai i a ia? Ka "iri" matou ki te tatari mo te tukunga o tenei poraka, a ko etahi e hiahia ana ki te panui ka rere mai ki a matou...

Hei aukati i tenei, ka "patua e matou" - ki te kore e taea e matou te raka i roto i tetahi wa poto (he wa poto), katahi ka whiwhi tatou i te tuunga mai i te turanga, engari ko te iti rawa ka kore e pokanoa ki a tatou. etahi atu.

Hei mahi i tenei, tautuhia te taurangi wahanga maukati_wā (mo nga putanga 9.3+) ranei/me tauākī_wā. Ko te mea nui hei maumahara ko te uara korero_timeout anake ka pa mai i te korero e whai ake nei. Arā, penei i te whakapiri - e kore e mahi:

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

Kia kore ai e pa ki te whakahoki i te uara "tawhito" o te taurangi i muri mai, ka whakamahia e matou te puka WHAKATAHI A-ROHE, e whakawhāiti ana i te whānuitanga o te tautuhinga ki te tauwhitinga o nāianei.

E maumahara ana matou ko te korero_timeout e pa ana ki nga tono katoa e whai ake nei kia kore ai te tauwhitinga e toro atu ki nga uara kore e whakaaetia mena he maha nga raraunga kei te ripanga.

#5: Tārua raraunga

Ki te kore te ripanga i tino putua, me tiaki ano nga raraunga ma te whakamahi i te ripanga rangitahi awhina:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Waitohu I TE COMMIT DROP ko te tikanga i te wa ka mutu te tauwhitinga, ka mutu te noho o te ripanga rangitahi, a kaore he take ki te muku a-ringa i roto i te horopaki hononga.

I te mea e kii ana matou kaore he nui o nga raraunga "ora", me tere te mahi.

Na, ko tera! Kaua e wareware i muri i te whakaotinga o te tauwhitinga oma KAUPAPA ki te whakarite i nga tatauranga ripanga mehemea e tika ana.

Te whakakotahi i te tuhinga whakamutunga

Ka whakamahia e matou tenei "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;

Ka taea te kore e kape i nga raraunga mo te wa tuarua?Ko te tikanga, ka taea mena kaore te oid o te teepu ake e herea ki etahi atu mahi mai i te taha BL, FK ranei mai i te taha 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;

Me whakahaere te tuhinga i runga i te ripanga puna me te tirotiro i nga inenga:

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

I pai nga mea katoa! Kua 50 nga wa kua heke te teepu, kua tere ano nga whakahōunga katoa.

Source: will.com

Ka rahua te VACUUM, ka horoi a ringa matou i te ripanga