ProHoster > Blog > Pulega > A le manuia le VACUUM, matou te fufulu le laulau ma le lima

A le manuia le VACUUM, matou te fufulu le laulau ma le lima

VACUUM e mafai ona "faʻamamaina" mai se laulau i PostgreSQL naʻo le a e leai se tasi e mafai ona vaai - o lona uiga, e leai se talosaga malosi e tasi na amata a'o le'i suia nei faamaumauga.

Ae fa'afefea pe a fai o lo'o iai pea se ituaiga le lelei (o le uta OLAP umi i luga o le database OLTP)? E faapefea mama mataalia sui laulau si'omia i fesili uumi ae le la'a i se salu?

A le manuia le VACUUM, matou te fufulu le laulau ma le lima

Fa'ata'atia le salu

Muamua, seʻi o tatou iloilo po o le ā le faafitauli tatou te mananaʻo e foʻia ma pe faapefea ona tulaʻi mai.

E masani lava ona tupu lea tulaga i luga o se laulau laititi, ae o le mea e tupu ai tele suiga. E masani lava o lenei pe ese mita/fa'apotopoto/fa'atatau, lea e masani ona faʻatinoina le UPDATE, poʻo fa'a-tulaga e fa'agasolo ai fa'asologa o mea tutupu faifaipea, fa'amaumauga o lo'o fa'aulu i taimi uma/TA'E.

Sei o tatou taumafai e toe faia le filifiliga ma fa'ailoga:

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;

Ma i se tulaga tutusa, i se isi fesoʻotaʻiga, o se talosaga umi, umi e amata, aoina mai ni fuainumera lavelave, ae e le afaina ai la tatou laulau:

SELECT pg_sleep(10000);

O lea ua matou fa'afouina le tau o se tasi o fa'atau i le tele, fa'atele. Mo le mama o le suʻega, seʻi o tatou faia lenei mea i fefaʻatauaiga eseese e faʻaaoga ai le dblinkpe faapefea ona tupu i le mea moni:

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

O le a le mea ua tupu? Aisea e oʻo lava i le faʻafou faigofie o se faʻamaumauga e tasi taimi fa'ataunu'u fa'aleaogaina ile 7 taimi — mai le 0.524ms i le 3.808ms? Ma o lo'o fa'atupula'ia lo tatou fa'ailoga ua fa'agasolo malie.

E sese uma a le MVCC.

E uiga uma lava MVCC masini, lea e mafua ai ona su'e le su'esu'ega i fa'aliliuga uma o le fa'amatalaga muamua. Se'i o tatou fa'amama la tatou laulau mai fa'aliliuga "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

Oi, e leai se mea e faamama! Fa'atasi O lo'o fa'alavelave ia i matou le talosaga o lo'o fa'agasolo - pe a uma, atonu o le a i ai se aso e manaʻo ai e liliu atu i nei faʻaliliuga (pe a pe a?), ma e tatau ona avanoa mo ia. Ma o lea e oo lava i le VACUUM FULL o le a le fesoasoani ia i matou.

"Fa'alalo" le laulau

Ae matou te iloa mautinoa o lena fesili e le manaʻomia la matou laulau. O le mea lea, o le a matou taumafai pea e toe faʻafoʻi le faʻatinoga o le faiga i tapulaʻa talafeagai e ala i le faʻaumatia mea uma e le manaʻomia mai le laulau - ia le itiiti ifo i le "manuʻa", talu ai ua fiu le VACUUM.

Ina ia faʻamalamalama atili, seʻi o tatou vaʻavaʻai i le faʻataʻitaʻiga o le tulaga o se laulau paluga. O lona uiga, o loʻo i ai se tafe tele o INSERT/DELETE, ma o nisi taimi e matua gaogao le laulau. Ae afai e le o gaogao, e tatau fa'asaoina mea o iai nei.

#0: Iloiloina le tulaga

E manino lava e mafai ona e taumafai e fai se mea i luga o le laulau e tusa lava pe maeʻa taʻaloga taʻitasi, ae e le taua tele - o le tausiga i luga o le ulu o le a sili atu le sili atu nai lo le faʻaogaina o fesili faʻatatau.

Seʻi o tatou faʻatulagaina le taʻiala - "ua oʻo i le taimi e galue ai" pe afai:

  • VACUUM na faʻalauiloaina i se taimi ua leva
    Matou te faamoemoe i se avega mamafa, ia tuu pea 60 sekone talu mai le [auto] VACUUM mulimuli.
  • lapopo'a le laulau fa'aletino nai lo le fa'amoemoe
    Sei o tatou faauigaina e faaluaina le numera o itulau (8KB poloka) e faatatau i le lapopoa aupito maualalo - 1 blk mo fa'aputuga + 1 blk mo fa'ailoga ta'itasi - mo se laulau e ono avanoa. Afai tatou te faʻamoemoe o le a tumau pea se aofaʻi o faʻamaumauga i totonu o le paʻu "masani", e talafeagai le faʻaogaina o lenei fua.

Talosaga fa'amaonia

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: Fa'aauau pea le VACUUM

E le mafai ona matou iloa muamua pe o se fesili tutusa o loʻo faʻalavelave tele ia i matou - o le a tonu le aofaʻi o faʻamaumauga ua "le aso" talu ona amata. O le mea lea, pe a tatou filifili e faʻagasolo le laulau, i soʻo se tulaga, e tatau ona tatou faʻatinoina muamua VACUUM - e le pei o le VACUUM FULL, e le faʻalavelave i faiga tutusa o loʻo galue ma faʻamaumauga faitau-tusi.

I le taimi lava e tasi, e mafai ona vave faʻamama le tele o mea tatou te manaʻo e aveese. Ioe, ma o fesili mulimuli ane i luga o lenei laulau o le a oʻo mai ia i matou e ala i le "hot cache", lea o le a faʻaitiitia ai lo latou umi - ma, o le mea lea, o le taimi atoa o le polokaina o isi e ala i la matou auaunaga tau fefaʻatauaiga.

#2: E iai se tasi i le fale?

Sei o tatou siaki pe o iai se mea i le laulau:

TABLE tbl LIMIT 1;

Afai e leai se fa'amaumauga e tasi o totoe, ona mafai lea ona tatou fa'apolopolo tele i le fa'agaioiga i le na'o le faia FUAFUAGA:

E tutusa lava le fa'atonuina ole DELETE ole fa'atonu mo laulau ta'itasi, ae sili atu le vave talu ai e le'o su'esu'eina laulau. E le gata i lea, e vave faʻasaʻoloto le avanoa disk, o lea e leai se manaʻoga e faia se taotoga VACUUM mulimuli ane.

Pe e te mana'omia le toe setiina o le fa'asologa o le laulau (RESTART IDENTITY) e pule lava oe.

#3: Tagata uma - feauaua'i!

Talu ai matou te galulue i se si'osi'omaga fa'atauva'a, a'o matou i ai iinei e siaki e leai ni fa'amaumauga i le laulau, e mafai e se tasi ona tusia se mea iina. E le tatau ona tatou leiloa lenei faʻamatalaga, o le a la? E sa'o, e tatau ona tatou mautinoa e leai se tasi e mafai ona tusia i lalo ma le mautinoa.

Ina ia faia lenei mea tatou te manaʻomia e mafai ai SERIALIZABLE- vavaeeseina mo la tatou fefaʻatauaiga (ioe, o iinei tatou te amata ai se fefaʻatauaiga) ma loka le laulau "malosi":

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

O lenei tulaga o le polokaina e fuafua i gaioiga tatou te mananao e fai i luga.

#4: Fete'ena'iga o tu'inanau

Matou te o mai iinei ma mananaʻo e "loka" le faʻailoga - faʻapefea pe a fai o loʻo galue se tasi i lena taimi, mo se faʻataʻitaʻiga, faitau mai ai? O le a matou "tautau" e faatali mo le tatalaina o lenei poloka, ma o isi e fia faitau o le a taufetuli mai ia i matou ...

Ina ia taofia lenei mea mai le tupu, o le a tatou "taulagaina i tatou lava" - afai e le mafai ona tatou maua se loka i totonu o se taimi (talitonu puupuu), ona tatou maua lea o se tuusaunoaga mai le faavae, ae o le mea sili tatou te le faʻalavelave tele. isi.

Ina ia faia lenei mea, seti le fesuiaiga o le sauniga loka_taimi (mo lomiga 9.3+) poʻo/ma fa'amatalaga_taimi. O le mea autu e manatua o le faʻamatalaga_timeout tau naʻo le faʻaaogaina mai le isi faʻamatalaga. O lona uiga, e pei o lenei i le fa'apipi'i- e le aoga:

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

Ina ia aua neʻi feagai ma le toe faʻaleleia o le "tuai" tau o le fesuiaiga mulimuli ane, matou te faʻaogaina le fomu FA'ATU I LE NU'U, lea e faʻatapulaʻaina le lautele o le faʻatulagaina i le fefaʻatauaiga o loʻo iai nei.

Matou te manatua lena faʻamatalaga_timeout e faʻatatau i talosaga uma mulimuli ane ina ia le mafai e le fefaʻatauaʻiga ona faʻalautele i tau e le taliaina pe afai e tele faʻamatalaga i le laulau.

#5: Kopi fa'amaumauga

Afai e le'o gaogao atoa le laulau, e tatau ona toe fa'asaoina fa'amaumauga e fa'aaoga ai se laulau le tumau fesoasoani:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Saini I LE COMMIT DROP o lona uiga o le taimi e muta ai le fefaʻatauaiga, o le laulau le tumau o le a le toe i ai, ma e leai se manaʻoga e tape ma le lima i le fesoʻotaʻiga fesoʻotaʻiga.

Talu ai matou te manatu e le tele ni faʻamatalaga "ola", o lenei gaioiga e tatau ona fai vave.

Ia, pau lava lena! Aua nei galo pe a uma le fefaʻatauaiga tamomoe ILOILO e fa'avasega fuainumera laulau pe a mana'omia.

Tu'u fa'atasi le fa'amaumauga mulimuli

Matou te faʻaaogaina lenei "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;

E mafai ona le kopiina faʻamatalaga mo le taimi lona lua?I le mataupu faavae, e mafai pe afai o le oid o le laulau lava ia e le o noatia i soʻo se isi gaioiga mai le itu BL poʻo le FK mai le itu 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;

Se'i o tatou fa'agasolo le tusitusiga i luga o le laulau fa'apogai ma siaki fua fa'atatau:

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

Na manuia mea uma! Ua fa'aitiiti le laulau i le 50 taimi ma o fa'afou uma ua toe tamo'e vave.

puna: www.habr.com

Faʻatau talimalo faʻatuatuaina mo nofoaga ma DDoS puipuiga, VPS VDS servers 🔥 Fa'atau le 'upega tafa'ilagi talimalo fa'atuatuaina ma le puipuiga DDoS, 'au'aunaga VPS VDS | ProHoster