🥇Калі пасуе VACUUM - чысцім табліцу ўручную

Вакуумныя можа "зачысціць" з табліцы ў PostgreSQL толькі тое, што ніхто не можа ўбачыць — гэта значыць няма ніводнага актыўнага запыту, які стартаваў раней, чым гэтыя запісы былі змененыя.

А калі такі непрыемны тып (працяглая OLAP-нагрузка на OLTP-базе) усё ж ёсць? Як пачысціць актыўна зменлівую табліцу у асяроддзі доўгіх запытаў і не наступіць на граблі?

Раскладваем граблі

Спачатку вызначым, у чым жа заключаецца і як наогул можа ўзнікнуць праблема, якую мы хочам вырашыць.

Звычайна такая сітуацыя здараецца на адносна невялікай табліцы, але ў якой адбываецца вельмі шмат змен. Звычайна гэта ці розныя лічыльнікі/агрэгаты/рэйтынгі, на якіх часта-часта выконваецца 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

Што ж адбылося? Чаму нават для найпростага UPDATE адзінага запісу час выканання дэградавала ў 7 разоў - з 0.524ms да 3.808ms? Ды і рэйтынг наш будуецца ўсё больш павольна і павольна.

Ва ўсім вінаваты 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 FULL нам не паможа.

«Схлопваем» табліцу

Але мы сапраўды ведаем, што таму запыту наша табліца не патрэбна. Таму паспрабуем усёткі вярнуць прадукцыйнасць сістэмы ў адэкватныя рамкі, выкінуўшы з табліцы ўсё лішняе хоць бы і уручную , раз VACUUM пасуе.

Каб было навочней, разгледзім ужо на прыкладзе выпадку табліцы-буфера. Гэта значыць ідзе вялікі струмень INSERT/DELETE, і часам у табліцы апыняецца наогул пуста. Але калі там не пуста, мы павінны захаваць яе бягучае змесціва.

#0: Ацэньваем сітуацыю

Зразумела, што можна спрабаваць нешта рабіць з табліцай хоць пасля кожнай аперацыі, але вялікага сэнсу гэта не мае - накладныя выдаткі на абслугоўванне будуць відавочна большыя, чым прапускная здольнасць мэтавых запытаў.

Сфармулюем крытэры - "ужо пара дзейнічаць", калі:

VACUUM запускаўся дастаткова даўно
Нагрузку чакаем большую, таму няхай гэта будзе 60 секунд з апошняга [auto]VACUUM.
фізічны памер табліцы больш мэтавага
Вызначым яго як падвоеную колькасць старонак (блокаў па 8KB) адносна мінімальнага памеру 1 blk на heap + 1 blk на кожны з індэксаў - Для патэнцыйна-пустой табліцы. Калі ж мы чакаем, што ў буферы "штатна" будзе заўсёды заставацца некаторы аб'ём дадзеных, гэтую формулу разумна падцюніць.

Праверачны запыт

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;

Калі не засталося ніводнага запісу, то мы можам моцна зэканоміць на апрацоўцы - проста выканаўшы АДКРЫЦЬ:

Яна дзейнічае гэтак жа, як безумоўная каманда DELETE для кожнай табліцы, але значна хутчэй, бо яна фактычна не скануе табліцы. Больш за тое, яна неадкладна вызваляе дыскавую прастору, так што выконваць аперацыю VACUUM пасля яе не патрабуецца.

Ці трэба вам пры гэтым скідаць лічыльнік паслядоўнасці табліцы (RESTART IDENTITY) - вырашайце самі.

#3: Усё - па-чарзе!

Паколькі мы працуем ва ўмовах высокай канкурэнтнасці, то пакуль мы тут правяраем адсутнасць запісаў у табліцы, нехта мог туды ўжо нешта запісаць. Страціць гэтую інфармацыю мы не павінны, значыць - што? Правільна, трэба зрабіць, каб ніхто ўжо сапраўды запісаць не мог.

Для гэтага нам неабходна ўключыць СЕРЫЯЛІЗУЕМАЯ-ізаляцыю для нашай транзакцыі (так, тут мы стартуем транзакцыю) і заблакаваць табліцу «намёртва»:

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

Менавіта такі ўзровень блакавання абумоўлены тымі аперацыямі, якія мы жадаем над ёй вырабляць.

#4: Канфлікт інтарэсаў

Мы тут прыходзім і хочам таблічку "залачыць" — а калі на ёй у гэты момант нехта быў актыўны, напрыклад, чытаў з яе? Мы «павіснем» у чаканні вызвалення гэтага блакавання, а іншыя жадаючыя пачытаць упруцца ўжо ў нас…

Каб такога не адбылося, ахвяруем сабой калі ўжо за вызначаны (дапушчальна малое) час блакаванне нам атрымаць усёткі не атрымалася, то мы атрымаем ад базы exception, але хоць бы не перашкодзім моцна астатнім.

Для гэтага выставім зменную сесіі lock_timeout (для версій 9.3+) або/і statement_timeout. Галоўнае памятаць, што значэнне statement_timeout ужываецца толькі з наступнага statement. Гэта значыць вось так у склейцы. не запрацуе:

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

Каб не займацца потым аднаўленнем "старога" значэння зменнай, выкарыстоўваем форму SET LOCAL, якая абмяжоўвае вобласць дзеяння наладкі бягучай транзакцыяй.

Памятаем, што statement_timeout распаўсюджваецца на ўсе наступныя запыты, каб транзакцыя не магла ў нас расцягнуцца да непрымальных велічынь, калі дадзеных у табліцы такі апынецца шмат.

#5: Капіюем дадзеныя

Калі табліца аказалася не зусім пустая — дадзеныя давядзецца перазахоўваць праз дапаможную часовую таблічку:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Сігнатура ON COMMIT DROP азначае, што ў момант заканчэння транзакцыі часовая табліца перастане існаваць, і займацца яе ручным выдаленнем у кантэксце злучэння не трэба.

Паколькі мы мяркуем, што "жывых" дадзеных не вельмі шмат, то гэтая аперацыя павінна прайсці дастаткова хутка.

Ну вось як бы і ўсё! Не забывайце пасля завяршэння транзакцыі запусціць 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;

А можна не капіраваць дадзеныя другі раз?У прынцыпе, можна, калі на oid самой табліцы не завязаныя нейкія іншыя актыўнасці са боку БЛ ці 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 разоў, і ўсё UPDATE зноў бегаюць хутка.

Крыніца: habr.com

Калі пасуе VACUUM - чысцім табліцу ўручную