Только соревнование позволит выявить победителя.
Только соревнование позволит выявить победителя.

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

Предисловие:

В современных высоконагруженных системах каждая настройка СУБД может стать как инструментом тонкой оптимизации, так и источником непредсказуемых проблем. В данной статье на основе серии нагрузочных тестов исследуется влияние параметра checkpoint_timeout на операционную скорость, ожидания и общую стабильность базы данных. Мы сравним три сценария (1, 15 и 30 минут), выявим «резонансные зоны», проанализируем трансформацию механизмов блокировок и предложим практические рекомендации по настройке. Результаты показывают, что выбор интервала контрольных точек — это не просто компромисс между производительностью и надёжностью, а сложное решение, способное кардинально изменить поведение системы под нагрузкой.

Тестовые сценарии

Тестовый сценарий-1 (SELECT BY PK)
-- scenario1.sql
--SELECT BY PK
CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$
DECLARE
 test_rec record ; 
BEGIN
SET application_name = 'scenario1';

SELECT * 
INTO test_rec
FROM bookings.airports_data 
WHERE airport_code = (
    SELECT airport_code 
    FROM bookings.airports_data 
    ORDER BY random() 
    LIMIT 1
);	
		
 return 0 ; 
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-2 (GROUP BY)
-- scenario2.sql
--GROUP BY
CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$
DECLARE
 test_rec record ;  
BEGIN
    SET application_name = 'scenario2';
	
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
INTO test_rec
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;


 return 0 ; 
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-3 (ORDER BY)
-- scenario3.sql
--ORDER BY 
CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$
DECLARE
 test_rec record ;  
BEGIN
    SET application_name = 'scenario3';
	
SELECT book_ref, 
       book_date, 
       total_amount,
       EXTRACT(DAY FROM book_date) as booking_day
INTO test_rec
FROM bookings.bookings 
WHERE total_amount > 0
ORDER BY book_date 
LIMIT (random() * 499 + 1)::int;  -- От 1 до 500 записей
	

 return 0 ; 
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-4 (JOIN)
-- scenario4.sql
--JOIN
CREATE OR REPLACE FUNCTION scenario4() RETURNS integer AS $$
DECLARE
 test_rec record ;  
BEGIN
    SET application_name = 'scenario4';
	
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
INTO test_rec
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;
	
	
 return 0 ; 
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-5 (INSERT + DELETE)
-- scenario5.sql
--INSERT bookings.bookings
--DELETE book_ref LIKE 'scenario5-TEST%';
CREATE OR REPLACE FUNCTION scenario5() RETURNS integer AS $$
DECLARE
 test_book_ref  character varying(60);
BEGIN
    SET application_name = 'scenario5';
	
    -- Генерируем уникальный book_ref для теста
    test_book_ref := 'scenario5-TEST' || lpad((random() * 999)::INT::text, 2, '0');

	-- 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';
	
	
 return 0 ; 
END
$$ LANGUAGE plpgsql;
Тестовый сценарий-6 (UPDATE)
-- scenario6.sql
-- Комплексный UPDATE для таблицы seats_remain
CREATE OR REPLACE FUNCTION scenario6() RETURNS integer AS $$
DECLARE
 test_rec record ;  
 test_book_ref CHAR(6);
BEGIN
    SET application_name = 'scenario6';
	
    -- 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;


	
	
 return 0 ; 
END
$$ LANGUAGE plpgsql;

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

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

Задача

  1. Провести сравнительный анализ влияния checkpoint_timeout на производительность и ожидания СУБД для синтетической нагрузки в ходе нагрузочного тестирования.

  2. Подготовить методику проведения сводного сравнительного анализа результатов нагрузочных тестов.

Эксперимент-1 : checkpoint='1min'

Граничные условия операционной скорости и ожиданий СУБД для checkpoint='1min'
Граничные условия операционной скорости и ожиданий СУБД для checkpoint='1min'

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

График изменения операционной скорости в ходе нагрузочного тестирования при checkpoint='1min'
График изменения операционной скорости в ходе нагрузочного тестирования при checkpoint='1min'

Ожидания СУБД

График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='1min'
График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='1min'

Эксперимент-2 : checkpoint='15min'

Граничные условия операционной скорости и ожиданий СУБД для checkpoint='15min'
Граничные условия операционной скорости и ожиданий СУБД для checkpoint='15min'

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

График изменения операционной скорости в ходе нагрузочного тестирования при checkpoint='15min'
График изменения операционной скорости в ходе нагрузочного тестирования при checkpoint='15min'

Ожидания СУБД

График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='15min'
График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='15min'

Эксперимент-3 : checkpoint='30min'

Граничные условия операционной скорости и ожиданий СУБД для checkpoint='30min'
Граничные условия операционной скорости и ожиданий СУБД для checkpoint='30min'

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

График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='30min'
График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='30min'

Ожидания СУБД

График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='30min'
График изменения ожидания СУБД в ходе нагрузочного тестирования при checkpoint='30min'

Общий сравнительный анализ метрик производительности

Влияние на операционную скорость

Прямое влияние:

  • Максимальная производительность снижается с увеличением checkpoint_timeout: с 2.6 млн (1 мин) до 1.9 млн (30 мин) — снижение на 27%.

  • Минимальная производительность улучшается при увеличении интервала: от 270 (1 мин) до 1,284 (30 мин) — рост в 4.7 раза, что указывает на меньшие просадки при редких контрольных точках.

  • Стабильность (R²) ухудшается при увеличении интервала, достигая минимума при 15 минутах (0.47), что означает менее предсказуемую производительность.

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

Влияние на ожидания

Критические изменения:

  • Максимальные ожидания достигают пика при 15 минутах (10 128), что на 16% выше, чем при 1 минуте.

  • Минимальные ожидания значительно возрастают с увеличением интервала (в 2.6 раза), что указывает на более высокий базовый уровень блокировок.

  • Рост ожиданий (угол наклона) максимален при 15 минутах (+42.43), что свидетельствует о наиболее интенсивном накоплении блокировок со временем.

Интерпретация: Оптимальным с точки зрения минимизации максимальных ожиданий является 30-минутный интервал, однако 15-минутный показывает наибольший рост блокировок в ходе теста.

Общие выводы о влиянии checkpoint_timeout

Баланс между производительностью и стабильностью:

  1. Короткий интервал (1 мин) обеспечивает максимальную пиковую производительность, но создает частые просадки и высокую волатильность.

  2. Средний интервал (15 мин) оказывается наихудшим по большинству показателей: максимальные ожидания, минимальная стабильность скорости, наибольший рост блокировок со временем.

  3. Длинный интервал (30 мин) обеспечивает наибольшую стабильность минимальной производительности и меньший уровень максимальных ожиданий, но ценой снижения пиковой производительности.

Рекомендация: Для рабочих нагрузок, чувствительных к просадкам производительности, рекомендуется использовать более длинные интервалы checkpoint_timeout (30 мин). Для систем, требующих максимальной пиковой производительности, могут быть предпочтительны более короткие интервалы, но с риском большей волатильности.

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

Сравнительный анализ стабильности системы

Влияние нагрузки (LOAD) на WAITINGS:

  • 1 мин: Сильная реакция на увеличение LOAD, быстрый рост WAITINGS

  • 15 мин: Наиболее чувствительная система - максимальный рост WAITINGS при увеличении LOAD

  • 30 мин: Наиболее устойчивая - меньший рост WAITINGS при той же нагрузке

Временные паттерны просадок:

  • 1 мин: Частые, кратковременные просадки (5-10 минут)

  • 15 мин: Редкие, глубокие и продолжительные просадки (15-30 минут)

  • 30 мин: Умеренные по глубине и продолжительности просадки

Выводы о стабильности системы

Наибольшие пики ожиданий и просадки производительности:

  1. Абс��лютный максимум WAITINGS (10,128): Эксперимент-2 (15 мин)

  2. Наибольшая просадка SPEED (99.99%): Эксперимент-1 (1 мин) - с 2,599,957 до 270

  3. Абсолютный минимум SPEED (140): Эксперимент-2 (15 мин)

  4. Наибольшая продолжительность просадок: Эксперимент-2 (15 мин)

Рейтинг стабильности (от наиболее стабильной к наименее):

  1. 30 минут: Наиболее стабильная работа, предсказуемое поведение, меньшие экстремумы

  2. 1 минута: Частые колебания, но быстрое восстановление, высокая пиковая производительность

  3. 15 минут: Наименее стабильная - максимальные просадки, медленное восстановление, непредсказуемое поведение

Критическое наблюдение:

15-минутный интервал демонстрирует "резонансный эффект" - совпадение периода контрольных точек с другими системными процессами приводит к наихудшим показателям. Это свидетельствует о том, что выбор checkpoint_timeout должен учитывать временные характеристики рабочей нагрузки.

Узкие места механизмов синхронизации

При checkpoint_timeout = 1 мин:

Узкое место: TIMEOUT и менеджер блокировок (LockManager)

  • Частые контрольные точки создают постоянную конкуренцию за LockManager

  • Положительная корреляция TIMEOUT-WAITINGS (0.41) указывает на таймауты как следствие ожиданий

  • Система тратит время на управление частыми точками восстановления

При checkpoint_timeout = 15 мин:

Узкое место: Буферный кэш (BufferMapping) + LOCK транзакций

  • Наиболее проблемная конфигурация

  • BufferMapping становится значимым (22.48% LWLock) - проблемы с отображением буферов

  • Максимальная корреляция LOCK-WAITINGS (0.96) - блокировки транзакций основной фактор

  • Отрицательная корреляция IO-WAITINGS (-0.56) - при росте ожиданий IO операции снижаются

  • Резонансный эффект: Период контрольных точек синхронизируется с другими процессами

При checkpoint_timeout = 30 мин:

Узкое место: Легковесные блокировки (LWLOCK)

  • Максимальная корреляция LWLOCK-WAITINGS (0.91)

  • Сильнейшая отрицательная корреляция TIMEOUT-WAITINGS (-0.72) - процессы дольше ждут без таймаута.

  • Система блокируется на уровне внутренних структур данных (LockManager)

  • Более предсказуемое поведение, но потенциальные deadlock-ситуации

Общие выводы

  1. Короткие интервалы (1 мин) → Менеджмент контрольных точек (LockManager, TIMEOUT)

  2. Средние интервалы (15 мин) → Буферный кэш + транзакционные блокировки (BufferMapping, LOCK)

  3. Длинные интервалы (30 мин) → Внутренние структуры данных (LWLOCK, LockManager)

Checkpoint_timeout = 15 минут создает "резонансную зону", где совпадают периодичности различных системных процессов, что приводит к максимальной конкуренции за ресурсы и появлению BufferMapping как нового узкого места. Этот интервал следует избегать в рабочих системах.

Оптимальная стратегия: Использовать либо очень короткие (1 мин), либо достаточно длинные (30 мин) интервалы, избегая промежуточных значений, которые могут синхронизироваться с другими системными процессами и создавать пиковую нагрузку на механизмы синхронизации.

Заключительные выводы

  1. Checkpoint_timeout не является линейным параметром - существует "резонансная зона" (около 15 мин), где наблюдаются наихудшие характеристики.

  2. Оптимальное значение зависит от рабочей нагрузки - для данной конфигурации и сценариев оптимальным является 30 минут.

  3. Негативное влияние контрольных точек можно минимизировать через:
    Правильный выбор интервала (избегать резонансных значений)
    Пропорциональную настройку связанных параметров
    Оптимизацию проблемных запросов
    Адекватный мониторинг ключевых метрик

  4. Рекомендуемая стратегия внедрения:
    Начинать с checkpoint_timeout = 30min
    Мониторить указанные метрики в течение 2-4 недель
    Корректировать значение на основе фактических данных
    Избегать значений в диапазоне 10-20 минут (резонансная зона)

Финальная рекомендация: 

Для систем со схожими характеристиками (объем данных, нагрузка, аппаратная конфигурация) использовать checkpoint_timeout = 30 минут как отправную точку с обязательным мониторингом LOCK, LWLOCK и BufferMapping событий.