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 снова бегают быстро.
    Тензор
    Разработчик системы СБИС

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

      +1
      На самом деле, очень мало людей знают про эту особенность MVCC постгреса. На собеседованиях никто не отвечает
        0
        Вы изобрели VACUUM FULL. Удерживая access exclusive прочитать всю таблицу, записать живые данные в новый relfilenode, подменить старый на новый.
          0
          Увы, нет.
          Параллельно выполняющийся запрос нам мешает — ведь он когда-то может захотеть обратиться к этим версиям (а вдруг?), и они должны быть ему доступны. И поэтому даже VACUUM FULL нам не поможет.
            0
            Вы изобрели именно vacuum full.
            Если параллельный запрос вам мешает — вы не сможете взять access exclusive.
              +1
              Нет, он мешает VACUUM FULL не наложить блокировку на таблицу, а считать предыдущие версии записей «полностью мертвыми» и не перенести их в новый relfilenode.
                0

                VACUUM FULL все же накладывает эксклюзивную блокировку (ожидает завершения транзакций на таблице). Проверить просто — открыть два окна, в одном начать транзакцию и выбрать что-то из таблицы, в другом выполнитьVACUUM FULL. Он не начнется пока не будет завершена транзакция в другом окне.


                Соглашусь с предыдущим комментарием — выглядит так, как будто можно заменить на


                SET statement_timeout = '1s';
                VACUUM FULL table_name;
                  +2
                  Безусловно, и 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 и не может быть выполнен внутри транзакции.
                    0

                    Ого. Интересно.Заставило почитать исходный код. Вы ведь используете репликацию с 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 и на что он влияет на мастере)

                      0
                      Все эти тесты гонялись на изолированном мастере v11, никаких реплик. Там еще кучка отдельных граблей была бы в придачу.
          0

          Спасибо за статью, узнал про запрос 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;
            0
            pg_repack — хороший инструмент для больших таблиц. А для своей «маленькой и быстрой» очереди хочется контролировать поведение от и до непосредственно из приложения.
            А имена индексов… на моей памяти они сами по себе большой ценности никогда не представляли.
            +1

            Полезное дело делаете!
            Пара уточнений.


            Да и последующие запросы по этой таблице пойдут у нас по «горячему кэшу»

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


            Для этого нам необходимо включить SERIALIZABLE-изоляцию для нашей транзакции

            Достаточно и Read Committed, вы же вручную ставите блокировку.

              0
              Спасибо за инфу, про кольцо VACUUM был не в курсе, учту на будущее. А кольцо — общее на все экземпляры? То есть пройдет ли VACUUM FULL быстрее, если перед ним выполнить VACUUM по той же таблице? Опыт подсказывает, что таки да.

              С READ COMMITED возникала, насколько помню, проблема при обращении параллельной транзакции. Вот только последний раз проверял такой кейс под реальной нагрузкой чуть ли не на 9.1 — возможно, был какой-то баг.
                0

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


                Насчёт Read Committed — скорее всего в чем-то другом дело было. Против Access Exclusive не попрешь.
                (К тому же Serializable работает только если и остальные транзакции используют тот же уровень. Иначе все это выражается в Repeatable Read.)

                  0
                  Я так понимаю, это кусочек shared buffers, но в pagecache операционки эти страницы будут доступны с очень большой вероятностью?
                    0

                    Это да.

                  0

                  Про кольца нигде особо не написано, но погрепайте buffer ring в исходниках.

                0

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

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

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое