PG_EXPECTO: Анализ производительности подсистемы IO по результатам нагрузочного тестирования СУБД PostgreSQL⁠⁠

Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).

В современных системах обработки данных производительность подсистемы ввода-вывода (IO) часто становится ключевым фактором, определяющим общую эффективность СУБД. Даже на мощном оборудовании неправильная настройка или неоптимальные паттерны доступа к данным могут привести к “узким местам”, которые сводят на нет всю производительность системы. В статье разбираются результаты нагрузочного тестирования PostgreSQL с помощью инструмента PG_EXPECTO, чтобы выявить скрытые проблемы IO, проанализировать их причины и предложить конкретные шаги по оптимизации.

Задача

Подготовить промпт для формирования нейросетью отчета по анализу производительности подсистемы IO по результатам нагрузочного тестирования.

Формирование файлов статистических данных и промптов для нейросети, по итогам нагрузочного тестирования

cd /postgres/pg_expecto/sh/performance_reports

./load_test_report.sh

Результирующие файлы:

• Статистические данные по производительности СУБД и инфраструктуры : 1.summary.txt

• Промпт для нейросети для формирования отчета по производительности СУБД и инфраструктуры : _1.prompt.txt

• Статистические данные по производительности подсистемы IO: _2.io_performance.txt

• Промпт для нейросети для формирования отчета по производительности подсистемы IO: _2.io_performance_prompt.txt

• Метрики производительности дискового устройства файловой системы /data: linux.x.iostat_vdd_performance.txt

• Метрики производительности дискового устройства файловой системы /wal linux.x.iostat_vdc_performance.txt

Нагрузка на СУБД

Показатели производительности IO

Диск vdd (/data)

IOPS

MB/s

Диск vdc (/wal)

IOPS

MB/s

Формирование итогового отчета по производительности IO

Промпт

Подготовь итоговый по результатам анализа производительности подсистемы IO

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

**Общая характеристика системы**

- Период анализа

- Основные устройства хранения

- Тип нагрузки

**Критические проблемы производительности**

**Анализ корреляций и паттернов нагрузки**

**Диагностика узких мест IO**

- r_await(ms)

- w_await(ms)

- aqu_sz

- proc_b

- cpu_wa(%)

- Корреляция speed с IOPS

- Корреляция speed с пропускной способностью

- Вывод по диагностике узких мест IO

**Рекомендации по оптимизации**

**Итоговый вывод по производительности IO**

Отчет по анализу производительности подсистемы IO

Период анализа: 2026-01-03 12:59 — 2026-01-03 14:48 (≈2 часа)
Основные устройства хранения:

• /data → устройство vdd (100 ГБ, LVM)

• /wal → устройство vdc (50 ГБ, LVM)

Тип нагрузки: OLAP-сценарий (соотношение чтения к записи ≈ 2.68:1). Преобладают операции чтения, что характерно для аналитических запросов.

1. Файловая система /data (устройство vdd)

Критические проблемы производительности

• Загрузка устройства 100% (%util = 100) на всём интервале наблюдения.

• Высокая длина очереди запросов (aqu_sz до 18), что указывает на перегрузку диска.

• Высокое время ожидания CPU для IO (cpu_wa > 10% в 100% наблюдений).

• Корреляция cache – w/s (0.5183) и buff – wMB/s (0.6776) — память неэффективно используется для снижения нагрузки на диск.

Анализ корреляций и паттернов нагрузки

• Отрицательная корреляция wa – util (–0.2384) — задержки CPU не связаны напрямую с утилизацией диска.

• Высокая корреляция cache – r/s (0.5023) — кэш слабо помогает снизить операции чтения.

• Соотношение чтение/запись ≈ 2.68:1 — подтверждает OLAP-характер нагрузки.

Диагностика узких мест IO

Вывод по диагностике:
Узкое место — не в диске, а в CPU и подсистеме памяти. Высокий cpu_wa и большая очередь (aqu_sz) указывают на то, что процессы блокируются из-за ожидания IO, но сами задержки диска (r_await, w_await) низкие. Возможно, проблема в недостаточном буферизации/кэшировании или в настройках СУБД.

Рекомендации по оптимизации

1. Увеличить оперативную память для более эффективного кэширования данных.

2. Настроить параметры СУБД:
   Увеличить shared_buffers (если PostgreSQL).
   Оптимизировать work_mem для тяжёлых запросов.

3. Проверить план запросов — возможно, избыточные full scan или отсутствие индексов.

4. Рассмотреть использование более быстрого хранилища (например, SSD) для /data, если нагрузка чтения критична.

5. Мониторить процессы в D состоянии (proc_b) — выявить конкретные запросы, вызывающие ожидание.

2. Файловая система /wal (устройство vdc)

Критические проблемы производительности

• Загрузка устройства в норме (%util ≤ 53%).

• Очередь запросов отсутствует (aqu_sz ≤ 1).

• Задержки записи минимальны (w_await ≈ 1 мс).

Анализ корреляций и паттернов нагрузки

• Отрицательная корреляция wa – util (–0.2795) — CPU не простаивает из-за IO.

• Слабая корреляция cache – w/s (0.4105) — кэш умеренно влияет на запись.

• Операции чтения отсутствуют (r/s = 0) — нагрузка чисто на запись (WAL-логи).

Диагностика узких мест IO

Вывод по диагностике:
Устройство vdc не является узким местом. Запись в WAL идёт эффективно, задержки минимальны, очередь не формируется.

Рекомендации по оптимизации

1. Текущая конфигурация адекватна — изменений не требуется.

2. Мониторить рост WAL — убедиться, что место не заканчивается.

3. При увеличении нагрузки на запись — рассмотреть отдельный быстрый SSD для WAL.

Итоговый вывод по производительности IO

1. Основное узкое место — устройство vdd (/data):
   Постоянная 100% загрузка, высокая очередь запросов.
   Проблема не в скорости диска, а в неэффективном использовании памяти и CPU.
   Рекомендуется оптимизировать кэширование и настройки СУБД.

2. Устройство vdc (/wal) работает в штатном режиме:
   Загрузка умеренная, задержки низкие.
   Не требует срочных изменений.

3. Общая система:
   Нагрузка имеет OLAP-характер (много чтений).
   CPU простаивает из-за ожидания IO (высокий wa), но диски не являются медленными — проблема в организации доступа к данным.
   Рекомендуется фокус на оптимизацию запросов и увеличение памяти.

Рекомендуемые действия в порядке приоритета:

1. Увеличить объём оперативной памяти.

2. Настроить параметры кэширования СУБД.

3. Провести анализ и оптимизацию медленных запросов.

4. Рассмотреть переход на SSD для /data, если нагрузка продолжит расти.

*Отчет составлен на основе анализа метрик iostat, vmstat и корреляций за период 2026-01-03 12:59–14:48.*