Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL⁠⁠

Взято с основного технического канала Postgres DBA

Очередная иллюстрация -ЛЮБОЙ ответ нейросетей необходимо экспериментально проверять.

Вопрос DeepSeek

Как изменится производительность СУБД PostgreSQL при нагрузке создаваемой pgbench и количестве подключений от 5 до 100 для СУБД размером 300MB при ресурсах CPU = 2 и RAM = 2GB при изменении настроек автовакуума с настроек по умолчанию на агрессивные настройки ?

Ответ DeepSeek

Исходные параметры:

• Размер БД: 300 MB

• Ресурсы: 2 CPU, 2 GB RAM

• Количество подключений: 5–100

• Нагрузка: pgbench в режиме TPC-B (смесь SELECT/UPDATE).

• Сравнение:
  Настройки по умолчанию:

autovacuum_vacuum_scale_factor = 0.2

autovacuum_vacuum_threshold = 50

autovacuum_vacuum_cost_delay = 20ms

autovacuum_vacuum_cost_limit = 200

Агрессивные настройки:

autovacuum_vacuum_scale_factor = 0.05 # Запуск при 5% изменений вместо 20%

autovacuum_vacuum_threshold = 25 # Нижний порог срабатывания

autovacuum_vacuum_cost_delay = 5ms # Меньшая задержка между операциями

autovacuum_vacuum_cost_limit = 1000 # Больше операций за один проход

Прогнозируемые результаты:

1. Низкая нагрузка (5–20 подключений):

• По умолчанию:
  Автовакуум запускается редко. Мертвые кортежи накапливаются, но не критично. Производительность стабильна.
  TPS: ~600–800.

• Агрессивные настройки:
  Автовакуум срабатывает чаще, но нагрузка от него незначительна. Мертвые кортежи удаляются своевременно.
  TPS: ~650–850 (+5–10%).

2. Средняя нагрузка (30–60 подключений):

• По умолчанию:
  Накопление мертвых кортежей приводит к:
  Увеличению размера таблиц (из-за "раздувания").
  Замедлению SELECT (сканирование большего объема данных).
  Блокировкам при UPDATE из-за долгих вакуумных операций.
  TPS: падение до 400–500.

• Агрессивные настройки:
  Частые, но короткие вакуум-сессии. Меньше "раздувания", быстрее доступ к данным.
  TPS: ~550–700 (+20–40%).

3. Высокая нагрузка (80–100 подключений):

• По умолчанию:
  Критическое накопление мертвых кортежей. Автовакуум не успевает очищать данные, что вызывает:
  Резкое падение TPS (до 200–300).
  Рост времени отклика (блокировки, конкуренция за I/O).

• Агрессивные настройки:
  Плюсы: Минимизация "раздувания", стабильный TPS (~450–600).
  Минусы: Конкуренция за ресурсы CPU между фоновым вакуумом и рабочими процессами. При 2 CPU возможны просадки из-за нехватки вычислительной мощности.

Ключевые метрики для сравнения:

1. TPS (Transactions Per Second):
   Агрессивные настройки дадут прирост на 20–40% при 30–60 подключениях.
   При 80–100 подключениях рост TPS будет менее значительным (+10–15%) из-за ограничений CPU.

2. Задержки (Latency):
   В агрессивном режиме задержки будут стабильнее, особенно в диапазоне 30–60 подключений.

3. Размер таблиц (Table Bloat):
   Агрессивный автовакуум уменьшит "раздувание" на 40–60% по сравнению с настройками по умолчанию.

4. Использование CPU:
   При агрессивных настройках нагрузка на CPU вырастет на 15–25% из-за частого выполнения вакуума.

Рекомендации:

1. Для тестовой конфигурации (2 CPU, 2 GB RAM):
   Агрессивные настройки целесообразны при 20–60 подключениях.
   При 80–100 подключениях требуется мониторинг CPU: если загрузка достигнет 90–100%, производительность может ухудшиться.

2. Оптимизация под высокую нагрузку:
   Увеличьте autovacuum_max_workers (если нужно параллелить вакуум).
   Настройте maintenance_work_mem (выделите 256–512 MB для операций вакуума).

3. Дисковый I/O:
   Если диск медленный (HDD), агрессивный автовакуум может вызвать contention I/O. Используйте SSD для минимизации рисков.

Заключение:

Агрессивные настройки автовакуума улучшат производительность на 20–40% при 30–60 подключениях, предотвращая "раздувание" таблиц. Однако при экстремальной нагрузке (80–100 подключений) и ограниченных ресурсах CPU (2 ядра) возможна конкуренция за процессорное время, что снизит эффективность. Тестирование на реальной инфраструктуре с мониторингом CPU/I/O обязательно.

Результаты сравнительных экспериментов

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

CPU = 2RAM = 2GB

Версия СУБД:Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

Сценарий нагрузки "Mix Without median time"

Эксперимент-1 : Настройки автовакуума по умолчанию

autovacuum_vacuum_scale_factor = 0.2

autovacuum_vacuum_threshold = 50

autovacuum_analyze_scale_factor = 0.1

autovacuum_analyze_threshold = 50

autovacuum_vacuum_cost_limit = -1

vacuum_cost_limit = 2000

autovacuum_vacuum_cost_delay = 2ms

Эксперимент-2 : "агрессивные" настройки автовакуума

autovacuum_vacuum_scale_factor = 0.01

autovacuum_vacuum_threshold = 25

autovacuum_analyze_scale_factor = 0.005

autovacuum_analyze_threshold = 10

autovacuum_vacuum_cost_limit = 2000

vacuum_cost_limit = 2000

autovacuum_vacuum_cost_delay = 2ms

maintenance_work_mem = 128MB

autovacuum_naptime = 5s

Результаты сравнительных экспериментов

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

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

Итоги и проверка гипотез DeepSeek

1. Операционная скорость для данной СУБД и данных ресурсах ВМ - не увеличилась и даже уменьшилась до -5% при оптимальной нагрузке.

2. Характерные ожидания - не изменились .

3. Гипотеза нейросети о влиянии "агрессивной" настройки автовакуума на производительность СУБД - не подтвердилась:

Агрессивные настройки автовакуума улучшат производительность на 20–40% при 30–60 подключениях, предотвращая "раздувание" таблиц.