Корреляционный анализ ожиданий СУБД PostgreSQL с использованием оперативно-тактического комплекса "PG_HAZEL" - Select Only

Требуется анализ под микроскопом.

Характер нагрузки

Сценарий "Select Only" - только читающая нагрузка.

Постановка задачи

Получение истории событий ожиданий БД и SQL запроса и анализ событий ожиданий СУБД с целью определения SQL запросов, оказывающих наибольшее влияние на производительность БД.

Версия СУБД и ресурсы ВМ

Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

Operating System: Astra Linux

Kernel: Linux 6.1.90-1-generic

processor       : 0

• model name      : Intel Xeon Processor (Skylake, IBRS, no TSX)
• cpu MHz         : 2693.670
• cpu cores       : 1

processor       : 1

• model name      : Intel Xeon Processor (Skylake, IBRS, no TSX)
• cpu MHz         : 2693.670
• cpu cores       : 1

RAM: 1GB

Database-1

База данных для сбора статистической информации производительности .

Database-2

Тестовая база данных для проведения нагрузочного тестирования .

Нагрузка создается пользовательским сценарием pgbench.

Рост количества подключений pgbench - экспоненциально от 6 до 111.

Предыдущая работа

Словарь терминов, используемых при анализе производительности СУБД

Статистические показатели операционной скорости.

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

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

Анализ операционной скорости

Характер изменения производительности баз данных - практически не отличается.

Ожидания

WAITING RATIO

Относительная доля(%), времени ожиданий от времени работы базы данных.

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

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

Анализ относительной доли ожиданий

Доля ожиданий для Database-2 практически нулевая.

Доля ожиданий для Database-1 значительно выше, но не превышает 5,5%.

WAIT_EVENT_TYPE (Типы ожиданий)

Database-1

Отрицательная корреляция по типам ожиданий

Ось X - точка наблюдения . Ось Y - количество ожиданий LWLock

Ось X - точка наблюдения . Ось Y - количество ожиданий IPC

Ось X - точка наблюдения . Ось Y - количество ожиданий Lock

Ось X - точка наблюдения . Ось Y - количество ожиданий IO

Наибольшее количество ожиданий типа LWLock.

Количество ожиданий других типов - незначительно.

Database-2

Сильная отрицательная корреляция с типом ожидания LWLock

Ось X - точка наблюдения . Ось Y - количество ожиданий LWLock

Количество ожиданий начинает непрерывно расти после точки 39(соответствует нагрузке 10 и более одновременных соединений).

Анализ типов ожиданий (WAIT_EVENT_TYPE)

Общее количество ожидания для Database-1 и Database-2 в целом невелико. После точки 39(соответствует нагрузке 10 и более одновременных соединений) - ожидания типа LWLock начинают непрерывно расти.

Общий корреляционный анализ ожиданий

Коэффициенты корреляции

• SPEED CORR: коэффициент корреляции между количеством активных сессий к БД и операционной скоростью.
• BUFFERPIN CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Bufferpin.
• EXTENSION CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Extension.
• IO CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IO.
• IPC CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IPC.
• LOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Lock.
• LWLOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа LWLock.

Итоги и гипотезы

1. Отрицательная корреляция нагрузки и операционной скорости для Database-1 выше , чем для Database-2.
2. Для данного сценария нагрузки для Database-1 ожидания типа IO, IPC, Lock , LWLock имеют отрицательную сильную корреляцию с операционной скоростью.
3. Для Database-2 сильная отрицательная корреляция только по ожиданию LWLock - легковесные блокировки.

Корреляционный анализ ожиданий для Database-2

Для проведения корреляционного анализа используется

Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event)

Наиболее коррелированные события ожидания(слабая и средняя корреляция):

1. LWLock/LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
2. LWLock/ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

Корреляция между событием ожидания(wait_event) и SQL запросами

SQL запросы , роли , корреляция с событиями ожидания. SQL запрос.

Пользовательский запрос и события ожидания оказывающий наибольшее влияние на снижение производительности БД.

select custom_test( $1 )

События ожидания, оказывающие наибольшее влияние на снижение производительности БД

1. LWLock/LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
2. LWLock/ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).

Итог и практическое применение результатов корреляционного анализа

Для оптимизации и повышению производительности запроса "select custom_test( $1 )" необходимо выявить причины и оптимизировать работу с легковесными блокировками.