Postgres DBA

Задача

Проанализировать количественное влияние на производительность СУБД повышения агрессивности настройки autovacuum для очень большой таблицы .

Начало работ

PG_HAZEL : Влияние агрессивного автовакуума на производительность СУБД PostgreSQL.

Конфигурация СУБД

CPU = 200

RAM = 1TB

DB Size = 10TB

Количество строк тестовой таблицы ~7 000 000 000

Сценарий нагрузки - смешанный ("Select only" + "Select + Update" + "Insert only")

Минимальная нагрузка = 5 сессий

Максимальная нагрузка = 115 сессии

Эксперимент-10K : агрессивные настройки autovacuum

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0);

ALTER TABLE pgbench_accounts SET (autovacuum_analyze_threshold = 10000);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_insert_scale_factor = 0);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_cost_delay = 0);

Эксперимент-1K : агрессивные настройки autovacuum

Снижение граничного условия в 10 раз.

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000);

ALTER TABLE pgbench_accounts SET (autovacuum_analyze_threshold = 1000);

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000);

Операционная скорость

Средний прирост производительности СУБД в эксперименте-1K = 9.5%

Ожидания СУБД

Среднее снижение количества ожиданий СУБД в эксперименте-1K = -4.51%

Ожидания IO

Среднее снижение количества ожиданий СУБД типа IO в эксперименте-1K = -4.51%

Ожидания IPC

Превышение ожиданий IPC в эксперименте-1K при нагрузке близкой к максимальной.

Среднее снижение количества ожиданий СУБД типа IPC в эксперименте-1K = -37.44%

Ожидания Lock

С ростом нагрузки ожидания Lock в эксперименте-1К начинают превышать ожидания в эксперименте-10K.

Среднее снижение количества ожиданий СУБД типа Lock в эксперименте-1K = -17.00%

Ожидания LWLock

В целом ожидания Lock в эксперименте-1К превышают ожидания в эксперименте-10K.

Среднее повышение количества ожиданий СУБД типа Lock в эксперименте-1K = 13.46%

Итог

Для данной СУБД и данного сценария синтетической нагрузки. При нагрузке на СУБД с 5 до 115 одновременных соединений :

• Снижение граничного условия старта autovacuum с 10 000 до 1 000 мёртвых строк , приводит к повышению производительности в среднем до 9.5%.

• Mаксимальный прирост производительности достигает 31%.

P.S.

Корреляционный анализ ожиданий по тестовым сценариям, будет подготовлен позже.

Задача

Проанализировать влияние на производительность СУБД агрессивной настройки autovacuum для очень большой таблицы .

Конфигурация СУБД

CPU = 200RAM = 1TBDB Size = 10TBКоличество строк тестовой таблицы ~70 00 000Сценарий нагрузки - смешанный ("Select only" + "Select + Update" + "Insert only")Минимальная нагрузка = 5 сессийМаксимальная нагрузка = 92 сессии

Эксперимент-1 : Базовые настройки autovacuum (BASELINE)

autovacuum_analyze_scale_factor = 0.005autovacuum_analyze_threshold = 50autovacuum_vacuum_cost_delay = 2msautovacuum_vacuum_cost_limit = 5600autovacuum_vacuum_insert_scale_factor = 0.01autovacuum_vacuum_scale_factor = 0.001autovacuum_vacuum_threshold = 50autovacuum_vacuum_insert_threshold = 1000autovacuum_work_mem = 1GB

Эксперимент-2 : агрессивная настройка autovacuum для тестовой таблицы

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0);ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 10000);ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0);ALTER TABLE pgbench_accounts SET (autovacuum_analyze_threshold = 10000);ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_insert_scale_factor = 0);ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 10000);ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_cost_delay = 0);

Метрики мониторинга

Autovacuum: Count of autovacuum workers

Эксперимент-1:BASELINE

Эксперимент-2:autovacuum

CPU iowait time

Эксперимент-1:BASELINE

Эксперимент-2:autovacuum

Disk utilization

Эксперимент-1:BASELINE

Эксперимент-2:autovacuum

CPU utilization

Эксперимент-1:BASELINE

Эксперимент-2:autovacuum

Результаты

Утилизация ресурсов увеличилась на 1-2%

Операционная скорость

Максимальное увеличение операционной скорости при использовании агрессивных настроек autovacuum составило 9%

Среднее увеличение операционной скорости при использовании агрессивных настроек autovacuum составило 4.5%

Ожидания СУБД

• Ожидания IO - возросли

• Ожидания IPC, Lock, LWLock - снизились

Ожидания IO

Среднее уменьшение количества ожиданий типа IO, в эксперименте-2 составило менее 1%

Ожидания IPC

Среднее увеличение количества ожиданий типа IPC, в эксперименте-2 составило 52%

Ожидания Lock

Среднее уменьшение количества ожиданий типа Lock, в эксперименте-2 составило менее -19%

Ожидания LWLock

Среднее увеличение количества ожиданий типа LWLock, в эксперименте-2 составило 19%

Итог

• Для данной СУБД и данного сценария нагрузки, применение агрессивных настроек autovacuum дало среднее увеличение производительности СУБД на 4.5%

• Увеличение утилизации ресурсов ОС - незначительно ~1-2%.

PS.

Корреляционный анализ событий ожиданий тестовых сценариев, будет выполнен позднее.

Задача

Подготовить базовую формулу расчета для базовой настройки конфигурационных параметров управления памятью для обслуживания СУБД :

shared_buffers

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

Если вы используете выделенный сервер с объёмом ОЗУ 1 ГБ и более, разумным начальным значением shared_buffers будет 25% от объёма памяти. Существуют варианты нагрузки, при которых эффективны будут и ещё большие значения shared_buffers, но так как Postgres Pro использует и кеш операционной системы, выделять для shared_buffers более 40% ОЗУ вряд ли будет полезно.

maintenance_work_mem

Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY.

autovacuum_work_mem

Задаёт максимальный объём памяти, который будет использовать каждый рабочий процесс автоочистки.

shared_buffers

40% от размера RAM.

PG_HAZEL : Влияние на производительность и характерные ожидания СУБД увеличения shared_buffers c 25 до 50% от размера RAM.

maintenance_work_mem

10% от размера RAM

Как рассчитать значение maintenance_work_mem для заданного количества мертвых строк

autovacuum_work_mem

1. Настроить параметра autovacuum для больших таблиц (~ более 1M строк)

autovacuum_vacuum_scale_factor = 0

autovacuum_analyze_scale_factor = 0

autovacuum_vacuum_insert_scale_factor = 0

autovacuum_vacuum_threshhold = 100000

autovacuum_analyze_threshhold = 100000

autovacuum_vacuum_insert_threshold = 100000

2. Установить autovacuum_max_workers

autovacuum_max_workers = 10

Тонкая настройка параметров autovacuum/autoanalyze(naptime, cost_delay) - будет рассмотрена позднее.

3. Установить значение autovacuum_work_mem

autovacuum_work_mem = 100000 * 6 * 1.2 ~ 1MB

Как рассчитать значение maintenance_work_mem для заданного количества мертвых строк

Базовый расчет памяти для обслуживания СУБД

shared_buffers + maintenance_work_mem + (autovacuum_work_mem * autovacuum_max_workers )

Пример :

RAM = 2GB

autovacuum_max_workers = 4

shared_buffers + maintenance_work_mem + (autovacuum_work_mem * autovacuum_max_workers ) = 1020 MB (~49%RAM).

Вопрос нейросети:

Какой параметр для настройки автовакуума для СУБД PostgreSQL эффективнее использовать для очень больших таблиц : autovacuum_vacuum_scale_factor или autovacuum_vacuum_threshold ?

Ответ DeepSeek:

Для очень больших таблиц в PostgreSQL эффективнее использовать параметр autovacuum_vacuum_threshold, а не autovacuum_vacuum_scale_factor. Вот обоснование:

Проблема autovacuum_vacuum_scale_factor для больших таблиц:

1. Масштабируемость:
   Формула запуска автовакуума:
   autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table_size.
   По умолчанию: threshold = 50, scale_factor = 0.2 (20%).
   Для таблицы на 1 млрд строк порог срабатывания:
   50 + 0.2 * 1 000 000 000 = 200 000 050 мертвых кортежей.
   Это приводит к редким запускам и накоплению огромного числа мертвых кортежей.

2. Риски:
   Замедление запросов: Длинные таблицы (из-за мертвых кортежей) снижают производительность.
   Блокировки: Очистка большого объема данных блокирует операции.
   Срыв транзакций: Если autovacuum не успевает очищать данные до заполнения autovacuum_freeze_max_age.

Почему autovacuum_vacuum_threshold эффективнее?

• Фиксированный порог:
  Позволяет задать абсолютное число мертвых кортежей для запуска (независимо от размера таблицы).
  Пример: ALTER TABLE big_table SET (autovacuum_vacuum_threshold = 1000000);
  Автовакуум запустится при достижении 1 млн мертвых кортежей, даже если таблица содержит 1 млрд строк.

• Гранулярность:
  Настройка применяется индивидуально к большим таблицам без влияния на маленькие.
  Оптимальные значения: от 100 000 до 10 000 000 (зависит от нагрузки и размера).

Рекомендуемая стратегия:

1. Уменьшить scale_factor:

ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01); -- 1%

Для таблицы на 1 млрд строк порог: 50 + 0.01 * 1e9 = 10 000 050.

2. Увеличить threshold:

ALTER TABLE big_table SET (autovacuum_vacuum_threshold = 1000000);

Порог: 1 000 000 (фиксированный, без умножения на размер).

3.Комбинировать параметры:

ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.005, -- 0.5%autovacuum_vacuum_threshold = 500000);

Формула: 500 000 + 0.005 * N.

Дополнительные настройки:

• autovacuum_vacuum_cost_limit (увеличить для ускорения):

ALTER TABLE big_table SET (autovacuum_vacuum_cost_limit = 2000); -- вместо 200 по умолчанию

autovacuum_naptime (уменьшить интервал проверок).

Итог:

Для очень больших таблиц:

• Основной параметр: autovacuum_vacuum_threshold (фиксированный порог).

• Вспомогательный: autovacuum_vacuum_scale_factor (уменьшать до 0.1–0.5%).

• Настраивать индивидуально для таблицы через ALTER TABLE.

• Мониторить эффективность через представление pg_stat_user_tables (столбцы n_dead_tup и last_autovacuum).

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

PG_HAZEL : Влияние настроек автовакуума таблицы на производительность СУБД

Задача

Провести тестирование методологии для оценки влияния оптимизации конфигурационных параметров СУБД на производительность СУБД.

Общий алгоритм действий и постановка эксперимента

1. Тестовый прогон нагрузочного тестирования по сценарию смешанной нагрузки с целью определения базовых показателей производительности, характерных ожиданий и нагрузки на СУБД соответствующей максимальной производительности (N_BASE) .

2. Изменение значений конфигурационных параметров. Нагрузочное тестирование с минимальной нагрузки до нагрузки N_BASE.

3. Сравнительный анализ изменения производительности и характерных ожиданий СУБД.

Конфигурация виртуальной машины

• CPU = 2

• RAM = 1GB

• Postgres Pro (enterprise certified) 15

• Размер тестовой БД = 10GB

Эксперимент-1 : Тестовый прогон при базовых значениях конфигурационных параметров

shared_preload_libraries = 'pg_wait_sampling, pgpro_stats'

wipe_file_on_delete = 'off'

wipe_xlog_on_free = 'off'

wipe_heaptuple_on_delete = 'off'

wipe_memctx_on_free = 'off'

wipe_mem_on_free = 'off'

track_io_timing = 'on'

listen_addresses = '0.0.0.0'

logging_collector = 'on'

log_directory = '/log/pg_log'

log_destination = 'stderr'

log_rotation_size = '0'

log_rotation_age = '1d'

log_filename = 'name.postgresql-%u.log'

log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| '

log_truncate_on_rotation = 'on'

log_checkpoints = 'on'

archive_mode = 'on'

archive_command = 'true'

max_connections = '1000'

log_connections = 'on'

log_disconnections = 'on'

Операционная скорость

Нагрузка, соответствующая максимальной производительности (N_BASE) = 26

Эксперимент-2 : Оптимизация конфигурационных параметров - проход 1

Измененные параметры

random_page_cost = '1.1'

effective_io_concurrency = '300'

autovacuum_max_workers = '2'

autovacuum_work_mem = '256MB'

vacuum_cost_limit = '4000'

shared_buffers = '512MB'

effective_cache_size = '1536MB'

maintenance_work_mem = '128MB'

max_parallel_workers = '2'

max_parallel_workers_per_gather = '2'

wal_level = 'minimal'

max_wal_senders = '0'

Эксперимент-2 : Оптимизация конфигурационных параметров - проход 2

Измененные параметры

shared_buffers = '819MB'

checkpoint_timeout = '60'

\c test_pgbench_custom

ALTER TABLE pgbench_accounts SET (fillfactor = 50);

ALTER TABLE pgbench_tellers SET (fillfactor = 50);

ALTER TABLE pgbench_branches SET (fillfactor = 50);

VACUUM FULL pgbench_branches ;

VACUUM FULL pgbench_tellers ;

VACUUM FULL pgbench_accounts ;

Сравнительный анализ изменений производительности и характерных ожиданий СУБД

Операционная скорость

Прирост скорости в эксперименте-1 по сравнению с базовыми показателями составил до 3.4% , в среднем 1.9%.

Прирост скорости в эксперименте-2 по сравнению с базовыми показателями составил до 4.7% , в среднем 2.3%.

Корреляция и абсолютные значения ожиданий СУБД

Ожидания типа IO

Снижение ожиданий типа IO в эксперименте-1 по сравнению с базовыми значениями составило до -5.7% , в среднем -2.8%.

Снижение ожиданий типа IO в эксперименте-2 по сравнению с базовыми значениями составило до -11.3% , в среднем -4.4%.

Ожидания типа LWLock

Снижение ожиданий типа LWLock в эксперименте-1 по сравнению с базовыми значениями составило до -40.5% , в среднем -7.7%.

Снижение ожиданий типа LWLock в эксперименте-2 по сравнению с базовыми значениями составило до -66.7% , в среднем -54.3%.

Итог

Для данной СУБД для сценария синтетической нагрузки оптимальными значениями конфигурационных параметров СУБД и хранения таблиц являются:

random_page_cost = '1.1'

effective_io_concurrency = '300'

autovacuum_max_workers = '2'

autovacuum_work_mem = '256MB'

vacuum_cost_limit = '4000'

effective_cache_size = '1536MB'

maintenance_work_mem = '128MB'

max_parallel_workers = '2'

max_parallel_workers_per_gather = '2'

wal_level = 'minimal'

max_wal_senders = '0'

shared_buffers = '819MB'

checkpoint_timeout = '60'

fillfactor = 50

При необходимости тонкая настройка конфигурации СУБД и параметров хранения таблиц - может быть продолжена .