Сегодня первые практические результаты долгой работы . Просто стало интересно, а что есть по теме в гугле в Рунете? И выяснилось , что если погуглить "корреляционный анализ производительности СУБД PostgreSQL" , то в выдаче, кроме моих статей ничего и нет .
Получается - тема никому, кроме меня не интересна 😯. А ведь, тема интересная . Жаль, конечно , с одной стороны - и поговорить, обсудить не с кем🫤. Но, с другой стороны - получается приоритет в исследовании , чтоли 🤔.
Ладно, как говорится - делай, что должен и будь, что будет.
Товарищ , нервы сожми в узду! Взялся за дело - не охай. Есть результат - посылай всех в п*зду! Нет результата - пох*й!
Анализ событий ожиданий СУБД и определение SQL запросов оказывающих наибольшее влияние на производительность БД.
Основное отличие от предыдущей методики анализа производительности.
Корреляционный анализ проводится не по СУБД в целом , а по отдельным базам данных - 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.
Итоги
Корреляция между активными сессиями и операционной скоростью для Database-1очень слабая => Увеличение нагрузки на БД практически не ведет к снижению производительности БД.
Корреляция между активными сессиями и операционной скоростью для Database-2очень сильная =>Увеличение нагрузки на БД ведет к заметному снижению производительности БД.
Для Database-1 отсутствует корреляция между операционной скоростью и ожиданиями => Снижение производительности БД не вызвано ожиданиями БД.
Для Database-2 наиболее сильная отрицательная корреляция между операционной скоростью и ожиданиями типа Lock =>Тяжелые блокировки оказывают наибольшее влияние на снижение производительности СУБД.
Корреляционный анализ ожиданий для Database-2
Для проведения корреляционного анализа используется
Корреляция между типом ожидания (wait_event_type) и событием ожидания(wait_event)
Наиболее коррелированные события ожидания(сильная корреляция):
Lock/extend: Ожидание при расширении отношения.
LWLock/BufferContent: Ожидание при обращении к странице данных в памяти.
Корреляция между событием ожидания(wait_event) и SQL запросами
SQL запросы , роли и корреляция с событиями ожиданияSQL запросы , роли и корреляция с событиями ожидания
Список SQL запросов
Результат корреляционного анализа для Database-2
Результат корреляционного анализа для Database-2
Пользовательский запрос и события ожидания оказывающий наибольшее влияние на снижение производительности БД.
select custom_test( $1 )
События ожидания, оказывающие наибольшее влияние на снижение производительности БД
Коэффициенты корреляции между событиями ожидания в БД и SQL запросе.
MultiXactOffsetSLRU: Ожидание при обращении к SLRU-кешу данных о смещениях мультитранзакций.
MultiXactGen: Ожидание при чтении или изменении общего состояния мультитранзакций.
extend: Ожидание при расширении отношения.
BufferContent: Ожидание при обращении к странице данных в памяти.
WALInsert: Ожидание при добавлении записей WAL в буфер в памяти.
ProcArray: Ожидание при обращении к общим структурам данных в рамках процесса (например, при получении снимка или чтении идентификатора транзакции в сеансе).
CheckpointerComm: Ожидание при управлении запросами fsync.
BufferMapping: Ожидание при связывании блока данных с буфером в пуле буферов.
DataFileExtend: Ожидание расширения файла данных отношения.
LockManager: Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
Итог и практическое применение результатов корреляционного анализа
Для оптимизации и повышению производительности запроса "select custom_test( $1 )" необходимо выявить причины и оптимизировать работу с мультитранзакциями.
Планы на будущее и развитие
Корреляционный анализ событий ожидания СУБД в зависимости от сценариев нагрузочного тестирования.
Короткий период медианного сглаживания - синий график.
Долгий период медианного сглаживания - красный график.
Ось X - точка наблюдения. Ось Y - значение операционной скорости
Отличительная особенность сценария "INSERT ONLY" - резкий скачок операционной скорости. Скорее всего причина - изменение нагрузки на СХД виртуальной машины.
Сессии в состоянии 'active'
Ось X - точка наблюдения. Ось Y - количество активных сессий на точку времени.
Корреляция между операционной скоростью и количество сессий в состоянии 'active'
График скользящей корреляции.
Ось X - точка наблюдения. Ось Y - значение коэффициента корреляции.
Обращает на себя внимание факт непостоянного значения скользящей корреляции, близкой к косинусоиде.
Сценарий "SELECT ONLY". Ось X - точка наблюдения. Ось Y - значение коэффициента корреляции.
Коэффициент корреляции между операционной скоростью и количеством активных сессий за период наблюдений = 0,868388508671336 .
Сильная положительная корреляция между операционной скоростью и нагрузкой на СУБД .
Корреляционный анализ ожиданий СУБД
Гипотеза
Для определения SQL запроса оказывающего наибольшее влияние необходимо определить запрос с наибольшим значением коэффициента корреляции между ожиданиями СУБД и ожиданиями по SQL запросу.
Короткий период медианного сглаживания - синий график.
Долгий период медианного сглаживания - красный график.
Ось X - точка наблюдения. Ось Y - значение операционной скорости
Как видно из графика - имеется краткосрочная и долгосрочная тенденция снижения производительности СУБД.
Сессии в состоянии 'active'
Ось X - точка наблюдения. Ось Y - количество активных сессий на точку времени.
Корреляция между операционной скоростью и количество сессий в состоянии 'active'
График скользящей корреляции.
Ось X - точка наблюдения. Ось Y - значение коэфициента корреляции.
Коэффициент корреляции между операционной скоростью и количеством активных сессий за период наблюдений = -0,993357128393598 .
Ключевые точки наблюдения.
1-19 : коэфaициент близок к 1
23 - отрицательное значение коэффициента корреляции
80 - значение коэффициента корреляции уменьшается(растет по модулю)
Общая интерпретация значений коэффициента корреляции :
Очень слабая корреляция: [0 до 0.2]
Слабая корреляция: (0.2 до 0.5].
️Средняя корреляция: (0.5 до 0.7] .
️Сильная корреляция: (0.7 до 0.9].
️Очень сильная корреляция: (0.9 до 1].
Результат корреляционного анализа операционной скорости и активными сессиями :
После точки наблюдения 23 - СУБД работает в нештатном режиме.
Очень сильная корреляция между нагрузкой на СУБД и операционной скоростью СУБД.
Ожидания СУБД
Отношение времени ожидания к общему времени работы СУБД
Ось X - точка наблюдения. Ось Y - % ожиданий
Начиная с точки 60 - относительная доля ожиданий резко увеличивается. СУБД работает в нештатном режиме.
Корреляционный анализ ожиданий СУБД
Гипотеза
Для определения SQL запроса оказывающего наибольшее влияние необходимо определить запрос с наибольшим значением коэффициента корреляции между ожиданиями СУБД и ожиданиями по SQL запросу.
Результат корреляционного анализа
Результат работы сервисного скрипта
Наибольшее влияние на снижение производительности СУБД оказывает SQL запрос: queryid = -3703375232510669542 .
Шаги корреляционного анализа
1. Корреляция между операционной скоростью и определенными типом ожиданиям
Lock = -0,991080979500333
LWLock = -0,952840750047627
IPC = -0,00747093318897355
BufferPin = 0
Extension = 0
IO = 0
Ожидания типа Lock имеет большую корреляцию по сравнению с ожиданиями типа LWLock.
Ожидания типа Lock
Ось X - точка наблюдения. Ось Y - количество ожиданий типа Lock
Ожидания типа LWLock
Ось X - точка наблюдения. Ось Y - количество ожиданий типа LWLock
2.Корреляция между типом ожидания Lock и событиями ожиданий
transactionid = 0,999996784494388
tuple = 0,989898319693633
relation = 0,884541891919045
Ожидания transactionid
Ось X - точка наблюдения. Ось Y - количество ожиданий transactionid
Ожидания tuple
Ось X - точка наблюдения. Ось Y - количество ожиданий tuple
3. Корреляция между ожиданиями transactionid и SQL запросами
queryid = -3703375232510669542
Ось Y - количество ожиданий Lock/tuple для queryid =-3703375232510669542
Итоги
Гипотеза подтверждена экспериментально для данного сценария нагрузки.
Необходимо продолжение проведение экспериментов по корреляционному анализу :
Дополнительные сценарии нагрузочного тестирования .
Анализ метрик производительности при продуктивной нагрузке на СУБД.
Прямая корреляция между количество активных сессий и производительностью СУБД . Или другими словами - чем выше нагрузка на СУБД , тем выше производительность.
Статистические показатели ожиданий СУБД - корреляция ожиданий и производительности СУБД
Рис.2. Корреляционный анализ ожиданий и производительности 13:00-13:28
Количество пользовательских запросов по которым имеются события ожидания СУБД - минимально.
Сильная обратная корреляция - чем выше нагрузка на СУБД тем ниже производительность. Явный признак инцидента производительности СУБД
Статистические показатели ожиданий СУБД - корреляция ожиданий и производительности СУБД
Рис.4. Корреляционный анализ ожиданий и производительности СУБД нисходящего тренда 13:28 - 13:47
Как видно из таблицы - количество ожиданий кардинально увеличилось. Явный признак - имеются серьезные проблемы с производительностью СУБД.
2.Определение наиболее значимой причины деградации производительности СУБД
Из Рис.4 видно, что наибольшая обратная корреляция между событиями ожидания и снижением производительности СУБД имеется для события LWLock / BufferMapping
Рис.5. Ожидание LWLock / BufferMapping
Как видно - количество ожиданий менее чем за 20 минут - весьма существенно.
Итак, первый результат
Первой( но конечно не единственной) причиной деградации производительности СУБД в период 13:28 - 13:47 является - большое количество ожиданий LWLock / BufferMapping при выполнении пользовательских запросов.
Чуть подробнее об ожидании BufferMapping
Ожидание при связывании блока данных с буфером в пуле буферов.
This event occurs when a session is waiting to associate a data block with a buffer in the shared buffer pool.
Context
The shared buffer pool is an PostgreSQL memory area that holds all pages that are or were being used by processes. When a process needs a page, it reads the page into the shared buffer pool. The shared_buffers parameter sets the shared buffer size and reserves a memory area to store the table and index pages. If you change this parameter, make sure to restart the database. For more information, see Shared Buffer Area.
The buffer_mapping wait event occurs in the following scenarios:
A process searches the buffer table for a page and acquires a shared buffer mapping lock.
A process loads a page into the buffer pool and acquires an exclusive buffer mapping lock.
A process removes a page from the pool and acquires an exclusive buffer mapping lock.
3. Определение запросов с максимальным количество ожиданий
Рис.6. Запросы с ожиданием LWLock / BufferMapping c количество более 100.
Далее, дело техники, используя утилиту pgpro_pwr по queryid, находим проблемный запрос за период 13:30 - 13:50(снимки pgpro_pwr формируются каждые 10 минут).
Запрос передается разработчикам , для анализа .
Дальнейшие события ожидания анализируются схожим образом. Если отсортировать таблицу Рис.4. по количеству пользовательских запросов(более 100) , то можно и нужно сформировать список проблемных запросов для передачи группе разработки на оптимизацию и доработку.
Рис.7. Список ожиданий отсортированный по количеству пользовательских запросов.
Итог
Статистический анализ производительности СУБД позволяет подтвердить наличие деградации производительности не дожидаясь деградации на уровне приложения.
Корреляционный анализ ожиданий и производительности СУБД позволяет быстрее определить корневую причину снижения производительности СУБД и определить список проблемных пользовательских запросов.
P.S.
В настоящее время ведутся работы по разработке и тестированию новой версии инструментария по мониторингу и анализу производительности СУБД PostgreSQL - "Орешник".
Методология статистического анализа производительности СУБД PostgreSQL будет довольно существенно дополнена и доработана.
Наглядный пример того, как можно что угодно "притянуть за уши".
Очень понравилось, как ИИ подводит теоретическую базу под графики.
Количество фильмов с Дрю Бэрримор коррелирует с количеством голосов, отданных за кандидата в сенаторы от Либертарианской партии в Алабаме.
Чем больше люди смотрели фильмов с Дрю Бэрримор, тем сильнее они осознавали важность личной свободы и невмешательства в их жизнь. Все эти романтические комедии и трогательные драмы, вероятно, разожгли желание индивидуальной свободы, что привело к всплеску поддержки кандидатов-либертарианцев в Алабаме. Как будто её экранное обаяние и харизма насылали чары политического пробуждения по всему штату.
Использование ГМО в соевых бобах в штате Айова коррелирует с выработкой геотермальной энергии в России.
По мере того, как ГМО-соя в Айове процветала, она неосознанно образовала обширную подземную сеть, случайно соединившись с обширными корневыми системами российских одуванчиков. Эта беспрецедентная трансконтинентальная коммуникация растений создала синергию, которая увеличила производство геотермальной энергии в России. По сути, это был пример технологии производства одуванчиков на основе сои, доказывающий раз и навсегда, что когда жизнь дает вам ГМО-соевые бобы, вы также получаете совершенно новый способ получения энергии, предоставленный вам совместными усилиями международной флоры.
Вчерашний день уже внушает некоторый оптимизм. Основной положительный момент в том, что оба мамонта (Сбер и Лукойл) показали внушительный рост по 1.5%-2%. Объемов, правда, по-прежнему нет. Но я на другое предлагаю обратить внимание: посмотрите, что вчера показал индекс гособлигаций RGBI - рост более процента и бычье поглощение сразу двух предшествующих дней снижения. Я не говорю, что дно достигнуто. Предлагаю лишь обратить внимание на сонаправленность движения двух индексов. Вполне может быть, что мы входим в период их сильной корреляции.
Прошу обратить внимание, что я выкладываю свои посты на разных площадках, потому рекомендую подписаться на мой тг канал, где они все есть в одном потоке. Так вы гарантированно ничего не пропустите.
