PG_EXPECTO : экспериментальная настройка Linux + PostgreSQL под нагрузку 70/20/10

Баланс между ОС и СУБД: искусство оптимизации RAM

План описывает пошаговую методику оптимизации параметров операционной системы и PostgreSQL для виртуальной машины с 8 ГБ оперативной памяти. Эксперименты направлены на достижение стабильной производительности при смешанной нагрузке (70% выборок, 20% вставок, 10% обновлений) через системное тестирование и анализ метрик.

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

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

Тонкая настройка RAM и ядра для PostgreSQL

План экспериментов по оптимизации параметров RAM для PostgreSQL

Цель: Определить оптимальные значения параметров управления памятью ОС и PostgreSQL для заданной нагрузки (70% SELECT, 20% INSERT, 10% UPDATE) на виртуальной машине с конфигурацией CPU=8, RAM=8ГБ, SSD-диски.

1. Этап 0: Базовый тест (Baseline)

1.1. Конфигурация по умолчанию
1.2. Проведение нагрузочного теста
1.3. Фиксация результатов
Сохранить все собранные данные и профили как эталон (baseline) для последующего сравнения.

2. Этап 1: Оптимизация параметров ядра Linux

2.1. Эксперимент 1.1: Настройка свопинга (vm.swappiness) и "грязных" страниц

Проверяемые значения:

• vm.swappiness = [1, 10, 60] (1 — минимум свопинга, 60 — значение по умолчанию).
• vm.dirty_background_ratio = [1, 5, 10]
• vm.dirty_ratio = [5, 15, 30]

Методика: Перебор комбинации значений. После каждого изменения применять настройки (sysctl -p) и запускать нагрузочный тест.

Ключевые метрики для анализа: Количество прочитанных/записанных свап-страниц (si/so в vmstat), задержки операций ввода-вывода, общая стабильность операционной скорости.

🎯 Рекомендуемая практическая стратегия (8-10 тестов)

Этап А: Поиск оптимальных значений по отдельности (5-6 тестов)

1. Базовый тест с настройками по умолчанию (например, swappiness=60, dirty_background_ratio=10, dirty_ratio=20).
2. Тест vm.swappiness: Фиксируем dirty_* параметры на разумных значениях (например, 5 и 15). Тестируем:
   swappiness = 1 (основная цель для БД)
   swappiness = 10
3. Тест vm.dirty_background_ratio: Фиксируем swappiness=1 и dirty_ratio=15. Тестируем:
   dirty_background_ratio = 1
   dirty_background_ratio = 5
4. Тест vm.dirty_ratio: Фиксируем swappiness=1 и dirty_background_ratio=5. Тестируем:
   dirty_ratio = 10
   dirty_ratio = 30

Этап Б: Проверка лучшей комбинации (2-4 теста)

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

• Пример целевой комбинации: [swappiness=1, dirty_background_ratio=5, dirty_ratio=10].
• Дополнительная проверка может потребоваться, если в комбинации dirty_ratio слишком близок к dirty_background_ratio (разница менее 5%).

📝 Что нужно делать после каждого теста

Для получения статистически значимых результатов после настройки каждой комбинации (sudo sysctl -p) необходимо:

1. Выполнить сброс кэшей ОС (опционально, но для чистоты эксперимента):
   sync; echo 3 | sudo tee /proc/sys/vm/drop_caches.
2. Перезапустить PostgreSQL, чтобы он переинициализировал работу с памятью:
   sudo systemctl restart postgresql.
3. Запустить нагрузочный тест.
4. Собрать и сохранить ключевые метрики с помощью pg_expecto

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

3. Этап 2: Оптимизация параметров памяти PostgreSQL

4.1. Эксперимент 2.1: Настройка shared_buffers

Проверяемые значения: shared_buffers = [1GB, 2GB, 3GB, 4GB] (от ~12% до 50% от RAM).
Ключевые метрики для анализа:

ОС:

• Активность чтения с дисков /data (iostat). Цель — снижение.

PostgreSQL:

• Коэффициент попадания в кэш БД (cache hit ratio, из pg_stat_database). Цель => 99%.
• Количество буферов, записанных процессами (buffers_backend в pg_stat_bgwriter). Цель => минимизация.

4.2. Эксперимент 2.2: Настройка work_mem

Проверяемые значения: work_mem = [4MB, 8MB, 16MB, 32MB].
Ключевые метрики для анализа:
PostgreSQL: Количество временных файлов на диск (temp_files, temp_bytes из pg_stat_database), операции сортировки и хеширования в памяти и на диске (из pg_stat_statements). Цель => минимизация записи на диск.

4.3. Эксперимент 2.3: Синтетическая настройка (shared_buffers + work_mem)

Методика: Протестировать 2-3 лучшие комбинации параметров, выявленные в экспериментах 2.1 и 2.2.

🔢 Подробный расчёт количества экспериментов

1. Эксперимент 2.1: Настройка shared_buffers

• Количество значений: 4 (1GB, 2GB, 3GB, 4GB).
• Необходимых тестов: 4. Каждое изменение shared_buffers требует полного перезапуска PostgreSQL, поэтому все 4 значения нужно протестировать отдельно.

2. Эксперимент 2.2: Настройка work_mem

• Количество значений: 4 (4MB, 8MB, 16MB, 32MB).
• Необходимых тестов: 4. Этот параметр можно менять на лету (ALTER SYSTEM SET + SELECT pg_reload_conf()), но для чистоты эксперимента и сброса статистики перед каждым тестом также рекомендуется перезапуск СУБД.

3. Эксперимент 2.3: Синтетическая настройка

• Количество комбинаций: 2-3 лучшие комбинации, выявленные в пунктах 2.1 и 2.2.
• Необходимых тестов: от 2 до 3.

Итого (максимум): 4 (shared_buffers) + 4 (work_mem) + 3 (комбинации) = 11 тестов.

🎯 Практическая рекомендация: Объединённая стратегия (6-7 тестов)

На практике этапы 2.1 и 2.2 можно объединить в одну серию тестов, чтобы не гонять одинаковую нагрузку дважды, так как эти параметры слабо влияют друг на друга при первичной настройке:

1. Определить базовое значение shared_buffers (например, 2GB — 25% от RAM, как в нашем раннем расчёте).
2. Зафиксировать shared_buffers = 2GB и протестировать все 4 значения work_mem. Это 4 теста.
3. Выбрать лучшее значение work_mem (например, 16MB).
4. Зафиксировать work_mem = 16MB и протестировать оставшиеся 3 значения shared_buffers (1GB, 3GB, 4GB). Это 3 теста.

Итого по оптимизированной стратегии: 4 + 3 = 7 тестов. Затем можно провести 1-2 финальных теста для проверки выбранной комбинации.

5. Этап 3: Финальный тест и анализ безопасности

5.1. Длительный стресс-тест
Запустить нагрузку (22 подключения) на 1-2 часа с подобранной оптимальной конфигурацией.
Цель: Проверить стабильность системы, отсутствие утечек памяти, плавную деградацию или рост производительности.

5.2. Анализ итоговых метрик с pg_expecto
С помощью pg_expecto провести детальный корреляционный анализ между событиями ожидания в СУБД и метриками инфраструктуры (IO, память) на финальном прогоне.
Сформировать итоговый отчет, сравнивая ключевые показатели с базовым тестом (Baseline).

Итог

План включает три основных этапа: настройку ядра Linux (swappiness, dirty pages), оптимизацию параметров PostgreSQL (shared_buffers, work_mem) и финальный стресс-тест. Предлагается стратегия из 8–10 тестов для ОС и 6–7 для СУБД, что позволяет эффективно определить конфигурацию, максимально использующую доступные 8 ГБ RAM без деградации производительности.