PG_HAZEL : Анализ ожиданий Timeout/SpinDelay для СУБД , в ходе нагрузочного тестирования

Любые результаты требуют детального анализа

Задача

Проанализировать изменение ожиданий Timeout/SpinDelay в ходе нагрузочного тестирования

SpinDelay Ожидание получения конкурирующей циклической блокировки.

Конфигурация виртуальной машины и тестовая нагрузка

CPU = 8

RAM = 8GB

Postgres Professional Enterprise 17

Тестовая БД 10GB

Рост нагрузки экспоненциально от 5 до 115

Тестовый сценарий MIX : Select only + Select/Update + Insert Only

Нагрузочное тестирование

Эксперимент-2

Конфигурационные параметры по рекомендации нейросети + shared_buffer = 40% RAM.

shared_buffers = '2GB'

max_wal_size = '4GB'

min_wal_size = '1GB'

effective_cache_size = '6GB'

work_mem = '24MB'

temp_buffers = '48MB'

random_page_cost = '1.1'

effective_io_concurrency = '200'

commit_delay = '0'

Результаты нагрузочного тестирования

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

Ось X - точка наблюдения. Ось Y - операционная скорость

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

Ось X - точка наблюдения. Ось Y - ожидания СУБД

Ожидания Timeout/SpinDelay

Ось X - точка наблюдения. Ось Y - ожидания Timeout/SpinDelay

Ось X - точка наблюдения. Ось Y - доля ожиданий Timeout/SpinDelay в общем количестве ожидания в промиле

Ключевые точки:

• TIMEOUT to WAITINGS PPM = 0 : 5-8 соединений
• TIMEOUT to WAITINGS PPM 0 - 0.30 : 8-26 соединений
• TIMEOUT to WAITINGS PPM 0.30 - 0.40 : 26-48 соединений
• TIMEOUT to WAITINGS PPM > 0.40 : более 48 соединений

Влияние ожиданий Timeout/SpinDelay на производительность СУБД

1. SpinDelay ≠ основной источник ожиданий при высокой конкуренции

• Что такое SpinDelay?
  Это цикл активного ожидания (spinlock) для кратковременных низкоуровневых блокировок (LWLocks). Используется, чтобы избежать дорогостоящего переключения контекста ОС, если блокировка освободится быстро.
• Почему он может не расти?
  При высокой конкуренции конфликты из-за LWLocks часто переходят в состояние тяжеловесных (heavyweight) блокировок, где ядра не крутятся в цикле, а сразу передают управление ОС. Вместо SpinDelay вы увидите ожидания в LWLock (в pg_stat_activity), но не в счётчиках спинлоков.

2. Основные причины падения производительности (не связанные со SpinDelay)

• Конфликты за ресурсы CPU:
  При нехватке CPU (очереди планировщика ОС) процессы PostgreSQL переходят в состояние CPU load, а не активного ожидания. Рост нагрузки усугубляет эту проблему.
  Диагностика: высокий %sys в top, утилизация CPU близкая к 100%.
• I/O-бутылочное горлышко:
  При нехватке IOPS/пропускной способности диска процессы блокируются в ожидании чтения/записи данных (IO: DataFileRead, IO: DataFileWrite).
  Диагностика: высокий await в iostat, очередь диска.
• Блокировки буферного кеша:
  Конкуренция за буферы (разделяемые/эксклюзивные блокировки) приводит к ожиданиям типа BufferPin, BufFileRead.
• Тяжеловесные блокировки (LockManager):
  Рост числа транзакций → учащение конфликтов за строки/таблицы (ROW EXCLUSIVE, ACCESS EXCLUSIVE). Ожидания видны в pg_locks и pg_stat_activity (wait_event).
• Параллельные запросы:
  Конкуренция за ресурсы параллельных воркеров (wait_event: ParallelAppend).

3.Когда SpinDelay всё же растёт?

• Короткие всплески нагрузки с активной конкуренцией за LWLocks (например, при частом доступе к страницам индексов).

Продолжение