Postgresql

Общая интерпретация значений коэффициента корреляции :

• Очень слабая корреляция: [0 до 0.2]

• Слабая корреляция: (0.2 до 0.5].

• ️Средняя корреляция: (0.5 до 0.7] .

• ️Сильная корреляция: (0.7 до 0.9].

• ️Очень сильная корреляция: (0.9 до 1].

Общая идея и последовательность действий

Шаг 1

Определяется тип ожидания (wait_event_type), имеющий наименьший коэффициент корреляции(❗отрицательная корреляция❗) с операционной скоростью.

Шаг 2

Определяется событие ожидания (wait_event) , имеющий наибольший коэффициент корреляции (❗положительная корреляция❗) с типом ожидания , определённом на предыдущем шаге.

Шаг 3

Определяется SQL запрос, имеющий наибольший коэффициент корреляции (❗положительная корреляция❗) между количеством ожидания(wait_event, определённом на предыдущем шаге) запроса и количеством ожиданий кластера .

ГИПОТЕЗА

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

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

Чем больше заданный промежуток анализа данных, тем надёжнее результат .

Доказательство

Гипотеза не может быть строго доказана по причине неполноты собираемых данных об ожиданиях СУБД.

Например - не собирается статистика ожиданий CPU.

Возможна ситуация - гипервизор уменьшил тактовую частоту CPU , но со стороны СУБД данное событие никак не будет зафиксировано.

Однако, для практических целей корреляционного анализа ожиданий СУБД, гипотеза может быть принята в качестве рабочей.

Дополнение по теме:

https://dzen.ru/a/Z8cqn5H5K3IHCENn

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

Установить характерные признаки ожиданий и результаты корреляционного анализа в зависимости от сценариев нагрузочного тестирования .

Database-1

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

Database-2

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

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

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

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

Сценарий 1 - "Select Only"

Только читающая нагрузка на тестовую БД .

Сценарий 2 - "Insert Only"

Только пишущая нагрузка на тестовую БД .

Сценарий 3 - "OLTP"

Имитация теста TPC-B . Нагрузка "SELECT + UPDATE" в тестовой БД .

Характерные признаки сценариев нагрузочного тестирования

Сценарий 1 - "Select Only"

Сценарий характеризуется

1. Cильной корреляцией с событиями ожидания:

• LWLock/LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.

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

2. Очень низким относительной долей ожиданий: менее 1%

Сценарий 2 - "Insert Only"

Сценарий характеризуется

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

• MultiXactOffsetSLRU: Ожидание при обращении к SLRU-кешу данных о смещениях мультитранзакций.

2. Не высокой относительной долей ожиданий: 17-35%

Сценарий 3 - "OLTP"

Сценарий характеризуется

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

• Lock / transactionid: Ожидание завершения транзакции.

• Lock / tuple: Ожидание при запросе блокировки для кортежа.

2. Высокой относительной долей ожиданий: 62-95%.

Продолжение работ по теме

Анализ результатов нагрузочного тестирования СУБД PostgreSQL с использованием разных сценариев оперативно-тактического комплекса "PG_HAZEL".

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

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

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

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

Статистические показатели производительности Баз Данных.

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

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

Ожидания

WAITING RATIO

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

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

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

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

Database-1

Database-2

Анализ типов ожиданий (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 запросами

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

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

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

select custom_test( $1 )

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

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 )" необходимо выявить причины и оптимизировать работу с мультитранзакциями.

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

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