PG_EXPECTO: Оптимизация подсистемы IO. Эксперимент-1: BASELINE

Заглянуть под капот данных: как IO становится узким местом СУБД

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

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

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

Задача

Подготовить промпт для формирования нейросетью отчета по анализу производительности подсистемы 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

График изменения IOPS в ходе нагрузочного тестирования для диска vdd

MB/s

График изменения пропускной способности(MB/s) в ходе нагрузочного тестирования для диска vdd

Диск vdc (/wal)

IOPS

График изменения IOPS в ходе нагрузочного тестирования для диска vdc

MB/s

График изменения пропускной способности(MB/s) в ходе нагрузочного тестирования для диска vdc

Формирование итогового отчета по производительности 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. Увеличить shared_buffers (если PostgreSQL).
2. Оптимизировать work_mem для тяжёлых запросов.

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

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

Мониторить процессы в 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

Основное узкое место — устройство vdd (/data):

• Постоянная 100% загрузка, высокая очередь запросов.
• Проблема не в скорости диска, а в неэффективном использовании памяти и CPU.
• Рекомендуется оптимизировать кэширование и настройки СУБД.

Устройство vdc (/wal) работает в штатном режиме:

• Загрузка умеренная, задержки низкие.
• Не требует срочных изменений.

Общая система:

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

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

1. Увеличить объём оперативной памяти.
2. Настроить параметры кэширования СУБД.
3. Провести анализ и оптимизацию медленных запросов.
4. Рассмотреть переход на SSD для /data, если нагрузка продолжит расти.

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

Эксперименты по изменению параметров ОС для оптимизации производительности IO