PG_EXPECTO 5.2 : OLAP - влияние vm.dirty_ratio/vm.dirty_background_ratio на производительность СУБД PostgreSQL.

OLAP-система в тисках дискового бутылочного горлышка.

Уменьшение параметров грязной памяти — интуитивно понятный шаг для снижения IO-нагрузки. Но что, если он лишь меняет "симптомы", не затрагивая корень проблемы? Итоги нагрузочного тестирования бросают вызов упрощенным подходам к тюнингу.

В статье представлены результаты сравнительного анализа производительности СУБД PostgreSQL под OLAP-нагрузкой. Исследование фокусируется на оценке влияния ключевых параметров виртуальной памяти ядра Linux — vm.dirty_ratio и vm.dirty_background_ratio — на поведение системы, паттерны ожиданий и итоговую эффективность.

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

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

Задача

Проанализировать влияние уменьшения значения vm.dirty_ratio/vm.dirty_background_ratio на производительность СУБД при имитации типа нагрузки "OLAP" в ходе нагрузочного тестирования.

Параметры инфраструктуры

sysctl vm.dirty_expire_centisecs

sysctl vm.dirty_ratio

sysctl vm.dirty_background_ratio

sysctl vm.vfs_cache_pressure

cat /sys/block/vdd/queue/read_ahead_kb

vm.dirty_expire_centisecs = 3000

vm.dirty_ratio = 30

vm.dirty_background_ratio = 10

vm.vfs_cache_pressure = 100

4096

Базовые значения производительности СУБД и инфраструктуры

Изменение параметров vm.dirty*

# sysctl -w vm.dirty_ratio=10

vm.dirty_ratio = 10

# sysctl -w vm.dirty_background_ratio=5

vm.dirty_background_ratio = 5

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

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

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

Среднее снижение операционной скорости, при изменении параметров vm.dirty_ratio/vm.dirty_background_ratio составило 26.06%

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

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

Среднее увеличение ожиданий СУБД , при изменении параметров vm.dirty_ratio/vm.dirty_background_ratio составило 10.45%

Сравнительный отчет по нагрузочному тестированию: BASELINE vs vm.dirty*

settings.txt - конфигурация СУБД и ОС

summary_OLAP.txt - базовые показатели производительности и ожиданий СУБД и метрик vmstat/iostat для типа нагрузки OLAP

summary.OLAP.vm.dirty.txt - показатели производительности и ожиданий СУБД и метрик vmstat/iostat для типа нагрузки OLAP и новых значениях vm.dirty_ratio/vm.dirty_background_ratio

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

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

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

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

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

**Итоговый вывод о характерных паттернах производительности и ожиданий СУБД и показателей vmstat/iostat для экспериментов**

**Итоговый вывод о влиянии уменьшения значения vm.dirty_ratio/vm.dirty_background_ratio на производительность СУБД и паттерны производительности, ожиданий и инфраструктуры**

Исключи рекомендации по оптимизации.

Сформируй отчет используя списки, без использования таблиц.

1. ОБЩАЯ ХАРАКТЕРИСТИКА СИСТЕМЫ В ЭКСПЕРИМЕНТАХ

• Оборудование идентично в обоих экспериментах:
• 8 ядер CPU (Intel Xeon)
• 8 ГБ RAM
• Раздельные дисковые подсистемы: data (100 ГБ), wal (50 ГБ), log (30 ГБ)
• Тип нагрузки: OLAP (аналитические запросы)
• Подтверждается высоким отношением прочитанных блоков к измененным (>150)
• Низкий процент попаданий в shared buffers (~58-59%)
• Доминирование операций чтения с диска

2. СРАВНИТЕЛЬНЫЕ ПАТТЕРНЫ ОЖИДАНИЙ И ПРОИЗВОДИТЕЛЬНОСТИ СУБД

• Эксперимент-1 (базовые настройки):
• Операционная скорость возрастала с углом наклона 40.51
• Общие ожидания возрастали с углом наклона 44.12
• Высокая корреляция между скоростью и ожиданиями (0.75)
• Основные типы ожиданий: IO (корреляция 0.78 с общими ожиданиями) и IPC (корреляция 0.99)
• 80% ожиданий IO вызывались операциями DataFileRead
• Эксперимент-2 (уменьшенные vm.dirty_*):
• Скорость возрастала медленнее (угол 38.94)
• Ожидания возрастали медленнее (угол 42.39)
• Корреляция скорости и ожиданий снизилась до 0.65
• Ожидания IO стали отрицательно коррелировать с общими ожиданиями (-0.85)
• IPC-ожидания остались строго коррелированными с общими ожиданиями (1.0)
• Практически исчезли ожидания IO из Pareto-диаграммы

3. СРАВНИТЕЛЬНЫЕ ПАТТЕРНЫ ПОКАЗАТЕЛЕЙ VMSTAT/IOSTAT

• Эксперимент-1:
• Очень высокая корреляция IO-ожиданий СУБД с wa (I/O wait) в vmstat (0.8748)
• Высокая корреляция LWLock-ожиданий с us и sy (0.9277 и 0.8802)
• Умеренная корреляция IO с bi/bo (0.44 и 0.36)
• 100% наблюдений с wa > 10%
• 100% наблюдений со свободной RAM < 5%
• Эксперимент-2:
• Корреляция IO-ожиданий с wa стала отрицательной (-0.6128)
• Корреляция LWLock с us/sy осталась очень высокой (0.9609 и 0.9288)
• Корреляция IO с bi/bo стала отрицательной или околонулевой
• Сохранилось 100% наблюдений с wa > 10%
• Сохранилось 100% наблюдений со свободной RAM < 5%

4. ИТОГОВЫЙ ВЫВОД О ХАРАКТЕРНЫХ ПАТТЕРНАХ ПРОИЗВОДИТЕЛЬНОСТИ И ОЖИДАНИЙ СУБД И ПОКАЗАТЕЛЕЙ VMSTAT/IOSTAT ДЛЯ ЭКСПЕРИМЕНТОВ

• В обоих экспериментах система демонстрирует паттерны, характерные для OLAP-нагрузки:
• Система ограничена производительностью дисков (чтение и запись)
• Низкая эффективность кэширования (hit ratio ~58-59%)
• Высокое время ожидания ввода-вывода (wa > 10% в 100% наблюдений)
• Потребление почти всей доступной оперативной памяти
• Ключевое различие: в Эксперименте-1 система испытывала сильные IO-ожидания, которые были тесно связаны с общими ожиданиями, тогда как в Эксперименте-2 IO-ожидания перестали быть доминирующим фактором
• В обоих случаях наблюдаются очень высокие корреляции IPC-ожиданий (BufferIo) с общими ожиданиями
• Корреляция переключений контекста (cs) с прерываниями (in) осталась экстремально высокой в обоих экспериментах (~0.96)

5. ИТОГОВЫЙ ВЫВОД О ВЛИЯНИИ УМЕНЬШЕНИЯ ЗНАЧЕНИЯ VM.DIRTY_RATIO/VM.DIRTY_BACKGROUND_RATIO НА ПРОИЗВОДИТЕЛЬНОСТЬ СУБД И ПАТТЕРНЫ ПРОИЗВОДИТЕЛЬНОСТИ, ОЖИДАНИЙ И ИНФРАСТРУКТУРЫ

• Уменьшение параметров vm.dirty_ratio/vm.dirty_background_ratio привело к изменению паттернов ожиданий:
• IO-ожидания перестали коррелировать с общими ожиданиями (корреляция стала отрицательной)
• IPC-ожидания (буферные операции) стали практически единственным источником ожиданий
• Производительность в Эксперименте-2 росла медленнее (меньший угол наклона скорости)
• Система стала менее зависимой от дисковых операций ввода-вывода как источника ожиданий
• Паттерны использования ресурсов ОС изменились:
• Исчезла корреляция между IO-ожиданиями СУБД и метриками bi/bo в vmstat
• Сохранилась высокая нагрузка на CPU (корреляция LWLock с us/sy)
• Изменение параметров виртуальной памяти не устранило фундаментальные ограничения системы:
• Высокое время ожидания ввода-вывода (wa) сохранилось на прежнем уровне
• Проблема нехватки оперативной памяти для кэширования не была решена
• Система осталась ограниченной производительностью дисковой подсистемы.

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

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

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

Среднее снижение IOPS , при изменении параметров vm.dirty_ratio/vm.dirty_background_ratio составило 3.71%

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

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

Среднее снижение пропускной способности , при изменении параметров vm.dirty_ratio/vm.dirty_background_ratio составило 4.22%

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

io_performance_OLAP.txt - показатели производительности IO при базовых значениях (baseline)

io_performance.OLAP.vm.dirty.txt - показатели производительности IO при уменьшении vm.dirty_ratio/vm.dirty_background_ratio

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

для дисковых устройств, используемых для файловых систем /data

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

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

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

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

**Сравниттельный отчет по производительности IO **

- Критические проблемы производительности по файловой системе для Эксперимента-1 и Эксперимента-2

- Анализ корреляций и паттернов нагрузки по файловой системе для Эксперимента-1 и Эксперимента-2

- Узкие места IO по файловой системе для Эксперимента-1 и Эксперимента-2

- Характерные паттерны показателей IO для Эксперимента-1 и Эксперимента-2

**Сравнение характерных паттернов для Эксперимента-1 и Эксперимента-2**

**Итоговый вывод о влиянии уменьшения значения vm.dirty_ratio/vm.dirty_background_ratio на производительность IO**

Исключи рекомендации по оптимизации.

Сформируй отчет используя списки, без использования таблиц.

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

• Период анализа:
• Эксперимент-1 (baseline): 2026-01-20 12:01 — 2026-01-20 13:51
• Эксперимент-2 (настройки vm.dirty): 2026-01-21 09:59 — 2026-01-21 11:48
• Основное устройство хранения: vdd
• Тип нагрузки: Ярко выраженный OLAP-сценарий (аналитическая обработка данных). Подтверждается высоким соотношением операций чтения к записи и паттерном высокой корреляции пропускной способности (MB/s) с операционной скоростью.

Сравнительный отчет по производительности IO

Критические проблемы производительности (общие для обоих экспериментов):

• Устройство постоянно перегружено. Наблюдается 100% случаев, когда загрузка (utilization) превышает 50%, достигая значений 90-97%.
• Высокое время отклика диска. В 100% случаев задержки на чтение (r_await) и запись (w_await) превышают 5 мс (достигая 10-18 мс и 6-7 мс соответственно).
• Хроническая очередь запросов. Средняя длина очереди (aqu_sz) постоянно больше 1, что указывает на скопление невыполненных IO-запросов.
• Процессоры простаивают в ожидании IO. В 100% наблюдений время ожидания процессора на IO (cpu_wa) превышает 10%, достигая 16-32%.
• Крайне низкая эффективность кэширования. Shared buffers HIT RATIO составляет около 58-59%, что указывает на неэффективное использование оперативной памяти для снижения нагрузки на диск.

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

• Эксперимент-1 (baseline):
• Сильная положительная корреляция между ожиданием процессора (wa) и загрузкой диска (util) (0.8021) подтверждает, что процессоры idle из-за медленного диска.
• Очень высокая корреляция между операционной скоростью и пропускной способностью диска (MB/s) (0.9828) — классический признак того, что производительность ограничена пропускной способностью накопителя.
• Корреляция скорости с IOPS отрицательная (-0.1472), что типично для OLAP с большими последовательными чтениями/записями, а не множеством мелких операций.
• Высокие корреляции объема кэша/буферов с операциями чтения (r/s) указывают, что память неэффективно смягчает нагрузку на диск.
• Эксперимент-2 (настройки vm.dirty):
• Корреляция wa и util стала еще выше (0.9229), что усиливает вывод о процессорах, ожидающих диск.
• Корреляция скорости с MB/s осталась очень высокой (0.7574), но немного снизилась по сравнению с baseline.
• Корреляция скорости с IOPS стала слабо положительной (0.6100), что может указывать на небольшие изменения в паттерне запросов.
• Соотношение прочитанных блоков к измененным увеличилось с ~157 до ~202, что указывает на еще более выраженный перекос в сторону операций чтения.

Узкие места IO:

• Основное узкое место в обоих экспериментах: Пропускная способность (throughput) дискового устройства vdd. Система упирается в пределы скорости последовательного чтения/записи (MB/s).
• Вторичное узкое место: Высокая задержка (latency) дисковых операций, что приводит к длинным очередям и простою процессоров.

Характерные паттерны показателей IO:

• Эксперимент-1 (baseline):
• Utilization: стабильно на уровне 90-95%.
• r_await: колеблется в диапазоне 9-14 мс.
• IOPS: относительно стабилен в районе ~3500-3800.
• MB/s: постепенно возрастает с ~100 до ~180 MB/s к концу периода.
• aqu_sz: высокая и изменчивая (31-47).
• Эксперимент-2 (настройки vm.dirty):
• Utilization: стабильно на уровне 97% в первой половине, затем постепенно снижается до 83%.
• r_await: стабильно высокий (16-18 мс) в первой половине, затем снижается до 12-14 мс.
• IOPS: более стабилен, в основном в диапазоне ~3380-3600.
• MB/s: начинается со ~114 MB/s, демонстрирует более плавный рост до ~184 MB/s.
• aqu_sz: стабильно высокий (54-56 в первой половине, затем снижается до 41-44).

Сравнение характерных паттернов для Эксперимента-1 и Эксперимента-2:

• Во втором эксперименте наблюдалась более стабильная, но изначально более высокая загрузка диска (utilization ~97% vs ~90-95%).
• Средняя длина очереди (aqu_sz) в начале второго эксперимента была значительно выше (54-56 vs 31-47), что указывает на накопление запросов.
• Пропускная способность (MB/s) во втором эксперименте начиналась с более высокого уровня и демонстрировала более плавную динамику роста.
• Время отклика на чтение (r_await) во втором эксперименте в первой половине было стабильно выше (17-18 мс vs 9-14 мс), но снизилось к концу теста.
• Показатель cpu_wa в обоих случаях оставался критически высоким (>16%), что подтверждает неизменность фундаментальной проблемы.

Итоговый вывод о влиянии уменьшения vm.dirty_ratio/vm.dirty_background_ratio на производительность IO

Уменьшение параметров vm.dirty_ratio и vm.dirty_background_ratio привело к изменению паттерна нагрузки, но не решило ключевых проблем производительности подсистемы IO. Наблюдаемые изменения (более стабильные и изначально более высокие показатели утилизации и длины очереди, более плавный рост пропускной способности) указывают на более агрессивную политику сброса "грязных" страниц памяти на диск. Это, однако, не снизило ни задержек дисковых операций, ни времени ожидания процессоров, ни общей перегруженности накопителя. Основное узкое место — ограниченная пропускная способность диска — осталось неизменным, что и определяет общую производительность системы в условиях данной OLAP-нагрузки.

Результат:

❌Откат изменения - гипотеза не подтверждена.

Послесловие

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