ProHoster > Blog > Tsamaiso > Ha VACUUM e hlōleha, re hloekisa tafole ka letsoho

Ha VACUUM e hlōleha, re hloekisa tafole ka letsoho

VACUUM e ka "hloekisa" ho tloha tafoleng ho PostgreSQL feela eng ha ho motho ya ka bonang - ke hore, ha ho kopo e le 'ngoe e sebetsang e qalileng pele litlaleho tsena li fetoloa.

Empa ho thoe'ng haeba mofuta o joalo o sa thabiseng (moroalo oa nako e telele oa OLAP ho database ea OLTP) o ntse o le teng? Joang hloekisa ka mafolofolo ho fetola tafole pota-potiloe ke lipotso tse telele 'me u sa hata ka rake?

Ha VACUUM e hlōleha, re hloekisa tafole ka letsoho

Ho ala bokahare

Pele, a re boneng hore na bothata boo re batlang ho bo rarolla ke bofe le hore na bo ka hlaha joang.

Hangata boemo bona bo etsahala tafoleng e batlang e le nyane, empa moo e hlahang teng liphetoho tse ngata. Hangata sena kapa se fapaneng metres/aggregates/ratings, eo hangata UPDATE e etsoang ho eona, kapa mokoloko oa lekhalo ho sebetsana le liketsahalo tse ling tse ntseng li tsoela pele, tseo lirekoto tsa tsona li lulang li TSENYA/Tlosa.

Ha re leke ho hlahisa khetho ka litekanyetso:

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;

'Me ka ho ts'oana, kamanong e' ngoe, kopo e telele, e telele e qala, ho bokella lipalo-palo tse rarahaneng, empa ha e ame tafole ea rona:

SELECT pg_sleep(10000);

Hona joale re ntlafatsa boleng ba e 'ngoe ea li-counters ka makhetlo a mangata. Bakeng sa bohloeki ba teko, ha re etseng sena ka litšebelisano tse fapaneng u sebelisa dblinkka moo e tla etsahala ka nnete:

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

Ho etsahetse eng? Hobaneng le bakeng sa UPDATE e bonolo ka ho fetisisa ea rekoto e le 'ngoe nako ea ts'ebetso e fokotsehile ka makhetlo a 7 - ho tloha 0.524ms ho 3.808ms? 'Me lintlha tsa rona li ntse li eketseha butle le ho feta.

Ke molato oa MVCC kaofela.

Ke tsohle ka Mokhoa oa MVCC, e etsang hore potso e shebe liphetolelong tsohle tse fetileng tsa ho kena. Kahoo ha re hloekiseng tafole ea rona liphetolelong tse "fu":

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, ha ho letho le lokelang ho hloekisoa! Bapisa Kopo e ntseng e sebetsa ea kena-kenana le rona - ka mor'a moo, ka letsatsi le leng a ka 'na a batla ho fetolela liphetolelong tsena (ho thoe'ng haeba?),' me li lokela ho fumaneha ho eena. 'Me ka hona le VACUUM FULL e ke ke ea re thusa.

“Ho heletsa” tafole

Empa re tseba hantle hore potso eo ha e hloke tafole ea rona. Ka hona, re ntse re tla leka ho khutlisetsa ts'ebetso ea sistimi ho meeli e lekaneng ka ho tlosa ntho e ngoe le e ngoe e sa hlokahaleng tafoleng - bonyane "ka letsoho", kaha VACUUM e fana ka eona.

Ho e hlakisa haholoanyane, a re shebeng mohlala oa nyeoe ea tafole ea buffer. Ke hore, ho na le phallo e kholo ea INSERT / DELETE, 'me ka linako tse ling tafole e se na letho. Empa haeba e se na letho, re tlameha boloka litaba tsa eona tsa hajoale.

#0: Ho hlahloba boemo

Ho hlakile hore u ka leka ho etsa ho hong ka tafole esita le ka mor'a ts'ebetso e 'ngoe le e' ngoe, empa sena ha se utloahale haholo - tlhokomelo ea tlhokomelo e tla ba kholo ho feta tlhahiso ea lipotso tse lebisitsoeng.

Ha re theheng litekanyetso - "ke nako ea ho nka khato" haeba:

  • VACUUM e qalile khale haholo
    Re lebeletse mojaro o boima, kahoo a ho be joalo Metsotsoana ea 60 ho tloha ka [auto]VACUUM ea ho qetela.
  • boholo ba tafole ya mmele bo boholo hofeta sepheo
    Ha re e hlalose e le habeli palo ea maqephe (li-blocks tsa 8KB) mabapi le boholo bo tlase - 1 blk bakeng sa qubu + 1 blk bakeng sa index ka 'ngoe - bakeng sa tafole e ka bang se na letho. Haeba re lebelletse hore palo e itseng ea data e tla lula e le "buffer" ka tloaelo, hoa utloahala ho fetola foromo ena.

Kopo ea netefatso

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: E ntse e le VACUUM

Re ke ke ra tseba esale pele hore na potso e ts'oanang e re sitisa haholo - hantle-ntle hore na ke lirekoto tse kae tse "fetetsoeng ke nako" ho tloha ha e qala. Ka hona, ha re etsa qeto ea ho sebetsana le tafole ka tsela e itseng, ho sa tsotellehe boemo leha e le bofe, re lokela ho qala ho phethahatsa ho eona VACUUM - ho fapana le VACUUM FULL, ha e kena-kenane le mekhoa e ts'oanang e sebetsang le data ea ho bala-ho ngola.

Ka nako e ts'oanang, e ka hloekisa hang-hang boholo ba seo re ka ratang ho se tlosa. E, 'me lipotso tse latelang tafoleng ena li tla ea ho rona ka "hot cache", e tla fokotsa nako ea bona - 'me, ka hona, nako eohle ea ho thibela ba bang ka ts'ebetso ea rona ea litšebeletso.

#2: Ho na le motho lapeng?

Ha re hlahlobeng hore na ho na le letho tafoleng ho hang:

TABLE tbl LIMIT 1;

Haeba ho se na rekoto e le 'ngoe e setseng, re ka boloka chelete e ngata ha re e sebetsa ka ho etsa feela KHOTHATSA:

E sebetsa ka mokhoa o ts'oanang le taelo e sa lekanyetsoang ea DELETE bakeng sa tafole ka 'ngoe, empa e potlakile haholo kaha ha e hlile ha e hlahlobe litafole. Ho feta moo, hang-hang e lokolla sebaka sa disk, kahoo ha ho hlokahale ho etsa ts'ebetso ea VACUUM ka mor'a moo.

Hore na o hloka ho seta botjha k'haontara ya tatelano ya tafole (RESTART IDENTITY) ho ho wena ho etsa qeto.

#3: Batho bohle - fapanyetsana!

Kaha re sebetsa maemong a competitive haholo, ha re le mona re ntse re hlahloba hore na ha ho na li-entries ka har’a tafole, motho a ka be a se a ngotse ho hong mono. Ha rea ​​​​lokela ho lahleheloa ke tlhahisoleseling ena, joale ho thoe'ng? Ke ’nete, re lokela ho etsa bonnete ba hore ha ho na motho ea ka e ngolang fatše.

Ho etsa sena re hloka ho nolofalletsa SERIALIZABE-ho itšehla thajana bakeng sa thekiso ea rona (e, mona re qala khoebo) ebe u notlela tafole "ka thata":

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

Boemo bona ba thibelo bo khethoa ke ts'ebetso eo re batlang ho e etsa ho eona.

#4: Khohlano ea thahasello

Re tla mona 'me re batla ho "notlela" letšoao - ho thoe'ng haeba motho e mong a ne a le mafolofolo ho eona ka nako eo, mohlala, ho bala ho eona? Re tla "fanyeha" re emetse hore block ena e lokolloe, 'me ba bang ba batlang ho bala ba tla kopana le rona ...

Ho thibela sena hore se se ke sa etsahala, re tla "itela" - haeba re ne re sa khone ho fumana senotlolo ka nako e itseng (e khuts'oane), joale re tla fumana mokhelo ho tloha setsing, empa bonyane re ke ke ra kena-kenana le ho feta. ba bang.

Ho etsa sena, beha sebopeho sa seshene lock_timeout (bakeng sa liphetolelo tsa 9.3+) kapa/le statement_timeout. Ntho e ka sehloohong eo u lokelang ho e hopola ke hore boleng ba statement_timeout bo sebetsa feela polelong e latelang. Ke hore, joalo ka gluing - e ke ke ea sebetsa: 

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

E le hore u se ke ua tlameha ho sebetsana le ho tsosolosa boleng ba "khale" ba ho fetoha hamorao, re sebelisa foromo PHETHA LEHAE, e lekanyetsang sebaka sa litlhophiso ho transaction ea hajoale.

Re hopola hore statement_timeout e sebetsa ho likopo tsohle tse latelang e le hore khoebo e se ke ea fihla ho boleng bo sa amoheleheng haeba ho na le data e ngata tafoleng.

#5: Kopitsa data

Haeba tafole e se na letho ka botlalo, data e tla tlameha ho bolokoa hape ho sebelisoa tafole e thusang ea nakoana:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Saena THABANG LEROTHA ho bolela hore ha ts'ebetso e fela, tafole ea nakoana e tla khaotsa ho ba teng, 'me ha ho hlokahale hore u e tlose ka letsoho molemong oa ho hokahanya.

Kaha re nka hore ha ho na data e ngata ea "phela", ts'ebetso ena e lokela ho etsahala kapele.

Che, ho joalo feela! U se ke ua lebala ka mor'a ho qeta transaction matha HLAHLOBA ho tloaeleha lipalo-palo tsa tafole ha ho hlokahala.

Ho kopanya script ea ho qetela

Re sebelisa "pseudo-python" ena:

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

Na hoa khoneha hore u se ke ua kopitsa data lekhetlo la bobeli?Ha e le hantle, hoa khoneha haeba oid ea tafole ka boeona e sa tlameletsoe mesebetsing efe kapa efe ho tloha lehlakoreng la BL kapa FK ho tloha lehlakoreng la 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;

Ha re kenye sengoloa tafoleng ea mohloli 'me re hlahlobe metrics:

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

Tsohle li ile tsa sebetsa! Tafole e fokotsehile ka makhetlo a 50 'me li-UPDATE li sebetsa ka potlako hape.

Source: www.habr.com

Eketsa ka tlhaloso