План эксперимента - увеличения max_autovacuum_workers для HighLoad

Задача

какое значение autovacuum_max_workers оптимально для СУБД PostgreSQL для размера СУБД 10TB, объем RAM 1TB , количеством CPU = 200 при нагрузке на 100 примерно 100-500 активных сессий ?

Гипотеза DeepSeek

Для указанной конфигурации PostgreSQL (10 ТБ данных, 1 ТБ RAM, 200 CPU, 100-500 активных сессий) оптимальное значение autovacuum_max_workers рекомендуется в диапазоне 15-25. Обоснование и сопутствующие настройки:

Ключевые факторы:

1. Высокая нагрузка (100-500 сессий):
   Большое количество транзакций генерирует значительный объем "мертвых" строк (dead tuples), требующих частой очистки.
2. Мощные ресурсы:
   200 CPU позволяют параллелизировать задачи вакуумирования.
   1 ТБ RAM обеспечивает буферы для работы воркеров без swap.
3. Размер БД (10 ТБ):
   Требуется больше воркеров для обслуживания крупных таблиц и индексов.

Рекомендуемые параметры:

autovacuum_max_workers = 20 # Стартовое значение. Диапазон: 15-25.
autovacuum_work_mem = 1–2GB # Выделение RAM на воркер (не более 2% от общей RAM).
autovacuum_vacuum_cost_limit = 2000–4000 # Повышает агрессивность вакуума.
autovacuum_naptime = 15s # Частота запуска задач (стандартное значение).

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

maintenance_work_mem = 4–8GB # Для ручного VACUUM/REINDEX.
work_mem = 256–512MB # Для сортировок и хэшей в запросах.
shared_buffers = 256–384GB # 25-35% от RAM (не более 40%!).
max_parallel_workers = 64 # Ограничить параллельные процессы (во избежание конфликтов).

Важные замечания:

1. Конкуренция за I/O:
   При увеличении числа воркеров возможна нагрузка на дисковую подсистему. Мониторинг ключевых метрик обязателен:
   pg_stat_progress_vacuum
   pg_stat_activity
   Статистика по I/O (iostat, pg_stat_io).
2. Адаптация под нагрузку:
   Начните с 20 воркеров, затем:
   Увеличьте до 25, если pg_stat_all_tables.n_dead_tup растёт для критических таблиц.
   Уменьшите до 15, если наблюдаются конфликты блокировок (pg_locks) или деградация I/O.
3. Разделение таблиц по частоте изменений:
   Для высоконагруженных таблиц используйте индивидуальные настройки:sqlCopyDownloadALTER TABLE крупная_таблица SET (
   autovacuum_vacuum_scale_factor = 0.01,
   autovacuum_vacuum_threshold = 10000
   );
   

Почему не больше 25?

• Риск блокировок: Воркеры конкурируют за доступ к таблицам.
• Потребление RAM: При 25 воркерах × 2 GB = 50 GB RAM (прирост против дефолта).
• CPU saturation: Параллельные задачи вакуума не должны вытеснять пользовательские запросы.

Мониторинг:

• Запрос для поиска "отстающих" таблиц:sqlCopyDownloadSELECT schemaname, relname, n_dead_tup,
  last_autovacuum, autovacuum_count
  FROM pg_stat_all_tables
  ORDER BY n_dead_tup DESC
  LIMIT 20;
  
• Логирование: Включите log_autovacuum_min_duration = 0 для детального аудита.

Итог: Начните с autovacuum_max_workers = 20, затем корректируйте в диапазоне 15-25 на основе мониторинга накопления dead tuples и нагрузки на CPU/I/O. Обязательно оптимизируйте сопутствующие параметры (autovacuum_work_mem, autovacuum_vacuum_cost_limit).