PostgreSQL для 1С: настройка памяти и автовакуум под учётную нагрузку

PostgreSQL из коробки настроен так, чтобы запуститься где угодно, включая слабый ноутбук. На сервере с 128 гигабайтами памяти он по умолчанию использует смешную долю. Разберём, как считать параметры памяти под учётную нагрузку 1С, почему автовакуум важнее всех остальных настроек вместе взятых и как диагностировать проблемы до того, как о них скажут пользователи.

Готовые конфигурации из интернета опасны тем, что не знают вашего профиля нагрузки. Считать надо от своих чисел.

shared_buffers — общий кэш страниц. Выделяется при старте и не меняется. Ориентир — четверть физической памяти.

Почему не половина и не больше: PostgreSQL, в отличие от других СУБД, активно опирается на файловый кэш операционной системы. Данные, вытесненные из shared_buffers, с высокой вероятностью остаются в кэше ОС и читаются быстро. Раздувание shared_buffers отнимает память у этого второго уровня и увеличивает нагрузку на механизм контрольных точек.

effective_cache_size — не выделяет ничего. Это подсказка планировщику о том, сколько памяти суммарно доступно под кэширование, включая кэш ОС. Ставим 60–75 % физической памяти. Заниженное значение заставляет планировщик избегать индексных сканирований там, где они выгодны.

work_mem — самый опасный параметр. Это лимит не на сессию, а на каждую операцию сортировки, хеширования или построения битовой карты внутри запроса.

Сложный отчёт 1С легко порождает 10–15 таких операций. Умножаем на число одновременных сессий — и получаем потенциальное потребление, которое надо соотносить с реальным объёмом памяти.

Значения work_mem здесь намеренно консервативные. Поднимать их надо адресно и по факту, а не заранее.

Одно предостережение перед расчётами.

Готовые калькуляторы конфигурации, которых в сети десяток, дают приличную отправную точку для универсальной OLTP-нагрузки, но они ничего не знают ни про то, что 1С активно использует временные таблицы, ни про то, что типовой отчёт на СКД порождает запрос с двумя десятками соединений, ни про профиль вашей конкретной базы — а именно эти особенности определяют, где у вас окажется узкое место.

Берите их вывод как черновик. Не как конфигурацию.

Есть точный признак: появление временных файлов. Когда операции не хватает памяти, она выгружает промежуточные данные на диск.

Включаем логирование всех временных файлов:

log_temp_files = 0
log_min_duration_statement = 3000

Первый параметр пишет в лог каждое создание временного файла с размером. Второй — все запросы дольше трёх секунд.

Через неделю смотрим лог. Если временные файлы создаются регулярно и их размер стабильно немного превышает work_mem — параметр стоит поднять. Если размеры измеряются гигабайтами — поднимать бесполезно, надо разбираться с запросом.

Статистику по базе целиком даёт представление:

SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_total,
       blks_read, blks_hit,
       round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database WHERE datname NOT LIKE 'template%';

Колонка cache_hit_pct заслуживает внимания отдельно. Для учётной базы нормальным считается значение выше 95 %. Ниже 90 % означает, что рабочий набор не помещается в память, и это аргумент либо за увеличение памяти, либо за shared_buffers.

Полезный приём вместо глобального повышения: задавать work_mem точечно для конкретной роли или сессии. Если тяжёлые отчёты строит выделенный пользователь, ему можно дать больше, не трогая остальных.

ALTER ROLE reports_user SET work_mem = '256MB';

При больших shared_buffers операционная система тратит заметные ресурсы на управление таблицей страниц. Каждый процесс PostgreSQL имеет свою, и при сотне соединений накладные расходы становятся ощутимыми.

Huge pages увеличивают размер страницы с 4 КБ до 2 МБ, сокращая таблицу страниц в сотни раз.

Настройка в две части. Сначала считаем, сколько страниц нужно:

# узнать требуемый объём
head -1 /var/lib/pgsql/16/data/postmaster.pid   # PID
grep ^VmPeak /proc/$(head -1 /var/lib/pgsql/16/data/postmaster.pid)/status
# число страниц = VmPeak / 2048 KB, с запасом 10 %

Затем резервируем в ядре и включаем в PostgreSQL:

# /etc/sysctl.d/90-postgres.conf
vm.nr_hugepages = 17000
vm.swappiness = 5
vm.overcommit_memory = 2
vm.overcommit_ratio = 90

# postgresql.conf
huge_pages = try

Значение try вместо on — сознательная страховка: если страниц не хватит, сервер запустится на обычных вместо отказа старта. Для боевого сервера после проверки можно ставить on, чтобы неудача была заметной, а не тихой.

Проверить, что работает:

grep -i huge /proc/meminfo

Ненулевое HugePages_Rsvd означает, что PostgreSQL их использует. По нашим замерам на серверах с 64 ГБ и выше выигрыш составляет 5–12 % на смешанной нагрузке — не революция, но бесплатно.

PostgreSQL не изменяет строки на месте. Обновление создаёт новую версию, старая остаётся мёртвой до уборки.

Учётная нагрузка 1С генерирует мёртвых строк очень много: движения регистров переписываются при каждом перепроведении документа, итоги пересчитываются, служебные таблицы обновляются постоянно.

Если автовакуум не успевает, таблица раздувается. Физически она занимает в разы больше, чем логически содержит данных, и любое сканирование читает лишние страницы. Деградация постепенная и незаметная, пока не станет катастрофической.

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

autovacuum_max_workers = 4
autovacuum_naptime = 20s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 3000

Смысл изменений: просыпаться чаще, срабатывать при 5 % изменённых строк вместо 20 %, и не тормозить себя лимитом стоимости — дефолтный лимит рассчитан на медленные диски двухтысячных.

Отдельно стоит настроить самые активные таблицы индивидуально. Для регистра, который переписывается тысячами строк в час, разумно опуститься ещё ниже:

ALTER TABLE public._accumrgt1234 SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 0
);

Найти кандидатов на индивидуальную настройку легко: это таблицы с наибольшим числом обновлений.

Основной запрос, который стоит выполнять раз в неделю:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 50000
ORDER BY dead_pct DESC
LIMIT 25;

Как читать результат:

• dead_pct устойчиво выше 20 % на крупных таблицах — автовакуум не справляется.
• last_autovacuum пустой или очень старый при большом n_dead_tup — либо автовакуум выключен, либо не доходит до этой таблицы.
• autovacuum_count растёт, а dead_pct не падает — вакуум запускается, но не может очистить строки. Почти всегда это долгая открытая транзакция.

Последний случай — самый коварный. Вакуум не может удалить версии строк, которые всё ещё могут понадобиться открытой транзакции. Одно зависшее соединение, открывшее транзакцию три дня назад, блокирует уборку по всей базе.

SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       now() - state_change AS idle_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Транзакции возрастом больше нескольких часов надо разбирать. Часто это оборванное соединение от внешней интеграции или зависший сеанс 1С.

Защита от такой ситуации задаётся параметрами:

idle_in_transaction_session_timeout = '30min'
statement_timeout = 0     -- для 1С не ограничиваем, тяжёлые отчёты законны

Первый параметр обрывает сессии, которые держат открытую транзакцию и ничего не делают. Это ровно тот случай, который блокирует вакуум.

Ещё одна группа параметров, влияющая на равномерность работы.

PostgreSQL периодически сбрасывает изменённые страницы из памяти на диск — это контрольная точка. Если она происходит редко и большими порциями, вы получаете периодические провалы производительности.

checkpoint_timeout = 15min
max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_compression = on

Смысл: растянуть контрольную точку во времени вместо резкого сброса. checkpoint_completion_target = 0.9 означает, что запись размазывается на 90 % интервала между точками.

Проверить, не происходят ли точки слишком часто:

SELECT num_timed, num_requested,
       round(100.0 * num_requested / NULLIF(num_timed + num_requested, 0), 1) AS forced_pct
FROM pg_stat_checkpointer;   -- в версиях до 17: pg_stat_bgwriter

Высокая доля вынужденных точек означает, что max_wal_size мал: журнал заполняется раньше, чем истекает таймер, и сброс запускается принудительно. Увеличение max_wal_size — прямое лечение.

И последнее общее замечание. Все перечисленные параметры взаимосвязаны, и менять их пачкой вслепую не стоит. Наш порядок: сначала память и huge pages, затем автовакуум, затем контрольные точки — с недельным наблюдением после каждого шага и фиксацией замеров. Три недели вместо одного вечера, зато вы точно знаете, что дало эффект, и можете откатить конкретное изменение, а не всю конфигурацию целиком.

Клиент — сеть автосервисов, 26 рабочих мест, УНФ на PostgreSQL 15, Astra Linux.

Обращение звучало так: «база растёт, места на диске не хватает, при этом документов вводим столько же, сколько год назад».

Цифры подтверждали странность. Год назад база занимала 42 ГБ, сейчас — 131 ГБ. Количество документов за тот же период выросло примерно на 18 %.

Первый же запрос по мёртвым строкам объяснил всё. На пяти крупнейших таблицах регистров доля мёртвых версий составляла от 61 до 78 %. То есть две трети физического объёма базы были мусором.

Поле last_autovacuum у этих таблиц было пустым. Не старым — пустым. Автовакуум не отработал по ним ни разу.

Смотрим pg_stat_activity. И находим соединение с открытой транзакцией возрастом 400 с лишним дней.

Это было соединение от самописной интеграции с системой учёта запчастей. Она открывала транзакцию, читала данные и не закрывала соединение — оставляла его в состоянии idle in transaction в расчёте на переиспользование. Интеграцию запустили в мае прошлого года, и с тех пор ровно одно соединение держало горизонт видимости на месте.

Механика в том, что вакуум не может удалить версию строки, которая ещё может понадобиться какой-либо активной транзакции. Одна открытая транзакция годичной давности означала, что удалять нельзя вообще ничего, изменённого за этот год.

Лечение шло в три шага.

Первый — обрыв зависшего соединения и установка idle_in_transaction_session_timeout в 30 минут, чтобы это не повторилось.

Второй — VACUUM FULL по пяти самым раздутым таблицам в выходное окно. Операция блокирующая и требует места под копию таблицы, поэтому делалась ночью и по одной. Заняла суммарно около пяти часов.

Третий — настройка агрессивного автовакуума по схеме из этой статьи, с индивидуальными параметрами для активных регистров.

Результат: база сжалась со 131 ГБ до 47 ГБ. Формирование отчёта по заказ-нарядам за месяц ушло с 96 секунд до 8. Проведение заказ-наряда — с 3,4 секунды до 0,7.

Отдельно стоит сказать про диагностику. Мы потратили на поиск причины около сорока минут, и почти всё это время ушло на первые два запроса. Проблема была видна сразу, как только посмотрели в правильное место.

А не смотрели туда год, потому что симптом — «база растёт» — выглядел естественным. База и должна расти. Никто не проверял, растут ли вместе с ней данные.

Сколько ставить shared_buffers для 1С?

Около четверти физической памяти. Больше половины давать не стоит: PostgreSQL активно использует файловый кэш операционной системы как второй уровень, и раздувание shared_buffers отнимает память у него, попутно утяжеляя контрольные точки.

Почему нельзя просто поставить work_mem побольше?

Он действует не на сессию, а на каждую операцию сортировки или хеширования внутри запроса. Сложный отчёт 1С порождает десяток таких операций, и при сорока одновременных пользователях щедрое значение превращается в требование в сотни гигабайт памяти.

Как понять, что автовакуум не справляется?

По доле мёртвых строк в pg_stat_user_tables: устойчивые значения выше 20 % на крупных таблицах — сигнал. Если при этом autovacuum_count растёт, а доля не падает, ищите долгую открытую транзакцию — она не даёт вакууму очистить версии строк.

Что даёт включение huge pages?

Сокращает накладные расходы на управление таблицей страниц, которая у каждого процесса PostgreSQL своя. По нашим замерам на серверах с 64 ГБ и больше выигрыш составляет 5–12 % на смешанной нагрузке. Настраивается в паре: резервирование страниц в ядре и параметр huge_pages в конфигурации.