DBA: когда пасует VACUUM — чистим таблицу вручную

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