Оптимизация автоматической очистки на отдельном сервере Базы данных Azure для PostgreSQL

Статья
03/30/2023

Область применения: отдельный сервер Базы данных Azure для PostgreSQL

Внимание

База данных Azure для PostgreSQL — одиночный сервер находится на пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для PostgreSQL — гибкий сервер. Дополнительные сведения о миграции на База данных Azure для PostgreSQL — гибкий сервер см. в статье "Что происходит с одним сервером База данных Azure для PostgreSQL?".

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

Обзор автоматической очистки

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

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

Автоматическую очистку можно настраивать, повышая ее эффективность. Значения, заданные по умолчанию в PostgreSQL, призваны обеспечить работу продукта на устройствах всех типов. В том числе на устройствах Raspberry Pi. Идеальные значения конфигурации зависят от следующего:

Общий объем доступных ресурсов, например, размер хранилища и SKU.
Использование ресурсов.
Характеристики отдельных объектов.

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

Если время от времени не выполнять очистку, накопление неиспользуемых кортежей может привести к:

раздуванию данных — большему размеру баз данных и таблиц;
большему размеру субоптимальных индексов;
увеличению числа операций ввода-вывода.

Отслеживание раздувания с помощью запросов автоматической очистки

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

SELECT relname,
       n_dead_tup,
       n_live_tup,
       (n_dead_tup / n_live_tup) AS DeadTuplesRatio,
       last_vacuum,
       last_autovacuum
FROM pg_catalog.pg_stat_all_tables
WHERE relname = 'XYZ'
ORDER BY n_dead_tup DESC;

Конфигурации автоматической очистки

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

Когда должна запускаться очистка?
Сколько данных нужно удалять после ее запуска?

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

Параметр	Description	Default value
autovacuum_vacuum_threshold	Указывает минимальное количество обновленных или удаленных кортежей, необходимое для вызова операции очистки в той или иной таблице. Значение по умолчанию — 50 кортежей. Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера. Параметр можно переопределять для отдельных таблиц, меняя параметры хранилища таблиц.	50
autovacuum_vacuum_scale_factor	Указывает долю размера таблицы, добавляемую к значению autovacuum_vacuum_threshold, чтобы решить, нужно ли вызывать операцию очистки. Значение по умолчанию — 0,2 (20 процентов размера таблицы). Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера. Параметр можно переопределять для отдельных таблиц, меняя параметры хранилища таблиц.	0,2
autovacuum_vacuum_cost_limit	Указывает значение предельной стоимости, которое будет использоваться в автоматических операциях очистки. Если задано значение –1 (по умолчанию), будет использоваться обычное значение параметра vacuum_cost_limit. Значение пропорционально распределяется между выполняющимися рабочими ролями автоматической очистки, если их несколько. Сумма ограничений для каждой рабочей роли не превышает значение этой переменной. Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера. Параметр можно переопределять для отдельных таблиц, меняя параметры хранилища таблиц.	-1
autovacuum_vacuum_cost_delay	Указывает значение задержки стоимости, которое будет использоваться в автоматических операциях очистки. Если задано значение –1, будет использоваться обычное значение параметра vacuum_cost_delay. Значение по умолчанию — 20 миллисекунд. Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера. Параметр можно переопределять для отдельных таблиц, меняя параметры хранилища таблиц.	20 мс
autovacuum_naptime	Указывает минимальную задержку между сеансами автоматической очистки для любой базы данных. В ходе каждого сеанса управляющая программа просматривает базу данных и по мере необходимости вызывает команды VACUUM и ANALYZE для таблиц в этой базе данных. Задержка измеряется в секундах. Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера.	15 с
autovacuum_max_workers	Указывает максимальное количество процессов автоматической очистки (помимо соответствующего средства запуска), которые могут выполняться одновременно. Значение по умолчанию — 3. Установите этот параметр только при запуске сервера.	3

Параметр можно переопределять для отдельных таблиц, меняя параметры хранилища таблиц.

Затраты на автоматическую очистку

Ниже указаны затраты на выполнение операции очистки.

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

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

Триггер запуска автоматической очистки

Автоматическая очистка вызывается, когда количество неиспользуемых кортежей превышает autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples. В этом случае reltuples — константа.

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

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

Заданный по умолчанию коэффициент масштабирования (20 процентов) хорошо работает с таблицами, содержащими мало неиспользуемых кортежей. Тем не менее он не работает с теми таблицами, где их процент высок. Например, в таблице размером 20 ГБ это 4 ГБ неиспользуемых кортежей, в то время как в таблице размером 1 ТБ — 200 ГБ неиспользуемых кортежей.

С PostgreSQL вы можете задавать эти параметры на уровне таблицы или экземпляра. В настоящее время эти параметры можно задавать на уровне таблицы только в Базе данных Azure для PostgreSQL.

Оценка затрат на автоматическую очистку

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

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

Процесс очистки считывает физические страницы и проверяет их на наличие неиспользуемых кортежей. Считается, что стоимость очистки каждой страницы в shared_buffers составляет 1 (vacuum_cost_page_hit). Всем остальным страницам назначается стоимость 20 (vacuum_cost_page_dirty) при наличии неиспользуемых кортежей или 10 (vacuum_cost_page_miss), если неиспользуемые кортежи отсутствуют. Операция очистки останавливается, если процесс превышает значение autovacuum_vacuum_cost_limit.

По достижении предела процесс переходит в спящий режим на время, заданное параметром autovacuum_vacuum_cost_delay, после чего запускается снова. Если предел не достигнут, автоматическая очистка запускается по истечении времени, заданного параметром autovacuum_naptime.

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

Параметр autovacuum_max_workers определяет максимальное количество процессов автоматической очистки, которые могут выполняться одновременно.

Оптимизация автоматической очистки для отдельных таблиц

Вы можете настроить параметры конфигурации для каждой таблицы. Приведем пример:

ALTER TABLE t SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);

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

Следующие шаги

Дополнительные сведения об использовании и настройке автоматической очистки доступны в приведенной ниже документации по PostgreSQL:

Chapter 18, Server configuration (Раздел 18, "Настройка сервера")
Chapter 24, Routine database maintenance tasks (Раздел 24, "Повседневные задачи обслуживания баз данных")

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