Сравнительный анализ паттернов производительности PostgreSQL: OLAP vs OLTP под нагрузкой с использованием PG_EXPECTO⁠⁠

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

Статья представляет собой глубокий сравнительный анализ паттернов производительности PostgreSQL 17 для двух принципиально разных типов нагрузки — аналитической (OLAP) и транзакционной (OLTP). Исследование проведено с помощью специализированного комплекса pg_expecto, предназначенного для статистического анализа и нагрузочного тестирования. Этот инструмент позволил не только имитировать сценарии, но и выявить тонкие корреляции между внутренними ожиданиями СУБД (DataFileRead, LWLock) и системными метриками (vmstat, iostat). В статье показано, как под нагрузкой ведут себя ключевые показатели: от hit ratio буферного кэша до времени отклика диска, и определены характерные «узкие места» для каждого типа задач.

ℹ️Это готовый кейс для использования в нагрузочных тестах.

Задача

Определить характерные паттерны производительности/ожиданий СУБД и показателей vmstat/iostat для типов нагрузки OLAP/OLTP в ходе нагрузочного тестирования.

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

scenario1 = 0.7

scenario2 = 0.2

scenario3 = 0.1

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

• СУБД: PostgreSQL 17

• Инструмент нагрузочного тестирования: pg_expecto

• Тестовая база данных: pgbench (10GB, простая структура)

• CPU = 8

• RAM = 8GB

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

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

OLAP

OLTP

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

Сравнительный отчет по нагрузочному тестированию: OLAP vs OLTP

1. Общая характеристика системы в экспериментах

Оборудование и конфигурация:

• CPU: 8 ядер (Intel Xeon)

• RAM: 8 ГБ

Дисковая подсистема:

• /data: 100 ГБ (данные)

• /wal: 50 ГБ (WAL)

• /log: 30 ГБ (логи)

• Файловая система: LVM

Настройки PostgreSQL:

• shared_buffers = 4 ГБ

• work_mem = 32 МБ

• max_connections = 1000

• autovacuum настроен агрессивно (naptime=1s)

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

• Эксперимент-1 (OLAP): Аналитические запросы, большие чтения, сканирования.

• Эксперимент-2 (OLTP): Транзакционные запросы, короткие операции, интенсивное чтение/запись.

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

Для OLAP (Эксперимент-1):

Основной тип ожиданий: IO (DataFileRead) — 99,75% от всех ожиданий типа IO.

Корреляция ожиданий IO с общими ожиданиями (WAITINGS): 0,78 (высокая).

Операционная скорость (SPEED):

• Рост сильно коррелирует с чтением с диска (корреляция 0,93).

⚠️Hit Ratio shared buffers крайне низкий: 57,72%.

Ключевые запросы:

• scenario1() вызывает 49,67% ожиданий IO.

• scenario2() и scenario3() также вносят значительный вклад.

Для OLTP (Эксперимент-2):

Основной тип ожиданий: IO (DataFileRead) — 100% от всех ожиданий типа IO.

Корреляция ожиданий IO с общими ожиданиями (WAITINGS): 1,0 (полная зависимость).

Операционная скорость (SPEED):

• Почти не растет (R²=0,10), но ожидания растут значительно (R²=0,90).

☑️Hit Ratio shared buffers приемлемый: 90,74%.

Ключевые запросы:

• scenario1() вызывает 79,62% ожиданий IO.

• scenario2() добавляет 12,96%.

3. Сравнительные паттерны показателей vmstat/iostat

Для OLAP:

Память (RAM):

• Свободной памяти < 5% в 100% наблюдений.

• Свопинг (swap in/out) присутствует⚠️, но в небольших объемах.

CPU:

• Высокая корреляция LWLock с user time (0,93) и system time (0,88).

• Очередь процессов (run queue) редко превышает число ядерℹ️.

Диск (IO):

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

• Корреляция IO с процессами в состоянии D (b) слабая (0,31).

Для OLTP:

Память (RAM):

• Свободной памяти < 5% в 100% наблюдений.

• Свопинг отсутствует☑️.

CPU:

• Высокая корреляция переключений контекста (cs) с прерываниями (in) (0,96) и user time (0,73).

• Очередь процессов не превышает число ядер☑️.

Диск (IO):

• Очень высокое время ожидания IO (wa > 10% в 100% наблюдений).

• Сильная корреляция IO с процессами в состоянии D (b) (0,98).

4. Итоговый вывод о характерных паттернах производительности

Для нагрузки типа OLAP:

Паттерн производительности: Система упирается в дисковый ввод-вывод.

Ключевые индикаторы:

• ⚠️Низкий hit ratio буферного кэша (57,72%).

• Высокая корреляция скорости операций с чтением с диска.

• Большой объем чтений (DataFileRead) и записей (shared_blks_write).

Проблемные точки:

• ⚠️Нехватка оперативной памяти.

• Диск не справляется с объемом чтений.

• ⚠️Высокая нагрузка на CPU из-за LWLock.

Для нагрузки типа OLTP:

Паттерн производительности: Система упирается в дисковые задержки и блокировки процессов.

Ключевые индикаторы:

• Почти полная корреляция ожиданий IO с общими ожиданиями (1,0).

• Высокое время ожидания IO (wa до 69%).

• Растущее количество процессов в состоянии D (b).

Проблемные точки:

• Дисковые операции становятся узким местом.

• Процессы блокируются из-за ожидания IO.

• ⚠️Высокая нагрузка на CPU из-за переключений контекста.

Общие выводы:

• Оба сценария показывают критическую зависимость от производительности диска.

• Память является ограничивающим ресурсом в обоих случаях.

• Настройки PostgreSQL (shared_buffers, work_mem) требуют оптимизации под конкретный тип нагрузки.ℹ️

Рекомендации:

• Для OLAP: Увеличить shared_buffers, оптимизировать запросы, рассмотреть увеличение RAM.

• Для OLTP: Настроить параметры дискового ввода-вывода, оптимизировать autovacuum, снизить contention.

Отчет подготовлен на основе анализа конфигурации системы и метрик производительности для двух типов нагрузки: OLAP (аналитическая) и OLTP (транзакционная).

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

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

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

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

• Эксперимент OLAP: 2026-01-20 12:01 — 2026-01-20 13:51

• Эксперимент OLTP: 2026-01-20 15:27 — 2026-01-20 17:13

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

• Устройство vdd (100 ГБ) через LVM-том vg_data-LG_data, смонтированное в /data

• Дополнительные устройства: vdc для WAL (/wal), vdb для логов (/log)

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

• Четко разделенные сценарии: OLAP (аналитический) и OLTP (транзакционный)

Отчет по типу нагрузки OLAP

Критические проблемы производительности по файловой системе /data

• Очень высокая корреляция (0.8021) между ожиданием процессором IO (wa) и загрузкой диска (util), что указывает на то, что процессы не могут работать из-за ожидания диска

• 100% наблюдений показывают загрузку устройства выше 50%

• 100% наблюдений имеют отклик на чтение и запись свыше 5 мс⚠️

• 100% наблюдений имеют глубину очереди запросов свыше 1⚠️

• Критически низкий Shared buffers HIT RATIO (57.72%)⚠️

Анализ корреляций и паттернов нагрузки по файловой системе /data

• Очень высокая корреляция (0.9095) между объемом памяти для буферов и количеством операций чтения с диска

• Очень высокая корреляция (0.8283) между объемом памяти для кэширования и количеством операций чтения с диска

• Очень высокая корреляция (0.9303) операционной скорости с прочитанными блоками

• Очень высокая корреляция (0.9905) операционной скорости с записанными блоками

• Высокая корреляция (0.9829) между shared_blks_hit и shared_blks_read

Узкие места IO по файловой системе /data

• Производительность ограничена пропускной способностью диска (корреляция скорости с MB/s = 0.9828)

• Высокая задержка операций чтения (r_await от 9 до 14 мс)⚠️

• Высокая средняя длина очереди запросов (aqu_sz от 31 до 47)⚠️

Характерные паттерны показателей IO для OLAP

• Загрузка диска (util): от 83% до 95%

• Задержка чтения (r_await): от 9 до 14 мс

• Задержка записи (w_await): от 6 до 7 мс

• IOPS: от 3,366 до 3,825 операций/сек

• Пропускная способность (MB/s): от 95 до 180 МБ/сек

• Процент времени ожидания CPU (wa): от 16% до 31%

• Отношение прочитанных блоков к измененным: 156.73:1

Отчет по типу нагрузки OLTP

Критические проблемы производительности по файловой системе /data

• Очень высокая корреляция (0.9526) между ожиданием процессором IO (wa) и загрузкой диска (util)

• 100% наблюдений показывают загрузку устройства на уровне 100%

• 100% наблюдений имеют глубину очереди запросов свыше 1

• Возрастающее количество процессов в состоянии uninterruptible sleep (коэффициент детерминации R² = 0.85)

Анализ корреляций и паттернов нагрузки по файловой системе /data

• Очень высокая корреляция (0.8728) между объемом памяти для буферов и количеством операций записи на диск

• Очень высокая корреляция (0.8637) между объемом памяти для буферов и объемом записи на диск

• Очень высокая корреляция (0.9545) между объемом памяти для кэширования и объемом чтения с диска

• Высокая корреляция (0.8937) операционной скорости с прочитанными блоками

• Низкая корреляция (0.0904) между shared_blks_hit и shared_blks_read

Узкие места IO по файловой системе /data

• Производительность не ограничена IO (отрицательная корреляция скорости с IOPS и MB/s)

• Проблемы могут быть связаны с CPU, блокировками или памятью

• Высокий процент времени ожидания CPU (wa) при относительно низких задержках диска

Характерные паттерны показателей IO для OLTP

• Загрузка диска (util): постоянная 100%

• Низкая задержка чтения (r_await): от 2 до 4 мс

• Низкая задержка записи (w_await): постоянная 2 мс

• IOPS: от 3,961 до 4,205 операций/сек

• Пропускная способность (MB/s): от 33 до 35 МБ/сек

• Процент времени ожидания CPU (wa): от 51% до 69%

• Отношение прочитанных блоков к измененным: 33.92:1

• Shared buffers HIT RATIO: 90.74%

Сравнение характерных паттернов для типов нагрузки OLAP и OLTP

Различия в загрузке диска:

• OLAP: загрузка варьируется от 83% до 95%

• OLTP: постоянная загрузка на уровне 100%

Различия в задержках операций:

• OLAP: высокие задержки чтения (9-14 мс), умеренные задержки записи (6-7 мс)

• OLTP: низкие задержки чтения (2-4 мс), очень низкие задержки записи (2 мс)

Различия в пропускной способности:

• OLAP: высокая пропускная способность (95-180 МБ/сек)

• OLTP: низкая пропускная способность (33-35 МБ/сек)

Различия в характере операций:

• OLAP: преобладание операций чтения (соотношение read/write = 156.73:1)

• OLTP: более сбалансированное соотношение операций (соотношение read/write = 33.92:1)

Различия в эффективности кэширования:

• OLAP: низкий hit ratio (57.72%), кэширование связано с большим чтением с диска

• OLTP: высокий hit ratio (90.74%), кэширование практически не влияет на дисковую нагрузку

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

Тип нагрузки существенно влияет на характер и производительность операций ввода-вывода:

Паттерны доступа к диску:

1. OLAP демонстрирует последовательный доступ с высокой пропускной способностью

2. OLTP показывает случайный доступ с низкой пропускной способностью, но высокой частотой операций

Узкие места производительности:

1. Для OLAP основным ограничением является пропускная способность диска

2. Для OLTP узкие места находятся не в подсистеме IO, а вероятно в CPU, блокировках или других ресурсах

Эффективность использования памяти:

1. OLAP неэффективно использует память для кэширования, что приводит к высокой дисковой нагрузке

2. OLTP эффективно использует кэширование, но это не устраняет проблему высокой загрузки CPU

Характер нагрузки на диск:

1. OLAP создает нагрузку на диск с высокой пропускной способностью и высокими задержками

2. OLTP создает интенсивную нагрузку с низкими задержками, но невысокой пропускной способностью

Влияние на систему:

1. Оба типа нагрузки приводят к высокой загрузке диска и значительному времени ожидания CPU

2. Различные паттерны нагрузки требуют разных подходов к мониторингу и анализу производительности

Послесловие

Проведенный анализ наглядно демонстрирует, что даже на идентичном оборудовании OLAP и OLTP нагрузки создают принципиально разные паттерны нагрузки на диск, память и CPU. Понимание этих различий — первый шаг к целевой оптимизации конфигурации и архитектуры.