Когда пасует VACUUM — чистим таблицу вручную / forpes.ru

Главная
Когда пасует VACUUM — чистим таблицу вручную

Когда пасует VACUUM — чистим таблицу вручную +20

25.12.2019 17:15

Kilor 19 4300 Источник

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 — он, в отличие от VACUUM FULL, параллельным процессам работать с данными на чтение-запись не мешает.

Заодно он может сразу вычистить большую часть того, что мы хотели бы убрать. Да и последующие запросы по этой таблице пойдут у нас по «горячему кэшу», что сократит их продолжительность — а, значит, и суммарное время блокировки других нашей обслуживающей транзакцией.

#2: Есть кто-нибудь дома?

Давайте проверим — есть ли в таблице вообще хоть что-то:

TABLE tbl LIMIT 1;

Если не осталось ни единой записи, то мы можем сильно сэкономить на обработке — просто выполнив TRUNCATE:

Она действует так же, как безусловная команда DELETE для каждой таблицы, но гораздо быстрее, так как она фактически не сканирует таблицы. Более того, она немедленно высвобождает дисковое пространство, так что выполнять операцию VACUUM после неё не требуется.

Надо ли вам при этом сбрасывать счетчик последовательности таблицы (RESTART IDENTITY) — решайте сами.

#3: Все — по-очереди!

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

Для этого нам необходимо включить SERIALIZABLE-изоляцию для нашей транзакции (да, тут мы стартуем транзакцию) и заблокировать таблицу «намертво»:

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 снова бегают быстро.

Комментарии (19)

le1ic
25.12.2019 20:29
#21061090
На самом деле, очень мало людей знают про эту особенность MVCC постгреса. На собеседованиях никто не отвечает

Melkij
25.12.2019 21:09
#21061202
Вы изобрели VACUUM FULL. Удерживая access exclusive прочитать всю таблицу, записать живые данные в новый relfilenode, подменить старый на новый.
1. Kilor Автор
  25.12.2019 21:11
  #21061212
  Увы, нет.
  
  Параллельно выполняющийся запрос нам мешает — ведь он когда-то может захотеть обратиться к этим версиям (а вдруг?), и они должны быть ему доступны. И поэтому даже VACUUM FULL нам не поможет.
  1. Melkij
    25.12.2019 21:17
    #21061222
    Вы изобрели именно vacuum full.
    Если параллельный запрос вам мешает — вы не сможете взять access exclusive.
    
    Kilor Автор
    25.12.2019 21:22
    #21061232
    Нет, он мешает VACUUM FULL не наложить блокировку на таблицу, а считать предыдущие версии записей «полностью мертвыми» и не перенести их в новый relfilenode.
    
    myz0ne
    25.12.2019 21:47
    #21061308
    VACUUM FULL все же накладывает эксклюзивную блокировку (ожидает завершения транзакций на таблице). Проверить просто — открыть два окна, в одном начать транзакцию и выбрать что-то из таблицы, в другом выполнитьVACUUM FULL. Он не начнется пока не будет завершена транзакция в другом окне.
    
    Соглашусь с предыдущим комментарием — выглядит так, как будто можно заменить на
    
    SET statement_timeout = '1s'; VACUUM FULL table_name;
    
    Kilor Автор
    25.12.2019 21:55
    #21061338
    +1
    Безусловно, и VACUUM FULL, и способ выше блокировку накладывают. Только VF не чистит:
    VACUUM FULL VERBOSE tbl; INFO: vacuuming "public.tbl" INFO: "tbl": found 0 removable, 10026 nonremovable row versions in 45 pages DETAIL: 10000 dead row versions cannot be removed yet.
    
    А так — да, логически они полностью идентичны, кроме момента, что VF сохраняет MVCC и не может быть выполнен внутри транзакции.
    
    myz0ne
    26.12.2019 00:17
    #21061640
    Ого. Интересно.Заставило почитать исходный код. Вы ведь используете репликацию с hot_standby_feedback? Как я понял standby вычисляет и посылает в этом случае ид транзакции с мастера (на standby нет своих номеров транзакций), которая еще хранит нужные ему строки и мастер не очищает строки чтобы не удалить строки которые нужны на standby.
    
    При этом truncate таким не страдает и спокойно очищает строки. Интересно, vacuum full это по сути запрос cluster, но в нем все равно проверяется какие строки должны оставаться видимы.
    
    https://github.com/postgres/postgres/blob/8ce3aa9b5914d1ac45ed3f9bc484f66b3c4850c7/src/backend/commands/cluster.c#L864
    
    egorov, звучит как тема для еще одной статьи — как работает host_standby_feedback и на что он влияет на мастере)
    
    Kilor Автор
    26.12.2019 00:27
    #21061652
    Все эти тесты гонялись на изолированном мастере v11, никаких реплик. Там еще кучка отдельных граблей была бы в придачу.

myz0ne
25.12.2019 21:19
#21061224
Спасибо за статью, узнал про запрос TABLE.
Возник вопрос — чем не подошел pg_repack?

И еще дополнение — в приведенном запросе имена индексов не сохранятся, добавится префикс swap. И так каждый раз.
Заголовок спойлера
CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL); INSERT INTO _swap_%table TABLE %table; DROP TABLE %table; ALTER TABLE _swap_%table RENAME TO %table;
1. Kilor Автор
  25.12.2019 21:24
  #21061242
  pg_repack — хороший инструмент для больших таблиц. А для своей «маленькой и быстрой» очереди хочется контролировать поведение от и до непосредственно из приложения.
  А имена индексов… на моей памяти они сами по себе большой ценности никогда не представляли.

erogov
25.12.2019 23:55
#21061592
+1
Полезное дело делаете!
Пара уточнений.

Да и последующие запросы по этой таблице пойдут у нас по «горячему кэшу»
Vacuum использует буферное кольцо в кеше, чтобы не вымывать из него полезные данные. Обратная сторона медали — в кеше ничего (почти ничего) не останется после его работы.

Для этого нам необходимо включить SERIALIZABLE-изоляцию для нашей транзакции
Достаточно и Read Committed, вы же вручную ставите блокировку.
1. Kilor Автор
  26.12.2019 00:08
  #21061616
  Спасибо за инфу, про кольцо VACUUM был не в курсе, учту на будущее. А кольцо — общее на все экземпляры? То есть пройдет ли VACUUM FULL быстрее, если перед ним выполнить VACUUM по той же таблице? Опыт подсказывает, что таки да.
  
  С READ COMMITED возникала, насколько помню, проблема при обращении параллельной транзакции. Вот только последний раз проверял такой кейс под реальной нагрузкой чуть ли не на 9.1 — возможно, был какой-то баг.
  1. erogov
    26.12.2019 00:38
    #21061668
    Кольцо — одно на операцию. По сути, это просто кусочек общего кеша, который из него временно «откушен».
    Думаю, ускорение там из-за того, что vacuum full ведь тоже чистит ненужные версии строк, ну и если перед этим vacuum уже прошелся, то и ему легче.
    
    Насчёт Read Committed — скорее всего в чем-то другом дело было. Против Access Exclusive не попрешь.
    (К тому же Serializable работает только если и остальные транзакции используют тот же уровень. Иначе все это выражается в Repeatable Read.)
    
    Kilor Автор
    26.12.2019 08:16
    #21062080
    Я так понимаю, это кусочек shared buffers, но в pagecache операционки эти страницы будут доступны с очень большой вероятностью?
    
    erogov
    26.12.2019 11:19
    #21062526
    Это да.
  1. erogov
    26.12.2019 00:43
    #21061676
    Про кольца нигде особо не написано, но погрепайте buffer ring в исходниках.

michailnikolaev
27.12.2019 12:06
#21066666
Об очень похожем метода мы рассказывали совсем недавно на YaTalks — https://youtu.be/hXH_tRBxFnA 05:02:53 (извиняюсь не смог с телефона получить точную ссылку — секция про очереди, доклад "Как Толока росла вместе с кластером PostgreSQL").

RinatJulchurin
27.12.2019 12:06
#21066668
Спасибо, отличная статья!
Не знал, что есть способ обойти MVCC. )
Нашел в документации соответствующее предупреждение:
«Команда TRUNCATE небезопасна с точки зрения MVCC. После опустошения таблицы она будет выглядеть пустой для параллельных транзакций, если они работают со снимком, полученным до опустошения.»

Когда пасует VACUUM — чистим таблицу вручную +20

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

Во всем виноват MVCC

«Схлапываем» таблицу

#0: Оцениваем ситуацию

#1: Все равно VACUUM

#2: Есть кто-нибудь дома?

#3: Все — по-очереди!

#4: Конфликт интересов

#5: Копируем данные

Собираем итоговый скрипт

Комментарии (19)

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор