Настоящее исследование осуществлено с применением инструментария pg_expecto, обеспечивающего строгую методологию репрезентативного нагрузочного тестирования. Данный инструмент позволил провести сравнительный анализ двух дискретных конфигураций СУБД PostgreSQL в контролируемых и идентичных условиях, моделирующих устойчивую OLAP-нагрузку. Ниже представлено краткое изложение методологии эксперимента, включая описание стенда, генерации нагрузочного паттерна и ключевых варьируемых параметров, что обеспечивает полную воспроизводимость и верифицируемость полученных результатов. Основной целью являлась эмпирическая проверка гипотезы о влиянии реконфигурации областей памяти (shared_buffers и work_mem) на комплексные показатели производительности системы.
Глоссарий терминов | Postgres DBA | Дзен
Теоретическая часть и рекомендация нейросети
📉 Гипотеза по уменьшению shared_buffers
Для данной OLAP-нагрузки и текущей конфигурации сервера снижение размера shared_buffers с 4 ГБ до 1-2 ГБ, с одновременным увеличением work_mem, приведет к росту общей производительности системы. Основная цель — не просто уменьшить кэш БД, а перенаправить высвободившуюся оперативную память на выполнение операций в памяти и ослабить нагрузку на подсистему ввода-вывода.
Параметры операционной системы
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 30
vm.dirty_background_ratio = 10
vm.vfs_cache_pressure = 100
vm.swappiness = 10
read_ahead_kb = 4096
Тестовые сценарии
Веса сценариев
scenario1 = 0.7
scenario2 = 0.2
scenario3 = 0.1
Нагрузка на СУБД в ходе экспериментов
Эксперимент-1: shared_buffers = 4GB + work_mem=32MB
work_mem
----------
32MB
shared_buffers
----------------
4GB
Эксперимент-2: shared_buffers = 2GB + work_mem=256MB
work_mem
----------
256MB
shared_buffers
----------------
2GB
Корреляционный анализ ожиданий
Операционная скорость
Среднее снижение операционной скорости при shared_buffers=2GB(work_mem=256MB) составило 38.38%.
Ожидания типа IO
Среднее увеличение ожиданий типа IO при shared_buffers=2GB(work_mem=256MB) составило 22.70%.
Ожидания типа IPC
Среднее снижение ожиданий типа IPC при shared_buffers=2GB(work_mem=256MB) составило 4.78%.
Производительность подсистемы IO(IOPS) для файловой системы /data
Среднее снижение IOPS при shared_buffers=2GB(work_mem=256MB) составило 3.29%.
Пропускная способность подсистемы IO(MB/s) для файловой системы /data
Среднее снижение пропускной способности(MB/s) при shared_buffers=2GB(work_mem=256MB) составило 313.74%.
Сводный отчет по влиянию параметров shared_buffers и work_mem на производительность инфраструктуры при OLAP-нагрузке
settings.txt – Конфигурация СУБД и инфраструктуры
4GB.vmstat.txt – Показатели vmstat – Эксперимент-1 shared_buffers=4GB work_mem=32MB
2GB.vmstat.txt – Показатели vmstat – Эксперимент-2 shared_buffers=2GB work_mem=256MB
Проанализируй показатели vmstat.
Подготовь комплексный(сводный) отчет по влиянию параметров shared_buffers/work_mem на производительность инфраструктуры для заданного характера нагрузки, имитирующего нагрузку OLAP.
Исключи из отчета рекомендации по оптимизации.
Для подготовки отчета используй списки, вместо таблиц.
1. Общие характеристики нагрузки
Нагрузка соответствует OLAP-сценарию (аналитические запросы, большие объёмы данных).
В обоих экспериментах наблюдался рост нагрузки (Load average увеличился с 5 до 22).
Параметры экспериментов:
- Эксперимент-1: shared_buffers = 4GB, work_mem = 32MB
- Эксперимент-2: shared_buffers = 2GB, work_mem = 256MB
2. Влияние на производительность ввода-вывода (I/O)
Эксперимент-1 (4GB / 32MB)
- Высокий I/O wait (wa): 100% наблюдений с wa > 10%.
- Корреляция ожиданий IO и записи (bo): высокая (0.6533), система ограничена производительностью записи на диск.
- Состояние процессов (b): слабая корреляция с ожиданиями IO (0.2611), количество процессов в состоянии непрерываемого сна не возрастает значительно.
- Отношение прочитанных блоков к изменённым: 177.98, подтверждение OLAP-нагрузки.
Эксперимент-2 (2GB / 256MB)
- Высокий I/O wait (wa): 97.27% наблюдений с wa > 10%.
- Корреляция ожиданий IO и записи (bo): высокая (0.6719), система также ограничена записью.
- Состояние процессов (b): очень высокая корреляция с ожиданиями IO (0.8774), процессы всё чаще переходят в состояние непрерываемого сна (ожидание диска).
- Отношение прочитанных блоков к изменённым: 268.01, нагрузка ещё более ориентирована на чтение.
Сравнение
- Уменьшение shared_buffers с 4GB до 2GB привело к усилению корреляции между ожиданием IO и блокированными процессами.
- В обоих случаях система ограничена производительностью IO, но во втором эксперименте дисковые ожидания сильнее влияют на состояние процессов.
3. Влияние на использование оперативной памяти (RAM)
Эксперимент-1 (4GB / 32MB)
- Свободная RAM: менее 5% в 100% наблюдений.
- Свопинг (swap in/out): используется незначительно (в 9.01% и 1.8% наблюдений соответственно).
Эксперимент-2 (2GB / 256MB)
- Свободная RAM: менее 5% в 100% наблюдений.
- Свопинг: не используется (0% наблюдений).
Сравнение
- Оба эксперимента показывают критически низкое количество свободной RAM.
- Свопинг практически отсутствует, что может указывать на эффективное использование файлового кэша ОС.
4. Влияние на эффективность кэширования (Shared buffers)
Эксперимент-1 (4GB / 32MB)
- Hit Ratio: 55.36% (критически низкий).
- Корреляция hit/read: очень высокая (0.9725), кэширование связано с большим чтением с диска.
Эксперимент-2 (2GB / 256MB)
- ⚠️Hit Ratio: 38.58% (ещё ниже, критически низкий).
- Корреляция hit/read: очень высокая (0.8698), аналогичная картина.
Сравнение
- ⚠️Уменьшение shared_buffers с 4GB до 2GB привело к снижению Hit Ratio на ~16.78%.
- ⚠️В обоих случаях кэширование недостаточно эффективно для данной нагрузки.
5. Влияние на загрузку центрального процессора (CPU)
Эксперимент-1 (4GB / 32MB)
- Корреляция LWLock и user time: очень высокая (0.9775).
- Корреляция LWLock и system time: очень высокая (0.9092).
- Очередь процессов (r): превышение числа ядер CPU в 16.22% наблюдений.
- System time (sy): не превышает 30% (все наблюдения).
Эксперимент-2 (2GB / 256MB)
- Корреляция LWLock и user time: очень высокая (0.8574).
- Корреляция LWLock и system time: высокая (0.6629).
- Очередь процессов (r): превышение числа ядер CPU в 3.64% наблюдений.
- System time (sy): не превышает 30% (все наблюдения).
Сравнение
- В Эксперименте-1 выше корреляция LWLock с системным временем, что может указывать на большее количество системных вызовов и переключений контекста.
- Очередь процессов (r) чаще превышает число ядер CPU в Эксперименте-1, но в обоих случаях это не является критичным.
6. Выводы по влиянию параметров
Уменьшение shared_buffers с 4GB до 2GB:
- Привело к снижению Hit Ratio (с 55.36% до 38.58%).
- Усилило корреляцию между ожиданием IO и блокированными процессами.
- Не вызвало существенных изменений в использовании свопинга и свободной RAM.
Увеличение work_mem с 32MB до 256MB:
- Не компенсировало снижение эффективности кэширования при уменьшении shared_buffers.
- Не привело к значительным изменениям в поведении CPU и очереди процессов.
- Общий характер нагрузки (OLAP) подтверждается высоким отношением чтения к записи и сильной зависимостью производительности от операций ввода-вывода.
Сводный отчет по влиянию параметров shared_buffers и work_mem на производительность подсистемы IO диска vdd для OLAP-нагрузки
settings.txt – Конфигурация СУБД и инфраструктуры
4GB.iostat.txt – Показатели iostat – Эксперимент-1 shared_buffers=4GB work_mem=32MB
2GB.vmstat.txt – Показатели iostat – Эксперимент-2 shared_buffers=2GB work_mem=256MB
Проанализируй показатели iostat.
Подготовь комплексный(сводный) отчет по влиянию параметров shared_buffers/work_mem на производительность подсистемы IO диска vdd для заданного характера нагрузки, имитирующего нагрузку OLAP.
Исключи из отчета рекомендации по оптимизации.
Для подготовки отчета используй списки, вместо таблиц.
1. Общая характеристика инфраструктуры и экспериментов
Диск данных: vdd (100 ГБ, LVM-том 99 ГБ, точка монтирования /data)
Конфигурация сервера:
- 8 CPU ядер
- 8 ГБ RAM
- Отдельные диски для WAL (/wal) и логов (/log)
Параметры сравнения:
- Эксперимент 1: shared_buffers = 4 ГБ, work_mem = 32 МБ
- Эксперимент 2: shared_buffers = 2 ГБ, work_mem = 256 МБ
- Продолжительность тестов: ≈110 минут каждый
2. Количественные показатели производительности подсистемы IO
2.1. Средние значения по экспериментам
Эксперимент 1 (shared_buffers=4GB, work_mem=32MB):
- Средняя утилизация диска: 90-94% → 80% (снижение к концу теста)
- Средний IOPS: ≈3500 операций/сек
- Средняя пропускная способность: ≈130 МБ/с → 180 МБ/с (рост к концу)
- Среднее время ожидания чтения: 9-14 мс
- Среднее время ожидания записи: 6-7 мс
- Средняя длина очереди: ≈43
- Средняя загрузка CPU на IO: ≈24%
Эксперимент 2 (shared_buffers=2GB, work_mem=256MB):
- Средняя утилизация диска: 93-97% (стабильно высокая)
- Средний IOPS: ≈3400 операций/сек
- Средняя пропускная способность: ≈139 МБ/с → 95-149 МБ/с (колебания)
- Среднее время ожидания чтения: 10-19 мс
- Среднее время ожидания записи: 7-10 мс
- Средняя длина очереди: ≈33 → 61 (рост)
- Средняя загрузка CPU на IO: ≈28% → 9% (снижение)
3. Качественные характеристики нагрузки
3.1. Характер ограничения производительности
Эксперимент 1:
- Тип ограничения: Пропускная способность диска
- Корреляция скорость-MB/s: Очень высокая (0.8191)
- Корреляция скорость-IOPS: Слабая (0.4128)
- Вывод: Производительность определяется объемом передаваемых данных
Эксперимент 2:
- Тип ограничения: Количество операций ввода-вывода
- Корреляция скорость-IOPS: Очень высокая (0.9256)
- Корреляция скорость-MB/s: Слабая (0.1674)
- Вывод: Нагрузка чувствительна к количеству IO операций
4. Влияние изменения параметров на поведение системы
4.1. Динамика изменения показателей во времени
Эксперимент 1 (shared_buffers=4GB):
- Утилизация диска снижается с 94% до 80% к концу теста
- Пропускная способность растет с 132 МБ/с до 180 МБ/с
- Загрузка CPU на IO снижается с 28% до 18%
- Стабильные показатели IOPS и времени отклика
Эксперимент 2 (shared_buffers=2GB):
- Высокая и стабильная утилизация диска (93-97%)
- Колебания пропускной способности (139 → 95 → 149 МБ/с)
- Значительный рост длины очереди (33 → 61)
- Увеличение времени ожидания чтения (10 → 19 мс)
- Снижение загрузки CPU на IO (28% → 9%)
4.2. Сравнительные изменения при уменьшении shared_buffers и увеличении work_mem
- Утилизация диска: Повысилась и стабилизировалась на высоком уровне
- ⚠️Время отклика: Увеличилось время ожидания операций чтения
- ⚠️Длина очереди: Выросла в 1.8 раза к концу теста
- Загрузка CPU на IO: Снизилась в 3 раза
- Характер нагрузки: Сместился с пропускной способности на IOPS-ограниченный режим
- Стабильность пропускной способности: Ухудшилась, появились значительные колебания
5. Выводы о влиянии конфигурации на OLAP-нагрузку
5.1. Конфигурация shared_buffers=4GB, work_mem=32MB:
- Обеспечивает более предсказуемую пропускную способность
- Демонстрирует улучшение производительности в ходе теста
- Оптимальна для операций, требующих последовательного чтения больших объемов данных
- Меньшая длина очереди и время отклика
5.2. Конфигурация shared_buffers=2GB, work_mem=256MB:
- Создает более высокую и стабильную нагрузку на диск
- Приводит к увеличению времени отклика операций
- Смещает характер нагрузки в сторону большего количества мелких операций
- Снижает нагрузку на CPU для операций ввода-вывода
- Увеличивает глубину очереди запросов к диску
Анализ и вывод по экспериментальному опровержению гипотезы нейросети
Используя данные vmstat по результатам экспериментов проанализируй причины и сформируй итоговый вывод по экспериментальному опровержению гипотезы нейросети "Для данной OLAP-нагрузки и текущей конфигурации сервера снижение размера shared_buffers с 4 ГБ до 1-2 ГБ, с одновременным увеличением work_mem, приведет к росту общей производительности системы. Основная цель — не просто уменьшить кэш БД, а перенаправить высвободившуюся оперативную память на выполнение операций в памяти и ослабить нагрузку на подсистему ввода-вывода."
Экспериментальные данные и их анализ
1. Ключевые метрики производительности
Эксперимент-1 (shared_buffers=4GB, work_mem=32MB):
- Hit Ratio: 55.36%
- I/O wait (wa > 10%): 100% наблюдений
- Процессы в состоянии непрерываемого сна: слабая корреляция с IO (0.2611)
- Свободная RAM < 5%: 100% наблюдений
Эксперимент-2 (shared_buffers=2GB, work_mem=256MB):
- ⚠️Hit Ratio: 38.58% (снижение на 16.78%)
- I/O wait (wa > 10%): 97.27% наблюдений
- Процессы в состоянии непрерываемого сна: очень высокая корреляция с IO (0.8774)
- Свободная RAM < 5%: 100% наблюдений
2. Анализ причинно-следственных связей
2.1. Эффективность кэширования
- ⚠️Уменьшение shared_buffers привело к значительному снижению Hit Ratio с 55.36% до 38.58%
- В обоих экспериментах наблюдалась очень высокая корреляция между hit и read (0.9725 и 0.8698), что указывает на то, что кэширование напрямую связано с чтением с диска
- ⚠️OLAP-нагрузка характеризуется большими объёмами данных, которые не помещаются даже в 4GB кэш, а уменьшение до 2GB усугубило ситуацию
2.2. Нагрузка на подсистему ввода-вывода
- В обоих экспериментах система была ограничена производительностью записи (высокая корреляция скорости операций с shared_blks_write: 0.9921 и 0.9970)
Уменьшение shared_buffers не ослабило нагрузку на I/O:
- В Эксперименте-2 процессы чаще переходили в состояние непрерываемого сна (корреляция увеличилась с 0.2611 до 0.8774)
- I/O wait оставался критически высоким в обоих случаях (>97% наблюдений)
2.3. Использование оперативной памяти
- Свободной оперативной памяти не было в обоих экспериментах (всегда <5%)
- ⚠️Гипотеза о "высвободившейся оперативной памяти" не подтвердилась — система продолжала использовать всю доступную RAM
- Увеличение work_mem с 32MB до 256MB не привело к заметным улучшениям в производительности или снижению нагрузки на I/O
2.4. Особенности OLAP-нагрузки
- Отношение прочитанных блоков к изменённым: 177.98 (Эксперимент-1) и 268.01 (Эксперимент-2)
- Это подтверждает чтение-ориентированный характер нагрузки с минимальными операциями записи
- ⚠️При такой нагрузке увеличение work_mem не компенсирует уменьшение shared_buffers, так как аналитические запросы требуют доступа к большим объёмам данных
Итоговый вывод
⚠️Гипотеза нейросети экспериментально опровергнута. Снижение размера shared_buffers с 4 ГБ до 2 ГБ при одновременном увеличении work_mem с 32 МБ до 256 МБ не привело к росту общей производительности системы для данной OLAP-нагрузки и конфигурации сервера.
Ключевые опровержения гипотезы:
Цель "перенаправить высвободившуюся оперативную память" не достигнута:
- В обоих экспериментах свободной RAM практически не было (<5%)
- Система полностью использовала доступную память независимо от распределения между shared_buffers и work_mem
Цель "ослабить нагрузку на подсистему ввода-вывода" не достигнута:
- Нагрузка на I/O осталась критически высокой (I/O wait >10% в >97% наблюдений)
- В Эксперименте-2 процессы чаще блокировались в ожидании I/O (корреляция увеличилась с 0.26 до 0.88)
Эффективность кэширования ухудшилась:
- Hit Ratio снизился на 16.78% при уменьшении shared_buffers
- Для OLAP-нагрузки с большими объёмами чтения это критично
Увеличение work_mem не компенсировало уменьшение shared_buffers:
- Несмотря на 8-кратное увеличение work_mem, общая производительность не улучшилась
- OLAP-запросы требуют доступа к большим данным, которые эффективнее кэшировать в shared_buffers
Фундаментальная причина:
Для данной OLAP-нагрузки, характеризующейся чтением больших объёмов данных с высоким отношением read/write (177-268:1), уменьшение размера кэша БД (shared_buffers) оказалось контрпродуктивным. Рабочий набор данных превышает доступный объём кэша даже при 4 ГБ, поэтому дальнейшее уменьшение только увеличивает количество обращений к диску, что подтверждается снижением Hit Ratio и сохранением высокого I/O wait.
⚠️Эксперимент показал, что для данной конфигурации и типа нагрузки приоритет должен быть отдан максимизации shared_buffers, а не work_mem.
Итоговый анализ причин ошибочного прогноза нейросети
Подготовь итог с перечислениям причин ошибочного прогноза нейросети.
Перечисление ключевых причин ошибочности гипотезы:
1. Неверная оценка характера использования памяти при OLAP-нагрузке
- Нейросеть предположила наличие "высвободившейся оперативной памяти", но эксперименты показали, что система и так использует всю доступную RAM (свободно <5%)
- Нет незадействованного ресурса памяти для перераспределения
2. Недооценка критической важности shared_buffers для OLAP-работы
- Для аналитических запросов с большими объёмами данных кэширование критически важно
- Уменьшение shared_buffers с 4GB до 2GB привело к снижению Hit Ratio на 16.78% (с 55.36% до 38.58%)
- Рабочий набор данных превышает даже 4GB кэша, поэтому его уменьшение только усугубляет ситуацию
3. Переоценка влияния work_mem на производительность OLAP
- Увеличение work_mem с 32MB до 256MB не компенсировало потери от уменьшения shared_buffers
- Для OLAP-запросов, работающих с большими таблицами, размер work_mem менее критичен, чем размер общего кэша
4. Неправильное понимание механизмов нагрузки на I/O
- Гипотеза предполагала, что уменьшение shared_buffers "ослабит нагрузку на I/O"
- Реальность: нагрузка на I/O осталась критической в обоих экспериментах (I/O wait >10% в >97% случаев)
- В Эксперименте-2 процессы стали чаще блокироваться в ожидании I/O (корреляция выросла с 0.26 до 0.88)
5. Игнорирование специфики read-intensive OLAP-нагрузки
- Отношение read/write составило 177:1 и 268:1 — это крайне чтение-ориентированная нагрузка
- При такой нагрузке оптимизация записи (commit_delay, wal_buffers) менее важна, чем оптимизация чтения
- Увеличение work_mem не решает проблему частого чтения больших объёмов данных с диска
6. Отсутствие учёта текущего состояния системы
- Нейросеть не учла, что система уже работает на пределе доступных ресурсов (свободной RAM нет, высокий I/O wait)
- В таких условиях перераспределение ресурсов без их увеличения не даёт положительного эффекта
7. Ошибочное предположение о "линейной" зависимости
- Гипотеза строилась на предположении, что уменьшение одного параметра + увеличение другого = улучшение производительности
- Реальность показала нелинейную зависимость, где shared_buffers оказался критичнее work_mem для данной нагрузки
8. Недооценка важности кэширования для повторных запросов
- Даже при OLAP-нагрузке часто выполняются повторяющиеся аналитические запросы
- Уменьшение shared_buffers снижает возможность кэширования результатов промежуточных вычислений
⚠️Фундаментальная ошибка:
Нейросеть применила шаблонное решение (уменьшить shared_buffers, увеличить work_mem), которое может работать для некоторых сценариев, но не учитывала:
- Экстремально чтение-ориентированный характер нагрузки (read/write > 100:1)
- Отсутствие свободного ресурса памяти для перераспределения
- То, что текущий размер shared_buffers уже недостаточен для рабочего набора данных
- Нелинейную природу зависимости между параметрами и производительностью
Вывод: Гипотеза была основана на общем шаблоне оптимизации, не адаптированном под конкретные характеристики нагрузки (крайне read-intensive OLAP) и текущее состояние системы (отсутствие свободных ресурсов).
Послесловие
Использование pg_expecto в этом исследовании позволило объективно оценить влияние параметров на производительность, исключив фактор случайности и обеспечив сравнимость результатов. Инструмент подтвердил свою ценность для задач тонкой настройки СУБД.