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

Классический анализ - разбить проблему на составные части и внимательно изучить.

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

Сценарий "Insert 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.

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

Основное отличие от предыдущей методики.

Корреляционный анализ проводится не по СУБД в целом , а по отдельным базам данных - Database-1 , Database-2.

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

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

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

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

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

Деградация производительности Database-2 существенно сильнее .

Ожидания

WAITING RATIO

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

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

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

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

Доля ожиданий , при работе Database-2 выше на порядки.

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

Database-1

Наиболее значимый тип ожидания LWLock

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

Database-2

Наиболее значимые типы ожиданий IO , Lock , LWLock.

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

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

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

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

Относительная доля ожиданий для Database-1 существенно ниже , чем по Database-2.

Типы ожиданий IO , Lock - отсутствуют при работе Database-1.

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

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

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

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

1. Корреляция между активными сессиями и операционной скоростью для Database-1 очень слабая => Увеличение нагрузки на БД практически не ведет к снижению производительности БД.
2. Корреляция между активными сессиями и операционной скоростью для Database-2 очень сильная =>Увеличение нагрузки на БД ведет к заметному снижению производительности БД.
3. Для Database-1 отсутствует корреляция между операционной скоростью и ожиданиями => Снижение производительности БД не вызвано ожиданиями БД.
4. Для Database-2 наиболее сильная отрицательная корреляция между операционной скоростью и ожиданиями типа Lock =>Тяжелые блокировки оказывают наибольшее влияние на снижение производительности СУБД.

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

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

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

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

1. Lock/extend: Ожидание при расширении отношения.
2. LWLock/BufferContent: Ожидание при обращении к странице данных в памяти.

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

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

Список SQL запросов

Результат корреляционного анализа для Database-2

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

select custom_test( $1 )

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

Коэффициенты корреляции между событиями ожидания в БД и SQL запросе.

1. MultiXactOffsetSLRU: Ожидание при обращении к SLRU-кешу данных о смещениях мультитранзакций.
2. MultiXactGen: Ожидание при чтении или изменении общего состояния мультитранзакций.
3. extend: Ожидание при расширении отношения.
4. BufferContent: Ожидание при обращении к странице данных в памяти.
5. WALInsert: Ожидание при добавлении записей WAL в буфер в памяти.
6. ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
7. CheckpointerComm: Ожидание при управлении запросами fsync.
8. BufferMapping: Ожидание при связывании блока данных с буфером в пуле буферов.
9. DataFileExtend: Ожидание расширения файла данных отношения.
10. LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.

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

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

Планы на будущее и развитие

Корреляционный анализ событий ожидания СУБД в зависимости от сценариев нагрузочного тестирования.