Задача

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

Малая СУБД

CPU = 2

RAM = 2GB

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

Тестовая таблица ~60 000 000 строк

Влияние настройки autovacuum на производительность СУБД

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

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

• Средняя относительная разница операционной скорости в экспериментах 1 и 2 составила : -3%

Операционная скорость при малой нагрузке ( до 10 соединений)

• Средняя относительная разница операционной скорости в экспериментах 1 и 2 составила : 4%

Операционная скорость при высокой нагрузке ( свыше 10 соединений)

• Средняя относительная разница операционной скорости в экспериментах 1 и 2 составила : -6%

Большая СУБД

CPU = 200

RAM = 1TB

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

Тестовая таблица ~70 000 000 000 строк

Влияние агрессивного автовакуума на производительность большой СУБД

• Средний прирост производительности в эксперименте-7 по сравнению с экспериментом-1 составил 13.30%

• Максимальный прирост производительности в эксперименте-7 по сравнению с экспериментом-1 составил 35.83%

Вывод

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

К вопросу о том, что нейросеть в обозримом будущем не может заменить инженера , обладающего данными экспериментов .

У нейросети просто нет экспериментальных данных, но проблема не в этом. Глобальная проблема нейросетей — выдача ответа в условиях недостатка данных.

Сказано уже 100500 раз — ВСЕ ОТВЕТЫ НЕЙРОСЕТИ НУЖНО ПРОВЕРЯТЬ ЭКСПЕРЕМЕНТАЛЬНО, НИКАКОЙ ИНФОРМИИ НЕЙРОСЕТЕЙ ДОВЕРЯТЬ НЕЛЬЗЯ .

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

Вводные данные

Оценить влияние кардинального снижения параметра хранения таблиц fillfactor c базового значения 100 до 50, при агрессивной настройке autovacuum.

Допустим какой-то продвинутый DBA ,

следующий в русле новых тенденций решил не тратить время и задал вопрос нейросети DeepSeek:

Как повлияет уменьшение параметра fillfactor c 100 до 50 для таблицы с количеством строк примерно 60000000 на количество и время работы autovacuum для СУБД PostgreSQL c ресурсами CPU=2 , RAM=2GB при интенсивных операциях SELECT + UPDATE примерно в среднем 1000 транзакций в секунду, при нагрузке от 5 до 48 одновременных сессиях, при агрессивных настройках autovacuum для тестовой таблицы : autovacuum_vacuum_scale_factor=0 и autovacuum_vacuum_threshold=1000 ?

Ответ нейросети DeepSeek

Уменьшение параметра fillfactor с 100 до 50 для таблицы с 60 млн строк в PostgreSQL при указанных условиях существенно повлияет на работу autovacuum, преимущественно негативно. Вот анализ последствий:

1. Увеличение размера таблицы и фрагментации

• Физический рост таблицы:
  При fillfactor=50 каждая страница таблицы заполняется только на 50%, что удваивает её физический размер (с ~60 млн строк до эффективных ~120 млн страниц). Это увеличит I/O-нагрузку и объём данных для обработки autovacuum.

• Рост индексов:
  Индексы также увеличатся, так как новые версии строк (от UPDATE) будут чаще размещаться в новых страницах (из-за нехватки места в исходных), что дополнительно нагрузит autovacuum.

2. Частота запуска autovacuum

• Агрессивные настройки:
  autovacuum_vacuum_threshold=1000 и autovacuum_vacuum_scale_factor=0 означают, что autovacuum запускается после каждых 1000 изменений (UPDATE/DELETE).

• Интенсивность UPDATE:
  При 1000 транзакций/сек (часть из которых — UPDATE) порог в 1000 изменений будет достигаться каждую секунду. Это приведёт к почти непрерывной работе autovacuum.

3. Время работы autovacuum

• Увеличение длительности циклов:
  Autovacuum должен сканировать вдвое больше страниц (из-за fillfactor=50), что резко увеличит время каждого запуска. Например:
  При fillfactor=100: сканирование 1 млн страниц.
  При fillfactor=50: сканирование ~2 млн страниц.

• Ресурсные ограничения:
  Слабые ресурсы (2 CPU, 2 GB RAM) не позволят autovacuum справляться с возросшим объёмом. Нехватка памяти вызовет активное использование диска (I/O), замедляя процесс.

4. Проблемы из-за нехватки ресурсов

• Конкуренция за I/O:
  Autovacuum будет постоянно читать/писать данные на диск, конкурируя с пользовательскими запросами (SELECT/UPDATE). Это увеличит задержку транзакций.

• Нехватка памяти:
  Буферный кэш (shared_buffers) ограничен ~512 MB (25% от 2 GB RAM). Таблица размером > 2x не поместится в кэш, что вызовет чтение с диска и замедлит все операции.

• Риск блокировок:
  Длительные циклы autovacuum могут конфликтовать с пользовательскими транзакциями, особенно при 48 одновременных сессиях.

5. HOT-обновления: частичное смягчение

• Преимущество fillfactor=50:
  Свободное место в страницах может повысить долю HOT-обновлений (когда новые версии строк помещаются в ту же страницу). Это уменьшит фрагментацию и нагрузку на индексы.

• Ограниченная польза:
  На фоне удвоенного объёма таблицы и слабых ресурсов этот эффект не компенсирует общее снижение производительности.

6. Риск накопления dead tuples

• Autovacuum может не успевать:
  При такой частоте UPDATE и нехватке ресурсов autovacuum будет отставать от нагрузки. Это вызовет:
  Рост dead tuples.
  Увеличение I/O (из-за чтения "мертвых" строк).
  Замедление SELECT (из-за необходимости фильтрации мёртвых версий).
  Блот (bloat) таблицы и индексов.

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

1. Не снижать fillfactor до 50 на такой конфигурации. Оптимально: fillfactor=80–90 (если требуются HOT-обновления).

2. Увеличить порог autovacuum:
   Например, autovacuum_vacuum_threshold = 10000 (вместо 1000), чтобы сократить частоту запусков.

Вывод: Уменьшение fillfactor до 50 на маломощном сервере с агрессивным autovacuum вызовет хроническую нехватку ресурсов, рост времени вакуумирования и общее снижение производительности. Без увеличения RAM/CPU это изменение не рекомендуется.

«Вот оно как» — подумает продвинутый DBA , следующий в русле новых тенденций и не будет менять параметр fillfactor.

Другой инженер, якудза старой школы

не верит бездушным алгоритмам и решает проверить всё лично.

И приходит к очень интересным, и прямо противоположным результатам.

Итоги

Для данной СУБД и данного сценария нагрузки , уменьшение параметра хранения таблиц на 50% c 100 до 50 , повысило эффективность работы autovacuum:

• Количество запусков autovacuum увеличилось на 261% c 193 до 697

• Максимальное время работы autovacuum уменьшилось на 29% с 936 сек. до 663 сек.

• Среднее время работы autovacuum уменьшилось на 73% с 46 сек. до 12 сек.

• Предельная нагрузка на autovacuum увеличивалась на 50% с 8 до 12 соединений.

Влияние уменьшения fillfactor на работу autovacuum

Мораль

Молодой DBA, следующий в русле новых тенденций и доверяющий математическим алгоритмам статистической обработки текстов —  потерял шанс сильно улучшить работу одного из ключевых механизмов СУБД PostgreSQL — autovacuum.

Старый, тертый жизнью и не доверяющий новым веяниям DBA — получил конкретный полезный результат и запланировал новые эксперименты по оптимизации и настройке СУБД.

Задача

Оценить влияние кардинального снижения параметра хранения таблиц fillfactor c базового значения 100 до 50, при агрессивной настройке autovacuum.

Начало работ

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

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

CPU = 2

RAM = 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

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

Тестовая таблица ~60 000 000 строк

Параметры настройки autovacuum на уровне СУБД

autovacuum_analyze_scale_factor 0.1

autovacuum_analyze_threshold 50

autovacuum_vacuum_cost_delay 2 ms

autovacuum_vacuum_cost_limit -1

autovacuum_vacuum_insert_scale_factor 0.2

autovacuum_vacuum_insert_threshold 1000

autovacuum_vacuum_scale_factor 0.2

autovacuum_vacuum_threshold 50

vacuum_cost_limit 2000

Параметры настройки autovacuum для тестовой таблицы

autovacuum_vacuum_scale_factor 0

autovacuum_vacuum_threshold 1000

autovacuum_analyze_scale_factor 0

autovacuum_analyze_threshold 1000

autovacuum_vacuum_insert_scale_factor 0

autovacuum_vacuum_insert_threshold 1000

autovacuum_vacuum_cost_delay 1

autovacuum_vacuum_cost_limit 2000

Нагрузка при тестировании : 5-48

Эксперимент-2 : fillfactor = 100

Интенсивность и показатели выполнения autovacuum

Нагрузка начала роста времени работы autovacuum = 8

Эксперимент-3 : fillfactor = 50

Нагрузка начала роста времени работы autovacuum = 12

Итоги

Для данной СУБД и данного сценария нагрузки , уменьшение параметра хранения таблиц на 50% c 100 до 50 , повысило эффективность работы autovacuum:

• Количество запусков autovacuum увеличилось на 261% c 193 до 697

• Максимальное время работы autovacuum уменьшилось на 29% с 936 сек. до 663 сек.

• Среднее время работы autovacuum уменьшилось на 73% с 46 сек. до 12 сек.

• Предельная нагрузка на autovacuum увеличивалась на 50% с 8 до 12 соединений.

Гипотеза об ограничении процесса autovacuum для высокопроизводительных СУБД:

1. Изменение параметров приводит к росту производительности СУБД

2. Рост производительности СУБД означает большее количество операций UPDATE в ходе нагрузочного тестирования

3. Принципиальное ограничение - производительность процесса autovacuum ограничена пропускной способности дисковой подсистемы.

Следствие :

Мертвые строки накапливаются быстрее, чем autovacuum успевает очищать

Итог:

• В случае стандартного режима работы , единственный выход - добавить регулярную очистку мертвых строк в период наименьшей нагрузки на СУБД.

• В случае режима работы 24/7 - только архитектурное решение - не допускать роста таблиц до огромных размеров , для которых используется очень большое количество операций INSERT/UPDATE.