PG_EXPECTO- work_mem: мифы и реальность производительности PostgreSQL

Больше — не значит быстрее. Ищем баланс между памятью и скоростью.

Задача эксперимента

Проанализировать влияние увеличения параметра work_mem, на производительность СУБД и метрики инфраструктуры, для заданного характера нагрузки .

Тестовый сценарий-1 (SELECT BY PK)

Тестовый сценарий-2 (GROUP BY)

Тестовый сценарий-3 (ORDER BY)

Тестовый сценарий-4 (JOIN)

Методология исследования

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

• СУБД: PostgreSQL 17
• Инструмент нагрузочного тестирования: pg_expecto
• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
• Условия тестирования: параллельная нагрузка от 5 до 22 сессий по каждому тестовому сценарию.

Начальная гипотеза , сформулированная нейросетью

Входные данные

Схема тестовой базы данных : demo_schema.sql

Размеры таблиц : tables.txt

Конфигурация СУБД : postgres.auto.conf

Таблица нагрузки: load.txt

Список queryid : sql_list.txt

Тестовые запросы и планы выполнения для work_mem=4MB: plans-1.txt

Используя предоставленные входные данные, сформулируй гипотезу о влиянии увеличения параметра work_mem в ходе нагрузочного тестирования по сравнению базовым значением work_mem=4MB (Эксперимент-1) в экспериментах : work_mem=8MB (Эксперимент-2), work_mem=16MB(Эксперимент-3), work_mem=32MB (Эксперимент-4), work_mem=64MB (Эксперимент-5). Уточни ключевые признаки значений ожиданий СУБД wait_event_type и метрик производительности инфраструктуры vmstat, iostat, указывающие на слишком большой размер work_mem в ходе данных экспериментов.

Гипотеза о влиянии увеличения work_mem

❓Гипотеза: Увеличение work_mem с 4MB до 8-64MB должно существенно улучшить производительность запросов, содержащих операции сортировки и хеширования, но после определённого порога (вероятно, 32-64MB при данной нагрузке) начнёт проявляться негативное влияние на общую стабильность системы.

Ожидаемые положительные эффекты:

1. Сокращение временных операций на диске: В текущих планах выполнения (work_mem=4MB) видно операции Sort с использованием диска (work_mem недостаточно). Увеличение параметра позволит выполнять больше операций в памяти.
2. Ускорение GROUP BY и ORDER BY: Запросы с агрегациями и сортировкой (как в представленных примерах) получат наибольший выигрыш.
3. Улучшение JOIN-операций: Хеш-соединения будут выполняться полностью в памяти.

Критический порог (ожидается):

• Эксперимент-2 (8MB): Умеренное улучшение, некоторые сортировки перейдут в память
• Эксперимент-3 (16MB): Значительное улучшение, большинство операций в памяти
• Эксперимент-4 (32MB): Пик производительности для данной нагрузки
• Эксперимент-5 (64MB): Возможна деградация из-за конкуренции за память

Ключевые признаки слишком большого work_mem

1. Wait Event Type в PostgreSQL (признаки проблем):

• IO/DataFileRead и IO/DataFileWrite: Увеличение числа операций ввода-вывода вместо уменьшения (парадоксальный эффект из-за вытеснения кэша)
• LWLock/buffer_content и LWLock/buffer_mapping: Блокировки, связанные с конкуренцией за буферы
• IPC/BgWriterMain: Активность фоновых писателей из-за нехватки shared_buffers
• CPU: Высокий процент ожидания процессора из-за busy-wait в условиях нехватки памяти

2. Метрики vmstat (признаки проблем):

• swpd (swap used): Рост использования свопа
• si/so (swap in/out): Активность подкачки
• wa (CPU wait I/O): Рост времени ожидания ввода-вывода (>20%)
• cs (context switches): Резкий рост переключений контекста
• free memory: Сокращение свободной памяти до минимума

3. Метрики iostat (признаки проблем):

• %util: Утилизация диска >70-80% (признак saturation)
• await: Рост времени отклика диска (>10-20ms для SSD, >50ms для HDD)
• avgqu-sz: Увеличение средней длины очереди
• w/s (writes per second): Рост операций записи временных файлов

➡️Гипотеза будет подтверждена, если в экспериментах 4-5 (32-64MB) при нагрузке 15-22 соединений появятся указанные выше признаки деградации, несмотря на улучшение производительности отдельных запросов при низкой нагрузке.

Результаты экспериментов по итогам нагрузочных тестирований

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

График изменения операционной скорости в ходе экспериментов для разных значений work_mem

Среднее снижение операционной скорости в ходе экспериментов по сравнению с базовым значением work_mem=4MB составило от 0.07%(64MB) до 1.29%(16MB)

ℹ️Итог

‼️Первоначальная гипотеза - не подтверждена.

⚠️Увеличение work_mem, для заданного характера нагрузки на СУБД - не влияет на производительность СУБД.

1. Анализ операционной скорости и ожиданий СУБД

Входные данные:

Схема тестовой базы данных : demo_schema.sql

Размеры таблиц : tables.txt

Конфигурация СУБД : postgres.auto.conf

Таблица нагрузки: load.txt

Список queryid : sql_list.txt

Тестовые запросы и планы выполнения для значения work_mem=4MB: plans-1.txt

Регрессионный и корреляционный анализ производительности и ожиданий СУБД для эксперимента N(1-5) : N.postgres.1.cluster_report_meta.txt

Статистические данные по производительности и ожиданиям СУБД (wait_event_type) для эксперимента N(1-5) : N.postgres.1.cluster_report_4graph.txt

Используя входные данные, сформируй сравнительный анализ для экспериментов: Эксперимент-1(work_mem=4MB), Эксперимент-2 (work_mem=8MB), Эксперимент-3(work_mem=16MB), Эксперимент-4(work_mem=32MB), Эксперимент-5(work_mem=64MB). Выдели ключевые отличия в производительности(speed), типах ожиданий СУБД(wait_event_type) , событий ожидания СУБД(wait_event). Сформируй итог по серии экспериментов.

Общие данные экспериментов

• Схема БД: Авиационные бронирования (bookings)
• Конфигурация СУБД: PostgreSQL с оптимизированными параметрами (shared_buffers=2GB, effective_cache_size=6GB и др.)
• Нагрузка: Постепенно возрастает от 5 до 22 единиц
• Количество измерений в каждом эксперименте: 110 временных точек
• Тестируемые сценарии: 4 SQL-сценария (scenario1-4)

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

1. Эксперимент-1 (work_mem=4MB)

• Производительность (SPEED):
• R² = 0.9500 (высокая стабильность)
• Угол наклона: -44.34 (умеренное снижение)
• Диапазон: 1 209 386 - 2 986 672
• Ожидания (WAITINGS):
• R² = 0.7800 (средняя стабильность)
• Угол наклона: -41.36
• Диапазон: 12 - 177
• Типы ожиданий: IO, IPC, LWLOCK, TIMEOUT
• Максимальные значения: IO=31, IPC=24

2. Эксперимент-2 (work_mem=8MB)

Производительность:

• R² = 0.9500
• Угол наклона: -44.33
• Диапазон: 1 183 349 - 2 987 009

Ожидания:

• R² = 0.7900
• Угол наклона: -41.66
• Диапазон: 8 - 119
• Типы ожиданий: LWLOCK, TIMEOUT
• IO и IPC отсутствуют

3. Эксперимент-3 (work_mem=16MB)

Производительность:

• R² = 0.9600 (наивысшая стабильность)
• Угол наклона: -44.44 (наибольшее снижение)
• Диапазон: 1 214 624 - 2 960 461

Ожидания:

• R² = 0.7900
• Угол наклона: -41.62
• Диапазон: 8 - 124
• Типы ожиданий: LWLOCK, TIMEOUT

4. Эксперимент-4 (work_mem=32MB)

Производительность:

• R² = 0.9500
• Угол наклона: -44.31
• Диапазон: 1 201 850 - 2 958 245

Ожидания:

• R² = 0.7900
• Угол наклона: -41.72 (наибольшее снижение ожиданий)
• Диапазон: 13 - 134
• Типы ожиданий: LWLOCK, TIMEOUT

5. Эксперимент-5 (work_mem=64MB)

Производительность:

• R² = 0.9500
• Угон наклона: -44.32
• Диапазон: 1 199 189 - 2 977 820

Ожидания:

• R² = 0.7800
• Угол наклона: -41.52
• Диапазон: 11 - 120
• Типы ожиданий: LWLOCK, TIMEOUT

Ключевые отличия по типам ожиданий

1. IO (Ввод/Вывод) ожидания

Присутствуют только в Эксперимент-1 (4MB)

• Максимальное значение: 31
• Исчезают при увеличении work_mem до 8MB и выше

ℹ️Вывод: Малый work_mem вызывает повышенную активность подкачки на диск

2. IPC (Межпроцессное взаимодействие) ожидания

Присутствуют только в Эксперимент-1 (4MB)

• Максимальное значение: 24
• Исчезают при work_mem ≥ 8MB

ℹ️Вывод: Недостаток памяти приводит к дополнительной синхронизации между процессами

3. LWLOCK (Легковесные блокировки) ожидания

Присутствуют во всех экспериментах

• Максимальные значения: 7 (стабильно)
• Высокая корреляция с WAITINGS (0.93-0.97)

❗Вывод: Независимо от work_mem, LWLOCK остаются основным источником ожиданий

4. TIMEOUT ожидания

Присутствуют во всех экспериментах

• Почти идеальная корреляция с WAITINGS (0.97-1.00)

❗Вывод: Основной компонент общих ожиданий системы

Корреляционный анализ

SPEED - WAITINGS корреляция:

• Эксперимент-1: 0.8700 (самая низкая)
• Эксперимент-2: 0.9100
• Эксперимент-3: 0.9100
• Эксперимент-4: 0.9000
• Эксперимент-5: 0.9000

ℹ️Тренд: Корреляция улучшается с увеличением work_mem до 8MB, затем стабилизируется

WAITINGS - LWLOCK корреляция:

• Высокая во всех экспериментах (0.93-0.97)
• Наивысшая в Эксперимент-3 (0.97)

WAITINGS - TIMEOUT корреляция:

• Почти идеальная (0.97-1.00)
• Достигает 1.00 в экспериментах 2-5

Анализ производительности

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

1. Эксперимент-2: 2 987 009 (8MB)
2. Эксперимент-1: 2 986 672 (4MB)
3. Эксперимент-5: 2 977 820 (64MB)
4. Эксперимент-3: 2 960 461 (16MB)
5. Эксперимент-4: 2 958 245 (32MB)

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

1. Эксперимент-3: 1 214 624 (16MB)
2. Эксперимент-1: 1 209 386 (4MB)
3. Эксперимент-4: 1 201 850 (32MB)
4. Эксперимент-5: 1 199 189 (64MB)
5. Эксперимент-2: 1 183 349 (8MB)

Динамика изменения под нагрузкой

• Нагрузка плавно возрастает от 5 до 22 единиц
• Все эксперименты показывают снижение производительности с ростом нагрузки (отрицательные углы наклона)
• Наибольшее снижение в Эксперимент-3 (16MB): -44.44
• Наименьшее снижение в Эксперимент-4 (32MB): -44.31

ℹ️Итоги по серии экспериментов

⚠️1. Критический порог work_mem

❗4MB: Недостаточно для данной нагрузки

• Появляются IO и IPC ожидания
• Наименьшая корреляция SPEED-WAITINGS
• Высокие максимальные ожидания (177)

ℹ️≥8MB: Достаточно для устранения IO/IPC ожиданий

• Качество обслуживания значительно улучшается

👍2. Оптимальный диапазон work_mem

ℹ️8-16MB: Наилучший баланс

• Эксперимент-2 (8MB): Максимальная пиковая производительность
• Эксперимент-3 (16MB): Наивысшая стабильность (R²=0.96)
• Отсутствие IO/IPC ожиданий

ℹ️3. Закон убывающей отдачи

⚠️Увеличение work_mem выше 16MB не дает значительных улучшений

• Эксперимент-4 (32MB) и Эксперимент-5 (64MB) показывают сходные результаты
• ➡️Дополнительная память не решает проблему LWLOCK и TIMEOUT ожиданий

4. Основные bottlenecks

1. LWLOCK: Независимо от work_mem (корреляция 0.93-0.97)
2. TIMEOUT: Основной компонент ожиданий (корреляция ~1.00)
3. IO/IPC: Только при недостаточном work_mem (4MB)

5. Рекомендации

1. Минимальный work_mem: 8MB (устраняет IO/IPC ожидания)
2. Оптимальный work_mem: 16MB (максимальная стабильность)

Вывод

1️⃣Значение work_mem=4MB явно недостаточно для данной нагрузки, вызывая дополнительные IO и IPC ожидания.

2️⃣Увеличение до 8MB решает эти проблемы.

❗Дальнейшее увеличение до 16MB улучшает стабильность работы, но значения выше 16MB не дают существенного прироста производительности.

Основными источниками ожиданий остаются LWLOCK и TIMEOUT, что указывает на необходимость оптимизации параллелизма и настроек таймаутов, а не только увеличения памяти.

2. Анализ производительности инфраструктуры (vmstat)

Входные данные:

Таблица нагрузки: load.txt

Тестовые запросы и планы выполнения: plans.txt

Статистические данные vmstat для эксперимента N(1-5) : N.linux.x.vmstat_4graph.txt

Используя входные данные, сформируй сравнительный анализ для экспериментов: Эксперимент-1(work_mem=4MB), Эксперимент-2 (work_mem=8MB), Эксперимент-3(work_mem=16MB), Эксперимент-4(work_mem=32MB), Эксперимент-5(work_mem=64MB).Выдели ключевые отличия в метриках vmstat. Сформируй итог по серии экспериментов.

Обзор экспериментов

Было проведено 5 экспериментов с различными значениями параметра work_mem в PostgreSQL:

• Эксперимент-1: work_mem=4MB
• Эксперимент-2: work_mem=8MB
• Эксперимент-3: work_mem=16MB
• Эксперимент-4: work_mem=32MB
• Эксперимент-5: work_mem=64MB

Данные собирались в течение ~110 минут при возрастающей нагрузке (от 5 до 22 единиц).

Ключевые метрики vmstat по экспериментам

1. Процессы в состоянии выполнения (procs_r)

• Эксперимент-1 (4MB): 21-49 процессов (рост в 2.3 раза)
• Эксперимент-2 (8MB): 21-50 процессов (рост в 2.4 раза)
• Эксперимент-3 (16MB): 21-50 процессов (рост в 2.4 раза)
• Эксперимент-4 (32MB): 21-51 процессов (рост в 2.4 раза)
• Эксперимент-5 (64MB): 21-51 процессов (рост в 2.4 раза)

ℹ️Вывод: Количество процессов в состоянии выполнения увеличивается пропорционально нагрузке во всех экспериментах, минимальная разница между конфигурациями.

‼️2. Свободная память (memory_free)

• Эксперимент-1 (4MB): 598 → 2613 MB (резкий рост в конце)
• Эксперимент-2 (8MB): 2774 → 2596 MB (стабильно высокий уровень)
• Эксперимент-3 (16MB): 2650 → 2471 MB (стабильное снижение)
• Эксперимент-4 (32MB): 203 → 220 MB (минимальные колебания)
• Эксперимент-5 (64MB): 333 → 220 MB (плавное снижение)

ℹ️Вывод: Эксперименты с меньшим work_mem показывают более высокий уровень свободной памяти, что ожидаемо.

‼️При work_mem=4MB наблюдается аномальный рост свободной памяти в конце теста.

3. CPU пользовательское время (cpu_us)

Все эксперименты: 89-95% (незначительные колебания)

4. CPU системное время (cpu_sy)

• Эксперимент-1 (4MB): 10 → 4% (снижение)
• Эксперимент-2 (8MB): 10 → 4% (снижение)
• Эксперимент-3 (16MB): 10 → 5% (снижение)
• Эксперимент-4 (32MB): 11 → 5% (снижение)
• Эксперимент-5 (64MB): 10 → 4% (снижение)

Вывод: Системное время CPU снижается с ростом нагрузки во всех экспериментах, что может указывать на оптимизацию работы ядра при высокой нагрузке.

5. Контекстные переключения (system_cs)

• Эксперимент-1 (4MB): 68,579 → 20,170 (снижение в 3.4 раза)
• Эксперимент-2 (8MB): 68,504 → 20,173 (снижение в 3.4 раза)
• Эксперимент-3 (16MB): 66,906 → 20,261 (снижение в 3.3 раза)
• Эксперимент-4 (32MB): 68,795 → 20,664 (снижение в 3.3 раза)
• Эксперимент-5 (64MB): 68,500 → 20,278 (снижение в 3.4 раза)

Вывод: Во всех экспериментах наблюдается значительное снижение контекстных переключений по мере роста нагрузки, что свидетельствует о более эффективном планировании процессов.

6. Блоки ввода-вывода (io_bo)

• Эксперимент-1 (4MB): 55 → 52 (незначительное снижение)
• Эксперимент-2 (8MB): 50 → 55 (незначительный рост)
• Эксперимент-3 (16MB): 54 → 63 (рост на 17%)
• Эксперимент-4 (32MB): 48 → 52 (рост на 8%)
• Эксперимент-5 (64MB): 54 → 57 (рост на 6%)

‼️Вывод: Наибольший рост блоков ввода-вывода наблюдается при work_mem=16MB, что может указывать на неоптимальное использование памяти для данной конфигурации.

ℹ️7.Графики изменения показателей использования памяти

Анализ использования памяти в ходе экспериментов

Входные данные:

Таблица нагрузки: load.txt

Показатели использования памяти для экспериментов - Эксперимент-1(work_mem=4MB), Эксперимент-2 (work_mem=8MB), Эксперимент-3(work_mem=16MB), Эксперимент-4(work_mem=32MB), Эксперимент-5(work_mem=64MB): memory.txt

Используя входные данные, сформируй сравнительный анализ для экспериментов: Эксперимент-1(work_mem=4MB), Эксперимент-2 (work_mem=8MB), Эксперимент-3(work_mem=16MB), Эксперимент-4(work_mem=32MB), Эксперимент-5(work_mem=64MB).Выдели ключевые отличия в метриках использования памяти vmstat. Сформируй итог по серии экспериментов.

1. Обзор данных

• Эксперименты: 5 вариантов настройки work_mem: 4 МБ, 8 МБ, 16 МБ, 32 МБ, 64 МБ.
• Метрики памяти (из vmstat):
• swpd — объём виртуальной памяти, использованной для свопинга.
• free — свободная оперативная память.
• buff — память, используемая для буферов.
• cache — память, используемая для кэша.
• Нагрузка: изменяется от 5 до 22 единиц в течение 110 измерений (файл load.txt).
• Временной ряд: 110 последовательных измерений для каждого эксперимента.

2. Динамика нагрузки

• Низкая нагрузка (5–8): измерения 1–33.
• Средняя нагрузка (9–13): измерения 34–77.
• Высокая нагрузка (15–22): измерения 78–110.

3. Анализ метрик по экспериментам

Эксперимент 1 (work_mem = 4 МБ)

• swpd: постоянное значение 58 МБ на всём протяжении, что указывает на стабильное использование свопинга.
• free: высокий начальный уровень (~600 МБ), постепенное снижение до ~500 МБ, затем резкий скачок до 2613 МБ на измерении 102 при высокой нагрузке.
• buff: стабилен (~468–470 МБ).
• cache: высокий начальный уровень (~6322 МБ), постепенно растёт до ~6373 МБ, затем резко падает до 4095 МБ при скачке free.

Эксперимент 2 (work_mem = 8 МБ)

• swpd: постоянное значение 58 МБ.
• free: начальный уровень высокий (~2774 МБ), плавное снижение до ~2596 МБ к концу теста.
• buff: стабилен (~474–480 МБ).
• cache: начальный уровень ~4140 МБ, плавный рост до ~4204 МБ.

Эксперимент 3 (work_mem = 16 МБ)

• swpd: постоянное значение 58 МБ.
• free: начальный уровень ~2650 МБ, снижение до ~2474 МБ к концу.
• buff: стабилен (~483–488 МБ).
• cache: начальный уровень ~4257 МБ, рост до ~4322 МБ.

Эксперимент 4 (work_mem = 32 МБ)

• swpd: постоянное значение 58 МБ.
• free: очень низкий уровень (~203–230 МБ) на всём протяжении.
• buff: стабилен (~528–533 МБ).
• cache: высокий уровень (~6651 МБ), снижение до ~6528 МБ при высокой нагрузке.

Эксперимент 5 (work_mem = 64 МБ)

• swpd: постоянное значение 58 МБ.
• free: низкий уровень (~330–220 МБ), снижение при увеличении нагрузки.
• buff: стабилен (~539–543 МБ).
• cache: высокий уровень (~6502–6550 МБ), снижение до ~6489 МБ в конце.

4. Ключевые отличия в метриках

ℹ️5. Итоги по серии экспериментов

Свопирование (swpd):

• Во всех экспериментах наблюдается постоянное использование своп-памяти (58 МБ), что может указывать на общий недостаток оперативной памяти или настройки ядра, не зависящие от work_mem.

Свободная память (free):

• При меньших значениях work_mem (4–16 МБ) свободной памяти больше, и система способна выделять её под процессы.
• При больших значениях work_mem (32–64 МБ) свободной памяти значительно меньше, что свидетельствует о активном использовании памяти под кэш и буферы.

Кэш (cache):

• ℹ️Наибольший объём кэша наблюдается при 32 МБ и 64 МБ, что указывает на эффективное использование памяти для кэширования данных.
• ‼️При 4 МБ происходит резкое освобождение кэша при высокой нагрузке, что может свидетельствовать о нехватке памяти и агрессивной политике её высвобождения.

Буферы (buff):

• Во всех экспериментах буферы остаются стабильными, что говорит о том, что work_mem не оказывает существенного влияния на использование буферной памяти.

Влияние нагрузки:

1. При увеличении нагрузки система стремится уменьшить свободную память и увеличить кэш, особенно при больших значениях work_mem.
2. Наибольшая стабильность метрик наблюдается при work_mem = 8 МБ и 16 МБ.

ℹ️6. Выводы

• Оптимальные значения work_mem: 8–16 МБ обеспечивают баланс между свободной памятью и использованием кэша, что способствует стабильной работе под нагрузкой.
• ❗Большие значения work_mem (32–64 МБ) приводят к дефициту свободной памяти, но эффективному кэшированию.
• ⚠️Маленькое значение work_mem (4 МБ) вызывает резкие изменения в памяти при высокой нагрузке, что может негативно влиять на производительность.

➡️Рекомендация: для данной системы с наблюдаемой нагрузкой рекомендуется установить work_mem в диапазоне 8–16 МБ для сбалансированного использования памяти.

Анализ влияния нагрузки

1. При нагрузке 8-12 соединений: Начало существенного роста процессов в состоянии выполнения
2. При нагрузке 13-15 соединений: Стабилизация контекстных переключений на низком уровне
3. При нагрузке 18-22 соединений: Максимальные значения метрик во всех экспериментах

ℹ️Итоговые выводы

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

• work_mem=4MB: Наиболее экономичное использование памяти, но возможны проблемы с производительностью сложных запросов
• work_mem=64MB: Максимальное использование памяти, но без существенного преимущества в производительности
• Оптимальный диапазон: work_mem=8-32MB обеспечивает баланс между использованием памяти и производительностью

2. Производительность системы

• Все конфигурации демонстрируют схожую производительность при возрастающей нагрузке
• Системное время CPU снижается при высокой нагрузке независимо от значения work_mem
• Контекстные переключения значительно уменьшаются при росте нагрузки

3. Ввод-вывод

• Наибольшая нагрузка на подсистему ввода-вывода наблюдается при work_mem=16MB
• Конфигурации с крайними значениями (4MB и 64MB) показывают лучшие результаты по этому показателю

ℹ️4. Рекомендации

Для данной рабочей нагрузки:

1. ℹ️work_mem=8MB является оптимальным выбором, обеспечивая баланс между использованием памяти и производительностью
2. work_mem=32MB можно рассматривать для систем с достаточным объемом RAM и требовательными запросами
3. ‼️Значения выше 32MB не дают существенного прироста производительности при данной нагрузке
4. ❗work_mem=4MB может быть недостаточным для сложных операций сортировки и соединения

Общий итог: Параметр work_mem оказывает умеренное влияние на общую производительность системы при данной нагрузке.

Различия между конфигурациями проявляются в основном в использовании памяти и нагрузке на подсистему ввода-вывода, а не в общей производительности CPU или количестве процессов.

ℹ️Общий итог по анализу результатов экспериментов

Сформулируй общий итог по анализу результатов экспериментов.

Проведённая серия экспериментов по влиянию параметра work_mem на производительность СУБД PostgreSQL и метрики инфраструктуры позволила сформулировать следующие ключевые выводы:

⚠️1. Гипотеза не подтвердилась

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

2. Критический порог для устранения IO и IPC ожиданий

• ❗При work_mem=4 МБ наблюдались IO и IPC ожидания, что указывало на нехватку памяти для операций ввода-вывода и межпроцессного взаимодействия.
• Уже при work_mem=8 МБ эти типы ожиданий полностью исчезли, что свидетельствует о достаточности этого значения для устранения дисковой подкачки в рамках данной нагрузки.

ℹ️3. Оптимальный диапазон значений

• 8–16 МБ — оптимальный диапазон, обеспечивающий баланс между использованием памяти и стабильностью работы:
• work_mem=8 МБ показал наивысшую пиковую производительность.
• work_mem=16 МБ обеспечил наивысшую стабильность (R²=0,96).
• ‼️Увеличение выше 16 МБ не дало значимых улучшений, указывая на закон убывающей отдачи.

4. Основные источники ожиданий (bottlenecks)

Независимо от значения work_mem основными источниками ожиданий были:

• ℹ️LWLOCK (лёгковесные блокировки) — высокая корреляция с общим временем ожиданий (0,93–0,97).
• ℹ️TIMEOUT — почти идеальная корреляция (до 1,00), что указывает на системные таймауты как ключевой лимитирующий фактор.
• ℹ️Это говорит о том, что для дальнейшего повышения производительности требуется оптимизация параллелизма и настроек таймаутов, а не только увеличение памяти.

5. Влияние на инфраструктуру (vmstat, память)

Использование памяти:

• При work_mem=32–64 МБ наблюдается дефицит свободной памяти, но эффективное использование кэша.
• При work_mem=4 МБ возникают резкие колебания в использовании памяти под нагрузкой.
• Наиболее стабильное поведение — при work_mem=8–16 МБ.

Нагрузка на CPU и IO:

• Все конфигурации показали схожие метрики CPU (пользовательское время ~90%).
• Нагрузка на ввод-вывод немного возрастала при work_mem=16 МБ, но оставалась в допустимых пределах.

6. Рекомендации

• ℹ️Минимальное значение: work_mem=8 МБ (для устранения IO/IPC ожиданий).
• 👍Оптимальное значение: work_mem=16 МБ (для максимальной стабильности и баланса использования ресурсов).
• ‼️Не рекомендуется увеличивать work_mem выше 32 МБ для данной нагрузки, так как это не даёт прироста производительности, но сокращает свободную память.
• ℹ️Фокус на оптимизацию: внимание следует уделить настройке параллелизма, устранению блокировок (LWLOCK) и оптимизации таймаутов.

➡️Заключение

Параметр work_mem важен для устранения операций ввода-вывода на диск при выполнении сортировок и хеш-соединений, однако его увеличение не является панацеей для повышения производительности.

ℹ️Для данной нагрузки и конфигурации достаточно значений в диапазоне 8–16 МБ.

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