Настройка Autovacuum в базе данных Azure для PostgreSQL

В этой статье представлены общие сведения о функции автовакуума для Базы данных Azure для PostgreSQL и руководства по устранению неполадок, доступные для мониторинга раздувания базы данных и блокировок автовакуума. Он также содержит сведения о том, насколько далеко база данных находится от чрезвычайной или замкнутой ситуации.

Что такое autovacuum?

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

• VACUUM — освобождает пространство в файлах базы данных, удаляя мертвые кортежи и отмечая это пространство как пригодное для повторного использования PostgreSQL. Он не обязательно уменьшает физический размер файлов базы данных на диске. Чтобы вернуть пространство в операционную систему, используйте операции, которые перезаписывают таблицу (например, VACUUM FULL или pg_repack), которые имеют дополнительные аспекты, такие как эксклюзивные блокировки или периоды обслуживания.
• АНАЛИЗ. Собирает статистику таблиц и индексов, которые планировщик запросов PostgreSQL использует для выбора эффективных планов выполнения.

Чтобы обеспечить правильную работу autovacuum, задайте для параметра сервера autovacuum значение ON. При включении PostgreSQL автоматически решает, когда следует запускать VACUUM или ANALYZE в таблице, гарантируя, что база данных остается эффективной и оптимизированной.

Принципы работы автоматической очистки

Autovacuum считывает страницы в поиске мертвых строк. Если он не находит мертвых кортежей, autovacuum отбрасывает страницу. Когда функция автоматической очистки находит неиспользуемые кортежи, она удаляет их. Стоимость основана на следующих параметрах:

Объем работы, который выполняет autovacuum, зависит от двух параметров.

Во всех поддерживаемых в настоящее время версиях PostgreSQL значение autovacuum_vacuum_cost_limit по умолчанию равно 200 (фактически— значение -1, что делает его равным значению регулярного vacuum_cost_limit, по умолчанию — 200).

Значение autovacuum_vacuum_cost_delay по умолчанию — 2 миллисекунда в PostgreSQL версии 12 и более поздних версиях (было 20 миллисекунд в версии 11).

Ограничение использования буфера (PostgreSQL 16+)

Начиная с PostgreSQL версии 16, можно использовать vacuum_buffer_usage_limit параметр для управления использованием памяти во время операций VACUUM, ANALYZE и autovacuum.

Этот параметр помогает предотвратить, чтобы VACUUM и autovacuum вытесняли слишком много полезных страниц из общих буферов, что способствует повышению общей эффективности работы базы данных во время операций обслуживания. Значение по умолчанию обычно устанавливается на основе shared_buffers, и его можно настроить для балансировки производительности вакуума с потребностями обычных операций базы данных.

Максимальное пороговое значение для autovacuum (PostgreSQL 18+)

Начиная с PostgreSQL версии 18, можно использовать параметр autovacuum_vacuum_max_threshold, чтобы задать верхний предел числа обновлений или удалений кортежей, которые активируют автовакуум.

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

Автоматическая очистка активируется 50 раз (50*20 мс = 1000 мс) каждую секунду. При каждой активации автоматическая очистка считывает 200 страниц.

Это означает, что за одну секунду автовакуум может сделать следующее:

• ~80 МБ/с [ (200 страниц/vacuum_cost_page_hit) * 50 * 8 КБ на страницу], если все страницы с неиспользуемыми кортежами находятся в общих буферах.
• ~8 МБ/с [ (200 страниц/vacuum_cost_page_miss) * 50 * 8 КБ на страницу], если все страницы с неиспользуемыми кортежами считываются с диска.
• ~4 МБ/с [ (200 страниц/vacuum_cost_page_dirty) * 50 * 8 КБ на страницу] — скорость записи автоматической очистки.

Мониторинг autovacuum

База данных Azure для PostgreSQL предоставляет следующие метрики для мониторинга автовакума.

Метрики автоматической очистки можно использовать для мониторинга и настройки производительности автоматической очистки в гибком сервере базы данных Azure для PostgreSQL. Каждая метрика создается в 30-минутном интервале и имеет до 93 дней хранения. Вы можете создавать оповещения для определенных метрик, а также разделять и фильтровать данные метрик с помощью DatabaseName измерения.

Как включить метрики autovacuum

• Метрики autovacuum отключены по умолчанию.
• Чтобы включить эти метрики, задайте для параметра metrics.autovacuum_diagnostics сервера значение ON.
• Этот параметр является динамическим, поэтому перезапуск экземпляра не требуется.

Список метрик «autovacuum»

Рекомендации по использованию метрик autovacuum

• Метрики autovacuum, использующие измерение DatabaseName, имеют ограничение в 30 баз данных .
• На SKU с возможностью повышения производительности ограничение составляет 10 баз данных для метрик, использующих атрибут DatabaseName.
• Ограничение измерения DatabaseName применяется к столбцу OID, который отражает порядок создания базы данных.

Дополнительные сведения см. в разделе «Метрики Autovacuum».

Используйте следующие запросы для мониторинга автоматической очистки:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

Следующие столбцы помогают определить, выполняется ли автоматическое заполнение действия таблицы:

Активация autovacuum

Действие autovacuum (ANALYZE или VACUUM) срабатывает, когда количество мертвых туплов превышает пороговое значение. Это число зависит от двух факторов: общее количество строк в таблице, а также фиксированное пороговое значение. По умолчанию ANALYZE запускается, когда происходит изменение 10% строк в таблице плюс 50 строк, а VACUUM запускается, когда происходит изменение 20% строк в таблице плюс 50 строк. Так как пороговое значение VACUUM в два раза больше, чем порог АНАЛИЗА, АНАЛИЗ активируется раньше чем ВАКУУМ.

Для версий PostgreSQL 13 и более поздних версий ANALYZE запускается по умолчанию, когда добавлено 20% строк таблицы плюс 1000 строк.

Точные уравнения для каждого действия:

• Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold или autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (для PostgreSQL версии 13 и позже)
• Автоматическая очистка = autovacuum_vacuum_scale_factor * кортежи + autovacuum_vacuum_threshold

Например, если у вас есть таблица со 100 строками, следующие уравнения показывают, когда запускаются действия анализа и вакуумизации.

Для обновлений и удалений: Autoanalyze = 0.1 * 100 + 50 = 60Autovacuum = 0.2 * 100 + 50 = 70

ANALYZE запускается после изменения 60 строк в таблице, а VACUUM запускается после изменения 70 строк в таблице.

Для вставок: Autoanalyze = 0.2 * 100 + 1000 = 1020

Триггеры ANALYZE после вставки 1020 строк в таблицу.

Ниже приведено описание параметров, используемых в уравнении:

Используйте следующий запрос, чтобы вывести список таблиц в базе данных и определить таблицы, которым требуется автоматическая очистка:

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Распространенные проблемы автоматической очистки

Ознакомьтесь со следующим списком распространенных проблем с процессом автовакума.

Отставание от сервера с высоким уровнем активности

Процесс автовакуума оценивает стоимость каждой операции ввода-вывода, накапливает общую сумму для каждой выполняемой операции и приостанавливается после достижения верхнего предела стоимости. В процессе используются два параметра сервера: autovacuum_vacuum_cost_delay и autovacuum_vacuum_cost_limit.

По умолчанию -1, что означает, что autovacuum_vacuum_cost_limit ограничение стоимости автовакуума использует то же значение, как vacuum_cost_limit параметр. Значение vacuum_cost_limit по умолчанию — 200. vacuum_cost_limit представляет стоимость ручного пылесоса.

Если задано значение autovacuum_vacuum_cost_limit -1, autovacuum использует vacuum_cost_limit параметр. Если вы задаете autovacuum_vacuum_cost_limit значение больше -1, autovacuum использует параметр autovacuum_vacuum_cost_limit.

Если автовакуум не справляется, рассмотрите возможность изменения следующих параметров:

Автоматическая очистка выполняется непрерывно

Если автова́куум работает непрерывно, это может повлиять на загрузку процессора и использование ввода-вывода на сервере. Ниже приведены некоторые возможные причины.

maintenance_work_mem

Используется управляющая программа autovacuum_work_memautovacuum, для которой задано значение -1 по умолчанию. Этот параметр по умолчанию означает, что autovacuum_work_mem использует то же значение, что и параметр maintenance_work_mem. В этой статье предполагается, что autovacuum_work_mem задано -1 и демон autovacuum использует maintenance_work_mem.

Если у maintenance_work_mem низкое значение, вы можете увеличить его до 2 ГБ на гибком серверном экземпляре Azure Database для PostgreSQL. Как правило, рекомендуется выделить 50 МБ на maintenance_work_mem для каждого 1 ГБ ОЗУ.

Большое количество баз данных

Автоматическая очистка пытается запустить рабочую роль в каждой базе данных каждые autovacuum_naptime сек.

Например, если сервер имеет 60 баз данных и autovacuum_naptime имеет значение 60 секунд, то рабочая роль autovacuum запускается каждые секунды [autovacuum_naptime/число баз данных].

Если в кластере больше баз данных, увеличьте autovacuum_naptime. В то же время сделайте процесс автовакуума более агрессивным, увеличивая параметры autovacuum_cost_limit и уменьшая параметры autovacuum_cost_delay. Можно также увеличить значение autovacuum_max_workers по умолчанию от 3 до 4 или 5.

Ошибки, связанные с нехваткой памяти

Чрезмерно агрессивные maintenance_work_mem значения могут периодически вызывать ошибки вне памяти в системе. Перед изменением параметра изучите доступную ОЗУ на сервере maintenance_work_mem .

Автоматическая очистка мешает работе

Если autovacuum потребляет слишком много ресурсов, попробуйте выполнить следующие действия:

Параметры автоматической очистки

Оценка параметров autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limitа также autovacuum_max_workers. Неправильное задание параметров автовакума может привести к сценариям, когда автовакум становится слишком разрушительным.

Если автовакум слишком разрушительный, рассмотрите следующие действия:

• Увеличьте autovacuum_vacuum_cost_delay, а autovacuum_vacuum_cost_limit уменьшите, если они превышают значение по умолчанию 200.
• Уменьшите число autovacuum_max_workers , если оно больше, чем значение по умолчанию 3.

Слишком много рабочих ролей автоматической очистки

Увеличение числа рабочих ролей автовакума не увеличивает скорость вакуума. Не используйте большое количество рабочих ролей autovacuum.

Увеличение числа рабочих ролей autovacuum приводит к увеличению потребления памяти. В зависимости от значения maintenance_work_memэто может привести к снижению производительности.

Каждый рабочий процесс автоматической очистки получает только (1/autovacuum_max_workers) от общего числа autovacuum_cost_limit, поэтому наличие большого количества рабочих ролей приводит к замедлению работы каждой из них.

Если увеличить число работников, увеличить autovacuum_vacuum_cost_limit и(или) уменьшить autovacuum_vacuum_cost_delay, чтобы ускорить вакуумный процесс.

Однако если задать параметр на уровне autovacuum_vacuum_cost_delay таблицы или autovacuum_vacuum_cost_limit параметрах, работники, работающие в этих таблицах, исключены из алгоритма балансировки [autovacuum_cost_limit/autovacuum_max_workers].

Защита от циклического возврата идентификатора транзакции автоматической очистки (TXID)

Если в базе данных происходит защита от переполнения идентификаторов транзакций, вы увидите сообщение об ошибке, например, что-то вроде следующего сообщения об ошибке:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Проблема с обходным решением возникает, когда база данных не вакуумируется или когда автовакум не удаляет слишком много мертвых кортежей.

Возможные причины этой проблемы включают следующие причины:

Интенсивная рабочая нагрузка

Тяжелая рабочая нагрузка вызывает слишком много мертвых кортежей в короткий период, что делает его трудным для автовакуума догнать. Неиспользуемые кортежи в системе со временем накапливаются, что приводит к снижению производительности запросов и циклическому возврату. Одна из причин возникновения этой ситуации может быть связана с тем, что параметры автовакуума не заданы достаточно, и он не поддерживается с занятой сервером.

Продолжительные транзакции

Любая длительная транзакция в системе не позволяет автовакуму удалять мертвые кортежи. Они блокируют процесс очистки. Удаление длительных транзакций освобождает неиспользуемые кортежи для удаления при запуске автоматической очистки.

Длительные транзакции можно обнаружить с помощью следующего запроса:

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

Подготовленные инструкции

Если есть подготовленные выражения, которые не зафиксированы, они препятствуют автоочистке мертвых кортежей. Следующий запрос помогает найти незавершенные подготовленные инструкции:

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

Используйте COMMIT PREPARED или ROLLBACK PREPARED для подтверждения или отмены этих инструкций.

Неиспользуемые слоты репликации

Неиспользуемые слоты репликации не позволяют автоматически удалять неиспользуемые кортежи. Следующий запрос помогает определить неиспользуемые слоты репликации:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Используйте pg_drop_replication_slot() для удаления неиспользуемых слотов репликации.

Когда база данных выполняется в защиту обтекателя идентификатора транзакции, проверьте наличие всех блокировщиков, как упоминалось ранее, и удалите блокировщики вручную, чтобы автовакум продолжался и завершен. Вы также можете увеличить скорость автовакуума, установив autovacuum_cost_delay значение 0 и увеличивая autovacuum_cost_limit значение больше 200. Однако изменения этих параметров не применяются к существующим рабочим рабочим службам автовакума. Перезапустите базу данных или вручную завершите работу существующих рабочих ролей, чтобы применить изменения параметров.

Требования к таблицам

Для отдельных таблиц можно задать параметры autovacuum. Эти параметры особенно важны для небольших и больших таблиц. Например, для небольшой таблицы, содержащей только 100 строк, autovacuum активирует операцию VACUUM при изменении 70 строк (как вычислено ранее). Если вы часто обновляете эту таблицу, вы можете столкнуться с сотнями операций автовакуума в день. Эти операции мешают автоматическому обслуживанию других таблиц, процент изменений в которых не является столь значительным. В таблице, содержащей миллиард строк, должно измениться 200 миллионов строк, чтобы активировать операции автоматической очистки. Установите подходящие параметры автоматической очистки, чтобы избежать таких ситуаций.

Чтобы задать параметры автовакуума для каждой таблицы, измените параметры сервера, как показано в следующих примерах:

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Только вставка

В версиях PostgreSQL 13 и более ранних autovacuum не запускается на таблицах с нагрузкой, связанной только с вставкой, поскольку отсутствуют мёртвые кортежи и свободное место, которое нужно освободить. Однако автоанализа выполняется для рабочих нагрузок только для вставки, так как есть новые данные. Недостатки этого поведения:

• Схема видимости таблиц не обновляется, поэтому производительность запросов, особенно в тех случаях, когда существуют только проверки индекса, начинает страдать со временем.
• База данных может столкнуться с защитой от циклического возврата идентификаторов транзакции.
• Биты подсказки не заданы.

Решения

PostgreSQL версии 13 и более ранних версий

С помощью расширения pg_cron можно настроить задание cron для планирования периодического анализа вакуума в таблице. Частота выполнения задания cron зависит от рабочей нагрузки.

Дополнительные сведения об использовании pg_cron в Базе данных Azure для PostgreSQL см. в специальных рекомендациях.

PostgreSQL 13 и более поздних версий

Autovacuum выполняется в таблицах с рабочей нагрузкой только для вставки. Два параметра сервера, autovacuum_vacuum_insert_threshold и autovacuum_vacuum_insert_scale_factor, помогают регулировать, когда может быть вызвана функция autovacuum в таблицах с только вставкой.

Руководства по устранению неполадок

Гибкий сервер базы данных Azure для PostgreSQL предоставляет на портале руководства по устранению неполадок, которые помогают отслеживать раздутость на уровне базы данных или отдельных схем и выявлять возможные помехи для процесса автовакуума.

Доступны два руководства по устранению неполадок:

• Мониторинг autovacuum - Используйте это руководство для мониторинга раздувания на уровне базы данных или отдельной схемы.
• Блокировщики autovacuum и обходное решение . Это руководство помогает определить потенциальные блокировщики автовакумов и получить сведения о том, насколько далеко базы данных на сервере находятся от обходного решения или чрезвычайных ситуаций.

Руководства по устранению неполадок также совместно используют рекомендации по устранению потенциальных проблем. Сведения о настройке и использовании руководств по устранению неполадок см. в руководствах по устранению неполадок.

Завершение процесса автовакуума: роль pg_signal_autovacuum_worker

Autovacuum — это важный фоновый процесс, так как он помогает эффективно хранить и выполнять обслуживание производительности в базе данных. В нормальном процессе автовакуумирования этот процесс отменяет себя после выполнения deadlock_timeout. Если пользователь выполняет инструкцию DDL в таблице, пользователю может потребоваться ждать интервала deadlock_timeout . Autovacuum не позволяет выполнять операции чтения или записи в таблице, запрошенной различными запросами подключения, добавляя задержку в транзакции.

Мы представили новую роль pg_signal_autovacuum_worker из PostgreSQL, которая позволяет членам, не являющимся суперпользователями, завершать текущую задачу автовакуума. Новая роль помогает пользователям получить безопасный и контролируемый доступ к процессу автовакуума. Пользователи, не обладающие правами суперпользователя, могут отменить процесс автовакуума, получив роль pg_signal_autovacuum_worker, с помощью команды pg_terminate_backend. Роль pg_signal_autovacuum_worker доступна в базе данных Azure для PostgreSQL, начиная с версии PostgreSQL 15 и более поздних.

Рекомендуемый подход для повторяющихся процессов autovacuum

В редких сценариях, таких как автоматическая очистка от оболочки, работники могут перезапуститься сразу после завершения, так как они критически важны для предотвращения исчерпания идентификатора транзакции. Чтобы свести к минимуму повторяющиеся конфликты, выполните следующие действия.

• Поставьте операцию DDL в очередь перед завершением:

  • Сеанс 1. Подготовка и запуск инструкции DDL.

  • Сеанс 2. Завершение процесса автоматической очистки.

    Это важно

    Эти два шага должны выполняться один за другим. Если инструкция DDL остается заблокированной слишком долго, она может хранить блокировки и блокировать другие операции DML на сервере.

• Завершение автоматической очистки и выполнение DDL: если DDL должен выполняться немедленно:

  • Завершите процесс автовакуума с помощью pg_terminate_backend().
  • Выполните инструкцию DDL непосредственно после завершения.

Действия, чтобы избежать повторяющихся конфликтов:

1. Предоставление роли пользователю

   GRANT pg_signal_autovacuum_worker TO app_user;
   

   1. Определение идентификатора процесса autovacuum

   SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();
   

2. Прекратить автовакуум

   SELECT pg_terminate_backend(<pid>);
   

3. Выполните инструкцию DDL немедленно

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

Рекомендации Помощника по Azure

Рекомендации Azure Advisor заранее определяют, имеет ли сервер высокий коэффициент раздутия или если сервер приближается к сценарию исчерпания транзакций. Вы также можете создавать оповещения Помощника по Azure для рекомендаций.

Вот эти рекомендации:

• Высокий коэффициент раздутия: высокий коэффициент раздутия может по-разному сказываться на производительности сервера. Одна из важных проблем заключается в том, что оптимизатор подсистемы PostgreSQL может бороться с выбором оптимального плана выполнения, что приводит к снижению производительности запросов. Поэтому рекомендация активируется, когда процент больших двоичных объектов на сервере достигает определенного порогового значения, чтобы избежать таких проблем с производительностью.

• Обходное решение транзакции. Этот сценарий является одним из наиболее серьезных проблем, с которыми может столкнуться сервер. После того как сервер находится в этом состоянии, он может перестать принимать все больше транзакций, что приводит к тому, что сервер станет доступен только для чтения. Поэтому рекомендация активируется, когда сервер пересекает пороговое значение 1 млрд транзакций.

Связанный контент

• Полное вакуумирование с помощью pg_repack в Базе данных Azure PostgreSQL
• Устранение неполадок с высоким использованием ЦП в Базе данных Azure для PostgreSQL
• Устранение неполадок с высокой загрузкой памяти в Базе данных Azure для PostgreSQL
• Устранение неполадок высокой нагрузки IOPS в базе данных Azure для PostgreSQL
• Устранение неполадок и определение медленных запросов в Базе данных Azure для PostgreSQL
• Параметры сервера в Базе данных Azure для PostgreSQL