GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Предисловие
Статья посвящена комплексному анализу производительности СУБД PostgreSQL, основанному на данных мониторинга и статистики. В ходе анализа выявлены ключевые проблемы: перегруженность дисковой подсистемы, contention блокировок, неоптимальное использование памяти и конфигурационные ошибки. В итоге сформированы практические рекомендации, сгруппированные по приоритетам и срокам внедрения, которые помогут стабилизировать и значительно улучшить работу базы данных.
Исходные данные
Регрессионный и корреляционный анализ
Операционная скорость
Ожидания СУБД
Ожидания типа IO
Ожидания типа IPC
Ожидания типа Lock
Ожидания типа LWLock
1. Сводный анализ проблем производительности
1.1. ТОП-3 ПРОБЛЕМЫ ПРОИЗВОДИТЕЛЬНОСТИ
1.1.1. Критическая нагрузка на дисковую подсистему /data
- Метрики: Утилизация дисков vdh/vdi/vdj/vdk >78-85% (ALARM: 100% наблюдений >50%)
- Глубина очереди: aqu_sz > 1.6-2.0 (ALARM: 100% наблюдений >1)
- Корреляция: WAITINGS-IO = 0.7540 (высокая), IO-wa = 0.2156 (средняя)
- Причина: Чрезмерные операции чтения (10-12k r/s) и записи (600-735 w/s) на диск
1.1.2. Экстремальная конкуренция за блокировки (LWLock)
- Метрики: Корреляция WAITINGS-LWLOCK = 0.9215 (очень высокая)
- Основные события: BufferMapping (12.26%), LockManager (4.68%), MultiXactGen (3.75%)
- Причина: Высокая конкурентная нагрузка на общие структуры данных СУБД
1.1.3. Недостаточное использование оперативной памяти для кэширования
- Метрики: Свободная RAM <5% (ALARM: 100% наблюдений)
- Проблема: memory_cache ~707 ГБ, но корреляция cache-r/s = 0.8581 (очень высокая)
- Причина: Кэш ОС неэффективно снижает нагрузку на диск
1.2. КОРРЕЛЯЦИОННЫЙ АНАЛИЗ ВЛИЯНИЯ НА SPEED
Отрицательная корреляция SPEED-WAITINGS = -0.8148 (при росте ожиданий скорость падает)
Наиболее влияющие типы ожиданий (по силе корреляции с WAITINGS):
- LWLock: 0.9215 (критическое влияние) — конкуренция за внутренние блокировки
- IPC: 0.8181 (высокое влияние) — межпроцессное взаимодействие
- Lock: 0.7546 (высокое влияние) — блокировки объектов БД
- IO: 0.7540 (высокое влияние) — операции ввода/вывода
BUFFERPIN (0.4369) и EXTENSION (0.2841) имеют умеренное влияние.
1.3. ИНФРАСТРУКТУРНЫЕ ОГРАНИЧЕНИЯ
1.3.1. Дисковая подсистема — КРИТИЧЕСКОЕ ОГРАНИЧЕНИЕ
- /data (vdh/vdi/vdj/vdk): утилизация 78-85%, очередь 1.6-2.0
- Пропускная способность: чтение 82-112 MB/s, запись 5.3-5.9 MB/s
- Проблема: Диски не справляются с нагрузкой, вызывая каскадные проблемы
1.3.2. Оперативная память — СУЩЕСТВЕННОЕ ОГРАНИЧЕНИЕ
- Использование: 1007 ГБ RAM, но свободно <14 ГБ (<1.4%)
- Распределение:
Кэш ОС: ~707 ГБ (70% RAM)
Буферы: ~1.5 ГБ (0.15%)
Проблема: Неоптимальное распределение между кэшем ОС и буферами PostgreSQL
1.3.3. CPU — НЕТ ЯВНЫХ ОГРАНИЧЕНИЙ
- Использование: user 30%, system 6%, I/O wait 2-3%
- Очередь: 69-71 процессов при 192 ядрах (36-37% загрузки)
- Резерв: Значительный запас мощности процессора
1.4. КОНФИГУРАЦИОННЫЕ ПРОБЛЕМЫ POSTGRESQL
Критические параметры, требующие корректировки:
- random_page_cost = 1.1 — слишком низко для дисков (даже для SSD)
- effective_io_concurrency = 300 — чрезмерно высокое для текущей дисковой подсистемы
- max_parallel_workers_per_gather = 0 — отключен параллелизм при 192 ядрах
- work_mem = 1GB — опасно высокое при многих одновременных сессиях
- shared_buffers = 251807MB (~246GB) — возможно чрезмерно для данной нагрузки
- autovacuum_vacuum_scale_factor = 0.001 — слишком агрессивный автовакуум
- commit_delay = 1000 — очень высокое значение, может увеличивать задержки
Потенциально проблемные настройки WAL:
- wal_keep_size = 500GB — огромный объем WAL файлов
- checkpoint_timeout = 15min + max_wal_size = 8GB — возможна интенсивная запись WAL
1.5. РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ (ПРИОРИТЕТ)
ВЫСОКИЙ ПРИОРИТЕТ (1-2 дня):
- Оптимизация дисковой подсистемы:
Проверить RAID-конфигурацию дисков /data
Оптимизировать файловую систему (noatime, nobarrier)
Рассмотреть разделение hot/cold данных - Коррекция критических параметров PostgreSQL:sqlALTER SYSTEM SET random_page_cost = 2.0;
ALTER SYSTEM SET effective_io_concurrency = 32;
ALTER SYSTEM SET work_mem = '128MB';
ALTER SYSTEM SET shared_buffers = '128GB'; -- уменьшить с 246GB
ALTER SYSTEM SET max_parallel_workers_per_gather = 4; - Оптимизация памяти ОС:
Настроить vm.swappiness = 1
Увеличить vm.dirty_background_ratio = 5
Уменьшить vm.dirty_ratio = 10
СРЕДНИЙ ПРИОРИТЕТ (1-2 недели):
- Оптимизация проблемных запросов:
Сфокусироваться на топ-20 QUERYID из Pareto-анализа
Особое внимание запросам с QUERYID: 940651181422038149, -1757223094415174739, 3548304247151540986 - Настройка автовакуума:sqlALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; - Мониторинг блокировок:
Внедрить мониторинг pg_locks и pg_stat_activity
Настроить alert при длительных блокировках
НИЗКИЙ ПРИОРИТЕТ (1 месяц):
- Архитектурные улучшения:
Рассмотреть partitioning для крупных таблиц
Оптимизация индексов (возможно, избыточные/неиспользуемые) - Аппаратные улучшения:
Рассмотреть NVMe диски для /data
Увеличить RAM для лучшего кэширования - Настройка расширенных параметров:sqlALTER SYSTEM SET commit_delay = 100;
ALTER SYSTEM SET checkpoint_completion_target = 0.7;
ALTER SYSTEM SET wal_keep_size = '64GB'; -- уменьшить с 500GB
МОНИТОРИНГ ПОСЛЕ ИЗМЕНЕНИЙ:
Ключевые метрики для отслеживания:
- Утилизация дисков (%util) — целевое <70%
- Длина очереди (aqu_sz) — целевое <1
- WAITINGS-LWLOCK корреляция — целевое <0.7
- Свободная RAM — целевое >5%
Предупреждение: Изменения вносить постепенно, с мониторингом после каждого шага. Начать с оптимизации дисковой подсистемы и корректировки random_page_cost/effective_io_concurrency, так как это даст наиболее быстрый эффект.
2. Анализ дисковой подсистемы и IO-проблем
2.1. ПРОБЛЕМНЫЕ ДИСКИ
ВЫВОД: Все 4 диска файловой системы /data находятся в критическом состоянии:
- Утилизация постоянно >75% (ALARM: 100% наблюдений >50%)
- Длина очереди >1.5 (ALARM: 100% наблюдений >1)
- Высокая корреляция с WAITINGS IO (0.7540)
WAL диск (vdg) в отличном состоянии:
- Утилизация <10%
- Очередь 0.1
- Специализированный WAL диск работает оптимально
2.2. ПАТТЕРНЫ ДОСТУПА К ДИСКАМ
Для дисков /data (vdh-vdk):
Для WAL диска (vdg):
КЛЮЧЕВЫЕ НАБЛЮДЕНИЯ:
- Аномально низкий r_await (0.15-0.16 мс) при высокой утилизации диска - указывает на эффективное кэширование на уровне контроллера/ОС, но не снимает проблему очередей
- Высокий w_await (17-19 мс) - дисковая подсистема не справляется с синхронной записью
- Мелкие операции чтения (8К) - типичный паттерн случайного доступа к индексам/небольшим таблицам
- Операции записи по 22К - типичный размер 2-3 страниц PostgreSQL (8К каждая)
2.3. ВЛИЯНИЕ ПАРАМЕТРОВ POSTGRESQL НА IO-НАГРУЗКУ
Проблемные настройки:
- random_page_cost = 1.1
Текущее влияние: Планировщик считает случайные чтения почти бесплатными
Результат: Чрезмерное использование индексных сканов вместо последовательных
Связь с IO: Высокий r/s (9-12k операций чтения в секунду) - effective_io_concurrency = 300
Текущее влияние: PostgreSQL пытается выполнять 300 параллельных IO-операций
Результат: Перегрузка дисковой подсистемы, большие очереди (aqu_sz > 1.5)
Связь с IO: Высокая утилизация дисков (>80%) - shared_buffers = 251807MB (~246GB)
Текущее влияние: 25% от 1ТБ RAM выделено под shared buffers
Результат: Недостаток памяти для кэша ОС, что видно по высокой корреляции cache-r/s (0.8581)
Связь с IO: Кэш ОС неэффективен, много операций идут на диск - work_mem = 1GB
Текущее влияние: Каждая операция сортировки/хеширования может использовать до 1ГБ
Результат: Потенциальное использование дисковых временных файлов
Связь с IO: Может увеличивать нагрузку на запись - checkpoint_completion_target = 0.9
Текущее влияние: Контрольные точки растягиваются на 90% интервала
Результат: Более равномерная, но постоянная фоновая запись
Связь с IO: Поддерживает постоянную нагрузку на запись
Дополнительные факторы:
- max_wal_size = 8GB + checkpoint_timeout = 15min - частые контрольные точки
- wal_keep_size = 500GB - огромный объем WAL файлов на диске
- autovacuum_vacuum_scale_factor = 0.001 - очень агрессивный автовакуум
2.4. ОПТИМИЗАЦИИ
2.4.1. Настройки PostgreSQL для снижения IO (немедленно)
-- КРИТИЧЕСКИЕ ИЗМЕНЕНИЯ (1-й приоритет)
ALTER SYSTEM SET random_page_cost = 4.0; -- Более реалистично для перегруженных дисков
ALTER SYSTEM SET effective_io_concurrency = 32; -- Уменьшить в 10 раз
ALTER SYSTEM SET shared_buffers = '128GB'; -- Уменьшить с 246GB до 128GB
-- ВАЖНЫЕ ИЗМЕНЕНИЯ (2-й приоритет)
ALTER SYSTEM SET work_mem = '128MB'; -- Уменьшить с 1GB
ALTER SYSTEM SET checkpoint_completion_target = 0.7; -- Меньше растягивать контрольные точки
ALTER SYSTEM SET max_wal_size = '16GB'; -- Увеличить для реже контрольных точек
ALTER SYSTEM SET wal_keep_size = '64GB'; -- Уменьшить с 500GB
-- ДОПОЛНИТЕЛЬНЫЕ ОПТИМИЗАЦИИ
ALTER SYSTEM SET effective_cache_size = '600GB'; -- Более реалистично (из 1ТБ RAM)
ALTER SYSTEM SET min_wal_size = '4GB';
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05; -- Менее агрессивный
2.4.2. Рекомендации по файловой системе/монтированию
Для /data (vdh-vdk):
# В /etc/fstab добавить опции:
noatime,nodiratime,data=writeback,barrier=0,nobh
# ИЛИ для ext4:
noatime,nodiratime,data=writeback,barrier=0
Для /wal (vdg):
# WAL диск уже хорошо работает, можно добавить:
noatime,nodiratime
Настройки ядра Linux:
# В /etc/sysctl.conf
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.dirty_expire_centisecs = 3000
vm.dirty_writeback_centisecs = 500
vm.swappiness = 1
vm.vfs_cache_pressure = 50
2.4.3. Аппаратные улучшения
Краткосрочные (1-3 месяца):
- Добавить диски в RAID массив /data - увеличить IOPS и пропускную способность
- Рассчитать текущие требования:
Текущий IOPS: ~12,000 r/s + ~700 w/s = ~12,700 IOPS
Целевой IOPS с запасом: 20,000+ IOPS - Оптимизировать RAID конфигурацию:
Если RAID 10: добавить 2-4 диска
Если RAID 5/6: рассмотреть переход на RAID 10
Среднесрочные (3-6 месяцев):
- Переход на NVMe диски для /data
Samsung PM9A3, Intel P5510 или аналоги
IOPS: 500,000-1,000,000 (увеличение в 40-80 раз) - Выделить отдельный диск для temp_tablespaces
- Рассмотреть RAM диски для частоиспользуемых индексов
Долгосрочные (6-12 месяцев):
- Внедрить кэширующий контроллер с SSD/NVMe кэшем
- Рассмотреть распределенное хранилище (Ceph, GlusterFS)
- Архитектурные изменения - шардинг, partitioning
2.4.4. Оптимизация запросов (на основе Pareto-анализа)
Топ-3 проблемных запроса по IO:
- QUERYID: -5038981907002478858
CALLS: 103,910,315
WAITINGS: 3,214 (2.37%)
Оптимизация: Индексирование, переписывание запроса - QUERYID: -4280293605113329019
CALLS: 32,134,168
WAITINGS: 3,141 (2.32%)
Оптимизация: Кэширование результатов, материализованные представления - QUERYID: 940651181422038149
CALLS: 1,938,049
WAITINGS: 3,805 (2.8%)
Оптимизация: Оптимизация JOIN, покрывающие индексы
2.4.5. План внедрения изменений
Фаза 1 (Неделя 1):
- Изменить параметры PostgreSQL: random_page_cost, effective_io_concurrency
- Настроить параметры ядра Linux
- Мониторинг изменений
Фаза 2 (Неделя 2):
- Оптимизировать топ-10 проблемных запросов
- Настроить файловую систему
- Уменьшить shared_buffers до 128GB
Фаза 3 (Неделя 3-4):
- Внедрить аппаратные улучшения (RAID расширение)
- Настроить мониторинг ключевых метрик
- Регулярный анализ производительности
Ключевые метрики для мониторинга после изменений:
- Утилизация дисков: цель <70%
- Длина очереди: цель <1
- Время отклика записи: цель <5 мс
- IOPS: снижение на 30-50%
3. Анализ блокировок и contention (LWLock, Lock)
3.1. ТИПЫ БЛОКИРОВОК С НАИБОЛЬШИМ ВЛИЯНИЕМ
LWLock (Lightweight Locks) - КРИТИЧЕСКАЯ ПРОБЛЕМА
Корреляция WAITINGS-LWLOCK = 0.9215 (самая высокая из всех типов ожиданий)
Влияние на SPEED: Высокая конкуренция за внутренние структуры данных PostgreSQL замедляет все операции.
Lock (Обычные блокировки) - СЕРЬЕЗНАЯ ПРОБЛЕМА
Корреляция WAITINGS-LOCK = 0.7546
Влияние на SPEED: Конкуренция за объекты БД приводит к очередям и ожиданиям.
3.2. ПРОБЛЕМНЫЕ QUERYID
Топ-10 запросов по суммарным ожиданиям блокировок:
Анализ соотношения CALLS/ROWS к WAITINGS:
- QUERYID -5038981907002478858:
CALLS: 103,910,315 (103 млн!)
ROWS: 103,910,315
WAITINGS: 10,878
Соотношение: 9,551 вызовов на 1 ожидание
Проблема: Экстремально высокая частота вызовов создает постоянную конкуренцию - QUERYID -4280293605113329019:
CALLS: 32,134,168 (32 млн)
ROWS: 32,134,168
WAITINGS: 10,639
Соотношение: 3,020 вызовов на 1 ожидание
Проблема: Высокая частота, возможно, тривиальный запрос вызывается слишком часто - QUERYID 940651181422038149:
CALLS: 1,938,049
ROWS: 1,938,049
WAITINGS: 12,723
Соотношение: 152 вызовов на 1 ожидание
Проблема: Высокие ожидания на относительно небольшое количество вызовов
Классификация проблемных запросов:
3.3. КОНФИГУРАЦИОННЫЕ ФАКТОРЫ
Проблемные настройки и их влияние:
- max_parallel_workers = 20 при max_parallel_workers_per_gather = 0
Проблема: Параллельные запросы отключены, но воркеры выделены
Влияние: Ресурсы простаивают, тяжелые запросы выполняются последовательно
Влияние на блокировки: Длительные запросы дольше удерживают блокировки
Рекомендация: Включить параллелизм для тяжелых запросов - maintenance_work_mem = 16GB
Проблема: Очень высокое значение для операций обслуживания
Влияние на блокировки: VACUUM и CREATE INDEX могут блокировать объекты надолго
Рекомендация: Уменьшить до 1-2GB - autovacuum_max_workers = 4
Проблема: При высокой нагрузке UPDATE/DELETE может быть недостаточно
Влияние на блокировки: Накопление мертвых строк → длительные VACUUM → блокировки
Рекомендация: Увеличить до 6-8, но с осторожностью - wal_keep_size = 500GB
Проблема: Чрезмерно большой объем WAL файлов
Влияние на блокировки: Не напрямую, но может влиять на операции архивации
Рекомендация: Уменьшить до разумного значения (64-128GB)
Дополнительные проблемные настройки:
- idle_in_transaction_session_timeout = 1h - слишком долго, транзакции могут висеть
- statement_timeout = 64h - экстремально высокое значение, запросы могут блокировать надолго
- commit_delay = 1000 - высокое значение может увеличивать время удержания блокировок
3.4. РЕКОМЕНДАЦИИ
3.4.1. Оптимизация запросов (Высокий приоритет)
Для топ-5 проблемных QUERYID:
- QUERYID -5038981907002478858 (103M вызовов):-- Внедрить кэширование на уровне приложения
-- Рассмотреть материализованные представления
-- Объединить с другими запросами - QUERYID 940651181422038149 (высокие ожидания):-- Добавить индексы для уменьшения времени блокировок
-- Разбить на более мелкие транзакции
-- Использовать SELECT ... FOR UPDATE SKIP LOCKED - Оптимизация шаблонов доступа:
Внедрить row-level lock timeouts:sqlSET lock_timeout = '5s';
Использовать оптимистичные блокировки вместо пессимистичных
3.4.2. Настройки PostgreSQL для снижения contention
-- НЕМЕДЛЕННЫЕ ИЗМЕНЕНИЯ:
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET deadlock_timeout = '500ms';
ALTER SYSTEM SET lock_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '10min';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
-- ДОПОЛНИТЕЛЬНЫЕ ОПТИМИЗАЦИИ:
ALTER SYSTEM SET max_locks_per_transaction = 256; -- Если много объектов
ALTER SYSTEM SET max_pred_locks_per_transaction = 256;
ALTER SYSTEM SET commit_delay = 10;
ALTER SYSTEM SET wal_keep_size = '64GB';
3.4.3. Мониторинг проблемных объектов БД
Скрипты для мониторинга:
- Поиск таблиц с высоким contention:
sql
SELECT
relname,
n_tup_upd + n_tup_del + n_tup_ins as total_changes,
n_dead_tup,
autovacuum_count,
last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;
- Мониторинг блокировок в реальном времени:
sql
SELECT
blocked_locks.pid AS blocked_pid,
blocking_locks.pid AS blocking_pid,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query,
blocked_activity.wait_event_type,
blocked_activity.wait_event,
age(now(), blocked_activity.query_start) AS blocked_duration,
age(now(), blocking_activity.query_start) AS blocking_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
- Топ объектов по блокировкам:
SELECT
relation::regclass AS table_name,
mode,
COUNT(*) AS lock_count,
MAX(age(now(), pg_stat_activity.query_start)) AS max_query_age
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE relation IS NOT NULL
AND NOT granted
GROUP BY relation, mode
ORDER BY lock_count DESC
LIMIT 10;
3.4.4. Архитектурные изменения
- Шардинг горячих таблиц - разделить нагрузку
- Оптимистичная модель данных - использовать версионирование вместо блокировок
- Очереди сообщений - для обработки фоновых задач
- Read Replicas - разделить нагрузку чтения/записи
3.4.5. План внедрения
Фаза 1 (День 1-2):
- Настроить мониторинг блокировок
- Изменить lock_timeout, statement_timeout
- Внедрить кэширование для высокочастотных запросов
Фаза 2 (День 3-7):
- Оптимизировать топ-3 проблемных запроса
- Настроить автовакуум: autovacuum_max_workers = 6
- Уменьшить maintenance_work_mem = 2GB
Фаза 3 (Неделя 2):
- Включить параллелизм: max_parallel_workers_per_gather = 2
- Настроить индексы для проблемных таблиц
- Внедрить скрипты мониторинга
Фаза 4 (Неделя 3-4):
- Архитектурные изменения (если необходимо)
- Настройка реплик для чтения
- Регулярный аудит производительности
3.4.6. Ключевые метрики для отслеживания
- LWLock contention: цель < 0.7 корреляции
- Количество deadlocks в сутки: цель < 10
- Среднее время ожидания блокировок: цель < 100мс
- Количество активных блокировок: цель < 50
- Эффективность автовакуума: доля мертвых строк < 10%
ВАЖНО: Изменения вносить постепенно, начиная с наименее рискованных (таймауты, мониторинг), затем переходить к оптимизации запросов и настройкам.
4. Анализ использования памяти и IPC-проблем
4.1. ИСПОЛЬЗОВАНИЕ RAM
Критическая проблема: свободная память <5%
- Текущее состояние: 13,572-13,914 MB свободно из 1,031,766 MB (1.3-1.35%)
- ALARM: 100% наблюдений показывают <5% свободной памяти
- Проблема: Система работает на пределе, любое увеличение нагрузки может вызвать OOM или свопинг
Распределение памяти (в среднем):
Корреляция с IO-операциями:
Для дисков /data (vdh-vdk):
- buff - r/s: 0.8775-0.8788 (ОЧЕНЬ ВЫСОКАЯ)
Интерпретация: Увеличение буферов не снижает операций чтения
Проблема: Буферы ОС неэффективны для снижения IO - buff - rMB/s: 0.8879-0.8886 (ОЧЕНЬ ВЫСОКАЯ)
Интерпретация: Объем чтения сильно коррелирует с буферами
Проблема: Данные не кэшируются эффективно - cache - r/s: -0.6642 до -0.6621 (ОТРИЦАТЕЛЬНАЯ)
Интерпретация: Кэш ОС фактически снижает операции чтения
Положительно: Кэш работает как должен - cache - w/s: -0.3070 до -0.2714 (СЛАБО ОТРИЦАТЕЛЬНАЯ)
Интерпретация: Кэш слабо влияет на операции записи
Вывод: Кэш ОС (707GB) работает правильно (отрицательная корреляция с чтением), но буферы ОС (1.5GB) неэффективны. Основная проблема - неоптимальное распределение между shared_buffers PostgreSQL и кэшем ОС.
4.2. IPC-ПРОБЛЕМЫ
Основные типы ожиданий IPC:
Корреляция WAITINGS-IPC = 0.8181 (высокая)
QUERYID с наибольшими IPC-ожиданиями:
Соотношение CALLS/IPC WAIT:
- Наилучшее: 940651181422038149 - 854 вызовов на 1 IPC ожидание
- Наихудшее: -5038981907002478858 - 54,920 вызовов на 1 IPC ожидание (но CALLS=103M!)
4.3. КОНФИГУРАЦИЯ ПАМЯТИ POSTGRESQL
Оценка адекватности текущих настроек:
Риски из-за текущей конфигурации:
- Двойное кэширование: 246GB в shared_buffers + 707GB в кэше ОС = 953GB (92% RAM)
Эффект: Одна и та же данные хранятся в двух местах
Потеря: До 246GB памяти используются неэффективно - work_mem = 1GB:
Риск: Один сложный запрос с сортировкой может использовать 1GB
При 10 параллельных запросах: 10GB RAM
При 100 соединениях: потенциально 100GB RAM - Нехватка памяти для ОС:
Свободно: 13.5GB (1.3%)
Потребности ОС: Ядро, сетевые буферы, slab
Риск: При пиковой нагрузке - OOM killer или свопинг - Автовакуум конкуренция:
4 воркера × 2GB = 8GB максимально для автовакуума
Плюс 16GB для ручных операций обслуживания
Итого: До 24GB могут быть заняты фоновыми операциями
4.4. СИСТЕМНЫЕ НАСТРОЙКИ
Параметры ядра (отсутствуют в отчете, но критичны):
Текущее состояние (предположительно):
- vm.swappiness: вероятно 60 (default)
- vm.dirty_background_ratio: вероятно 10 (default)
- vm.dirty_ratio: вероятно 20 (default)
- vm.dirty_expire_centisecs: вероятно 3000 (30 seconds)
- vm.dirty_writeback_centisecs: вероятно 500 (5 seconds)
Проблемы с default настройками:
- swappiness=60: Слишком агрессивный свопинг при нехватке памяти
- dirty_background_ratio=10: 100GB "грязных" страниц перед фоновой записью
- dirty_ratio=20: 200GB "грязных" страниц перед принудительной синхронной записью
Настройки huge_pages = on
Преимущества:
- Уменьшает overhead на page tables
- Улучшает производительность TLB
- Экономит память
Проблемы:
- Требует предварительного выделения:# Для 246GB shared_buffers с размером страницы 2MB:
# 246GB / 2MB = 126,000 huge pages
# На практике нужно ~128,000 страниц - Недостаток памяти для ОС, если выделено неправильно
- Сложность управления при изменении shared_buffers
Текущая ситуация: Скорее всего, huge_pages настроены неправильно или не выделены, что может вызывать проблемы с запуском PostgreSQL.
4.5. РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ПАМЯТИ
4.5.1. Немедленные изменения (высокий приоритет)
A. Настройки PostgreSQL:
-- КРИТИЧЕСКИЕ ИЗМЕНЕНИЯ (немедленно)
ALTER SYSTEM SET shared_buffers = '64GB'; -- Уменьшить с 246GB (в 4 раза)
ALTER SYSTEM SET work_mem = '128MB'; -- Уменьшить с 1GB (в 8 раз)
ALTER SYSTEM SET maintenance_work_mem = '2GB'; -- Уменьшить с 16GB
ALTER SYSTEM SET autovacuum_work_mem = '512MB'; -- Уменьшить с 2GB
-- ДОПОЛНИТЕЛЬНЫЕ ОПТИМИЗАЦИИ
ALTER SYSTEM SET effective_cache_size = '700GB'; -- Отразить реальный кэш ОС
ALTER SYSTEM SET temp_buffers = '32MB'; -- Уменьшить с 64MB
B. Настройки ядра Linux (в /etc/sysctl.conf):
# КРИТИЧЕСКИЕ ПАРАМЕТРЫ
vm.swappiness = 1 # Минимизировать свопинг
vm.dirty_background_ratio = 5 # 50GB для фоновой записи (вместо 100GB)
vm.dirty_ratio = 10 # 100GB для принудительной записи (вместо 200GB)
vm.dirty_expire_centisecs = 3000 # 30 секунд (нормально)
vm.dirty_writeback_centisecs = 500 # 5 секунд (нормально)
# ОПТИМИЗАЦИЯ ПАМЯТИ
vm.overcommit_memory = 2 # Строгая политика overcommit
vm.overcommit_ratio = 90 # Разрешить overcommit 90% RAM
vm.vfs_cache_pressure = 50 # Меньше давления на кэш VFS
vm.min_free_kbytes = 1048576 # 1GB минимальной свободной памяти (0.1% от 1TB)
# HUGE PAGES (если используются)
vm.nr_hugepages = 32768 # 32,768 × 2MB = 64GB (для shared_buffers)
vm.hugetlb_shm_group = 999 # GID группы PostgreSQL
Применить настройки:
sysctl -p
4.5.2. Оптимизация IPC проблем (средний приоритет)
A. Настройка PostgreSQL для снижения IPC:
-- Уменьшить конкуренцию за буферы
ALTER SYSTEM SET bgwriter_delay = '200ms'; -- Увеличить с 10ms
ALTER SYSTEM SET bgwriter_lru_maxpages = 1000; -- Увеличить с 400
-- Оптимизация autovacuum для снижения ProcArray contention
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; -- Увеличить с 2ms
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000; -- Уменьшить с 5600
-- Улучшение управления транзакциями
ALTER SYSTEM SET max_prepared_transactions = 100; -- Если не используется, уменьшить
B. Оптимизация проблемных запросов:
-- Для QUERYID -5038981907002478858 (103M вызовов)
-- Внедрить кэширование на уровне приложения
-- Рассмотреть материализованные представления
-- Увеличить interval для частых обновлений
-- Для запросов с высокими IPC ожиданиями
SET max_parallel_workers_per_gather = 0; -- Отключить параллелизм для этих запросов
SET synchronous_commit = off; -- Для batch операций
4.5.3. Настройка Huge Pages (высокий приоритет)
Проверка текущего состояния:
# Проверить выделенные huge pages
grep Huge /proc/meminfo
# Проверить использование huge pages PostgreSQL
cat $PGDATA/postmaster.pid | head -1 | xargs grep -a 'Huge'
Настройка huge pages для 64GB shared_buffers:
# 1. Рассчитать необходимое количество страниц
# 64GB / 2MB = 32,768 страниц
# 2. Добавить в /etc/sysctl.conf
vm.nr_hugepages = 32768
# 3. Настроить группу для shared memory
sudo groupadd -g 999 postgres
sudo usermod -a -G postgres postgres
# 4. Установить лимиты памяти для пользователя postgres
echo "postgres soft memlock unlimited" >> /etc/security/limits.conf
echo "postgres hard memlock unlimited" >> /etc/security/limits.conf
# 5. Перезагрузить настройки
sysctl -p
4.5.4. Мониторинг и диагностика
Скрипты для мониторинга памяти:
- Общее использование памяти:
#!/bin/bash
echo "=== Memory Usage ==="
free -h
echo ""
echo "=== Huge Pages ==="
grep -E 'HugePages_Total|HugePages_Free|HugePages_Rsvd|HugePages_Surp' /proc/meminfo
echo ""
echo "=== PostgreSQL Memory ==="
ps aux | grep postgres | grep -v grep | awk '{sum += $6} END {print "Total RSS: " sum/1024 " MB"}'
- Мониторинг IPC в PostgreSQL:
-- IPC статистика
SELECT
datname,
count(*),
sum(backend_start) as total_backends,
sum(xact_start) as total_transactions
FROM pg_stat_activity
GROUP BY datname;
-- Buffer cache hit ratio
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as buffer_hit_ratio
FROM pg_statio_user_tables;
- Оповещения при проблемах с памятью:
# В crontab
*/5 * * * * /usr/bin/free | grep Mem | awk '{if ($7/$2 * 100 < 5) print "WARNING: Free memory less than 5%"}' | mail -s "Memory Alert" admin@example.com
4.5.5. План внедрения
Фаза 1 (День 1):
- Изменить настройки ядра: vm.swappiness=1, vm.dirty_*
- Уменьшить work_mem до 128MB
- Настроить мониторинг памяти
Фаза 2 (День 2):
- Уменьшить shared_buffers до 64GB
- Настроить huge pages для нового размера
- Перезапустить PostgreSQL с новыми настройками
Фаза 3 (День 3-5):
- Оптимизировать топ-3 запроса по IPC
- Настроить автовакуум параметры
- Внедрить скрипты мониторинга
Фаза 4 (День 6-7):
- Анализ эффективности изменений
- Дополнительная тонкая настройка
- Документирование конфигурации
4.5.6. Ключевые метрики для отслеживания
- Свободная память: Цель >5% (50GB+)
- Buffer cache hit ratio: Цель >99%
- IPC ожидания: Цель снижение на 30-50%
- Время отклика запросов: Цель улучшение на 20%
- Отсутствие свопинга: swap_si/swap_so = 0
4.5.7. Долгосрочные рекомендации
- Аппаратное улучшение:
Увеличить RAM до 1.5-2TB для лучшего кэширования
Рассмотреть NVMe диски для снижения потребности в кэше - Архитектурные изменения:
Внедрить read replicas для распределения нагрузки
Рассмотреть partitioning для больших таблиц
Внедрить connection pooling (pgbouncer) - Проактивный мониторинг:
Внедрить Prometheus + Grafana для мониторинга памяти
Настроить автоматические алерты при проблемах
Регулярный анализ тенденций использования памяти
ВАЖНО: Все изменения вносить постепенно, с обязательным мониторингом после каждого шага. Начинать с наименее рискованных изменений (настройки ядра), затем переходить к настройкам PostgreSQL, и только потом к оптимизации запросов.
5. Анализ запросов (Парето-анализ)
5.1. ТОП-10 QUERYID ПО ТИПАМ ОЖИДАНИЙ
5.1.1. IO-bound запросы (нагрузка на диск)
Общий паттерн IO: Все запросы имеют одинаковые WAIT_EVENT:
- DataFileRead - чтение данных с диска
- WALWrite - запись в WAL
- DataFileWrite - запись данных на диск
- SLRURead/SLRUWrite - доступ к SLRU кэшам (subtransaction, multixact)
5.1.2. IPC-bound запросы (межпроцессное взаимодействие)
Общий паттерн IPC:
- BufferIO - операции ввода/вывода в буферный кэш
- ProcArrayGroupUpdate - обновление массива процессов (высокая конкурентность)
5.1.3. LWLock-bound запросы (внутренние блокировки)
Общий паттерн LWLock:
- BufferMapping - конкуренция за хэш-таблицу буферов (самая частая проблема)
- LockManager - конкуренция за менеджер блокировок
- WALInsert - конкуренция за вставку в WAL
5.1.4. Lock-bound запросы (блокировки объектов)
Общий паттерн Lock:
- transactionid - блокировки по ID транзакции (взаимоблокировки)
- extend - блокировки расширения файлов (активный рост таблиц)
- tuple - блокировки строк (конкуренция за обновление одних строк)
5.2. ХАРАКТЕРИСТИКИ ПРОБЛЕМНЫХ ЗАПРОСОВ
5.2.1. Топ-5 запросов по суммарным ожиданиям
5.2.2. Запросы с экстремальными характеристиками
По CALLS (частота выполнения):
- -5038981907002478858: 103,910,315 вызовов (103 млн!)
- -4280293605113329019: 32,134,168 вызовов (32 млн)
- -1757223094415174739: 10,514,634 вызовов (10 млн)
По WAITINGS на вызов (интенсивность ожиданий):
- 940651181422038149: 0.00656 ожиданий/вызов
- 6300411387403852926: 0.00653 ожиданий/вызов
- -1738298181192624679: 0.00642 ожиданий/вызов
По комплексности проблем (присутствие во всех списках):
Все топ-10 запросов из IO списка присутствуют во всех четырех списках, что указывает на системные проблемы, а не на проблемы отдельных запросов.
5.3. КЛАССИФИКАЦИЯ ПРОБЛЕМ
5.3.1. Категория A: Запросы с экстремально высокой частотой вызовов
Рекомендация: Кэширование на уровне приложения, объединение запросов, материализованные представления.
5.3.2. Категория B: Запросы с высокими ожиданиями на вызов
Рекомендация: Оптимизация запросов, индексирование, уменьшение времени транзакций.
5.3.3. Категория C: Запросы с комплексными проблемами
Рекомендация: Системная оптимизация (параметры БД, инфраструктура).
5.3.4. Категория D: Запросы с потенциальными проблемами производительности
Рекомендация: Проверить необходимость такого количества вызовов, возможно объединение.
5.4. РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ
5.4.1. Индексирование (Высокий приоритет)
Для высокочастотных запросов:
-- 1. Для -5038981907002478858 (103M вызовов)
-- Предполагая, что это поиск по ключу:
CREATE INDEX CONCURRENTLY idx_query_5038
ON your_table (search_column)
INCLUDE (returned_columns);
-- 2. Для -4280293605113329019 (32M вызовов)
-- Возможно, нужен покрывающий индекс:
CREATE INDEX CONCURRENTLY idx_query_4280
ON your_table (col1, col2, col3)
WHERE condition_column = constant;
-- 3. Для запросов с высокими ожиданиями Lock (extend)
-- Индексы для уменьшения full table scans:
CREATE INDEX CONCURRENTLY idx_reduce_scans
ON large_table (frequently_filtered_column);
Рекомендации по индексам:
- Использовать CREATE INDEX CONCURRENTLY чтобы не блокировать запись
- Рассмотреть BRIN индексы для временных рядов
- Регулярно выполнять REINDEX CONCURRENTLY для борьбы с bloat
- Использовать pg_stat_all_indexes для мониторинга эффективности
5.4.2. Переписывание запросов (Средний приоритет)
Для -5038981907002478858 (103M вызовов):
-- Текущий (предположительно):
SELECT * FROM table WHERE id = $1;
-- Оптимизация 1: Кэширование на уровне приложения
-- Внедрить Redis/Memcached для кэширования результатов
-- Оптимизация 2: Пакетная обработка
-- Вместо 103M отдельных запросов:
SELECT * FROM table WHERE id IN ($1, $2, ..., $1000);
-- Оптимизация 3: Материализованное представление
CREATE MATERIALIZED VIEW mv_cache AS
SELECT id, data, updated_at
FROM table
WHERE some_condition;
-- Обновление по расписанию
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_cache;
Для запросов с высокими ожиданиями LWLock:
-- Текущий (с проблемами BufferMapping):
UPDATE table SET col = val WHERE id = $1;
-- Оптимизация: Уменьшение конкурентных обновлений
-- Использовать очередь сообщений для сериализации
-- Или использовать advisory locks:
BEGIN;
SELECT pg_advisory_xact_lock(id) FROM table WHERE id = $1;
UPDATE table SET col = val WHERE id = $1;
COMMIT;
5.4.3. Изменение паттернов доступа (Высокий приоритет)
1. Connection Pooling:
yaml
# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb pool_size=100
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 100
2. Read/Write разделение:
-- Настройка hot_standby_feedback
ALTER SYSTEM SET hot_standby_feedback = on;
-- Направление read-only запросов на реплики
-- В приложении:
-- writes -> primary
-- reads -> replicas
3. Batch processing для высокочастотных операций:
python
# Вместо:
for item in items:
db.execute("INSERT INTO table VALUES (%s)", item)
# Использовать:
from psycopg2.extras import execute_batch
execute_batch(cur, "INSERT INTO table VALUES (%s)", items, page_size=1000)
5.4.4. Настройки параметров на уровне запросов
Для проблемных запросов:
-- 1. Уменьшение lock_timeout для избежания длительных блокировок
SET LOCAL lock_timeout = '2s';
-- 2. Увеличение work_mem для сортировок/хеширования (осторожно!)
SET LOCAL work_mem = '256MB';
-- 3. Отключение параллелизма для коротких запросов
SET LOCAL max_parallel_workers_per_gather = 0;
-- 4. Изменение параметров планировщика
SET LOCAL random_page_cost = 1.5;
SET LOCAL effective_cache_size = '700GB';
-- 5. Управление WAL для batch операций
SET LOCAL synchronous_commit = off;
SET LOCAL wal_level = minimal; -- для временных данных
Шаблоны для разных типов запросов:
-- Для высокочастотных read-only запросов:
BEGIN;
SET LOCAL statement_timeout = '1s';
SET LOCAL lock_timeout = '100ms';
SELECT * FROM table WHERE id = $1;
COMMIT;
-- Для update интенсивных операций:
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL idle_in_transaction_session_timeout = '30s';
UPDATE table SET col = val WHERE id = $1;
COMMIT;
-- Для batch операций:
BEGIN;
SET LOCAL synchronous_commit = off;
SET LOCAL maintenance_work_mem = '2GB';
-- batch operations
COMMIT;
5.4.5. План оптимизации по приоритетам
Неделя 1: Быстрые победы
- Внедрить connection pooling (pgbouncer)
- Добавить покрывающие индексы для топ-3 высокочастотных запросов
- Настроить statement_timeout = '30s' на уровне БД
Неделя 2: Оптимизация запросов
- Переписать -5038981907002478858 с использованием кэширования
- Внедрить batch processing для INSERT/UPDATE операций
- Оптимизировать JOIN в 940651181422038149
Неделя 3: Архитектурные изменения
- Внедрить read replicas для распределения нагрузки
- Настроить partitioning для горячих таблиц
- Внедрить очередь сообщений для фоновой обработки
Неделя 4: Мониторинг и тонкая настройка
- Настроить автоматический сбор статистики
- Внедрить alerting при проблемах с запросами
- Регулярный ревью и оптимизация запросов
5.4.6. Ключевые метрики для отслеживания
Резюме:
Проблемы носят системный характер - почти все топ-запросы страдают от всех типов ожиданий. Это указывает на необходимость комплексной оптимизации: начиная с инфраструктуры (диски, память), через настройки PostgreSQL, и заканчивая оптимизацией конкретных запросов и архитектурными изменениями.
6. Комплексные рекомендации и план действий
6.1. КРАТКОСРОЧНЫЕ МЕРЫ (1-2 дня)
6.1.1. Критические изменения конфигурации PostgreSQL
Приоритет 1: Оптимизация дисковой подсистемы (даст эффект сразу)
-- Изменения в postgresql.auto.conf
ALTER SYSTEM SET random_page_cost = 4.0; -- Было 1.1 (реалистично для перегруженных дисков)
ALTER SYSTEM SET effective_io_concurrency = 32; -- Было 300 (уменьшить в 10 раз)
ALTER SYSTEM SET effective_cache_size = '700GB'; -- Отразить реальный кэш ОС
Приоритет 2: Снижение нагрузки на память
ALTER SYSTEM SET work_mem = '128MB'; -- Было 1GB (уменьшить в 8 раз)
ALTER SYSTEM SET lock_timeout = '30s'; -- Предотвратить вечные блокировки
ALTER SYSTEM SET statement_timeout = '10min'; -- Было 64h (разумный лимит)
Приоритет 3: Настройка мониторинга
-- Включить дополнительные метрики
ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET track_activities = on;
ALTER SYSTEM SET track_counts = on;
6.1.2. Оптимизация самых проблемных запросов
Немедленные действия:
- Для QUERYID -5038981907002478858 (103M вызовов):-- Внедрить временный кэш через материализованное представление
CREATE MATERIALIZED VIEW IF NOT EXISTS cache_5038 AS
SELECT * FROM source_table WHERE conditions;
-- Обновлять каждые 5 минут
REFRESH MATERIALIZED VIEW CONCURRENTLY cache_5038; - Для запросов с высокими LWLock ожиданиями:sql-- Добавить индекс для уменьшения времени сканирования
CREATE INDEX CONCURRENTLY idx_urgent ON problem_table(problem_column); - Внедрить ограничения на уровне приложения:
Ограничить максимальное количество одновременных запросов
Внедрить circuit breaker для проблемных эндпоинтов
6.1.3. Мониторинг ключевых метрик
Дашборд для первых 48 часов:
# Скрипт для мониторинга в реальном времени
#!/bin/bash
watch -n 5 "
echo '=== DISK IO ==='
iostat -dx /dev/vdh /dev/vdi /dev/vdj /dev/vdk 2 1 | tail -n +7
echo ''
echo '=== MEMORY ==='
free -h
echo ''
echo '=== POSTGRESQL WAITS ==='
psql -c \"SELECT wait_event_type, count(*) FROM pg_stat_activity WHERE wait_event_type IS NOT NULL GROUP BY 1 ORDER BY 2 DESC;\"
echo ''
echo '=== TOP QUERIES ==='
psql -c \"SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;\"
"
Ключевые метрики (целевые значения):
- Дисковая утилизация: < 70% (сейчас 78-86%)
- Очередь дисков: < 1 (сейчас 1.6-2.0)
- Свободная память: > 5% (сейчас 1.3%)
- LWLock ожидания: < 100/сек (сейчас ~500/сек)
6.2. СРЕДНЕСРОЧНЫЕ МЕРЫ (1-2 недели)
6.2.1. Оптимизация схемы БД и индексов
Неделя 1: Анализ и планирование
-- 1. Анализ индексов
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
idx_scan as scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- Неиспользуемые индексы
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
-- 2. Анализ bloat
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) as dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;
Неделя 2: Реализация оптимизаций
- Удаление неиспользуемых индексов (экономия места, ускорение записи)
- Создание покрывающих индексов для топ-10 проблемных запросов
- Оптимизация partitioning для больших таблиц
- Настройка fillfactor для таблиц с частыми обновлениями
6.2.2. Настройка Vacuum/Analyze стратегии
-- Для таблиц с высокой активностью UPDATE/DELETE
ALTER TABLE problem_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_analyze_threshold = 1000,
autovacuum_vacuum_cost_limit = 2000,
autovacuum_vacuum_cost_delay = '10ms'
);
-- Ежедневный мониторинг
CREATE TABLE vacuum_monitor AS
SELECT
schemaname,
tablename,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
n_dead_tup
FROM pg_stat_user_tables;
6.2.3. Анализ и переработка проблемных запросов
Методология:
- Неделя 1: Топ-5 запросов по WAITINGS
- Неделя 2: Топ-10 запросов по общему времени выполнения
Инструменты:
-- Подробный анализ запроса
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, WAL)
SELECT * FROM problem_table WHERE conditions;
-- Поиск альтернативных планов выполнения
SET enable_hashjoin = off;
EXPLAIN SELECT * FROM problem_table WHERE conditions;
SET enable_hashjoin = on;
SET enable_mergejoin = off;
EXPLAIN SELECT * FROM problem_table WHERE conditions;
SET enable_mergejoin = on;
6.2.4. Настройка файловой системы и параметров ОС
Оптимизация /etc/sysctl.conf:
# Оптимизация памяти
vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.dirty_expire_centisecs = 3000
vm.dirty_writeback_centisecs = 500
vm.vfs_cache_pressure = 50
vm.min_free_kbytes = 1048576 # 1GB
# Оптимизация сети (для распределенных систем)
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728
net.ipv4.tcp_rmem = 4096 87380 134217728
net.ipv4.tcp_wmem = 4096 65536 134217728
Оптимизация монтирования дисков:
bash
# В /etc/fstab для /data
UUID=xxxx /data ext4 defaults,noatime,nodiratime,data=writeback,barrier=0 0 0
# Для /wal
UUID=yyyy /wal ext4 defaults,noatime,nodiratime 0 0
6.3. ДОЛГОСРОЧНЫЕ МЕРЫ (1-3 месяца)
6.3.1. Аппаратные улучшения
Месяц 1: Диагностика и планирование
- Анализ текущей производительности дисков:bashfio --filename=/dev/vdh --rw=randread --ioengine=libaio --direct=1 \
--name=test --bs=8k --iodepth=64 --runtime=60 --numjobs=4 \
--group_reporting --output=fio_results.txt - Расчет требований:
Текущий IOPS: ~12,700
Целевой IOPS с запасом: 20,000+
Рекомендуемое решение: NVMe диски или расширение RAID массива
Текущие расширения:
- Вариант A (если бюджет ограничен):
Добавить 4 диска в RAID 10 массив /data
Общий размер: +14TB, IOPS: +8,000 - Вариант B (оптимальный):
Переход на NVMe диски Samsung PM9A3
IOPS: 500,000+ (увеличение в 40 раз)
Задержка: < 100µs (улучшение в 100 раз)
Месяц 3: Память и процессор
- Увеличение RAM до 1.5TB для лучшего кэширования
- Оптимизация NUMA для 4-х сокетной системы
6.3.2. Архитектурные изменения
Микроархитектурные улучшения:
- Внедрение read replicas для распределения нагрузки yaml# Конфигурация реплики
primary_conninfo = 'host=primary port=5432 user=replicator'
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s - Шардинг больших таблиц:sql-- По диапазону дат
CREATE TABLE measurements_2024_01 PARTITION OF measurements
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- По хэшу
CREATE TABLE users_0 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 0); - Внедрение кэширующего слоя:python# Redis кэш для высокочастотных запросов
import redis
import pickle
def cached_query(query_id, params):
cache_key = f"query:{query_id}:{hash(params)}"
cached = redis.get(cache_key)
if cached:
return pickle.loads(cached)
result = execute_query(query_id, params)
redis.setex(cache_key, 300, pickle.dumps(result)) # 5 минут
return result
6.3.3. Внедрение расширенных инструментов мониторинга
Стек мониторинга:
- Prometheus + Grafana для сбора и визуализации метрик
- pg_stat_monitor для расширенной статистики запросов
- pg_qualstats для анализа условий WHERE
- pg_wait_sampling для детального анализа ожиданий
Кастомные дашборды:
- Disk Performance Dashboard
- Query Performance Trends
- Lock Contention Monitor
- Memory Usage Analytics
Автоматические алерты:
yaml
# Alertmanager конфигурация
groups:
- name: postgresql_alerts
rules:
- alert: HighDiskUtilization
expr: disk_utilization > 80
for: 5m
annotations:
description: 'Disk {{ $labels.device }} is {{ $value }}% utilized'
- alert: HighLockContention
expr: rate(pg_lock_wait_seconds_total[5m]) > 10
for: 2m
annotations:
description: 'High lock contention detected'
6.4. ПРИОРИТИЗАЦИЯ
6.4.1. Наибольший эффект при минимальных изменениях
Топ-5 low-hanging fruits:
- random_page_cost = 4.0 - немедленное улучшение планов запросов
- effective_io_concurrency = 32 - снижение очередей на дисках
- Внедрение pgbouncer - уменьшение нагрузки на соединения
- Индекс для -5038981907002478858 - самый частый запрос
- Настройка vm.swappiness = 1 - предотвращение свопинга
Ожидаемый эффект: 30-50% улучшение производительности
6.4.2. Наиболее рискованные изменения
Высокий риск:
- Изменение shared_buffers - может вызвать OOM или деградацию
- Изменение схемы partitioning - требует остановки приложения
- Миграция на новые диски - риск потери данных
Стратегия митигации:
- Тестирование на staging окружении
- Постепенный rollout (canary deployment)
- Подготовленные планы отката
6.4.3. Измерение эффекта от каждой оптимизации
Метрика эффективности:
sql
-- Таблица для отслеживания изменений
CREATE TABLE optimization_metrics (
id serial PRIMARY KEY,
change_name text,
change_date timestamp DEFAULT now(),
metric_name text,
before_value numeric,
after_value numeric,
improvement_percent numeric
);
-- Пример заполнения
INSERT INTO optimization_metrics (change_name, metric_name, before_value, after_value)
VALUES
('random_page_cost change', 'avg_query_time', 150.5, 120.3),
('effective_io_concurrency change', 'disk_queue_length', 1.8, 1.2);
KPI для измерения успеха:
- Disk utilization: < 70% (сейчас 85%)
- Query response time (p95): < 100ms (улучшение на 50%)
- Throughput: увеличение на 30%
- Error rate: снижение на 90%
6.5. МОНИТОРИНГ ПОСЛЕ ИЗМЕНЕНИЙ
6.5.1. Ключевые метрики для отслеживания
Обязательные метрики (каждые 5 минут):
sql
-- 1. Производительность дисков
SELECT device, util, aqu_sz, await
FROM iostat_metrics
WHERE device IN ('vdh', 'vdi', 'vdj', 'vdk');
-- 2. Ожидания PostgreSQL
SELECT wait_event_type, count(*),
round(100.0 * count(*) / sum(count(*)) OVER (), 2) as pct
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL
GROUP BY wait_event_type;
-- 3. Производительность запросов
SELECT queryid, calls, mean_time,
(mean_time * calls) as total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
Расширенные метрики (каждый час):
- Buffer cache hit ratio
- Index usage statistics
- Lock contention by object
- WAL generation rate
6.5.2. Пороговые значения для alerting
Критические алерты (PagerDuty/SMS):
- Disk utilization > 90% более 5 минут
- No free memory (OOM imminent)
- Database not accepting connections
- Replication lag > 5 minutes
Предупреждения (Email/Slack):
- Disk utilization > 80% более 15 минут
- Lock contention > 100 locks/minute
- Query timeout rate > 5%
- Connection count > 80% of max_connections
Информационные алерты (Dashboard only):
- Performance degradation > 20%
- Index bloat > 30%
- Autovacuum falling behind
6.5.3. План отката при проблемах
Процедура отката (Runbook):
## Экстренный откат изменений PostgreSQL
### Шаг 1: Оценка ситуации (первые 5 минут)
1. Проверить доступность БД
2. Собрать основные метрики
3. Определить scope проблемы
### Шаг 2: Быстрый откат (5-15 минут)
Если проблема в конфигурации:
```bash
# Восстановить backup конфигурации
cp /var/lib/pgsql/data/postgresql.conf.backup /var/lib/pgsql/data/postgresql.conf
# Перезапустить PostgreSQL
systemctl restart postgresql-14
# Проверить доступность
pg_isready -h localhost -p 5432
Шаг 3: Откат изменений схемы (15-60 минут)
sql
-- Для каждого изменения схемы иметь обратную миграцию
BEGIN;
-- Откат индекса
DROP INDEX CONCURRENTLY IF EXISTS problem_index;
-- Откат изменения таблицы
ALTER TABLE problem_table DROP COLUMN IF EXISTS new_column;
COMMIT;
Шаг 4: Коммуникация
- Уведомить команду разработки
- Обновить статус-страницу
- Запланировать пост-мортем
Шаг 5: Пост-мортем анализ
- Собрать все логи и метрики
- Провести root cause анализ
- Обновить runbook
- Создать задачи по предотвращению
text
**Checklist для безопасного деплоя:**
- [ ] Backup конфигурации
- [ ] Backup данных
- [ ] Проверка на staging
- [ ] Постепенный rollout
- [ ] Мониторинг после деплоя
- [ ] План отката готов
**Автоматизированные тесты после изменений:**
```bash
#!/bin/bash
# Health check script
pg_isready -h localhost -p 5432 || exit 1
psql -c "SELECT 1" || exit 1
psql -c "SELECT pg_is_in_recovery()" | grep -q f || exit 1
psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active'" || exit 1
echo "All checks passed"
ИТОГОВАЯ ДОРОЖНАЯ КАРТА
Неделя 1: Стабилизация
- Day 1-2: Конфигурационные изменения
- Day 3-4: Мониторинг и базовая оптимизация
- Day 5-7: Анализ результатов и корректировка
Неделя 2-3: Оптимизация
- Индексы и схемы
- Запросы и паттерны доступа
- Файловая система и ОС
Месяц 2: Архитектурные улучшения
- Read replicas
- Кэширование
- Connection pooling
Месяц 3: Аппаратные улучшения
- Дисковая подсистема
- Память
- Расширенный мониторинг
Критерии успеха через 3 месяца:
- Disk utilization: < 70% (улучшение с 85%)
- Query p95 latency: < 100ms (улучшение на 50%)
- System stability: 99.9% uptime
- Team productivity: 40% меньше времени на firefighting
Финансовое обоснование:
- Текущие потери: 2 часа downtime в месяц × $500/час = $1,000/мес
- Затраты на оптимизацию: $5,000 (время команды + инструменты)
- ROI: 5 месяцев (без учета улучшения производительности)
- Дополнительная выгода: Улучшение user experience, снижение рисков
Резюме: План начинается с быстрых, безопасных изменений, которые дадут немедленный эффект. Затем постепенно внедряются более сложные оптимизации, каждая из которых измеряется и валидируется. Ключевой принцип - итеративный подход с постоянным мониторингом и корректировкой.
7. Диагностика специфичных проблем конфигурации
7.1. НАСТРОЙКИ АВТОВАКУУМА
Текущие настройки:
- autovacuum_vacuum_scale_factor = 0.001
- autovacuum_analyze_scale_factor = 0.005
- autovacuum_vacuum_cost_delay = 2ms
- autovacuum_max_workers = 4
- autovacuum_work_mem = 2GB
Влияние на производительность:
Критическая проблема: Слишком агрессивный автовакуум
- autovacuum_vacuum_scale_factor = 0.001 (0.1%) - экстремально низкое значение
Проблема: Автовакуум запускается при изменении всего 0.1% строк таблицы
Пример: Для таблицы с 10 млн строк → вакуум после 10,000 изменений
Результат: Постоянные запуски автовакуума, конкуренция с пользовательскими запросами - autovacuum_analyze_scale_factor = 0.005 (0.5%) - очень низкое значение
Проблема: Частые обновления статистики
Результат: Дополнительная нагрузка на CPU и блокировки таблиц
Проблема с производительностью:
- autovacuum_vacuum_cost_delay = 2ms - слишком короткая задержка
Проблема: Автовакуум работает почти без пауз
Результат: Постоянная фоновая нагрузка на IO (видно по высокой записи на диск) - autovacuum_max_workers = 4 - может быть недостаточно
Проблема: При высокой нагрузке UPDATE/DELETE воркеры не успевают
Результат: Накопление мертвых строк (видно по bloat в статистике) - autovacuum_work_mem = 2GB - опасно высокое значение
Проблема: Каждый воркер может использовать до 2GB
Результат: При 4 воркерах → до 8GB памяти, что может вызвать OOM
Рекомендации:
-- Безопасные изменения (немедленно)
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05; -- 5% вместо 0.1%
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1; -- 10% вместо 0.5%
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; -- Увеличить в 5 раз
-- Оптимизация памяти и параллелизма
ALTER SYSTEM SET autovacuum_work_mem = '512MB'; -- Уменьшить в 4 раза
ALTER SYSTEM SET autovacuum_max_workers = 6; -- Увеличить на 50%
7.2. НАСТРОЙКИ КОНТРОЛЬНЫХ ТОЧЕК
Текущие настройки:
- checkpoint_timeout = 15min
- checkpoint_completion_target = 0.9
- max_wal_size = 8GB
- min_wal_size = 2GB
Связь с наблюдаемыми IO-проблемами:
Проблема 1: Слишком частые контрольные точки
- checkpoint_timeout = 15min - очень короткий интервал
Расчет WAL генерации: При записи 10MB/s → за 15 минут генерируется 9GB WAL
Проблема: max_wal_size = 8GB меньше, чем потенциальная генерация за 15 минут
Результат: Контрольные точки срабатывают по размеру, а не по времени
Проблема 2: Высокая фоновая запись
- checkpoint_completion_target = 0.9 - очень высокое значение
Механизм: PostgreSQL растягивает запись грязных страниц на 90% интервала между контрольными точками
Проблема: При 15-минутных интервалах → 13.5 минут постоянной фоновой записи
Результат: Постоянная нагрузка на диск (видно по steady-state записи на диски /data)
Проблема 3: Несбалансированные размеры WAL
- min_wal_size = 2GB, max_wal_size = 8GB
Проблема: Маленький разброс (всего 4x разница)
Результат: Частые переключения WAL сегментов и контрольные точки
Рекомендации:
-- Увеличить интервалы и объемы
ALTER SYSTEM SET checkpoint_timeout = '30min'; -- Удвоить интервал
ALTER SYSTEM SET max_wal_size = '32GB'; -- Увеличить в 4 раза
ALTER SYSTEM SET min_wal_size = '8GB'; -- Увеличить в 4 раза
-- Оптимизация фоновой записи
ALTER SYSTEM SET checkpoint_completion_target = 0.7; -- Уменьшить нагрузку
ALTER SYSTEM SET bgwriter_delay = '200ms'; -- Увеличить с 10ms
ALTER SYSTEM SET bgwriter_lru_maxpages = 1000; -- Увеличить с 400
7.3. ПАРАМЕТРЫ ПАРАЛЛЕЛИЗМА
Текущие настройки:
- max_parallel_workers = 20
- max_parallel_workers_per_gather = 0 ← ПРОБЛЕМА
- max_parallel_maintenance_workers = 20
Почему параллельные запросы отключены?
Анализ ситуации:
- max_parallel_workers_per_gather = 0 полностью отключает параллельное выполнение запросов
- При этом max_parallel_workers = 20 и max_parallel_maintenance_workers = 20 настроены
- Парадокс: Параллелизм доступен, но не используется для запросов
Возможные причины:
- Историческое решение: Возможно, в прошлом параллельные запросы вызывали проблемы
- Консервативная настройка: Боязнь contention при 192 ядрах
- Ошибка конфигурации: Несогласованность настроек
Проблемы из-за отключения параллелизма:
- Неэффективное использование CPU: 192 ядра, но тяжелые запросы выполняются одним процессом
- Длительные операции: Full table scans, большие JOIN выполняются последовательно
- Увеличение времени блокировок: Длительные запросы дольше удерживают блокировки
Рекомендации:
-- Постепенное включение параллелизма
ALTER SYSTEM SET max_parallel_workers_per_gather = 2; -- Начать с малого
ALTER SYSTEM SET max_parallel_workers = 32; -- 25% от 192 ядер
ALTER SYSTEM SET parallel_setup_cost = 10; -- Уменьшить threshold
ALTER SYSTEM SET parallel_tuple_cost = 0.001; -- Уменьшить стоимость
-- Для maintenance операций
ALTER SYSTEM SET max_parallel_maintenance_workers = 8; -- Уменьшить с 20
7.4. НАСТРОЙКИ ПАМЯТИ
Текущие настройки:
- work_mem = 1GB ← КРИТИЧЕСКАЯ ПРОБЛЕМА
- shared_buffers = 251807MB (~246GB)
- temp_buffers = 64MB
- maintenance_work_mem = 16GB
Риски и возможности оптимизации:
Критическая проблема: work_mem = 1GB
- Риск: Каждая операция сортировки/хеширования может использовать до 1GB
- Пример: 100 одновременных запросов с сортировкой → потенциально 100GB RAM
- Реальность: При наблюдаемой нагрузке это может вызвать OOM killer
Проблема: shared_buffers = 246GB (24% от 1ТБ RAM)
- Текущее влияние: Двойное кэширование (shared_buffers + OS cache)
- Эффективность: 707GB в кэше ОС показывает, что данные хорошо кэшируются
- Проблема: 246GB в shared_buffers могут быть избыточными
Проблема: maintenance_work_mem = 16GB
- Риск: Операции VACUUM, CREATE INDEX могут использовать много памяти
- Конкуренция: Может конфликтовать с autovacuum_work_mem = 2GB
Проблема: temp_buffers = 64MB
- Контекст: Используется для временных таблиц в сессии
- Риск: Может быть недостаточно для сложных запросов с временными таблицами
Рекомендации:
-- Безопасное уменьшение work_mem
ALTER SYSTEM SET work_mem = '128MB'; -- Уменьшить в 8 раз
ALTER SYSTEM SET hash_mem_multiplier = 2.0; -- Для hash операций
-- Оптимизация shared_buffers
ALTER SYSTEM SET shared_buffers = '128GB'; -- Уменьшить в 2 раза
ALTER SYSTEM SET effective_cache_size = '700GB'; -- Реальный кэш ОС
-- Настройка временной памяти
ALTER SYSTEM SET temp_buffers = '256MB'; -- Увеличить для сложных запросов
ALTER SYSTEM SET maintenance_work_mem = '4GB'; -- Уменьшить с 16GB
-- Для больших сортировок во временных файлах
ALTER SYSTEM SET temp_file_limit = '20GB';
7.5. РАСШИРЕНИЯ И МОДУЛИ
shared_preload_libraries = 'plpgsql, dblink, pgpro_scheduler, postgres_fdw,
uuid-ossp, pg_hint_plan, pg_wait_sampling, pgpro_stats'
Влияние на производительность:
1. pgpro_stats
- Назначение: Сбор расширенной статистики (Postgres Pro)
- Параметр: pgpro_stats.max = 20000
- Влияние:
Позитивное: Детальная статистика для анализа
Негативное: Дополнительная нагрузка на CPU и память
Проблема: Может вызывать дополнительные LWLock ожидания (видно в статистике)
2. pg_hint_plan
- Назначение: Подсказки для оптимизатора запросов
- Влияние:
Позитивное: Возможность фиксировать планы запросов
Негативное: Неправильные подсказки могут ухудшить производительность
Статистика: Использование подсказок не видно в отчете
3. pg_wait_sampling
- Назначение: Сбор статистики по ожиданиям
- Влияние:
Позитивное: Детальная диагностика ожиданий
Негативное: Дополнительная нагрузка (минимальная)
Важность: Критически важен для анализа текущих проблем
4. pgpro_scheduler
- Назначение: Планировщик задач Postgres Pro
- Влияние: Дополнительные фоновые процессы, может создавать contention
5. postgres_fdw
- Назначение: Foreign data wrappers
- Влияние: Если не используется - бесполезная нагрузка
Рекомендации:
-- 1. Оптимизация pgpro_stats
ALTER SYSTEM SET pgpro_stats.max = 5000; -- Уменьшить с 20000
-- 2. Проверить использование расширений
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;
-- 3. Отключить неиспользуемые расширения
-- Если postgres_fdw не используется:
-- ALTER SYSTEM SET shared_preload_libraries = 'plpgsql, dblink, pgpro_scheduler,
-- uuid-ossp, pg_hint_plan, pg_wait_sampling, pgpro_stats';
-- 4. Мониторинг влияния расширений
CREATE VIEW extension_impact AS
SELECT
name,
settings,
pg_size_pretty(pg_total_relation_size(oid)) as size
FROM pg_extension;
СИНТЕЗ ВЗАИМОСВЯЗЕЙ
Как настройки создают каскадные проблемы:
Цепочка проблем:
- Агрессивный автовакуум → Частые запуски → Конкуренция за IO и блокировки
- Частые контрольные точки → Постоянная фоновая запись → Высокая утилизация дисков
- Отключенный параллелизм → Длительные запросы → Увеличение времени блокировок
- Большой work_mem → Риск OOM → Потенциальный свопинг
- Избыточные shared_buffers → Двойное кэширование → Неэффективное использование памяти
Корреляция с наблюдаемыми метриками:
- Высокая запись на диск (w/s 600-735) ↔ Частые контрольные точки
- Высокие LWLock ожидания ↔ Конкуренция от автовакуума и расширений
- Длительные блокировки ↔ Отсутствие параллелизма для тяжелых запросов
Приоритет исправлений:
Высокий риск/высокая отдача (День 1):
-- 1. Исправить критический work_mem
ALTER SYSTEM SET work_mem = '128MB';
-- 2. Уменьшить агрессивность автовакуума
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms';
-- 3. Включить базовый параллелизм
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;
Средний риск (День 2-3):
-- 4. Оптимизировать контрольные точки
ALTER SYSTEM SET checkpoint_timeout = '30min';
ALTER SYSTEM SET max_wal_size = '32GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.7;
-- 5. Оптимизировать shared_buffers
ALTER SYSTEM SET shared_buffers = '128GB';
Низкий риск/тонкая настройка (Неделя 1):
-- 6. Оптимизировать расширения
ALTER SYSTEM SET pgpro_stats.max = 5000;
-- 7. Оптимизировать временную память
ALTER SYSTEM SET temp_buffers = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '4GB';
-- 8. Оптимизировать параллелизм
ALTER SYSTEM SET max_parallel_workers = 32;
ALTER SYSTEM SET parallel_setup_cost = 10;
План валидации изменений:
Для каждой группы настроек:
-- Мониторинг автовакуума
SELECT
schemaname,
relname,
last_autovacuum,
n_dead_tup,
autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 10;
-- Мониторинг контрольных точек
SELECT
checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint
FROM pg_stat_bgwriter;
-- Мониторинг параллелизма
SELECT
query,
plans,
total_plan_time,
executions,
workers_planned,
workers_launched
FROM pg_stat_statements
WHERE workers_planned > 0;
Метрики успеха:
- Снижение IO записи на 20-30%
- Уменьшение LWLock ожиданий на 30-40%
- Улучшение времени отклика запросов на 15-25%
- Стабильное использование памяти (< 90% от RAM)
Риски и митигация:
- Риск: Слишком резкое уменьшение work_mem → ошибки "out of memory"
Митигация: Постепенное уменьшение: 1GB → 512MB → 256MB → 128MB - Риск: Увеличение max_wal_size → больше места на диске
Митигация: Мониторинг использования WAL, наличие 2x свободного места - Риск: Включение параллелизма → увеличение нагрузки на CPU
Митигация: Начинать с max_parallel_workers_per_gather = 2, мониторить CPU usage
Заключение:
Настройки PostgreSQL явно неоптимальны и создают каскадные проблемы. Начинать нужно с наиболее критичных (work_mem, автовакуум), затем оптимизировать контрольные точки и память, и только потом включать параллелизм и тонкую настройку расширений.