Оптимизация автоматической очистки на отдельном сервере Базы данных Azure для PostgreSQL
ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных 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 определяет максимальное количество процессов автоматической очистки, которые могут выполняться одновременно.
С PostgreSQL вы можете задавать эти параметры на уровне таблицы или экземпляра. В настоящее время эти параметры можно задавать на уровне таблицы только в Базе данных Azure для PostgreSQL.
Оптимизация автоматической очистки для отдельных таблиц
Вы можете настроить параметры конфигурации для каждой таблицы. Приведем пример:
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, "Повседневные задачи обслуживания баз данных")