В мире PostgreSQL, как и в автоспорте, не существует единой идеальной стратегии для всех трасс. Выбор интервала контрольных точек (checkpoint_timeout) — это пит-стоп: можно заезжать часто для максимальной скорости на прямых, но рискуя потерять время на самом заезде, или реже — для стабильного и предсказуемого ритма всей гонки. Всё зависит от типа «трассы» — характера нагрузки на вашу базу данных.

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

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

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

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

Вариант нагрузки №1

  • Высокая нагрузка на CPU

  • Высокая читающая нагрузка

  • Низкая пишущая нагрузка.

Тестовый сценарий-1 (SELECT BY PK)
SELECT *
FROM bookings.airports_data
WHERE airport_code = (
  SELECT airport_code
  FROM bookings.airports_data
  ORDER BY random()
  LIMIT 1
);
Тестовый сценарий-2 (GROUP BY)
WITH random_airplanes AS (
  SELECT airplane_code
  FROM bookings.airplanes_data
  ORDER BY random()
  LIMIT (random() * 9 + 1)::int -- От 1 до 10 значений
)
SELECT s.fare_conditions,
   COUNT(*) as seat_count,
   COUNT(DISTINCT s.airplane_code) as airplane_types
FROM bookings.seats s
WHERE s.airplane_code IN (SELECT airplane_code FROM random_airplanes)
GROUP BY s.fare_conditions
ORDER BY seat_count DESC;
Тестовый сценарий-3 (ORDER BY)

Новости
Статьи
Видео
Ролики
Сохранённое
Видеоигры
Детям
Всё о Дзене
Вакансии
Дзен на 
iOS и Android

Ещё
Postgres DBA
ваш канал
--Тестовый сценарий-3 (ORDER BY)
SELECT book_ref,
   book_date,
   total_amount,
   EXTRACT(DAY FROM book_date) as booking_day
FROM bookings.bookings
WHERE total_amount > 0
ORDER BY book_date
LIMIT (random() * 499 + 1)::int; -- От 1 до 500 записей
Тестовый сценарий-4 (JOIN)
WITH random_departures AS (
  SELECT airport_code
  FROM bookings.airports_data
  ORDER BY random()
  LIMIT (random() * 9 + 1)::int
),
random_validity_date AS (
  SELECT
   date_trunc('day',
   lower(validity) +
   (random() * EXTRACT(EPOCH FROM (upper(validity) - lower(validity)))) * interval '1 second'
   ) as random_date
  FROM bookings.routes
  WHERE validity IS NOT NULL
   AND upper(validity) > lower(validity) + interval '7 days'
  ORDER BY random()
  LIMIT 1
)
SELECT r.route_no,
   r.departure_airport,
   a_dep.city->>'en' as departure_city,
   r.arrival_airport,
   a_arr.city->>'en' as arrival_city,
   r.duration,
   r.days_of_week
FROM bookings.routes r
JOIN bookings.airports_data a_dep ON r.departure_airport = a_dep.airport_code
JOIN bookings.airports_data a_arr ON r.arrival_airport = a_arr.airport_code
CROSS JOIN random_validity_date rvd
WHERE r.departure_airport IN (SELECT airport_code FROM random_departures)
 AND r.validity @> rvd.random_date
 AND array_length(r.days_of_week, 1) > 0
ORDER BY r.departure_airport, r.route_no
LIMIT 300;
Тестовый сценарий-5 (INSERT + DELETE)
-- INSERT тестовой брони
  INSERT INTO bookings.bookings(book_ref, book_date, total_amount)
  VALUES (test_book_ref, now(), 0.0)
  ON CONFLICT (book_ref) DO NOTHING;
PERFORM pg_sleep(0.02);
-- DELETE тестовой брони
  DELETE FROM bookings.bookings
  WHERE book_ref LIKE 'scenario5-TEST%' AND book_date < now() - interval '5 minute';
Тестовый сценарий-6 (UPDATE)
WITH completed_flights AS (
  SELECT DISTINCT flight_id
  FROM bookings.flights
  WHERE status = 'Arrived'
   AND actual_arrival < (
   SELECT actual_arrival
   FROM bookings.flights
   WHERE actual_arrival IS NOT NULL
   ORDER BY random()
   LIMIT 1
   )
  LIMIT 5
)
UPDATE gen.seats_remain sr
SET available = (
  SELECT count(*)
  FROM bookings.seats s
  JOIN bookings.routes r ON r.airplane_code = s.airplane_code
  JOIN bookings.flights f ON f.route_no = r.route_no
  WHERE f.flight_id = sr.flight_id
)
FROM completed_flights cf
WHERE sr.flight_id = cf.flight_id;

Весовые коэффициенты тестовых сценариев

scenario1 = 1.0
scenario2 = 1.0
scenario3 = 1.0
scenario4 = 1.0
scenario5 = -1.0  #Неконкурентная нагрузка = 1 соединение
scenario6 = 0.5

Вариант нагрузки №2

  • Низкая нагрузка на CPU

  • Низкая читающая нагрузка

  • Высокая пишущая нагрузка.

Тестовый сценарий-1 (SELECT)
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;
select br.bbalance
into test_rec
from pgbench_branches br
join pgbench_accounts acc on (br.bid = acc.bid )
where acc.aid = current_aid ;
Тестовый сценарий-2 (UPDATE)
UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE aid = current_aid ;

UPDATE pgbench_tellers SET tbalance = tbalance + current_delta WHERE tid = current_tid ;

UPDATE pgbench_branches SET bbalance = bbalance + current_delta WHERE bid = current_bid ;
Тестовый сценарий-3 (INSERT)
FOR counter IN 1..1000
LOOP
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES ( current_tid , current_bid , current_aid , current_delta , CURRENT_TIMESTAMP );
END LOOP;

Весовые коэффициенты тестовых сценариев

scenario1 = 1.0
scenario2 = 0.5
scenario3 = 0.5

Исходные данные экспериментов:

Вариант нагрузки-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-ов без чрезмерного накопления изменений

Выводы

  1. Checkpoint_timeout существенно влияет на производительность при высокой пишущей нагрузке, определяя баланс между накладными расходами и объемом единовременной записи.

  2. Экстремальные значения (1 мин и 30 мин) неоптимальны:

    • 1 мин: слишком высокие накладные расходы, низкая начальная производительность

    • 30 мин: быстрая деградация, высокие пиковые ожидания

  3. Наиболее стабильное поведение наблюдается при checkpoint_timeout=15 мин, хотя и с быстрой деградацией производительности.

  4. Основной лимитирующий фактор — IO-ожидания (DataFileRead), что необычно для пишущей нагрузки и указывает на необходимость оптимизации процессов чтения при checkpoint-ах.

  5. Рекомендуется тестирование промежуточных значений 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 минут.

Итог

Параметр 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 для рабочей нагрузки.

Итог

Параметр checkpoint_timeout не является ключевым фактором для производительности ОС при заданном профиле нагрузки (низкий CPU, низкое чтение, высокая запись). Основные проблемы связаны с подсистемой IO и нехваткой памяти. Увеличение checkpoint_timeout до 15–30 минут может снизить нагрузку на CPU от LWLock, но не решит фундаментальных проблем с IO и памятью.

Итоговый вывод с точки зрения производительности ОС

Для данной инфраструктуры виртуальной машины и заданных вариантов нагрузки, параметр checkpoint_timeout не является ключевым фактором для производительности инфраструктуры из-за неоптимальной настройки IO и RAM.