🥇ВАКУУМ сәтсіз болған кезде үстелді қолмен тазалаймыз

VACUUM бұл тек PostgreSQL-дегі кестеден «тазалай алады». ешкім көре алмайды - яғни бұл жазбалар өзгертілмей тұрып басталған бірде-бір белсенді сұрау жоқ.

Бірақ мұндай жағымсыз түрі (OLTP дерекқорында ұзақ мерзімді OLAP жүктемесі) бар болса ше? Қалай? белсенді өзгеретін кестені тазалаңыз ұзақ сұраулармен қоршалған және тырмаға баспайсыз ба?

Тырманды орналастырайық

Алдымен, біз шешкіміз келетін мәселенің не екенін және оның қалай туындауы мүмкін екенін анықтайық.

Бұл жағдай әдетте орын алады салыстырмалы түрде кішкентай үстелде, бірақ ол қай жерде болады көп өзгерістер. Әдетте бұл әртүрлі есептегіштер/агрегаттар/рейтингтер, UPDATE өте жиі орындалатын немесе буфер кезегі жазбалары үнемі INSERT/DELETE болатын оқиғалардың кейбір тұрақты ағынын өңдеу үшін.

Рейтингтері бар нұсқаны шығаруға тырысайық:

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;

Сонымен қатар, басқа байланыста өте ұзақ сұрау басталады, кейбір күрделі статистиканы жинайды, бірақ үстелімізге әсер етпейді:

SELECT pg_sleep(10000);

Енді біз есептегіштердің бірінің мәнін бірнеше рет жаңартамыз. Эксперименттің тазалығы үшін біз мұны жасаймыз dblink арқылы бөлек транзакцияларда, бұл іс жүзінде қалай болады:

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

Не болды? Неліктен бір жазбаның ең қарапайым ЖАҢАЛЫҚТАРЫ үшін де орындау уақыты 7 есеге төмендеді — 0.524 мс-тен 3.808 мс дейін? Ал біздің рейтингіміз барған сайын баяу құрылуда.

Барлығына MVCC кінәлі

Мұның бәрі туралы MVCC механизмі, бұл сұрауды жазбаның барлық алдыңғы нұсқаларын қарауға мәжбүр етеді. Сонымен, «өлі» нұсқалар кестесін тазалап көрейік:

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

О, тазалайтын ештеңе жоқ! Параллельде ағымдағы сұрау жұмысымызға кедергі келтіруде — ақыр соңында, ол бұл нұсқаларға бір күні қол жеткізгісі келуі мүмкін (егер болса?) және олар қолжетімді болуы керек. Сондықтан тіпті ВАКУМ ФУЛЛ бізге көмектеспейді.

Біз үстелді ұрамыз

Бірақ біз бұл сұрауға біздің кестемізді қажет етпейтінін анық білеміз. Сондықтан кестеден барлық қажет емес деректерді жою арқылы жүйе өнімділігін ақылға қонымды шектерге қайтаруға тырысайық - VACUUM жұмыс істемейтіндіктен, кем дегенде қолмен.

Бұл түсінікті болу үшін буферлік кестенің мысалын қарастырайық. Яғни, INSERT/DELETE үлкен ағыны бар, кейде кесте толығымен бос болады. Бірақ бос болмаса, біз керек оның ағымдағы мазмұнын сақтаңыз.

№0: Жағдайды бағалаңыз

Әрбір операциядан кейін кестемен бірдеңе жасауға тырысуға болатыны анық, бірақ мұның мағынасы жоқ — техникалық қызмет көрсетуге жұмсалатын шығындар мақсатты сұраулардың өткізу қабілетінен көбірек болатыны анық.

Критерийлерді тұжырымдаймыз - «әрекет ету уақыты келді», егер:

ВАКУУМ көп уақыт бұрын іске қосылған
Біз ауыр жүк күтеміз, солай болсын 60 секунд соңғы [авто]ВАКУУМнан бері.
кестенің физикалық өлшемі мақсаттыдан үлкенірек
Оны минималды өлшемге қатысты беттердің екі еселенген саны (8 КБ блоктар) ретінде анықтайық - Бір үймеге 1 блк + индекске 1 блк — ықтимал бос кесте үшін. Егер буферде әрқашан деректердің белгілі бір көлемі болады деп күтсек, бұл формуланы реттеу орынды болады.

Тексеру сұрауы

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 — VACUUM FULL-тен айырмашылығы, ол оқу-жазу деректерімен жұмыс істейтін параллельді процестерге кедергі жасамайды.

Сонымен қатар, ол біз жойғымыз келетін нәрселердің көпшілігін бірден тазарта алады. Осы кестедегі келесі сұрауларды біз әлі де өңдейміз. «ыстық кэш» арқылы, бұл олардың ұзақтығын қысқартады - демек, біздің қызмет көрсету транзакциямыз арқылы басқаларды блоктаудың жалпы уақыты.

№2: Үйде біреу бар ма?

Кестеде бірдеңе бар-жоғын тексерейік:

TABLE tbl LIMIT 1;

Егер бірде-бір жазба қалмаса, біз жай ғана іске қосу арқылы өңдеуге көп нәрсені үнемдей аламыз ТРУНКАТ:

Ол әрбір кесте үшін шартсыз ЖОЮ пәрменімен бірдей жұмыс істейді, бірақ әлдеқайда жылдамырақ, себебі ол кестелерді нақты сканерлемейді. Сонымен қатар, ол дискілік кеңістікті бірден қалпына келтіреді, сондықтан кейін ВАКУМ операциясын орындаудың қажеті жоқ.

Кесте реті есептегішін (RESTART IDENTITY) қалпына келтіру қажет пе, соны өзіңіз шешесіз.

№3: Барлығы, бір уақытта!

Біз жоғары бәсекелестік ортада жұмыс істейтіндіктен, кестеде жоқ жазбаларды тексеріп жатқанда, біреу сонда бірдеңе жазған болуы мүмкін. Біз бұл ақпаратты жоғалтпауымыз керек, сонда ше? Дұрыс, ол жерге ешкім ештеңе жаза алмайтынына көз жеткізуіміз керек.

Мұны істеу үшін бізге қосу керек СЕРИЯЛЫҚ- транзакциямызды оқшаулау (иә, біз транзакцияны осы жерден бастаймыз) және кестені «өлі» құлыптаймыз:

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

Дәл осы блоктау деңгейі біз оған орындағымыз келетін операциялармен анықталады.

№4: Мүдделер қақтығысы

Біз осында келіп, белгіні «құлыптағымыз» келеміз, бірақ егер сол кезде біреу белсенді болса, мысалы, оны оқу сияқты? Біз құлыпты күтеміз, ал басқа оқығысы келетіндер бізбен бірге қалады...

Бұған жол бермеу үшін біз «өзімізді құрбан етеміз» - егер біз әлі де белгілі бір (қолайлы қысқа) уақыт ішінде құлыпты ала алмасақ, онда біз дерекқордан ерекшелік аламыз, бірақ кем дегенде біз басқаларға тым көп араласпаймыз.

Ол үшін сеанс айнымалысын орнатамыз lock_timeout (9.3+ нұсқалары үшін) немесе/және мәлімдеме_күту уақытыЕсте сақтау керек ең бастысы, statement_timeout мәні келесі мәлімдемеден бастап ғана қолданылады. Сонымен, келесідей конкатенацияда: ол жұмыс істемейді:

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

Айнымалының «ескі» мәнін кейін қалпына келтірмеу үшін пішінді қолданамыз ЖЕРГІЛІКТІ ОРНАТУ, ол параметр ауқымын ағымдағы транзакцияға шектейді.

Кестеде деректер көп болса, транзакция қабылданбайтын ұзындыққа созылмауы үшін мәлімдеме_уақыты барлық кейінгі сұрауларға қолданылатынын есте сақтаңыз.

№5: Деректерді көшіріңіз

Кесте толығымен бос болмаса, деректерді қосалқы уақытша кесте арқылы қайта сақтау керек болады:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Қол қою ON COMMIT DOP транзакцияның соңында уақытша кестенің жұмысын тоқтататынын және оны қосылым контекстінде қолмен жоюдың қажеті жоқ дегенді білдіреді.

«Тірі» деректер көп емес деп есептейтіндіктен, бұл операция өте жылдам болуы керек.

Ал, бұл туралы! Транзакцияны аяқтағаннан кейін ұмытпаңыз ANALYZE іске қосыңыз қажет болған жағдайда кесте статистикасын қалыпқа келтіру.

Соңғы сценарийді құрастыру

Біз бұл «псевдо-питонды» қолданамыз:

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

Деректерді екінші рет көшірмеуге бола ма?Негізінде, кестенің оидының өзі BL жағындағы басқа әрекеттермен немесе ДҚ жағындағы FK әрекеттерімен байланысты болмаса мүмкін:

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

Сценарийді бастапқы кестеде іске қосып, көрсеткіштерді тексерейік:

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

Барлығы жұмыс істеді! Кесте 50 есе қысқарды және барлық ЖАҢАЛЫҚТАР қайтадан жылдам іске қосылды.

Ақпарат көзі: www.habr.com

VACUUM сәтсіз болғанда, біз үстелді қолмен тазалаймыз.