GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Предисловие:
Представьте мощный спортивный автомобиль, который почему-то еле ползёт в час пик. Виноваты не мотор и не колёса, а неправильно настроенная коробка передач и перегруженные дороги. Точно так же высокопроизводительная СУБД PostgreSQL может буквально задыхаться из-за неочевидных проблем конфигурации и инфраструктуры. Этот разбор — история одной такой системы, где 192 ядра, терабайты памяти и SSD оказались бесполезны против каскада мелких, но критичных ошибок в настройках.
Исходные данные и детальный анализ
Главные проблемы: диагностика за 5 минут
1. Диски кричат о помощи
Четыре диска, хранящие основные данные (/data), работают на пределе: загрузка 78–85%, очереди запросов постоянно больше 1.5. При этом диск для журнала транзакций (WAL) почти простаивает.
Вывод: система пытается читать и писать слишком много мелких блоков данных, а дисковая подсистема не справляется. Корень зла — в настройках PostgreSQL, которые заставляют СУБД считать диски сверхбыстрыми.
2. Война блокировок (LWLock)
Внутренние лёгкие блокировки PostgreSQL превратились в узкое горлышко. Корреляция между ожиданиями LWLock и падением скорости работы — 0.92 (максимальная!). Особенно страдают операции управления буферами и блокировками. Это признак высокой конкуренции между запросами за общие ресурсы СУБД.
3. Память: иллюзия изобилия
Из 1 ТБ оперативной памяти свободно меньше 1.5% — система на грани. При этом 246 ГБ выделены под кеш PostgreSQL (shared_buffers), а ещё 707 ГБ занимает кеш ОС.
Получается двойное кеширование одних и тех же данных — колоссальная растрата ресурсов.
4. Топ-3 ошибки в настройках PostgreSQL
- random_page_cost = 1.1 — СУБД считает, что случайное чтение с диска почти бесплатно, и слишком активно использует индексы, создавая лавину мелких I/O-операций.
- effective_io_concurrency = 300 — система пытается выполнять 300 операций ввода-вывода одновременно, перегружая и без того забитые диски.
- work_mem = 1GB — каждый сложный запрос может зарезервировать гигабайт памяти. При сотне одновременных запросов это гарантированная нехватка памяти (OOM).
Что делать? План спасения
Первая неделя: быстрые и безопасные правки
- Пересчитать стоимость операций:
ALTER SYSTEM SET random_page_cost = 4.0; — дать понять планировщику, что диски перегружены.
ALTER SYSTEM SET effective_io_concurrency = 32; — снизить давление на дисковую подсистему. - Остановить мотовство памяти:
ALTER SYSTEM SET work_mem = '128MB'; — снизить риск OOM в 8 раз.
ALTER SYSTEM SET shared_buffers = '128GB'; — сократить двойное кеширование, освободив ~120 ГБ для ОС. - Успокоить фоновые процессы:
Увеличить интервал запуска автоматической очистки (autovacuum) и контрольных точек, чтобы они не соревновались с пользовательскими запросами.
Первый месяц: стратегические улучшения
- Оптимизировать топ-5 самых частых и "тяжёлых" запросов. Запрос, выполняемый 100 млн раз в день, — первый кандидат на кеширование или переписывание.
- Настроить мониторинг блокировок (Locks, LWLock) для оперативного выявления новых узких мест.
- Рассмотреть аппаратный апгрейд: добавление дисков в массив или переход на NVMe-накопители для данных даст радикальное ускорение I/O.
Заключение
Производительность сложной системы — это не только мощность железа, но и её сбалансированность.
Данный случай показывает, как неверные приоритеты в настройках (ставка на "быстрые" диски и огромную память) свели на нет преимущества мощного сервера. Решение лежит не в дорогом апгрейде, а в тонкой настройке: согласовании параметров СУБД с реальным поведением инфраструктуры и нагрузки. Исправление нескольких конфигурационных файлов может дать эффект, сопоставимый с добавлением десятков новых дисков.