Найти в Дзене
Postgres DBA
64 подписчика

PG_EXPECTO 5.2 : Иммитация OLAP нагрузки

1
6 мин
Данный отчет представляет результаты комплексного анализа производительности СУБД PostgreSQL под нагрузкой, имитирующей OLAP. Глоссарий терминов | Postgres DBA | Дзен GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL Протестировать сценарий нагрузочного тестирования для имитации типа нагрузки "OLAP" scenario1 = 0.7 scenario2 = 0.2 scenario3 = 0.1 Проанализируй данные по метрикам производительности и ожиданий СУБД , метрикам инфраструктуры vmstat/iostat. Подготовь итоговый отчет по результатам анализа. Для построения отчета используй списки вместо таблиц. Нагрузочное тестирование выявило критические проблемы с производительностью, связанные преимущественно с дисковым вводом-выводом (I/O) и недостаточным кэшированием. Система работает в режиме, близком к OLAP-сценарию, с интенсивным чтением данных с диска. Система испытывает критическую нехватку ресурсов I/O и памяти, что приводит к значительному снижению производитель
Оглавление
OLAP под давлением: бутылочное горлышко на /data
OLAP под давлением: бутылочное горлышко на /data

Данный отчет представляет результаты комплексного анализа производительности СУБД PostgreSQL под нагрузкой, имитирующей OLAP.

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

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

Задача

Протестировать сценарий нагрузочного тестирования для имитации типа нагрузки "OLAP"

Тестовый сценарий для имитации нагрузки OLAP

--OLAP -- scenario1.sql -- 5.2 CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE  test_rec record ; BEGIN WITH branch_summary AS (   SELECT    b.bid,    COUNT(a.aid) as account_count,    SUM(a.abalance) as total_balance,    AVG(a.abalance) as avg_balance   FROM pgbench_branches b   LEFT JOIN pgbench_accounts a ON b.bid = a.bid   GROUP BY b.bid ), transaction_summary AS…
Postgres DBAВчера

Изменения в версии 5.2⚠️

Тестовые сценарии : scenario-2, scenario-3.

-- scenario2.sql -- INSERT -- 5.2 CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$ BEGIN --------------------------------------------------- --СЦЕНАРИЙ 3 - INSERT ONLY -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ТОЛЬКО ДЛЯ scale = 685 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! INSERT INTO pgbench_history ( tid, bid, aid, delta, mtime ) VALUES ( floor(random…
Postgres DBAВчера
-- scenario3.sql -- UPDATE -- 5.2 CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$ DECLARE  current_aid bigint ;  current_delta bigint ; BEGIN -- Генерация случайного сдвига   current_delta := (ROUND(RANDOM())::BIGINT) * 10 + 1; -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ТОЛЬКО ДЛЯ scale = 685 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   -- Атомарный выбор…
Postgres DBAВчера

Веса сценариев

scenario1 = 0.7

scenario2 = 0.2

scenario3 = 0.1

Тестовая среда, инструменты и конфигурация СУБД:

Нагрузка на СУБД в ходе экспериментов

-2

Корреляционный анализ производительности и ожиданий СУБД

-3

Операционная скорость

Изменение операционной скорости СУБД в ходе нагрузочного тестирования
Изменение операционной скорости СУБД в ходе нагрузочного тестирования

Ожидания СУБД

Изменение ожиданий СУБД в ходе нагрузочного тестирования
Изменение ожиданий СУБД в ходе нагрузочного тестирования

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

-6
-7

IOPS (Производительность IO)

Изменение производительности IO(IOPS) в ходе нагрузочного тестирования
Изменение производительности IO(IOPS) в ходе нагрузочного тестирования

MB/s (Пропуская способность IO)

-9

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

Проанализируй данные по метрикам производительности и ожиданий СУБД , метрикам инфраструктуры vmstat/iostat. Подготовь итоговый отчет по результатам анализа. Для построения отчета используй списки вместо таблиц.

Основные выводы

Нагрузочное тестирование выявило критические проблемы с производительностью, связанные преимущественно с дисковым вводом-выводом (I/O) и недостаточным кэшированием. Система работает в режиме, близком к OLAP-сценарию, с интенсивным чтением данных с диска.

Выявленные проблемы

1. Проблемы с дисковым вводом-выводом (I/O)

  1. Чрезмерное время ожидания I/O (wa)
    100% наблюдений показывают время ожидания I/O > 10%
    Среднее значение wa составляет 20-30%
  2. Низкая эффективность кэширования
    Hit Ratio shared buffers составляет всего 57.72% (критически низкое значение)
    Высокая корреляция скорости операций с чтением/записью на диск (0.93-0.99)
  3. Доминирование операций чтения с диска
    Отношение прочитанных блоков к измененным: 156.7 (характерно для OLAP)
    DataFileRead составляет 99.75% всех ожиданий типа IO

2. Проблемы с использованием памяти

  1. Недостаток оперативной памяти
    100% наблюдений показывают свободную RAM < 5%
    Зафиксировано использование свопинга (9-11% наблюдений)
  2. Низкая эффективность shared buffers
    Одновременный рост hit и read указывает на смешанную нагрузку

3. Проблемы с процессором

  1. Высокая нагрузка на CPU
    Очень высокая корреляция LWLock с user time (0.93) и system time (0.88)
    Рост очереди процессов до 10 при 8 ядрах CPU
  2. Проблемы с переключением контекста
    Высокая корреляция переключений контекста с прерываниями (0.97)

Анализ SQL-нагрузки

Ключевые проблемные запросы

  1. QueryID: -4489358326315084845 (scenario1)
    Вызывает 100% ожиданий IPC
    Вызывает 98.79% ожиданий Timeout
    Ответственен за 24.92% ожиданий IO
  2. QueryID: -8774367544127786281 (scenario2)
    Ответственен за 49.67% ожиданий IO
  3. QueryID: 8385759386935767265 (scenario3)
    Ответственен за 25.41% ожиданий IO

Типы ожиданий

  1. IO ожидания (коэффициент корреляции с общими ожиданиями: 0.78)
    DataFileRead: 99.75%
    DataFileWrite, DataFileExtend, BuffileWrite/Read: менее 0.25%
  2. IPC ожидания (коэффициент корреляции: 0.99)
    BufferIo: 100%
  3. Timeout ожидания (коэффициент корреляции: 0.88)
    SpinDelay: 100%

Заключение

Система испытывает критическую нехватку ресурсов I/O и памяти, что приводит к значительному снижению производительности. Рекомендуется незамедлительно увеличить ресурсы сервера и оптимизировать проблемные запросы, после чего провести повторное тестирование.

Анализ производительности IO для файловой системы /data

Подготовь отчет по результатам анализа производительности подсистемы IO
для дисковых устройств, используемых для файловых систем /data /wal
**Общая характеристика системы**
- Период анализа
- Основные устройства хранения
- Тип нагрузки
Состав отчета по файловой системе:
**Критические проблемы производительности по файловой системе**
**Анализ корреляций и паттернов нагрузки по файловой системе**
**Диагностика узких мест IO по файловой системе**
- r_await(ms)
- w_await(ms)
- aqu_sz
- proc_b
- cpu_wa(%)
- Корреляция speed с IOPS
- Корреляция speed с пропускной способностью (MB/s)
- Вывод по диагностике узких мест IO
**Рекомендации по оптимизации файловой системы**
**Итоговый вывод по производительности IO**
Для построения отчета используй списки вместо таблиц.

Период анализа: 2026-01-20 12:01 – 2026-01-20 13:51 (≈2 часа)
Основные устройства хранения:

  • /data — устройство vdd (100 ГБ)
  • /wal — устройство vdc (50 ГБ)

Тип нагрузки: OLAP/аналитическая (преобладают операции чтения, высокий объем данных, низкий hit ratio кэша).

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

Для файловой системы /data (vdd)

  • ALARM: Процессы не могут работать из-за ожидания диска (высокая корреляция wa–util = 0.802).
  • ALARM: Более 50% наблюдений — загрузка диска >50%, задержки чтения/записи >5 мс, глубина очереди >1.
  • ALARM: Hit Ratio shared buffers критически низкий (57.72%).
  • ALARM: Рост скорости операций напрямую зависит от роста чтения/записи с диска.

Для файловой системы /wal (vdc)

  • WARNING: 34% наблюдений — загрузка диска >50%.
  • OK: Задержки чтения/записи в норме (<5 мс), глубина очереди в норме.

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

/data (vdd)

  • Корреляция buff–r/s: 0.9095 (очень высокая) — неэффективное использование буферной памяти.
  • Корреляция cache–r/s: 0.8283 (высокая) — неэффективное кэширование.
  • Корреляция speed–shared_blks_read: 0.9303 — скорость операций сильно зависит от чтения с диска.
  • Корреляция speed–shared_blks_write: 0.9905 — система ограничена производительностью записи.

/wal (vdc)

  • Корреляция speed–IOPS: 0.8283 (высокая).
  • Корреляция speed–MB/s: 0.8913 (очень высокая) — производительность ограничена пропускной способностью.

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

/data (vdd)

  • r_await(ms): 9–14 мс (выше нормы >5 мс).
  • w_await(ms): 6–7 мс (выше нормы >5 мс).
  • aqu_sz: 31–47 (высокая глубина очереди).
  • proc_b: 3–4 процесса в состоянии uninterruptible sleep.
  • cpu_wa(%): 16–31% (критически высоко, норма <10%).
  • Корреляция speed–IOPS: -0.1472 (отрицательная) — IO не является ограничением.
  • Корреляция speed–MB/s: 0.9828 (очень высокая) — ограничение пропускной способностью.
  • Вывод: Узкое место — пропускная способность диска, высокие задержки, перегруженная очередь.

/wal (vdc)

  • r_await(ms): 0.5–1 мс (норма).
  • w_await(ms): 0.5–1 мс (норма).
  • aqu_sz: 0–1 (низкая очередь).
  • proc_b: 0–1 процесс в uninterruptible sleep.
  • cpu_wa(%): 16–31% (высокий, но не связан с vdc напрямую).
  • Корреляция speed–IOPS: 0.8283 (высокая).
  • Корреляция speed–MB/s: 0.8913 (высокая).
  • Вывод: Устройство работает нормально, загрузка умеренная, задержки низкие.

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

Для /data (vdd)

  • Увеличить объем shared_buffers для улучшения hit ratio.
  • Настроить work_mem для агрегаций и сортировок.
  • Оптимизировать запросы: уменьшить объем чтения, использовать индексы, материализованные представления.
  • Настроить параметры commit_delay, wal_buffers для снижения нагрузки на запись.

Для /wal (vdc)

  • Мониторинг загрузки: при росте нагрузки выше 50% — рассмотреть увеличение пропускной способности.
  • Проверить настройки WAL: wal_buffers, checkpoint_segments.

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

  • /data (vdd) является критическим узким местом системы: высокие задержки, глубина очереди, низкий hit ratio кэша. Нагрузка — аналитическая (OLAP), производительность ограничена пропускной способностью диска.
  • /wal (vdc) функционирует в пределах нормы, но требует наблюдения из-за умеренной загрузки.
  • Общая система испытывает высокий CPU wait IO из-за медленного диска vdd. Требуется оптимизация конфигурации СУБД и рассмотрение апгрейда дискового подсистемы.

Итог

Система работает на пределе возможностей текущей конфигурации. Предложенные рекомендации являются обязательным минимумом для стабилизации работы под аналитической нагрузкой.