В прошлых уроках мы разобрали отдельные параметры конфигурации, планировщик и чтение планов через explain analyze. Теперь соберём это в инженерную задачу: как настроить postgresql под конкретный профиль работы. Профилей два разных, и тянуть их одной конфигурацией нельзя. Первый - OLTP: много коротких транзакций, тысячи мелких чтений и записей в секунду (касса, склад, веб-бэкенд). Второй - отчётность и аналитика: редкие, но тяжёлые запросы, которые молотят миллионы строк. Отдельным большим блоком идёт специфика РФ - postgresql для 1с, потому что 1С ведёт себя на сервере БД не как обычное приложение, и без правильной сборки и параметров работает медленно или криво.
Как это работает
OLTP и отчётность нагружают сервер по-разному, и оптимизации у них конфликтуют. OLTP упирается в задержку одной транзакции и в конкуренцию за блокировки и за WAL. Здесь важно, чтобы рабочий набор данных лежал в памяти (shared_buffers и кэш ОС), чтобы коммиты не ждали диск дольше нужного, и чтобы autovacuum успевал чистить мёртвые версии строк (MVCC из урока 34), иначе таблицы пухнут и всё проседает.
Отчётность упирается в пропускную способность одного запроса. Ей нужно много памяти на сортировку и хэши (work_mem), агрессивный параллелизм и крупные порции чтения с диска. Если на одном сервере живут оба профиля, их разводят: глобально ставят консервативные значения, а тяжёлым отчётам поднимают work_mem и параллелизм локально - через SET внутри сессии или per-role настройкой, чтобы один отчёт не выжрал память у сотни кассиров.
Ключевая развилка по памяти простая. shared_buffers - это общий буферный кэш страниц, обычно 25 процентов ОЗУ как старт. work_mem - это лимит на ОДНУ операцию сортировки или хэша в ОДНОМ запросе, и запрос может взять несколько таких кусков сразу, а параллельные воркеры - ещё по копии. Поэтому work_mem умножается на реальную конкуренцию, и завышенное значение при сотнях соединений уводит сервер в OOM. Отсюда правило: высокий work_mem - не глобально, а точечно отчётам.
Теперь про 1С. Она проектировалась под СУБД, где временные таблицы создаются и наполняются на лету десятками за запрос. PostgreSQL по умолчанию не собирает статистику по таблице сразу после наполнения - он ждёт autovacuum. Для обычного приложения это норма, а для 1С смертельно: планировщик видит свежую временную таблицу без статистики, считает её крошечной и строит ужасный план (вложенные циклы вместо хэша). Лечит это расширение online_analyze - оно запускает ANALYZE немедленно после INSERT/UPDATE в таблицу, и планировщик сразу получает реальные оценки. Второе расширение, plantuner, с опцией fix_empty_table заставляет считать пустую таблицу действительно пустой, а не как одну строку - это убирает ещё один класс кривых планов. Оба расширения входят в сборки Postgres Pro и в сборку PostgreSQL для 1С от 1С, поэтому под 1С берут именно такой дистрибутив, а не ванильный.
SQL и примеры
Базовый OLTP-набор в postgresql.conf (значения - старт под сервер, скажем, 64 ГБ ОЗУ, крутить по факту):
Код: Выделить всё
shared_buffers = 16GB # ~25% ОЗУ
effective_cache_size = 48GB # подсказка планировщику: память под кэш всего
work_mem = 32MB # на ОДНУ сортировку/хэш; осторожно при многих коннектах
maintenance_work_mem = 2GB # для VACUUM, CREATE INDEX, REINDEX
max_connections = 100 # держим низким, остальное - через пул
wal_compression = on
checkpoint_completion_target = 0.9
max_wal_size = 16GB # реже контрольные точки = ровнее запись
random_page_cost = 1.1 # для SSD/NVMe, по умолчанию 4 - это про HDD
Код: Выделить всё
-- глобально в конфиге
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
-- а внутри сессии конкретного отчёта поднимаем точечно
SET work_mem = '1GB';
SET max_parallel_workers_per_gather = 8;
-- тяжёлый отчёт по демобазе bookings: выручка по месяцам
SELECT date_trunc('month', f.scheduled_departure) AS m,
sum(tf.amount) AS revenue
FROM bookings.ticket_flights tf
JOIN bookings.flights f ON f.flight_id = tf.flight_id
GROUP BY 1
ORDER BY 1;
RESET work_mem;
Код: Выделить всё
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.city, count(*)
FROM bookings.ticket_flights tf
JOIN bookings.flights f ON f.flight_id = tf.flight_id
JOIN bookings.airports a ON a.airport_code = f.departure_airport
GROUP BY a.city
ORDER BY count(*) DESC;
Код: Выделить всё
# обязательно для корректной работы 1С
online_analyze.enable = on
online_analyze.table_type = 'temporary' # анализировать временные таблицы
online_analyze.local_tracking = on
online_analyze.min_interval = 10000
plantuner.fix_empty_table = on
standard_conforming_strings = off # совместимость со строками 1С
escape_string_warning = off
max_locks_per_transaction = 256 # 1С плодит много объектов на транзакцию
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS online_analyze;
CREATE EXTENSION IF NOT EXISTS plantuner;
SHOW shared_preload_libraries; -- оба должны быть в списке
- work_mem задрали глобально (например 1GB) при max_connections под тысячу - и сервер ловит OOM, потому что память умножается на число параллельных сортировок, а не выдаётся один раз.
- Отключили fsync ради скорости на тесте и забыли вернуть на проде. fsync = off означает: при сбое питания база может стать нечитаемой целиком. Скорость есть, данных нет.
- Поставили max_connections = 2000 вместо пула. Каждое соединение - это процесс и память; правильно держать max_connections низким и ставить пул (PgBouncer, для 1С обычно transaction-режим не подходит из-за временных таблиц - берут session-режим).
- Под 1С взяли ванильный PostgreSQL без online_analyze - и удивляются тормозам отчётов. Без немедленного ANALYZE временных таблиц 1С работает на кривых планах.
- Скопировали чужой postgresql.conf целиком. Конфиг привязан к железу и профилю нагрузки; чужие 64 ГБ shared_buffers на вашем сервере с 16 ГБ ОЗУ положат старт.
- Забыли про autovacuum под OLTP: при высокой записи дефолтных порогов мало, таблицы пухнут (bloat), и быстрый сервер за неделю превращается в медленный.
- random_page_cost = 4 на NVMe - планировщик избегает индексов там, где они выгодны. Для SSD ставьте около 1.1.
- Посмотрите текущие значения: SHOW shared_buffers; SHOW work_mem; SHOW max_connections; SHOW random_page_cost; запишите их.
- На демобазе bookings выполните тяжёлый отчёт (выручка по месяцам выше) с EXPLAIN (ANALYZE, BUFFERS) при дефолтном work_mem и найдите в плане строку Sort Method - external merge Disk означает нехватку памяти.
- Сделайте SET work_mem = '256MB'; повторите тот же EXPLAIN ANALYZE и сравните: ушёл ли Disk, упало ли общее время.
- Создайте временную таблицу CREATE TEMP TABLE t AS SELECT * FROM bookings.ticket_flights LIMIT 200000; и сразу выполните EXPLAIN на JOIN с ней БЕЗ ручного ANALYZE - оцените, как планировщик угадал число строк.
- Выполните ANALYZE t; повторите EXPLAIN и сравните оценки строк до и после - это ровно то, что online_analyze делает автоматически под 1С.
- Через RESET work_mem; верните значение и убедитесь через SHOW, что вернулось.
- Чем отличаются shared_buffers и work_mem по смыслу и по тому, на что умножается каждый из них при нагрузке?
- Почему высокий work_mem опасно ставить глобально и как правильно дать память тяжёлому отчёту?
- Какую проблему планировщика решает расширение online_analyze и почему она критична именно для 1С?
- Что делает plantuner.fix_empty_table и какой класс кривых планов это убирает?
- Чем грозит fsync = off на продакшене и зачем его вообще иногда выключают?
- Почему под высокую нагрузку держат низкий max_connections и ставят пул соединений вместо тысяч прямых коннектов?
