Инженерный контур 1С. Часть 2 - Профилирование и настройка PostgreSQL: анализ ожиданий, pg_stat_statements и ликвидация узких мест

5 сентября 2026 · infolimp.ru

Контекст и аудитория

• Профиль читателя: Ведущие разработчики 1С, технические архитекторы корпоративных систем, администраторы баз данных (DBA) и инженеры по оптимизации производительности, обеспечивающие надежность и масштабируемость высоконагруженных инсталляций «1С:Предприятие 8.3».
• Исходное состояние системы: Сквозной практический кейс серии - продуктивный контур «Торговый контур»:
• Контекст внедрения: Промышленная эксплуатация СУБД под непрерывной учетной нагрузкой.
• База данных: Объем 1.8 ТБ под управлением СУБД PostgreSQL 16 (ОС Linux).
• Прикладная конфигурация: «1С:Комплексная автоматизация» / «1С:ERP 2.5» с глубокой функциональной адаптацией механизмов проведения документов, оперативного складского учета и контура взаимодействия с внешними системами.
• Аппаратная инфраструктура: Сервер СУБД с 128 ГБ оперативной памяти, 32 процессорными ядрами (vCPU), высокопроизводительным массивом твердотельных накопителей NVMe в конфигурации RAID-10.
• Профиль нагрузки: Смешанная нагрузка (OLTP-транзакции проведения документов в сочетании с фоновыми регламентными расчетами и непрерывными входящими интеграционными API-запросами). До 350 одновременно активных сеансов пользователей в часы пиковой отгрузки; более 40 000 транзакций обязательной поштучной маркировки в сутки; постоянный входящий поток HTTP-вызовов от логистических провайдеров (3PL) и витрин маркетплейсов по схемам FBO/FBS.
• Ограничения:
• Недопустимость деградации времени отклика системы при выполнении критических учетных операций (жесткий SLA на проведение документов отгрузки).
• Категорическая недопустимость компромиссов с надежностью хранения данных: строгое обеспечение транзакционных гарантий ACID (запрет на отключение механизмов журналирования предзаписи WAL и синхронной фиксации).
• Совокупная стоимость владения (TCO): оптимизация должна сохранять эксплуатационные показатели и совокупную стоимость владения (TCO) за счет системной балансировки параметров СУБД и ликвидации узких мест, исключая необоснованное наращивание аппаратных ресурсов.

В чём проблема

• Симптомы:
• Деградация времени отклика ключевых транзакций: 95-й процентиль (p95) длительности проведения документов отгрузки достигает 14.2 секунды при целевом нормативном значении менее 2.5 секунды.
• Циклические задержки ввода-вывода (I/O Spikes): Периодическое зависание пользовательских интерфейсов и обработчиков API-запросов на 5–12 секунд, синхронизированное по времени со сбросом грязных страниц буферного кэша на диск в процессе фиксации контрольных точек (checkpoints).
• Неоптимальные планы выполнения запросов СУБД: Массовое использование планировщиком неэффективных алгоритмов вложенных циклов (Nested Loop) при соединении с временными таблицами, порождающее миллионы чтений страниц и резкий рост времени выполнения запросов до десятков секунд.
• Деградация пропускной способности и распухание физического объема таблиц и индексов (bloat): Интенсивное обновление записей в регистрах накопления и сведений сопровождается накоплением неактуальных («мертвых») версий строк, достигающим 28–35% от объема таблиц. В результате возникает деградация пропускной способности при последовательном сканировании страниц и неоправданный рост затрат памяти.
• Конкуренция за блокировки: Параллельное выполнение массовых проводок и интенсивных внешних интеграций приводит к взаимным блокировкам; конкуренция за блокировки в таблицах оперативных итогов провоцирует критический рост времени ожидания транзакций.
• Почему типового инструментария недостаточно:
• Недостаточность стандартной конфигурации PostgreSQL для специфики платформы 1С: Базовые настройки СУБД по умолчанию ориентированы на системы с минимальными ресурсами (shared_buffers = 128MB, work_mem = 4MB, checkpoint_timeout = 5min). В продуктивном контуре с базой объемом 1.8 ТБ и 350 параллельными сеансами такие параметры вызывают катастрофический дефицит буферного кэша (коэффициент попадания в кэш падает ниже 90%), лавинообразную запись на диск и исчерпание пула блокировок объектов (out of shared memory).
• Особенности транслятора SDBL платформы 1С и потеря селективности:
  1. Генерация временных таблиц без статистики: Транслятор платформы SDBL (System Database Layer) преобразует сложные пакетные запросы 1С в серии физических временных таблиц (pg_temp). На этапе создания временной таблицы в СУБД отсутствует информация о распределении данных: нет гистограмм, статистики по наиболее частым значениям (MCV) и точной оценки количества строк.
  2. Эвристическая оценка кардинальности: При отсутствии статистики планировщик PostgreSQL принимает консервативную оценку кардинальности временной таблицы по умолчанию - ровно 1000 строк. Когда в реальной эксплуатации во временную таблицу помещается 50 000 партий номенклатуры, планировщик ошибочно полагает, что таблица мала, и выбирает соединение методом вложенных циклов (Nested Loop) с многократным поиском по индексу основной таблицы. Это увеличивает объем чтений страниц в сотни раз по сравнению с соединением хешированием (Hash Join) или слиянием (Merge Join).
• Несглаженный сброс контрольных точек: При несглаженных параметрах контрольных точек СУБД выполняет запись модифицированных буферов с максимальной интенсивностью дисковой подсистемы, формируя глубокую очередь ввода-вывода (I/O queue), которая блокирует выполнение рядовых транзакций проведения документов.
• Запаздывание фонового процесса автоматической очистки (Autovacuum Lag): Высокая частота модификаций строк в регистрах («ТоварыНаСкладах», «РасчетыСКлиентами») требует агрессивной очистки устаревших версий. Стандартный коэффициент autovacuum_vacuum_scale_factor = 0.2 инициирует очистку только после изменения 20% объема таблицы. Для таблицы размером 200 ГБ это означает накопление десятков гигабайт мертвых строк до момента запуска рабочего процесса очистки, что дестабилизирует индексы B-tree и снижает скорость чтения.

Архитектура решения

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

Компоненты архитектуры

1. Конфигурационный профиль postgresql-1c.conf: Сбалансированный набор системных параметров, настроенный под работу транслятора SDBL, твердотельные накопители NVMe и предотвращение сбоев по исчерпанию оперативной памяти.
2. Расширение pg_stat_statements: Модуль ядра СУБД, выполняющий непрерывный сбор агрегированной статистики по нормализованным SQL-запросам: суммарное и среднее время выполнения, число вызовов, процент попадания в буферный кэш, чтение/запись на диск, использование временных файлов.
3. Инструмент pg_profile: Автономный механизм регулярных снимков состояния производительности СУБД с возможностью построения дифференциальных отчетов в формате HTML между произвольными моментами времени (например, до и во время пика нагрузки).
4. Прикладная контекстная привязка через application_name: Передача платформой 1С метаданных сеанса в атрибут соединения PostgreSQL позволяет связывать тяжелый SQL-запрос с конкретным пользователем, номером сеанса и выполняемой операцией.

Методология балансировки параметров оперативной памяти

Главная задача конфигурации памяти - исключить конкуренцию между буферным кэшем СУБД (shared_buffers), системным дисковым кэшем операционной системы (Page Cache) и локальной памятью сессий (work_mem).

Общая оперативная память хоста (128 ГБ) распределяется по следующей модели:

$$\text{RAM}{\text{total}} = \text{shared_buffers} + \text{Page Cache}{\text{OS}} + (\text{Active Sessions} \times \text{Sort Operations} \times \text{work_mem}) + \text{OS Overhead}$$

+-----------------------------------------------------------------------------+
|                       Общий объем RAM хоста: 128 ГБ                         |
+-----------------------+-----------------------------+-----------------------+
| shared_buffers: 32 ГБ |  Page Cache ОС: ~64–70 ГБ   | work_mem пул: ~24 ГБ  |
| (25% от всей RAM)     |  (Кэширование страниц диска | (400 соединений       |
| Хранение активных     |   ядром Linux, чтение WAL   |  × 1-2 сортировки     |
| страниц таблиц/индекс.|   и временных файлов)       |  × 48 МБ) + Резерв ОС |
+-----------------------+-----------------------------+-----------------------+

1. shared_buffers = 32GB (25% от RAM): Для СУБД под управлением Linux выделение более 25–30% памяти под shared_buffers нецелесообразно из-за двойного кэширования: ядро Linux эффективно использует свободную оперативную память в качестве страничного кэша (Page Cache).
2. effective_cache_size = 96GB (75% от RAM): Значение информирует планировщик о доступном объеме кэша (память СУБД + страничный кэш ОС), влияя на выбор между сканированием по индексу (Index Scan) и последовательным чтением (Seq Scan).
3. work_mem = 48MB: Задает лимит памяти для одной операции сортировки, хеширования или построения битовой карты. При 350 параллельных сеансах завышение этого параметра создает прямую угрозу исчерпания оперативной памяти и аварийной остановки процессов ОС (OOM Killer).
4. temp_buffers = 16MB: Буфер для временных таблиц в рамках сеанса, критичный для пакетных запросов 1С.

Пошаговая реализация

Шаг 1: Применение параметров конфигурационного профиля postgresql-1c.conf

В файл конфигурации СУБД вносятся инженерно выверенные параметры. Файл подключается через директиву include_dir = 'conf.d' или include:

# 1. Распределение оперативной памяти
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 48MB
maintenance_work_mem = 2GB
temp_buffers = 16MB

# 2. Сглаживание контрольных точек (Checkpoints)
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 32GB
min_wal_size = 4GB
wal_compression = on
wal_buffers = 16MB

# 3. Параметры дисковой подсистемы NVMe
seq_page_cost = 1.0
random_page_cost = 1.1
effective_io_concurrency = 200

# 4. Процесс автоматической очистки (Autovacuum)
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 20s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_cost_limit = 1000
autovacuum_vacuum_cost_delay = 2ms

# 5. Сбор телеметрии и профилирование
shared_preload_libraries = 'pg_stat_statements, pg_profile'
track_io_timing = on
track_functions = all
track_activity_query_size = 4096

# 6. Специфика платформы 1С:Предприятие
max_connections = 400
max_locks_per_transaction = 256
escape_string_warning = off
standard_conforming_strings = on
row_security = off
fsync = on
synchronous_commit = on
idle_in_transaction_session_timeout = 10min
lock_timeout = 60s

Инженерное обоснование параметров: - checkpoint_completion_target = 0.9: при таймауте контрольной точки 15 минут СУБД равномерно распределяет сброс страниц на протяжении $15 \times 0.9 = 13.5$ минут. Это полностью устраняет пиковые задержки дискового ввода-вывода (I/O spikes). - random_page_cost = 1.1 при seq_page_cost = 1.0: для твердотельных накопителей NVMe время случайного доступа сопоставимо с последовательным. Снижение random_page_cost со стандартного значения 4.0 до 1.1 предотвращает отказ планировщика от использования эффективных индексов. - autovacuum_vacuum_scale_factor = 0.05: очистка инициируется при модификации 5% строк таблицы вместо 20%, что предотвращает прогрессирующее распухание регистров. - autovacuum_vacuum_cost_limit = 1000 и cost_delay = 2ms: увеличение квоты дисковых операций ускоряет очистку объемных таблиц в 5 раз по сравнению со стандартными значениями (200 и 20ms) без создания критической нагрузки на диски NVMe.

Применение параметров, требующих аллокации разделяемой памяти (shared_preload_libraries, shared_buffers, max_connections, max_locks_per_transaction), выполняется с плановым перезапуском службы:

sudo systemctl restart postgresql@16-main

Шаг 2: Развертывание расширений телеметрии и настройка сбора статистики

Расширения развертываются в выделенной схеме dba скриптом init-extensions.sql:

CREATE SCHEMA IF NOT EXISTS dba;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA dba;
CREATE EXTENSION IF NOT EXISTS pg_profile SCHEMA dba;

GRANT USAGE ON SCHEMA dba TO PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA dba TO PUBLIC;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA dba TO PUBLIC;

Для проверки фиксации статистики выполняется тестовый запуск запроса к pg_stat_statements:

SELECT calls, total_exec_time, query 
FROM dba.pg_stat_statements 
ORDER BY total_exec_time DESC LIMIT 5;

Параметр track_io_timing = on обеспечивает измерение времени чтения (blk_read_time) и записи (blk_write_time) блоков в миллисекундах, что позволяет выявлять запросы, простаивающие в очередях дисковой подсистемы.

Шаг 3: Формирование дифференциальных отчетов pg_profile

Для локализации периодов пиковых задержек настраивается регулярное создание снимков активности через скрипт scripts/pg-profile-cron.sh:

# Ручной вызов создания снимка активности
/usr/local/bin/pg-profile-cron.sh snapshot

# Генерация дифференциального отчета между двумя последними снимками
/usr/local/bin/pg-profile-cron.sh report

Дифференциальный отчет в формате HTML агрегирует: 1. Wait Events (события ожидания): долю времени, проведенную сессиями в ожидании блокировок таблиц/строк (Lock:relation, Lock:tuple), дискового чтения (IO:DataFileRead) и синхронизации журнала предзаписи (IO:WALSync). 2. Top SQL by Execution Time & I/O: перечень операторов, утилизировавших максимальный бюджет процессорного времени и дисковых операций за исследуемый интервал. 3. Checkpoint & Background Writer Activity: объем сброшенных буферов, длительность контрольных точек и количество принудительных синхронизаций по исчерпанию max_wal_size.

Шаг 4: Расследование графа взаимоблокировок и длительных ожиданий

При возникновении очередей ожидания на стороне прикладного контура диагностика выполняется запуском скрипта sql/locks-tree.sql.

Запрос выполняет рекурсивное построение иерархии блокировок на основе pg_locks, pg_stat_activity и встроенной функции pg_blocking_pids:

WITH RECURSIVE lock_tree AS (
    SELECT 
        blocked.pid AS blocked_pid,
        unnest(pg_blocking_pids(blocked.pid)) AS blocker_pid
    FROM pg_stat_activity blocked
    WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
),
tree_hierarchy AS (
    SELECT 
        DISTINCT lt.blocker_pid AS pid,
        NULL::integer AS parent_pid,
        1 AS depth,
        ARRAY[lt.blocker_pid] AS path
    FROM lock_tree lt
    WHERE lt.blocker_pid NOT IN (SELECT blocked_pid FROM lock_tree)
    UNION ALL
    SELECT 
        lt.blocked_pid AS pid,
        th.pid AS parent_pid,
        th.depth + 1 AS depth,
        th.path || lt.blocked_pid AS path
    FROM lock_tree lt
    JOIN tree_hierarchy th ON lt.blocker_pid = th.pid
    WHERE NOT (lt.blocked_pid = ANY(th.path))
)
SELECT 
    repeat('  +- ', th.depth - 1) || th.pid::text AS tree_view,
    CASE WHEN th.parent_pid IS NULL THEN 'ROOT BLOCKER' ELSE 'BLOCKED' END AS status,
    sa.application_name,
    sa.state,
    round(EXTRACT(EPOCH FROM (clock_timestamp() - sa.xact_start))::numeric, 2) AS xact_duration_sec,
    sa.query
FROM tree_hierarchy th
JOIN pg_stat_activity sa ON th.pid = sa.pid
ORDER BY th.path;

Пример диагностического вывода:

tree_view |    status    |              application_name               |       state       | xact_duration_sec |                      query
----------+--------------+---------------------------------------------+-------------------+-------------------+--------------------------------------------------
148201    | ROOT BLOCKER | 1CV8C: TradeContour (session 814, Ivanova)  | idle in transacti | 142.50            | UPDATE _accumrg1420 SET _fld1425 = ...
  +- 149312| BLOCKED      | 1CV8C: TradeContour (session 902, Petrov)   | active            | 12.10             | SELECT ... FROM _accumrg1420 WHERE ... FOR SHARE
  +- 149504| BLOCKED      | 1CV8C: TradeContour (session 915, Sidorov)  | active            | 9.80              | INSERT INTO _accumrg1420 ...

Инженер мгновенно идентифицирует корневую сессию (PID 148201, пользователь Ivanova), удерживающую транзакцию в состоянии idle in transaction на протяжении 142 секунд из-за паузы в клиентском коде или модального диалогового окна, что вызвало каскадную блокировку зависимых сессий проведения документов.

Проверка результата и метрики

Результаты применения оптимизационного профиля и диагностических инструментов в рамках проекта «Торговый контур» зафиксированы на основе непрерывной телеметрии:

Динамика 95-го процентиля времени проведения документов (p95 latency)
Секунды
  16 +  +--------------------+ (14.2 с: деградация I/O при checkpoints + Nested Loops)
  14 +  | Исходное состояние |
  12 +  +--------------------+
  10 +            \\
   8 +             \\
   6 +              \\  Применение postgresql-1c.conf,
   4 +               \\ сглаживание checkpoints и autovacuum
   2 +                +---► +----------------------+
   0 +----------------------+-- 2.5 с (Норматив) --+--------------------► Время

Устранение пиковых задержек достигнуто за счет трех взаимосвязанных факторов: 1. Растягивание сброса модифицированных страниц во времени (checkpoint_completion_target = 0.9) ликвидировало насыщение дисковой очереди NVMe. 2. Повышение точности планов запросов транслятора SDBL за счет адекватного соотношения random_page_cost = 1.1 исключило выбор вложенных циклов (Nested Loop) при соединении с крупными таблицами. 3. Упреждающая фоновая очистка (autovacuum_vacuum_scale_factor = 0.05) исключила раздувание таблиц регистров и деградацию структуры B-tree индексов.

Риски и ограничения

При настройке СУБД PostgreSQL в корпоративных инсталляциях «1С:Предприятие» необходимо строго соблюдать эксплуатационные ограничения:

1. Риски завышения параметра work_mem (Угроза отказа по исчерпанию памяти OOM)

Распространенная ошибка администрирования - назначение work_mem из простого расчета Доступная память / Число соединений (например, $64\text{ ГБ} / 400 \approx 160\text{ МБ}$). Механизм риска: Параметр work_mem выделяется не на сессию, а на каждую операцию сортировки, построения хеш-таблицы или агрегации внутри плана выполнения запроса. Сложный запрос проведения документа в ERP-системе может содержать несколько операций соединения хешированием (Hash Join) и сортировок одновременно (3–5 операторов на один запрос). При расчете $400\text{ соединений} \times 4\text{ операции} \times 160\text{ МБ} \approx 256\text{ ГБ}$ требуемой памяти сервер мгновенно исчерпает физическую память (128 ГБ), что приведет к активации механизма Linux OOM Killer и аварийной остановке основного процесса postgres. Правило: В продуктивных контурах значение work_mem должно поддерживаться на консервативном уровне 32–64 МБ. Для тяжелых регламентных ночных расчетов лимит памяти должен увеличиваться локально в рамках конкретной сессии (SET work_mem = '512MB';).

2. Категорическая недопустимость отключения fsync и synchronous_commit

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

3. Ограничения параллельного выполнения запросов (parallel query) в контуре 1С

Механизм параллельного выполнения запросов в PostgreSQL ориентирован на аналитические запросы (OLAP) к крупным постоянным таблицам. При взаимодействии с платформой 1С: - Временные таблицы (pg_temp), формируемые транслятором SDBL, не поддерживают параллельное сканирование. - Запуск параллельных рабочих процессов (parallel workers) для транзакционных запросов OLTP создает существенные накладные расходы на межпроцессорное взаимодействие (IPC) и конкуренцию за блокировки буферного кэша. - В условиях 350 параллельных сеансов включение параллелизма быстро исчерпывает бюджет доступных процессорных ядер. Правило: Параметр max_parallel_workers_per_gather в продуктивном контуре 1С должен быть строго ограничен (значение 0 или 2).

Итоги

Практические результаты

• Проведена системная оптимизация СУБД PostgreSQL 16 под специфику учетной нагрузки платформы «1С:Предприятие 8.3» без дополнительных затрат на приобретение серверного оборудования (сохранение совокупной стоимости владения - TCO).
• Ликвидированы критические факторы нестабильности: циклические всплески задержек ввода-вывода при контрольных точках, деградация планов запросов SDBL во временных таблицах и распухание регистров накопления.
• Развернут инструментальный контур профилирования (pg_stat_statements, pg_profile, скрипты анализа блокировок), обеспечивающий быстрое расследование инцидентов с детализацией до сеанса пользователя 1С.
• Эксплуатационные показатели продуктивного контура «Торговый контур» стабилизированы: 95-й процентиль времени проведения ключевых документов сокращен с 14.2 до 2.5 секунды при росте коэффициента попадания в кэш до 98.5%.

Переход к следующей части

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

В следующей публикации «Часть 3. Анализ потребления памяти рабочими процессами: локализация утечек и настройка кластера серверов 1С» мы перейдем на уровень приложения: детально разберем физику потребления оперативной памяти процессами rphost, настроим профили безопасности кластера для отсечки неконтролируемых запросов, выстроим правила мягкого перезапуска процессов и реализуем изоляцию фоновых заданий для исключения деградации интерактивных пользователей.