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

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

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

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

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

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

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

Задача

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

Тестовый сценарий для имитации нагрузки 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Вчера

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

-- OLTP -- scenario1.sql -- 5.2 CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE  test_rec record ;  current_aid bigint ; BEGIN -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! -- ТОЛЬКО ДЛЯ scale = 685 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! current_aid = floor(random() * (68500000 - 1 + 1)) + 1 ; select acc.abalance into test_rec from pgbench_accounts acc…
Postgres DBAВчера

Тестовые сценарии : 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

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

OLAP

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

OLTP

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

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

-5

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

settings.txt - конфигурация СУБД и ОС
summary_OLAP.txt - показатели производительности и ожиданий СУБД и метрик vmstat/iostat для типа нагрузки OLAP
summary_OLTP.txt- показатели производительности и ожиданий СУБД и метрик vmstat/iostat для типа нагрузки OLTP
Подготовь сравнительный отчет по нагрузочного тестирования для сценариев имитации OLAP(Эксперимент-1) и OLTP(Эксперимент-2)
Состав отчета :
**Общая характеристика системы в экспериментах**
- Тип нагрузки
**Сравнительные паттерны ожиданий и производительности СУБД**
**Сравнительные паттерны показателей vmstat/iostat**
**Итоговый вывод о характерных паттернах производительности и ожиданий СУБД и показателей vmstat/iostat для типов нагрузки OLAP и 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

-6
-7

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

Подготовь отчет по результатам анализа производительности подсистемы IO
для дисковых устройств, используемых для файловых систем /data
**Общая характеристика системы**
- Период анализа
- Основные устройства хранения
- Тип нагрузки
**Отчет по типу нагрузки OLAP**
- Критические проблемы производительности по файловой системе
- Анализ корреляций и паттернов нагрузки по файловой системе**
- Узкие места IO по файловой системе**
- Характерные паттерны показателей IO для
**Отчет по типу нагрузки OLTP**
- Критические проблемы производительности по файловой системе
- Анализ корреляций и паттернов нагрузки по файловой системе**
- Узкие места IO по файловой системе**
- Характерные паттерны показателей IO для
**Сравнение характерных паттернов для типоа нагрузки OLAP и OLTP**
**Итоговый вывод о влиянии типа нагрузки на производительность IO**
Исключи из отчета рекомендации по оптимизации.
Сформируй отчет , используя списки , без использования таблиц.

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

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

  • Эксперимент 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. Понимание этих различий — первый шаг к целевой оптимизации конфигурации и архитектуры.