Задача

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

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

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

Забавное , или интересное в ответе нейросети YandexGPT в том, что , я точно знаю откуда взята данная цитата :
https://dzen.ru/a/Z_decDLxQCTdLsJ2

Тщеславие - мой самый любимый грех ;-)