От дисковых ожиданий к кэшированию: как shared_buffers меняет игру.
Данный отчёт суммирует результаты сравнительного нагрузочного тестирования СУБД PostgreSQL с использованием комплекса pg_expecto 5.1. Целью экспериментов была объективная оценка влияния ключевого параметра памяти — shared_buffers — на производительность базы данных и метрики инфраструктуры, в первую очередь подсистемы ввода-вывода. Тестирование имитировало смешанную OLAP-нагрузку с преобладанием операций чтения на идентичных конфигурациях, где единственной переменной был размер буферного кэша (1 ГБ в первом эксперименте и 4 ГБ во втором).
Глоссарий терминов | Postgres DBA | Дзен
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Задача
Провести сравнительный анализ влияния размера shared_buffers на производительность СУБД PostgreSQL и подсистемы IO для версии pg_expecto 5.1.
Важное изменение в версии 5.1⚠️
Изменены тестовые сценарии scenario-2 и scenario-3.
scenario-2 (INSERT)
scenario-3 (UPDATE)
scenario-1 (SELECT) - без изменений
Веса тестовых сценариев и параметры нагрузочного тестирования
# Веса сценариев по умолчанию
scenario1 = 0.7
scenario2 = 0.2
scenario3 = 0.1
Измененные параметры операционной системы для оптимизации⚠️
1️⃣Общие параметры производительности
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
2️⃣Параметры IO-планировщика
[mq-deadline] kyber bfq none
3️⃣Настройки кэширования и буферизации
vm.vfs_cache_pressure = 50
4️⃣Оптимизация параметров файловой системы
/dev/mapper/vg_data-LG_data on /data type ext4 (rw,noatime,nodiratime)
5️⃣Изменение размера буферов для операций с блочными устройствами
read_ahead_kb = 256
Нагрузка на СУБД в ходе экспериментов
Эксперимент-1(shared_buffers = 1GB)
Эксперимент-2(shared_buffers = 4GB)
Корреляционный анализ производительности и ожиданий СУБД
Операционная скорость
Среднее увеличение операционной скорости в Эксперименте-2 (shared_buffers=4GB) составило 70.76%
Ожидания СУБД
Среднее уменьшение ожиданий в Эксперименте-2 (shared_buffers=4GB) составило 12.97%
Производительность подсистемы IO
IOPS (Производительность IO)
Среднее увеличение производительности IO в Эксперименте-2 (shared_buffers=4GB) составило 4.28%
MB/s (Пропускная способность IO)
Среднее уменьшение пропускной способности IO в Эксперименте-2 (shared_buffers=4GB) составило 5.23%
Сравнительный анализ производительности и ожиданий СУБД и метрик vmstat
1.summary.txt - показатели производительности СУБД и vmstat в ходе эксперимента-1
2.summary.txt - показатели производительности СУБД и vmstat в ходе эксперимента-2
Проанализируй данные по метрикам производительности и ожиданий СУБД , метрикам инфраструктуры vmstat.
Подготовь сравнительный итоговый отчет по результатам анализа производительности СУБД и инфраструктуры.
Подготовь итоговый вывод о влиянии значения параметра shared_buffers на производительности СУБД и инфраструктуры для данного характера нагрузки и заданных параметров СУБД и операционной системы.
Исключи из отчета рекомендации по оптимизации.
Для построения отчета используй списки , а не таблицы.
Сравнительный анализ производительности СУБД и инфраструктуры при изменении параметра shared_buffers
1. Общие характеристики экспериментов
Характер нагрузки: OLAP-сценарий (чтение преобладает над записью, соотношение read/write >1.5)
Период тестирования: ~2 часа в каждом эксперименте
Параметры инфраструктуры:
- CPU: 8 ядер
- RAM: 7.7 GB
- Диски: отдельные разделы для данных (100GB) и WAL (50GB)
- Настройки СУБД: одинаковые в обоих экспериментах, кроме shared_buffers
- Тестовая нагрузка: идентичная последовательность роста нагрузки (LOAD от 5 до 22)
2. Сравнительные показатели производительности СУБД
Эксперимент-1 (shared_buffers = 1GB):
Операционная скорость (SPEED):
- Минимум: 318 667
- Максимум: 374 484
- Угол наклона регрессии: +42.56 (положительный рост)
Ожидания СУБД (WAITINGS):
- Минимум: 31 225
- Максимум: 86 203
- Угол наклона регрессии: +43.54
- Корреляция SPEED-WAITINGS: +0.94 (сильная прямая зависимость)
Эксперимент-2 (shared_buffers = 4GB):
Операционная скорость (SPEED):
- Минимум: 444 586
- Максимум: 611 850
- Угол наклона регрессии: -23.65 (отрицательный тренд)
Ожидания СУБД (WAITINGS):
- Минимум: 25 052
- Максимум: 78 773
- Угол наклона регрессии: +43.62
- Корреляция SPEED-WAITINGS: -0.64 (обратная зависимость)
3. Анализ ожиданий (Wait Events)
Общие характеристики:
- В обоих экспериментах доминируют ожидания типа IO (99% и 98.9% соответственно)
- Основное событие ожидания: DataFileRead (чтение данных с диска)
Изменения при увеличении shared_buffers:
- Общее количество ожиданий IO снизилось с 5 101 704 до 4 509 006 (-11.6%)
- Доля операций scenario1 в общих ожиданиях IO выросла с 73.03% до 78.74%
- Доля операций scenario2 в общих ожиданиях IO снизилась с 17.13% до 13.48%
4. Сравнительный анализ метрик инфраструктуры (VMSTAT)
Показатели I/O подсистемы:
Корреляция IO-wa: осталась критически высокой в обоих случаях (0.9407 и 0.9393)
Корреляция IO-b: осталась критически высокой в обоих случаях (0.9761 и 0.9757)
Процессы в состоянии D (uninterruptible sleep):
- Эксперимент-1: 56.48% наблюдений превышали количество ядер CPU
- Эксперимент-2: 42.99% наблюдений превышали количество ядер CPU
Использование памяти:
- Свободная RAM: в обоих экспериментах менее 5% в 100% наблюдений
- Свопинг: отсутствует в обоих экспериментах
Показатели CPU:
- Нагрузка CPU: в пределах нормы в обоих экспериментах
- Очередь выполнения (procs_r): в пределах нормы
5. Влияние shared_buffers на ключевые взаимосвязи
Зависимость производительности от операций чтения:
- Эксперимент-1: Корреляция speed - shared_blks_read = 0.9958 (очень сильная)
- Эксперимент-2: Корреляция speed - shared_blks_read = 0.9691 (сильная, но снизилась)
Зависимость производительности от операций записи:
- Эксперимент-1: Корреляция speed - shared_blks_write = 0.7805 (сильная)
- Эксперимент-2: Корреляция speed - shared_blks_write = -0.9178 (сильная обратная)
6. Итоговый вывод о влиянии shared_buffers
Для производительности СУБД:
- Увеличение shared_buffers с 1GB до 4GB привело к значительному росту операционной скорости (до +63.4% в пике)
- Минимальная производительность улучшилась на 39.5%
- Изменилась динамика производительности: с положительного тренда на отрицательный при увеличении нагрузки
- Преобразовало зависимость между скоростью и ожиданиями с прямой на обратную
Для инфраструктуры:
- Снизило процент времени, когда процессы в состоянии D превышали количество ядер CPU (с 56.48% до 42.99%)
- Не устранило проблему высокой корреляции между ожиданиями IO и метриками wa/b
- Не повлияло на использование оперативной памяти (в обоих случаях активно используется)
Для характера нагрузки (OLAP-сценарий):
- Уменьшило общее количество операций чтения с диска на 11.6%
- Изменило распределение ожиданий между основными запросами
- Преобразовало зависимость производительности от операций записи с прямой на обратную
Общий эффект
Параметр shared_buffers = 4GB показал существенно лучшие результаты для данной конфигурации и характера нагрузки, позволив эффективнее кэшировать данные в памяти и снизив зависимость производительности от операций ввода-вывода, что особенно критично для OLAP-сценариев с преобладанием операций чтения.
Анализ производительности IO для файловой системы /data
settings.txt - конфигурация СУБД и инфраструктуры
1.io_performance.txt - показатели производительности IO в ходе эксперимента-1
2.io_performance.txt - показатели производительности IO в ходе эксперимента-2
Подготовь сравнительный отчет по результатам анализа производительности подсистемы IO в ходе экспериментов для дисковых устройств, используемых для файловой системы /data
**Общая характеристика системы**
- Тип нагрузки
Состав отчета по файловой системе:
**Критические проблемы производительности по файловой системе**
**Анализ корреляций и паттернов нагрузки по файловой системе**
**Диагностика узких мест IO по файловой системе**
- r_await(ms)
- w_await(ms)
- aqu_sz
- proc_b
- cpu_wa(%)
- Корреляция speed с IOPS
- Корреляция speed с пропускной способностью (MB/s)
- Вывод по диагностике узких мест IO
Подготовь итоговый вывод о влиянии значения параметра shared_buffers на производительности подсистемы IOдля данного характера нагрузки и заданных параметров СУБД и операционной системы.
Исключи из отчета рекомендации по оптимизации.
Для построения отчета используй списки , а не таблицы.
Сравнительный отчет по производительности подсистемы IO для файловой системы /data
Общая характеристика системы
- Тип нагрузки: Смешанная OLAP/OLTP нагрузка с преобладанием операций чтения (соотношение read/write >1.5).
- Оборудование:
- CPU: 8 ядер (x86_64)
- RAM: 7.5 ГБ
- Диск для /data: vdd (100 ГБ, логический том vg_data-LG_data)
- Конфигурация СУБД (ключевые параметры):
- shared_buffers: 1 ГБ (Эксперимент-1) / 4 ГБ (Эксперимент-2)
- effective_cache_size: 6 ГБ
- work_mem: 32 МБ
- maintenance_work_mem: 512 МБ
- max_parallel_workers_per_gather: 1
- effective_io_concurrency: 300
- commit_delay: 1000
Критические проблемы производительности по файловой системе /data
- Высокая загрузка диска (utilization = 100% в ≥50% наблюдений в обоих экспериментах).
- Высокое время ожидания IO процессором (cpu_wa >10% в ≥50% наблюдений).
- Большое количество процессов в состоянии ожидания IO (proc_b превышает количество ядер CPU в ≥25% наблюдений).
- Постоянно высокая длина очереди запросов (aqu_sz >1 в ≥50% наблюдений).
Анализ корреляций и паттернов нагрузки по файловой системе /data
Корреляция cpu_wa — util:
- Очень высокая (≈0.90–0.94) в обоих экспериментах → процессы блокируются из-за ожидания диска.
Корреляция speed — shared_blks_read:
- Очень высокая (≈0.97–0.99) → производительность напрямую зависит от чтения с диска.
Корреляция shared_blks_hit — shared_blks_read:
- Очень высокая (≈0.92–0.98) → кэширование сопровождается активным чтением с диска, что характерно для смешанной OLTP+OLAP нагрузки.
Соотношение read/write:
- Эксперимент-1: ≈4.37 (очень высокое)
- Эксперимент-2: ≈2.91 (высокое)
- Преобладание операций чтения подтверждает OLAP-компоненту нагрузки.
Диагностика узких мест IO по файловой системе /data
r_await (среднее время чтения):
- Эксперимент-1: 3–7 мс
- Эксперимент-2: 2–5 мс
- Улучшение при увеличении shared_buffers.
w_await (среднее время записи):
- Эксперимент-1: 2–5 мс
- Эксперимент-2: 2–14 мс
- В целом низкое, запись не является узким местом.
aqu_sz (средняя длина очереди):
- Стабильно >1 в обоих экспериментах → запросы накапливаются в очереди.
proc_b (процессы в uninterruptible sleep):
- Эксперимент-1: превышает количество ядер CPU в >50% наблюдений.
- Эксперимент-2: превышает количество ядер CPU в 25–50% наблюдений.
- Улучшение при увеличении shared_buffers.
cpu_wa (%):
- Эксперимент-1: 58–74%
- Эксперимент-2: 50–71%
- Снижение при увеличении shared_buffers.
Корреляция speed — IOPS:
- Эксперимент-1: положительная (≈0.95) → производительность зависит от IOPS (OLTP-паттерн).
- Эксперимент-2: отрицательная (≈ -0.70) → производительность не ограничена IOPS.
Корреляция speed — MB/s:
- Эксперимент-1: положительная (≈0.96) → производительность ограничена пропускной способностью (OLAP-паттерн).
- Эксперимент-2: отрицательная (≈ -0.87) → пропускная способность не является узким местом.
Вывод по диагностике узких мест IO:
- В Эксперименте-1 система ограничена как IOPS, так и пропускной способностью диска (смешанная нагрузка).
- В Эксперименте-2 зависимость от дисковых характеристик снижается, но высокая загрузка диска сохраняется.
Итоговый вывод о влиянии параметра shared_buffers на производительность подсистемы IO
Увеличение shared_buffers с 1 ГБ до 4 ГБ привело к:
- Снижению зависимости производительности от дисковых операций (ослабление корреляций speed — IOPS и speed — MB/s).
- Улучшению времени отклика на операции чтения (r_await).
- Уменьшению количества процессов, ожидающих IO (proc_b).
- Снижению времени ожидания IO процессором (cpu_wa).
Однако диск (vdd) остаётся узким местом:
- utilization сохраняется на уровне 100%.
- Очередь запросов (aqu_sz) остаётся высокой.
При увеличении shared_buffers характер нагрузки смещается от явной зависимости от дисковых характеристик (OLTP/OLAP) к сценарию, где узкие места могут быть связаны с другими ресурсами (CPU, блокировки, параметры параллелизма).
Итог
Увеличение параметра shared_buffers с 1 ГБ до 4 ГБ привело к значительному качественному изменению в поведении системы под нагрузкой.
- Для СУБД: Производительность (операционная скорость) выросла на 39.5–63.4%, а зависимость между скоростью и ожиданиями ввода-вывода трансформировалась с прямой на обратную. Количество обращений к диску для чтения сократилось на 11.6%.
- Для инфраструктуры: Нагрузка на подсистему ввода-вывода осталась критически высокой, однако уменьшилось количество процессов, блокированных в ожидании диска, и улучшилось среднее время отклика на операции чтения (r_await).
- Общий эффект: Более крупный буферный кэш эффективнее обслуживал OLAP-составляющую нагрузки, кэшируя данные и снижая давление на диск. Это сместило узкие места системы: если при shared_buffers=1GB производительность жёстко зависела от характеристик диска (IOPS и пропускной способности), то при shared_buffers=4GB эта зависимость ослабла, хотя диск по-прежнему оставался полностью загруженным.