Настройка autovacuum в База данных Azure для PostgreSQL — гибкий сервер

Статья
08/15/2024

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для PostgreSQL — гибкий сервер

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

Что такое автоматическая очистка

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

VACUUM — освобождает место на диске, удаляя мертвые кортежи.
АНАЛИЗ. Собирает статистику, чтобы помочь оптимизатору PostgreSQL выбрать лучшие пути выполнения для запросов.

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

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

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

Параметр	Описание
`vacuum_cost_page_hit`	Стоимость чтения страницы, которая уже находится в общих буферах и не требует чтения диска. Значение по умолчанию равно 1.
`vacuum_cost_page_miss`	Стоимость получения страницы, которая не используется в общих буферах. Значение по умолчанию — 10.
`vacuum_cost_page_dirty`	Стоимость записи на страницу, когда мертвые кортежи находятся в нем. Значение по умолчанию: 20.

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

Параметр	Описание
`autovacuum_vacuum_cost_limit`	Объем работы autovacuum выполняется в одном пути.
`autovacuum_vacuum_cost_delay`	Число миллисекунда, которое автовакум заснул после достижения предела затрат, указанного параметром `autovacuum_vacuum_cost_limit` .

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

Что касается autovacuum_vacuum_cost_delay, в Postgres версии 11 по умолчанию используется значение 20 миллисекунд, в то время как в Postgres версии 12 и выше значение по умолчанию — 2 миллисекунда.

Автоматическая очистка активируется 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

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

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;

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

Параметр	Описание
`dead_pct`	Процент мертвых кортежей при сравнении с живыми кортежами.
`last_autovacuum`	Дата последнего времени, когда таблица была автовакумирована.
`last_autoanalyze`	Дата последнего анализа таблицы.

Когда PostgreSQL активирует автоматическую очистку

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

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

Autoanalyze = autovacuum_analyze_scale_factor * кортежей + autovacuum_analyze_threshold или autovacuum_vacuum_insert_scale_factor * кортежей + autovacuum_vacuum_insert_threshold (для версий >PG = 13)
Автоматическая очистка = autovacuum_vacuum_scale_factor * кортежи + autovacuum_vacuum_threshold

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

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

Анализ триггеров после изменения 60 строк в таблице и триггеры вакуума при изменении 70 строк в таблице.

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

Анализ триггеров после вставки 1020 строк в таблицу

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

Параметр	Описание
`autovacuum_analyze_scale_factor`	Процент вставок, обновлений и удалений, которые активируют АНАЛИЗ таблицы.
`autovacuum_analyze_threshold`	Указывает минимальное количество кортежей, вставленных или обновленных или удаленных для АНАЛИЗА таблицы.
`autovacuum_vacuum_insert_scale_factor`	Процент вставок, которые активируют ANLYZE в таблице.
`autovacuum_vacuum_insert_threshold`	Указывает минимальное количество кортежей, вставленных в таблицу.
`autovacuum_vacuum_scale_factor`	Процент обновлений и удалений, который активирует VACUUM в таблице.

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

 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;

Примечание.

Запрос не учитывает, что автовакум можно настроить на основе каждой таблицы с помощью команды DDL alter table.

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

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

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

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

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

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

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

Параметр	Описание
`autovacuum_vacuum_cost_limit`	По умолчанию: `200`. Ограничение затрат может быть увеличено. Необходимо отслеживать использование ЦП и операций ввода-вывода в базе данных до и после внесения изменений.
`autovacuum_vacuum_cost_delay`	Postgres версии 11 — по умолчанию `20 ms`. Параметр может быть уменьшен до `2-10 ms`. Postgres версии 12 и более поздних — по умолчанию: `2 ms`.

Примечание.

Значение autovacuum_vacuum_cost_limit распределяется пропорционально между работающими рабочими рабочая роль автовакумов, чтобы при наличии более одного сумма ограничений для каждой рабочей роли не превышала значения autovacuum_vacuum_cost_limit параметра.
autovacuum_vacuum_scale_factor — другой параметр, который может активировать вакуум в таблице на основе накопления мертвых кортежей. По умолчанию: разрешенный диапазон: 0.20.05 - 0.1. Коэффициент масштабирования зависит от рабочей нагрузки и должен быть задан в зависимости от объема данных в таблицах. Прежде чем менять значение, изучите рабочие нагрузки и отдельные тома таблицы.

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

Непрерывное выполнение автовакуума может повлиять на использование ЦП и операций ввода-вывода на сервере. Ниже приведены некоторые из возможных причин:

`maintenance_work_mem`

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

При maintenance_work_mem низком уровне может быть увеличено до 2 ГБ на гибком сервере База данных Azure для 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.

Примечание.

Это сообщение об ошибке является давним недосмотром. Как правило, не требуется переключаться в однопользовательский режим. Вместо этого можно выполнить необходимые команды VACUUM и запустить настройку для быстрой работы VACUUM. Хотя вы не можете использовать язык обработки данных (DML), вы по-прежнему можете запустить VACUUM.

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

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

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

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

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

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

    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 строк, функция автоматической очистки активирует операцию 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);

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

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

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

Решения

Версии Postgres <= 13

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

Пошаговые инструкции по использованию pg_cron см. в разделе Расширения.

Postgres 13 и более поздние версии

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

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

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

Поделиться через

Настройка autovacuum в База данных Azure для PostgreSQL — гибкий сервер

Что такое автоматическая очистка

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

Мониторинг autovacuum

Когда PostgreSQL активирует автоматическую очистку

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

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

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

`maintenance_work_mem`

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

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

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

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

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

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

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

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

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

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

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

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

Решения

Версии Postgres <= 13

Postgres 13 и более поздние версии

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

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

Обратная связь

Дополнительные ресурсы

Поделиться через

Настройка autovacuum в База данных Azure для PostgreSQL — гибкий сервер

Что такое автоматическая очистка

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

Мониторинг autovacuum

Когда PostgreSQL активирует автоматическую очистку

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

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

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

maintenance_work_mem

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

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

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

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

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

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

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

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

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

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

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

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

Решения

Версии Postgres <= 13

Postgres 13 и более поздние версии

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

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

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

Обратная связь

Дополнительные ресурсы

`maintenance_work_mem`