В мире PostgreSQL, как и в автоспорте, не существует единой идеальной стратегии для всех трасс. Выбор интервала контрольных точек (checkpoint_timeout) — это пит-стоп: можно заезжать часто для максимальной скорости на прямых, но рискуя потерять время на самом заезде, или реже — для стабильного и предсказуемого ритма всей гонки. Всё зависит от типа «трассы» — характера нагрузки на вашу базу данных.
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Методология исследования
Тестовая среда, инструменты и конфигурация СУБД:
- СУБД: PostgreSQL 17
- Тестовые базы данных:
- Вариант нагрузки №1 - "Демобаза 2.0" (большой размер, сложная схема)
- Вариант нагрузки №2 - Тестовая база pgbench (большой размер, простая схема)
- Условия тестирования: параллельная нагрузка от 7 до 22 сессий (количество сессий для тестового сценария определяется согласно весовому коэффициенту).
Вариант нагрузки №1
- Высокая нагрузка на CPU
- Высокая читающая нагрузка
- Низкая пишущая нагрузка.
Вариант нагрузки №2
- Низкая нагрузка на CPU
- Низкая читающая нагрузка
- Высокая пишущая нагрузка.
Исходные данные экспериментов:
Вариант нагрузки-1:Часть-1:СУБД.
Вариант нагрузки-1:Часть-2:Инфраструктура.
Вариант нагрузки-2:Часть-1:СУБД.
Вариант нагрузки-2:Часть-2:Инфраструктура.
Вариант нагрузки №1 - анализ с точки зрения производительности СУБД
checkpoint_timeout=1 минута:
- Преимущества: Наивысшая пиковая производительность (2 599 957)
- Недостатки:
- Наиболее выраженное снижение производительности со временем
- Сильная обратная корреляция между скоростью и ожиданиями (-0.79)
- Высокая нагрузка на LockManager
checkpoint_timeout=15 минут:
- Преимущества: Более плавное снижение производительности
- Недостатки:
- Наибольшие максимальные ожидания (10 128)
- Появление конкуренции за буферы (BufferMapping)
- Наихудшая минимальная производительность (140)
checkpoint_timeout=30 минут:
- Преимущества: Наиболее стабильные показатели
- Недостатки:
- Наименьшая максимальная производительность (1 888 191)
- Средние показатели по всем метрикам
Рекомендации для заданной нагрузки
Для варианта нагрузки №1 (высокая CPU, высокая читающая нагрузка, низкая пишущая):
- Оптимальное значение checkpoint_timeout: 15-30 минут
- Более редкие checkpoint снижают конкуренцию за ресурсы
- Уменьшают нагрузку на подсистему ввода-вывода
- Обеспечивают более стабильную производительность
Общий вывод
Параметр checkpoint_timeout оказывает значительное влияние на производительность СУБД при заданной нагрузке. Для варианта с высокой читающей и низкой пишущей нагрузкой рекомендуются более редкие checkpoint (15-30 минут), что обеспечивает более стабильную производительность и снижает конкуренцию за системные ресурсы.
Вариант нагрузки №2 - анализ с точки зрения производительности СУБД
Положительные эффекты увеличения интервала:
- Более высокая начальная производительность:
- При checkpoint_timeout=15-30мин производительность на 44% выше, чем при 1 мин
- Меньше накладных расходов на частые checkpoint-ы
Негативные эффекты увеличения интервала:
- Более быстрая деградация производительности:
- Производительность падает быстрее при длинных интервалах checkpoint
- Рост максимальных ожиданий:
- При checkpoint_timeout=30мин ожидания достигают 123 362 (против 112 965 при 1 мин)
- Усиление корреляции между скоростью и ожиданиями:
- Корреляция усиливается с -0.53 до -0.94, что указывает на более сильную зависимость производительности от ожиданий
Механизмы влияния checkpoint_timeout на пишущую нагрузку
При коротком интервале (1 мин):
- Частые checkpoint-ы → меньше данных для записи за раз
- Меньший объем WAL-файлов между checkpoint-ами
- Более равномерная нагрузка на IO, но больше накладных расходов
- Меньше блокировок extend (выше доля в общем объеме, но меньше абсолютное значение)
При длинном интервале (15-30 мин):
- Редкие checkpoint-ы → накопление больших объемов изменений
- Более интенсивные всплески записи при checkpoint-ах
- Увеличение объема DataFileRead (чтение "грязных" страниц для записи)
- Рост конкуренции за ресурсы (LWLock, BufferContent)
Рекомендации для заданного варианта нагрузки
Для высокой пишущей нагрузки с низким чтением:
- Оптимальный диапазон checkpoint_timeout: 5-10 минут
- Компромисс между накладными расходами (1 мин) и всплесками нагрузки (15-30 мин)
- Позволяет снизить частоту checkpoint-ов без чрезмерного накопления изменений
Выводы
- Checkpoint_timeout существенно влияет на производительность при высокой пишущей нагрузке, определяя баланс между накладными расходами и объемом единовременной записи.
- Экстремальные значения (1 мин и 30 мин) неоптимальны:
- 1 мин: слишком высокие накладные расходы, низкая начальная производительность
- 30 мин: быстрая деградация, высокие пиковые ожидания
- Наиболее стабильное поведение наблюдается при checkpoint_timeout=15 мин, хотя и с быстрой деградацией производительности.
- Основной лимитирующий фактор — IO-ожидания (DataFileRead), что необычно для пишущей нагрузки и указывает на необходимость оптимизации процессов чтения при checkpoint-ах.
- Рекомендуется тестирование промежуточных значений checkpoint_timeout (5-10 мин) для нахождения оптимального баланса для конкретной конфигурации и нагрузки.
Итоговый вывод с точки зрения производительности СУБД
Чем выше интенсивность пишущей нагрузки на СУБД тем более оптимальнее будет уменьшение интервала между контрольными точками.
Вариант нагрузки №1 - анализ производительности ОС
Влияние параметра checkpoint_timeout
- Нагрузка на CPU:
- Увеличение checkpoint_timeout с 1 до 15 минут снижает нагрузку на CPU (очередь процессов уменьшается с 98.21% до 79.57%).
- Дальнейшее увеличение до 30 минут ухудшает показатель (88.89%).
- Использование памяти:
- Увеличение checkpoint_timeout снижает дефицит свободной RAM (с 100% до 77.78%).
- Наиболее эффективно значение 30 минут.
- I/O:
- Параметр не оказывает значимого влияния на I/O при низкой пишущей нагрузке.
Итог
Параметр checkpoint_timeout влияет на баланс между нагрузкой на CPU и использованием памяти. Для заданного варианта нагрузки оптимальным является значение 15 минут, которое обеспечивает наименьшую нагрузку на CPU при приемлемом использовании памяти. Однако системные ресурсы (CPU и RAM) недостаточны для данной нагрузки, что требует дополнительной оптимизации или масштабирования.
Вариант нагрузки №2 - анализ производительности ОС
Параметр checkpoint_timeout почти не влияет на IO-проблемы:
- Во всех трёх экспериментах наблюдается одинаково высокий уровень ожиданий IO (wa).
- Количество процессов в состоянии uninterruptible sleep (ожидание IO) стабильно превышает количество ядер CPU.
- Это указывает на системную проблему с подсистемой IO, не связанную напрямую с частотой контрольных точек.
Увеличение checkpoint_timeout снижает нагрузку на CPU от LWLock:
- В эксперименте-1 (1 мин) корреляция LWLock-sy = 0.7721 (ALARM).
- В эксперименте-2 (15 мин) корреляция снижается до 0.4114 (INFO).
- В эксперименте-3 (30 мин) — 0.6755 (WARNING).
- Вывод: Более редкие контрольные точки снижают конкуренцию за лёгковесные блокировки (LWLock) в ядре.
Память — узкое место:
- Во всех экспериментах свободной памяти < 5%.
- Несмотря на это, свопинг не используется, что может указывать на:
- Неэффективное использование кэширования/буферизации.
- Возможную утечку памяти или недостаточный размер RAM для рабочей нагрузки.
Диски недогружены:
- При заявленной "высокой пишущей нагрузке" диски загружены лишь на 0.02–0.03%.
- Это может означать:
- Нагрузка буферизуется в памяти и ещё не сброшена на диск.
- Проблема не в дисках, а в подсистеме ввода-вывода на уровне ОС или СУБД.
Итог
Параметр checkpoint_timeout не является ключевым фактором для производительности ОС при заданном профиле нагрузки (низкий CPU, низкое чтение, высокая запись). Основные проблемы связаны с подсистемой IO и нехваткой памяти. Увеличение checkpoint_timeout до 15–30 минут может снизить нагрузку на CPU от LWLock, но не решит фундаментальных проблем с IO и памятью.
Итоговый вывод с точки зрения производительности ОС
Для данной инфраструктуры виртуальной машины и заданных вариантов нагрузки, параметр checkpoint_timeout не является ключевым фактором для производительности инфраструктуры из-за неоптимальной настройки управления IO и RAM.