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

Для подробного анализа причин - нужен инструмент.

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

Сценарий "OLTP" - SELECT + UPDATE.

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

Получение истории событий ожиданий БД и 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-1 практически нулевая. Не превышает 0.5%.

Доля ожиданий для Database-2 составляет основное время работу базы данных от 62 до 95%.

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

Database-1

Ожидания - практически отсутствуют

Ожидания по базе данных отсутствуют.

Корреляции с операционной скоростью - нет.

Database-2

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

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

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

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

Ожидания по для Database-1 - отсутствуют.

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

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

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

Корреляция с ожиданиями для Database-1 - отсутствует.

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

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

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

1. Имеется сильная обратная корреляция между ожиданиями Lock и LWLock для Database-2.

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

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

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

Средняя и очень сильная корреляция с событиями ожидания.

1. Lock / transactionid: Ожидание завершения транзакции.
2. Lock / tuple: Ожидание при запросе блокировки для кортежа.
3. LWLock / LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
4. LWLock / ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
5. LWLock / BufferContent : Ожидание при обращении к странице данных в памяти.
6. LWLock / WALWrite: Ожидание при записи буферов WAL на диск.

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

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

SQL запросы

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

select custom_test( $1 )

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

1. Lock / transactionid: Ожидание завершения транзакции.
2. Lock / tuple: Ожидание при запросе блокировки для кортежа.

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

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