PG_EXPECTO: Анализ производительности СУБД PostgreSQL - от диагностики до оптимизации инфраструктуры.

Больше скорости — выше нагрузка: как оптимизация СУБД раскрыла новые узкие места

В ходе исследования были проведены два эксперимента — базовый (Эксперимент 1) и оптимизированный (Эксперимент 8) — с целью оценки влияния настроек ввода-вывода на производительность СУБД. Анализ охватил метрики производительности, время ожиданий, корреляции, а также системные показатели (vmstat). Результаты выявили как значительные улучшения, так и новые вызовы, требующие дальнейшей оптимизации на уровне операционной системы.

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Глоссарий терминов | Postgres DBA | Дзен

Начальные настройки

Текущая оптимизация настроек инфраструктуры

Запрос

1.summary.txt - Эксперимент-1 (Базовые настройки IO)

8.summary.txt - Эксперимент-8 (Оптимизация настроек IO)

Подготовь сравнительные анализ производительности и ожиданий СУБД и метрик vmstat

Состав отчета :

**Сравнение метрик производительности и ожиданий СУБД**

**Сравнение и анализ корреляций и паттернов ожиданий СУБД**

**Сравнение проблемы производительности СУБД**

**Диагностика и сравнение узких мест СУБД**

**Сравнение метрик vmstat**

**Сравнение и анализ корреляций vmstat**

**Сравнение критических проблемы vmstat**

**Диагностика и сравнение узких мест vmstat**

**Итоговый сравнительный анализ по производительности СУБД и vmstat**

1. Сравнение метрик производительности и ожиданий СУБД

Производительность (SPEED):

• Эксперимент 1: 265 388 - 284 895 единиц
• Эксперимент 8: 433 242 - 473 502 единиц
• Улучшение: +63% (минимальное значение), +66% (максимальное значение)

Ожидания СУБД (WAITINGS):

• Эксперимент 1: 26 976 - 72 905 единиц
• Эксперимент 8: 22 990 - 68 097 единиц
• Улучшение: -15% (минимальное значение), -6.6% (максимальное значение)

Ключевые изменения:

1. Значительно возросла производительность при снижении общего времени ожиданий
2. Коэффициент детерминации R² для SPEED увеличился с 0.38 до 0.77, что указывает на лучшую предсказуемость производительности
3. Угол наклона регрессии для SPEED стал более отрицательным (-41.32 vs -31.57), что означает более выраженную тенденцию снижения производительности при росте нагрузки

2. Сравнение и анализ корреляций и паттернов ожиданий СУБД

Корреляция SPEED-WAITINGS:

• Эксперимент 1: -0.71 (сильная отрицательная)
• Эксперимент 8: -0.93 (очень сильная отрицательная)
• Вывод: В оптимизированной конфигурации производительность стала более чувствительной к времени ожиданий

Распределение типов ожиданий:

• IO ожидания остались доминирующими в обоих экспериментах (~99%)
• В эксперименте 8 появились дополнительные типы LWLock (WALInsert, XidGen), что может указывать на более интенсивную работу с журналом транзакций

Паттерны запросов:

• QueryID 65003243407475111 (scenario1) увеличил долю вызовов с 12.3M до 21.0M
• Доля ожиданий IO для scenario1 увеличилась с 80.18% до 83.24%

3. Сравнение проблем производительности СУБД

Общие проблемы (оба эксперимента):

1. IO остается главным узким местом (ожидания IO составляют ~99% всех ожиданий)
2. Высокая корреляция WAITINGS-IO (1.0 в обоих случаях)

Улучшения в эксперименте 8:

1. Снижение общего времени ожиданий на 15-50%
2. Улучшение корреляции между различными типами ожиданий

4. Диагностика и сравнение узких мест СУБД

Эксперимент 1:

• Основная проблема: Ожидания IO (DataFileRead - 99.93%)
• Вторичные проблемы: Lock (extend - 67.61%), LWLock (ProcArray - 51.61%)

Эксперимент 8:

• Основная проблема: Ожидания IO (DataFileRead - 99.68%)
• Вторичные проблемы: более диверсифицированы (Lock: extend - 57.01%, LWLock: ProcArray - 40.61%, BufferContent - 26.66%)

5. Сравнение метрик vmstat

Процессы:

• Эксперимент 1: procs_b (блокированные) 5-14
• Эксперимент 8: procs_b 5-13
• Изменение: Незначительное улучшение

Ввод-вывод:

• Эксперимент 1: io_bi 27 584-29 457; io_bo 16 394-20 019
• Эксперимент 8: io_bi 27 570-29 939; io_bo 20 617-25 034
• Изменение: Увеличение операций записи (bo) на ~25%

CPU:

• Эксперимент 1: cpu_wa 44-50%
• Эксперимент 8: cpu_wa 39-45%
• Улучшение: Снижение времени ожидания IO на CPU

6. Сравнение и анализ корреляций vmstat

Критические корреляции:

IO-b (процессы в uninterruptible sleep):

• Эксперимент 1: 0.9755 (ALARM)
• Эксперимент 8: 0.9690 (ALARM)
• Состояние: Без изменений

IO-bo (блоки на запись):

• Эксперимент 1: 0.6559 (WARNING)
• Эксперимент 8: 0.9402 (ALARM)
• Ухудшение: Корреляция усилилась

LWLock-us (user time):

• Эксперимент 1: 0.9100 (ALARM)
• Эксперимент 8: 0.9551 (ALARM)
• Ухудшение: Корреляция усилилась

7. Сравнение критических проблем vmstat

Общие проблемы (оба эксперимента):

1. Высокое время ожидания IO (wa > 10% в 100% наблюдений)
2. Высокая корреляция IO-b (процессы ждут ответа от диска)
3. Недостаток свободной RAM (<5% в 100% наблюдений)

Изменения в эксперименте 8:

1. Появилась высокая корреляция IO-bi (0.9000, ALARM)
2. Усилилась корреляция IO-bo (с 0.6559 до 0.9402)
3. Незначительно снизился cpu_wa (с 44-50% до 39-45%)

8. Диагностика и сравнение узких мест vmstat

Эксперимент 1:

• Основное узкое место: Подсистема ввода-вывода
• Вторичные проблемы: Высокий user time из-за LWLock

Эксперимент 8:

• Основное узкое место: Подсистема ввода-вывода (усилившаяся проблема)
• Новая проблема: Высокая нагрузка на чтение/запись блоков (bi/bo)
• Сохраняющаяся проблема: Конкуренция за CPU из-за LWLock

9. Итоговый сравнительный анализ по производительности СУБД и vmstat

Положительные изменения после оптимизации:

1. Увеличение производительности СУБД на 63-66%
2. Снижение общего времени ожиданий СУБД на 6.6-15%
3. Улучшение предсказуемости производительности (R² вырос с 0.38 до 0.77)
4. Снижение времени ожидания IO на CPU (cpu_wa снизился на 5%)

Отрицательные изменения/сохраняющиеся проблемы:

1. Усилилась корреляция между IO и операциями чтения/записи
2. Возросла нагрузка на подсистему ввода-вывода (увеличились bi/bo)
3. Сохраняется проблема процессов в состоянии uninterruptible sleep
4. Не решена проблема нехватки свободной памяти

Выводы:

1. Оптимизация настроек IO улучшила общую производительность, но выявила новые узкие места
2. Подсистема ввода-вывода остается главным ограничивающим фактором, причем нагрузка на нее увеличилась
3. Улучшение производительности СУБД достигнуто ценой увеличения нагрузки на IO-подсистему
4. Требуется дальнейшая оптимизация для снижения корреляции между IO и операциями чтения/записи
5. Проблема с памятью требует отдельного внимания, так как остается критической в обоих конфигурациях

Рекомендация: Дальнейшая оптимизация должна быть направлена на балансировку нагрузки между CPU и IO, а также на решение проблемы нехватки оперативной памяти.

Продолжение экспериментов по тонкой оптимизаций параметров операционной системы

Запрос

settings.txt - конфигурация СУБД и инфраструктуры

1.summary.vmstat.txt - Эксперимент-1 (Базовые настройки IO)

8.summary.vmstat.txt - Эксперимент-8 (Оптимизация настроек IO)

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

**Получение текущего значения параметра настройки операционной системы**

**Обоснование изменения значения параметра настройки операционной системы**

**Изменение значения параметра настройки операционной системы**

План изменений

На основе анализа предоставленных данных (конфигурация инфраструктуры, результаты базового и оптимизированного экспериментов) можно выделить следующие критические проблемы операционной системы, требующие оптимизации:

1. Высокая нагрузка на I/O подсистему (wa > 10% в 100% наблюдений, много процессов в D состоянии).
2. Недостаток свободной оперативной памяти (<5% в 100% наблюдений).
3. Высокое соотношение чтения к записи (OLAP-паттерн), что требует оптимизации кэширования и стратегии сброса данных на диск.

Вот список рекомендуемых оптимизаций на стороне ОС (Linux):

1. Оптимизация управления "грязной" памятью (Dirty Pages)

Проблема: Слишком агрессивные или, наоборот, пассивные настройки могут приводить к "лавинообразному" сбросу данных на диск, вызывая пики нагрузки (io_wa) и рост процессов в состоянии D (непрерываемый сон).

1️⃣Параметр: vm.dirty_background_ratio / vm.dirty_background_bytes

• Получение текущего значения: sysctl vm.dirty_background_ratio
• Обоснование: Определяет процент от общей памяти, при превышении которого система фоново начинает записывать "грязные" страницы на диск. При высоком OLAP-чтении и нехватке памяти для кэша нужно более агрессивно освобождать RAM под новые данные, но без фанатизма, чтобы не создавать избыточную фоновую запись.
• Рекомендуемое изменение: Уменьшить значение (например, с типичных 10% до 3-5%). Или задать абсолютное значение в байтах для более точного контроля.
• Изменение значения:

# Временное изменение

sysctl -w vm.dirty_background_ratio=3

# ИЛИ использование байт (например, для 150MB)

sysctl -w vm.dirty_background_bytes=157286400

# Постоянное изменение - добавить в /etc/sysctl.conf

echo "vm.dirty_background_ratio = 3" >> /etc/sysctl.conf

sysctl -p

2️⃣Параметр: vm.dirty_ratio / vm.dirty_bytes

• Получение текущего значения: sysctl vm.dirty_ratio
• Обоснование: Определяет максимальный процент "грязной" памяти, при достижении которого процессы будут блокироваться (D состояние) на время записи. Это напрямую связано с алертом IO-b. Нужно снизить этот порог, чтобы процессы блокировались реже и на более короткое время.
• Рекомендуемое изменение: Уменьшить значение (например, с типичных 20% до 10-15%).
• Изменение значения:

sysctl -w vm.dirty_ratio=10

echo "vm.dirty_ratio = 10" >> /etc/sysctl.conf

sysctl -p

3️⃣Параметр: vm.dirty_expire_centisecs

• Получение текущего значения: sysctl vm.dirty_expire_centisecs
• Обоснование: Определяет время (в сотых долях секунды), в течение которого "грязная" страница может находиться в памяти перед тем, как её необходимо записать. Уменьшение значения делает запись более частой, но менее объемной "пачками", что сглаживает нагрузку на диск.
• Рекомендуемое изменение: Уменьшить (например, с 3000 (30 сек) до 1000-1500 (10-15 сек)).
• Изменение значения:

sysctl -w vm.dirty_expire_centisecs=1000

echo "vm.dirty_expire_centisecs = 1000" >> /etc/sysctl.conf

sysctl -p

2. Оптимизация политики Swappiness

Проблема: При почти полной загрузке RAM (free < 5%) система может начать агрессивно использовать swap, даже если swap не используется явно (si/so=0 в данных). Это может создавать ненужную нагрузку на I/O и CPU.

1️⃣Параметр: vm.swappiness

• Получение текущего значения: sysctl vm.swappiness (обычно 60).
• Обоснование: Управляет склонностью ядра к вытеснению страниц из оперативной памяти в swap. Для сервера БД, где важна производительность дисковых операций, свопинг часто вреден. Поскольку данных для свопинга нет, можно резко снизить это значение, чтобы ядро в первую очередь сбрасывало на диск кэш файловой системы ("грязные" страницы), а не искало кандидатов на своп.
• Рекомендуемое изменение: Уменьшить до 1-10.
• Изменение значения:

sysctl -w vm.swappiness=5

echo "vm.swappiness = 5" >> /etc/sysctl.conf

sysctl -p

3. Выбор и настройка I/O Scheduler (планировщика дисков)

Проблема: Слишком большая очередь (procs_b) и высокий io_wa могут быть вызваны неоптимальным планировщиком. В виртуальной среде (KVM) планировщики deadline или none (noop) часто работают лучше, чем cfq (completely fair queuing).

1️⃣Параметр: I/O Scheduler для дисков данных (vdd, vdc, vdb).

• Получение текущего значения: cat /sys/block/vd[x]/queue/scheduler (например, для vdd).
• Обоснование: Для виртуальных дисков в KVM, где гостевая ОС не управляет физическим железом, накладные расходы сложных планировщиков (cfq) могут быть излишни. Планировщик deadline обеспечивает предсказуемое время отклика, а noop просто передает запросы гипервизору, что часто оптимально.
• Рекомендуемое изменение: Установить deadline или none для дисков с данными (vdd, vdc). Для системного диска (vda) можно оставить cfq или deadline.
• Изменение значения:

# Для каждого диска данных (vdd, vdc, vdb)

echo "deadline" > /sys/block/vdd/queue/scheduler

# ИЛИ для noop (часто лучше в KVM/VirtIO)

echo "none" > /sys/block/vdd/queue/scheduler

# Для автоматизации при загрузке

# Создать/отредактировать файл /etc/udev/rules.d/60-io-schedulers.rules

# Добавить правило (пример для VirtIO дисков):

ACTION=="add|change", KERNEL=="vd[b-z]", ATTR{queue/scheduler}="deadline"

# Перезапустить udev или перезагрузиться

udevadm control --reload-rules && udevadm trigger

❓4. Увеличение лимитов на количество открытых файлов и процессов (опционально)

Проблема: При высокой нагрузке и большом количестве соединений к БД (max_connections=1000) стандартные лимиты ОС могут быть исчерпаны.

Параметр: nofile (макс. число открытых файлов) и nproc (макс. число процессов) для пользователя postgres.

• Получение текущего значения: ulimit -n и ulimit -u (от пользователя postgres) или cat /etc/security/limits.conf.
• Обоснование: PostgreSQL использует много файловых дескрипторов (на каждое соединение, на каждый файл таблицы). Лимиты должны быть выше, чем max_connections + запас.
• Рекомендуемое изменение: Установить nofile не менее 65535 и nproc не менее 4096.
• Изменение значения:

# В файле /etc/security/limits.conf или лучше в /etc/security/limits.d/postgres.conf

echo "postgres soft nofile 65535" >> /etc/security/limits.d/postgres.conf

echo "postgres hard nofile 65535" >> /etc/security/limits.d/postgres.conf

echo "postgres soft nproc 8192" >> /etc/security/limits.d/postgres.conf

echo "postgres hard nproc 8192" >> /etc/security/limits.d/postgres.conf

# Также проверить системный лимит на уровне systemd (если используется)

# в /etc/systemd/system.conf или сервисном файле postgresql.service

# DefaultLimitNOFILE=65535

⚠️Порядок применения и проверки:

1. Сделать бэкап текущих настроек (sysctl -a > sysctl_backup.conf, cp /etc/security/limits.conf /etc/security/limits.conf.backup).
2. Вносить изменения по одному или малыми группами (например, сначала vm.dirty_*, потом swappiness, потом планировщик).
3. После каждого изменения проводить нагрузочное тестирование и сравнивать метрики wa, procs_b, io_bi/bo, memory_free.
4. Мониторить логи ОС (dmesg, /var/log/syslog) на предмет ошибок или предупреждений.
5. Изменения через sysctl -w действуют до перезагрузки. После нахождения оптимальных значений зафиксировать их в /etc/sysctl.conf.

⚠️Важное предупреждение:

Настройки vm.dirty_* очень чувствительны и зависят от общей нагрузки и паттерна работы. Резкое уменьшение значений может, наоборот, увеличить нагрузку на I/O из-за постоянного фонового сброса. Необходим тщательный подбор на тестовой нагрузке.

ℹ️Итог:

Оптимизация настроек СУБД позволила увеличить производительность на 63–66% и снизить время ожиданий на 6,6–15%, а также улучшить предсказуемость работы системы. Однако возросла нагрузка на подсистему ввода-вывода, сохранились проблемы с нехваткой памяти и процессами в состоянии непрерываемого сна. Для дальнейшего улучшения рекомендуется оптимизация управления памятью, выбор планировщика дисков и настройка параметров ОС, направленных на балансировку нагрузки между CPU и IO.