Горячее
Лучшее
Свежее
Подписки
Сообщества
Блоги
Эксперты
Войти
Забыли пароль?
или продолжите с
Создать аккаунт
Я хочу получать рассылки с лучшими постами за неделю
или
Восстановление пароля
Восстановление пароля
Получить код в Telegram
Войти с Яндекс ID Войти через VK ID
Создавая аккаунт, я соглашаюсь с правилами Пикабу и даю согласие на обработку персональных данных.
ПромокодыРаботаКурсыРекламаИгрыПополнение Steam
Пикабу Игры +1000 бесплатных онлайн игр
Станьте Детективом! Решайте логические головоломки, чтобы найти преступника! 
Множество уровней и интересных историй! События и задачи дня!

Тебе предстоит раскрывать массу разных дел, в этом тебе поможет известный всем сыщикам метод дедукции.

Детектив - логические головоломки

Головоломки, Казуальные, Логическая

Играть

Топ прошлой недели

  • Oskanov Oskanov 8 постов
  • alekseyJHL alekseyJHL 6 постов
  • XpyMy XpyMy 1 пост
Посмотреть весь топ

Лучшие посты недели

Рассылка Пикабу: отправляем самые рейтинговые материалы за 7 дней 🔥

Нажимая кнопку «Подписаться на рассылку», я соглашаюсь с Правилами Пикабу и даю согласие на обработку персональных данных.

Спасибо, что подписались!
Пожалуйста, проверьте почту 😊

Новости Пикабу Помощь Кодекс Пикабу Реклама О компании
Команда Пикабу Награды Контакты О проекте Зал славы
Промокоды Скидки Работа Курсы Блоги
Купоны Biggeek Купоны AliExpress Купоны М.Видео Купоны YandexTravel Купоны Lamoda
Мобильное приложение

Корреляция

С этим тегом используют

Postgresql Субд Статистика Все
38 постов сначала свежее
1
kznalp
kznalp
2 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример)⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Если правильно и вовремя обслуживать - СУБД будет летать !

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

  1. Проанализировать инциденты снижения скорости СУБД.

  2. Установить причины снижения скорости СУБД.

  3. Сформировать список мероприятий для устранения причин снижения скорости СУБД.

  4. Итог.

Шаблон решения задачи

https://dzen.ru/a/Z-4mhu9oFCnB-jp9

Отчетность для анализа

https://dzen.ru/a/Z-6YLjZRni5hLvtD

1.Проанализировать инциденты снижения скорости СУБД

Ожидания и корреляция по инцидентам снижения скорости СУБД

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Фрагмент таблицы инцидентов снижения скорости СУБД

Столбцы таблицы:

  • ID : идентификатор инцидента снижения скорости СУБД

  • START TIME : время начала инцидента

  • FINISH TIME : время окончания инцидента

  • № : порядковый номер

  • IO : количество ожидания типа IO на время начала инцидента

  • IO CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями IO за отрезок [ START TIME - 1 ЧАС ; START TIME ]

  • LWLock : количество ожидания типа LWLock на время начала инцидента

  • LWLock CORRELATION : коэффициент корреляции между операционной скоростью и ожиданиями LWLock за отрезок [ START TIME - 1 ЧАС ; START TIME ]

Количество ожидания типа IO , LWLock по инцидентам

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - ID инцидента. Ось Y - количество ожидания типа IO на начало инцидента

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - ID инцидента. Ось Y - количество ожидания типа LWLock на начало инцидента

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - ID инцидента. Ось Y - коэффициент корреляции между всеми ожиданиями и ожиданиями типа IO , LWLock на начало инцидента

Особенности инцидентов 34 , 36 :

  1. Коэффициент корреляции между ожиданиями СУБД в целом и ожиданиями типа LWLock больше , чем между ожиданиями СУБД в целом и ожиданиями типа IO.

  2. Количество ожидания типа LWLock меньше чем количество ожидания типа IO.

Графики операционной скорости и ожиданий по инцидентам снижения скорости

Инцидент 34

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

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

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

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

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

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

Инцидент 36

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

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

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий в целом по СУБД.

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

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

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

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

Для справки: ожидания типа IO , LWLock по данным отчета "Top wait events" pgpro_pwr

Инцидент 34

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Инцидент 36

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

2. Установить причины снижения скорости СУБД

SQL запросы, имеющие наибольшую долю ожидания заданного типа

Инцидент 34

Ожидания типа IO

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа IO

Ожидания типа LWLock

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа LWLock

Столбцы таблицы:

  • QUERYID : queryid SQL выражения , из представления pgpro_stats.

  • PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.

  • CALLS : количество выполнений SQL выражения

  • WAITINGS : количество ожиданий

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение

  • WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.

Результат:

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Инцидент 36

Ожидания типа IO

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа IO

Ожидания типа LWLock

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика вызовов и ожидания по запросам имеющим ожидания типа LWLock

Столбцы таблицы:

  • QUERYID : queryid SQL выражения , из представления pgpro_stats.

  • PGPRO_PWR_QUERYID : шестнадцатеричное значение queryid , для использования в отчетах pgpro_pwr.

  • CALLS : количество выполнений SQL выражения

  • WAITINGS : количество ожиданий

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение

  • WAITINGS PPM : доля(в промилле) ожиданий типа IPC по данному SQL среди всех ожиданий по всем SQL за анализируемый период.

Результат:

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Главная причина снижения скорости СУБД

Запрос queryid=2092406791392746781 имеет набольшую долю ожидания типа IO и LWLock среди всех запросов имеющих корреляцию ожидания с типом IO, LWLock.

Текст запроса

Доступен в pgpro_pwr

План выполнения запроса

Доступен в pgpro_pwr

События ожидания при выполнении запроса 2092406791392746781

Инцидент 34

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика выполнения и событий ожидания по запросу 2092406791392746781

Столбцы таблицы:

  • timestamp : точка времени сбора статистических данных уровня SQL.

  • datname : База данных, в которой выполнялся SQL запрос.

  • rolname : Роль, под которой выполнялся SQL запрос.

  • CALLS : Количество выполнений запроса .

  • WAITINGS : Количество ожиданий типа IO , LWLock .

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение.

  • WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

  • SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

  • IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

  • LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Инцидент 36

Анализ производительности СУБД PostgreSQL с использованием PG_HAZEL (практический пример) Субд, Postgresql, Мониторинг, Производительность, Корреляция, Длиннопост

Статистика выполнения и событий ожидания по запросу 2092406791392746781

Cтолбцы таблицы:

  • timestamp : точка времени сбора статистических данных уровня SQL.

  • datname : База данных, в которой выполнялся SQL запрос.

  • rolname : Роль, под которой выполнялся SQL запрос.

  • CALLS : Количество выполнений запроса .

  • WAITINGS : Количество ожиданий типа IO , LWLock .

  • WAITINGS TO CALLS : количество ожиданий на одно выполнение.

  • WAIT_EVENTS : события ожидания wait_event , возникающие при выполнении SQL запроса .

  • SQL : текст SQL запроса (не приведен).

События ожидания возникающие при выполнении SQL запроса:

  • IO / DSMFillZeroWrite : Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

  • LWLock / ParallelHashJoin : Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

  • LWLock / BufferMapping : Ожидание при связывании блока данных с буфером в пуле буферов.

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

3. Cписок мероприятий для устранения причин снижения скорости СУБД .

Мероприятия для снижения ожиданий DSMFillZeroWrite

События ожидания DSMFillZeroWrite в PostgreSQL связаны с операциями записи в разделяемую память (shared memory), где необходимо заполнить область нулями перед использованием.

Это может происходить при работе с большими объектами или при высокой нагрузке на систему.

Чтобы снизить количество таких событий, можно рассмотреть следующие шаги:

1. Оптимизация запросов и транзакций:

- Убедитесь, что ваши запросы оптимизированы и не выполняют избыточных операций.

- Сократите длительность транзакций, чтобы уменьшить нагрузку на разделяемую память.

2. Настройка параметров конфигурации:

- Увеличьте размер разделяемой памяти (shared_buffers), чтобы уменьшить частоту операций записи в разделяемую память.

- Настройте параметры, связанные с кэшированием и буферизацией, чтобы уменьшить количество операций записи.

3. Оптимизация использования разделяемой памяти:

- Убедитесь, что ваши приложения и расширения эффективно используют разделяемую память.

- Избегайте создания большого количества временных объектов, которые могут приводить к увеличению операций записи в разделяемую память.

Мероприятия для снижения ожиданий ParallelHashJoin

Ожидания ParallelHashJoin могут возникать из-за того, что PostgreSQL использует параллельные запросы для выполнения операций, таких как Hash Join. Это может привести к увеличению количества ожиданий, особенно если у вас много одновременных запросов или ограниченные ресурсы.

1. Отключить параллельные запросы:

- Вы можете отключить параллельные запросы, установив параметр max_parallel_workers_per_gather в 0. Это отключит использование параллельных рабочих процессов для операций, таких как Hash Join.

2. Оптимизировать индексы:

- Убедитесь, что у вас есть правильные индексы на таблицах, участвующих в запросе. Индексы могут помочь ускорить выполнение запросов и уменьшить необходимость в параллельных операциях.

3. Анализ и вакуумизация таблиц:

- Периодически выполняйте команды ANALYZE и VACUUM для обновления статистики и очистки мертвых строк. Это поможет оптимизатору запросов выбрать более эффективные планы выполнения.

4. Настройка параметров планировщика:

- Настройте параметры, такие как random_page_cost и cpu_tuple_cost, чтобы повлиять на выбор плана выполнения запроса. Например, уменьшение random_page_cost может сделать индексные сканирования более привлекательными.

5. Использование правильных операторов JOIN:

- Попробуйте использовать другие типы соединений, такие как Nested Loop или Merge Join, если они подходят для вашего запроса. Вы можете временно отключить Hash Join, установив параметр enable_hashjoin в off.

6. Оптимизация запросов:

- Проверьте, можно ли оптимизировать сами запросы, например, добавив дополнительные условия в WHERE-clause или используя более эффективные подзапросы.

Примеры команд для настройки параметров:

-- Отключить параллельные запросы

SET max_parallel_workers_per_gather = 0;

-- Отключить Hash Join

SET enable_hashjoin = off;

-- Установить параметры планировщика

SET random_page_cost = 1.1;

SET cpu_tuple_cost = 0.01;

Мероприятия для снижения ожиданий BufferMapping

Ожидания на BufferMapping в PostgreSQL могут возникать из-за интенсивных операций чтения, когда база данных часто обращается к данным на диске вместо кэша. Это может происходить, когда рабочий набор данных превышает доступную память, что приводит к частым операциям ввода-вывода (I/O).

1. Увеличение shared_buffers:

- Увеличение параметра shared_buffers может помочь уменьшить количество операций ввода-вывода, так как больше данных будет храниться в памяти.

2. Оптимизация запросов:

- Проверьте и оптимизируйте ваши запросы, чтобы уменьшить количество операций ввода-вывода. Используйте индексы и другие методы оптимизации для уменьшения количества данных, которые нужно считывать из диска.

3. Увеличение effective_cache_size:

- Параметр effective_cache_size помогает PostgreSQL лучше оценивать доступную память для кэширования данных. Увеличение этого параметра может улучшить планирование запросов.

4. Увеличение work_mem и maintenance_work_mem:

- Увеличение параметров work_mem и maintenance_work_mem может помочь уменьшить количество операций ввода-вывода, особенно при выполнении операций сортировки и хранения данных.

5. Анализ и оптимизация индексов:

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

6. Обновление аппаратного обеспечения:

- Если возможно, обновите аппаратное обеспечение, особенно увеличьте объем оперативной памяти и используйте более быстрые диски (например, SSD).

7. Распределение нагрузки:

- Анализируйте и оптимизируйте распределение нагрузки между сессиями, чтобы уменьшить конкуренцию за ресурсы.

Мероприятия для снижения ожиданий ProcArrayLock

Задержки, связанные с блокировкой ProcArrayLock, могут возникать из-за интенсивной активности рабочих процессов, которые создают конкуренцию за доступ к ProcArray. Это особенно актуально при выполнении параллельных запросов и операций, таких как walsender.

Для уменьшения задержек ProcArray можно рассмотреть следующие шаги:

1. Оптимизация рабочих процессов:

- Уменьшите количество одновременно выполняемых рабочих процессов, чтобы снизить нагрузку на ProcArrayLock.

- Оптимизируйте параллельные запросы, чтобы уменьшить их длительность и уменьшить время блокировки.

2. Настройка параметров конфигурации:

- Уменьшите значение параметра max_standby_streaming_delay, чтобы уменьшить задержку репликации.

- Настройте параметры, связанные с параллелизмом, такие как max_parallel_workers_per_gather и max_worker_processes, чтобы управлять количеством рабочих процессов.

3. Оптимизация хранения данных:

- Убедитесь, что у вас используется оптимальное хранилище данных, например, AWS EBS GP3, для уменьшения задержек ввода-вывода.

4. Итог

Использование корреляционного анализа ожиданий с помощью оперативно-тактического комплекса pg_hazel позволяет резко сократить время на поиск корневой причины снижения скорости СУБД и оперативно предоставить мероприятия для устранения причин.

Показать полностью 20
[моё] Субд Postgresql Мониторинг Производительность Корреляция Длиннопост
2
kznalp
kznalp
2 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Хирург и DBA это холодная голова и горячее сердце.

Начало :

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск потенциально проблемных SQL запросов при продуктивной нагрузке .

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

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

Проанализировать причины снижения скорости СУБД и найти проблемные запросы:

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Дашбоард мониторинга производительности СУБД

Операционная скорость СУБД - снижается.

Ожидания СУБД - растут.

Метрика относительной доли ожиданий - исключена из анализа.

Словарь терминов , используемых при корреляционном анализе.

Операционная скорость на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Ожидания на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - общее количество ожиданий СУБД

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий и определение потенциально проблемных SQL запросов

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

Таблица коэффициентов корреляции

  1. Сильная отрицательная корреляция между скоростью и ожиданиями .

  2. Наиболее сильная положительная корреляция между всеми ожиданиями и ожиданиями типа IPC.

  3. Сильная положительная корреляция между всеми ожиданиями и ожиданиями типа LWLock , IO .

Корреляционный анализ на уровне запросов SQL по ожиданию типа IPC

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

TOP-10 таблицы коэффициентов корреляции для SQL запросов с ожиданиями типа IPC

Столбцы таблицы

  • QUERYID : id SQL запроса

  • PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

  • CORRELATION : коэффициент корреляции между ожиданиями типа IPC по всем SQL запросам и ожиданиям типа IPC по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа IPC по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

  • WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа IPC для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 3985919093425059746

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

История выполнений и ожиданий запроса 3985919093425059746

События ожидания:

  • BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

  • ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

  • ExecuteGather Ожидание активности дочернего процесса при выполнении узла плана Gather.

Корреляционный анализ на уровне запросов SQL по ожиданию типа LWLock

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

TOP-10 таблицы коэффициентов корреляции для SQL запросов с ожиданиями типа LWLock.

Столбцы таблицы

  • QUERYID : id SQL запроса

  • PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

  • CORRELATION : коэффициент корреляции между ожиданиями типа LWLock по всем SQL запросам и ожиданиям типа LWLock по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа LWLock по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

  • WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа LWLock для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 2092406791392746781

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

История выполнений и ожиданий запроса 2092406791392746781

События ожидания:

  • ParallelHashJoin Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Корреляционный анализ на уровне запросов SQL по ожиданию типа IO

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

TOP-10 таблицы коэффициентов корреляции для SQL запросов с ожиданиями типа IO

Столбцы таблицы

  • QUERYID : id SQL запроса

  • PGPRO_WR_QUERYID : HEX значение queryid , для использования в отчетах pgpro_pwr.

  • CORRELATION : коэффициент корреляции между ожиданиями типа IO по всем SQL запросам и ожиданиям типа IO по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа IO по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количествe выполнений. Среднее количество ожидания за одно выполнение.

  • WAITINGS PCT : Относительная доля (промилле) количества ожиданий типа IO для данного SQL запроса в общем количества ожиданий типа IPC по всем запросам.

Таблица отсортирована по столбцам "WAITINGS PCT" DESC , "WAITINGS TO CALL" DESC , "CORRELATION" DESC .

Потенциально проблемный запрос - 5680299967307342186

Корреляционный анализ ожиданий СУБД PostgreSQL - продолжение Субд, Postgresql, Производительность, Корреляция, Длиннопост

История выполнений и ожиданий запроса 5680299967307342186

События ожидания:

  • DataFileExtend Ожидание расширения файла данных отношения.

  • DataFileRead Ожидание чтения из файла данных отношения.

Итог

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

Показать полностью 14
[моё] Субд Postgresql Производительность Корреляция Длиннопост
0
3
kznalp
kznalp
2 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке⁠⁠

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Работа DBA в чем то , очень отдаленно, напоминает работу хирурга.

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

Проанализировать причины снижения скорости СУБД и найти проблемные запросы

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Дашбоард мониторинга производительности СУБД

Операционная скорость СУБД - снижается.

Ожидания СУБД - растут.

Порядок проведения корреляционного анализа

Операционная скорость на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Ожидания на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Ось X - точка наблюдения Ось Y - количество ожиданий СУБД

Корреляционный анализ на уровне кластера

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Таблица коэффициентов корреляции

  1. Средняя отрицательная корреляция между операционной скоростью и ожиданиями.

  2. Тип ожидания имеющий наибольшую корреляция с общим количеством ожиданий - IO.

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Статистика выполнений и ожиданий по отдельным SQL запросам по типу ожидания IO

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

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

Столбцы таблицы

  • QUERYID : id SQL запроса

  • CORRELATION : коэффициент корреляции между ожиданиями типа IO по всем SQL запросам и ожиданиям типа IO по конкретному запросу.

  • CALLS : общее количество выполнений запроса за анализируемый период.

  • WAITINGS : Ожидания типа IO по конкретному запросу.

  • WAITINGS TO CALL : Отношение количество ожиданий к количество выполнений. Среднее количество ожидания за одно выполнение.

Таблица отсортирована по столбцам QUERYID / WAITINGS TO CALL .

Статистика выполнений и ожиданий по выбранным SQL-запросам

-7843470278038126227

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Статистика выполнений и ожиданий для queryid =-7843470278038126227

События ожидания:

  • DataFilePrefetch: Ожидание асинхронной предвыборки из файла данных отношения.

  • DataFileRead : Ожидание чтения из файла данных отношения.

  • DataFileWrite : Ожидание записи в файл данных отношения.

  • WALSync : Ожидание помещения файла WAL в надёжное хранилище.

  • WALWrite : Ожидание записи в файл WAL.

Результат анализа по запросу -7843470278038126227:

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

-8198400089192679786

Корреляционный анализ ожиданий СУБД PostgreSQL - поиск проблемных SQL запросов при продуктивной нагрузке Субд, Postgresql, Производительность, Корреляция, Длиннопост

Статистика выполнений и ожиданий для queryid =-8198400089192679786

События ожидания:

  • DataFileRead : Ожидание чтения из файла данных отношения.

Результат анализа по запросу -8198400089192679786

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

Итог

Использование корреляционного анализа позволяет определить степень влияния потенциально проблемных SQL запросов на общую скорость и ожидания СУБД.

Показать полностью 9
[моё] Субд Postgresql Производительность Корреляция Длиннопост
0
kznalp
kznalp
2 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности⁠⁠

Взято с основного технического канала Postgres DBA

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Для быстрой работы системы - каждая отдельная часть должна быть настроена идеально.

Словарь терминов

Постановка проблемы эмерджентности СУБД

Решение проблемы

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

Операционная скорость и скользящая корреляция на уровне СУБД

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Анализ графика

  1. Имеется снижение производительности

  2. Скользящая корреляция постоянно в положительной зоне

Т.е. малым значениям операционной скорости соответствуют малые значения ожиданий.

Снижения производительности нет ?

Корреляционный анализ на уровне баз данных

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

В основе остается та же идея

Основная гипотеза корреляционного анализа ожиданий СУБД

Новые метрики производительности СУБД

1. Относительная доля(%) баз данных, имеющих отрицательную корреляцию между операционной скоростью и ожиданиями.

2. Относительная доля(%) баз данных, имеющих среднюю и сильную отрицательную корреляцию между операционной скоростью и ожиданиями.

Если теперь посмотреть значение данных метрик, за тот же период наблюдения, появляются совершенно новые детали для начала анализа.

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - значение Метрики 1 и 2.

Результат анализа метрик относительной доли отрицательной корреляции.

  1. Рост количества баз данных имеющих отрицательную корреляцию между скоростью и ожиданиями

  2. Рост количества баз данных имеющих среднюю и сильную отрицательную корреляцию между скоростью и ожиданиями

Главный итог использования новых метрик

Имеются базы данных по которым возможно снижение скорости работы , хотя на уровне СУБД снижения не установлено.

Далее , используя полученные данные, уже можно примерить использованную ранее методику, хорошо зарекомендовавшую себя при нагрузочном тестировании.

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

Корреляционный анализ ожиданий баз данных и выявление SQL запросов вызывающих наибольшее влияние на снижение скорости баз данных.

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Таблица коэфициентов корреляции по базам данных.

  • База данных DB-1 - имеет наиболее сильную отрицательную корреляцию.

  • База DB-2 - исключается из дальнейшего анализа. Причина - отсутствие отрицательной корреляции по событию типу ожидания.

Корреляционный анализ по базе данных "DB-1"

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

SQL запрос, имеющий наибольшую корреляцию с типом ожидания IO

Корреляционный анализ по базе данных "DB-3"

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

SQL запрос, имеющий наибольшую корреляцию с типом ожидания IO

Корреляционный анализ по базе данных "DB-4"

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - количество ожиданий типа Extension по базе данных DB-4

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

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

Корреляционный анализ ожиданий СУБД при продуктивной нагрузке. Решение проблемы эмерджентности Исследования, Инженер, Субд, Postgresql, Корреляция, Длиннопост

SQL запросs, имеющий наибольшую корреляцию с типами ожидания Extension , LWLock.

Итог

  1. Использование корреляционного анализа позволяет выявить SQL выражения потенциально, имеющие влияние на скорость работы базы данных.

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

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

  4. Для решения проблемы эмерджентности СУБД , необходимо мониторить метрики производительности на уровне отдельных баз данных , а не СУБД в целом.

Показать полностью 13
[моё] Исследования Инженер Субд Postgresql Корреляция Длиннопост
0
kznalp
kznalp
2 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Является ли СУБД эмерджентной системой ?⁠⁠

Взято с основного технического канала Postgres DBA

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

В некоторых системах - целое больше, чем сумма его частей.

Эмердже́нтность или эмерге́нтность (англиц. от emergent «возникающий, неожиданно появляющийся»)[1] в теории систем — наличие у системы свойств, не присущих её компонентам по отдельности; несводимость свойств системы к сумме свойств её компонентов.

В связи с началом тестирования комплекса pg_hazel под продуктивной нагрузкой возникает очень важный вопрос:

Можно ли рассчитывать метрики производительности СУБД суммируя значения метрик отдельных баз данных, составляющих кластер PostgreSQL ?

Словарь терминов оперативно-тактического комплекса "PG_HAZEL".

Экспериментальные данные

В качестве источника данных для расчета метрик производительности кластера , используется представление pgpro_stats_totals

G.4.4.2. Представление pgpro_stats_totals

Агрегированная статистика, собранная модулем, выдаётся через представление pgpro_stats_totals.

Операционная скорость СУБД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Количество ожиданий СУБД

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Ось X - точка наблюдения. Ось Y - Количество ожиданий по СУБД

Коэффициент корреляции между операционной скоростью и ожиданиями составляет -0,8736.

Вопрос : является ли ситуация существенного снижения операционной скорости и роста ожиданий СУБД - инцидентом деградации производительности СУБД?

Анализ операционной скорости по отдельным базам данных

Для расчета операционной скорости по отдельному SQL выражению используется представления pgpro_stats_statements

G.4.4.1. Представление pgpro_stats_statements

Статистика, собираемая модулем, выдаётся через представление с именем pgpro_stats_statements. Это представление содержит отдельные строки для каждой комбинации идентификатора базы данных, идентификатора пользователя и идентификатора запроса

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

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Таблица значение операционной скорости СУБД и отдельных баз данных.

Для простоты достаточно рассмотреть графики операционной скорости для баз данных по которым количество значений наиболее близко к количеству значений по СУБД в целом - test_db-7 , test_db-6 и test_db-4.

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Вывод по итогам анализа операционной скорости при работе СУБД под продуктивной нагрузкой

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

Корреляционный анализ на уровне баз данных

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

Список БД имеющих обратную корреляцию между операционной скоростью и ожиданиями

Интересные детали

База данных DB-1 имеет значение коэффициента корреляции между значениями операционной скорости и ожиданиями = -0.3985 , но если рассчитать коэффициент корреляции между операционной скоростью и количеством ожиданий конкретного типа, то все значения положительны.

Если посмотреть график истории ожиданий, становится понятно, почему так происходит.

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Получается слабая отрицательная корреляция

Однако , если посмотреть графики истории ожиданий по типам ожиданий IO , IPC , LWLock корреляция будет совсем другой :

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Является ли СУБД эмерджентной системой ? Исследования, Субд, Postgresql, Корреляция, Длиннопост

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

Вывод

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

Необходимо подготовить методику применения корреляционного анализа в условиях продуктивной нагрузки на СУБД.

Таким образом, можно сформулировать гипотезу

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

Показать полностью 14
[моё] Исследования Субд Postgresql Корреляция Длиннопост
3
1
kznalp
kznalp
3 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов⁠⁠

Взято с основного технического канала Postgres DBA

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

Результат известен заранее. Но детали , могут быть интересны.

Задача эксперимента

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

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

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

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

1)UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

2)SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

3)UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

4) UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

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

При проведении сравнительного эксперимента ,таблица pgbench_accounts создается без ограничения первичного ключа.

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

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

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

https://dzen.ru/a/Z7h6qwE1CT3q-E73?share_to=link

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

https://dzen.ru/a/Z7gXkYKsy2syEMGG?share_to=link

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

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

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График операционной скорости при использовании индекса.

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График операционной скорости без использования индекса.

Сравнительный анализ операционной скорости

  1. Без индекса скорость существенно ниже

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

Ожидания

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количества ожиданий при использовании индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количества ожиданий без использования индекса

Сравнительный анализ ожиданий

  1. Характер графика практически не изменился

  2. Абсолютные значение количества ожиданий без использования индекса незначительно ниже.

WAITING RATIO

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

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График относительной доли ожиданий при использовании индекса.

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График относительной доли ожиданий без использования индекса.

Сравнительный анализ относительной доли ожиданий

  1. Характер графика практически не изменился

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

Минимальные и максимальные количества ожиданий при использовании индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

Минимальные и максимальные количества ожиданий без использования индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

Анализ WAIT_EVENT_TYPE

  1. Характер ожидания , при проведении нагрузочного тестирования без использования индексов принципиально изменился.

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

Ожидания Lock

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количество ожидания при использовании индекса.

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количество ожидания без использования индекса.

Ожидания LWLock

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количество ожидания при использовании индекса.

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количество ожидания без использования индекса.

Сравнительный анализ ожиданий Lock , LWLock

  1. Характер графиков и количества ожиданий практически не изменились и не зависят от отсутствия индекса.

Ожидания IO

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количество ожидания без использования индекса.

Ожидания IPC

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

График количество ожидания без использования индекса.

Сравнительный общий корреляционный анализ ожиданий

Основная гипотеза корреляционного анализа ожиданий СУБД

https://dzen.ru/a/Z6zzzMzWGmYuR-bG?share_to=link

Корреляция операционной скорости и ожиданий и событий ожидания при использовании индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

Корреляция операционной скорости и ожиданий и событий ожидания без использовании индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

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

  • SPEED CORR: коэффициент корреляции между количеством активных сессий к БД и операционной скоростью.

  • BUFFERPIN CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Bufferpin.

  • EXTENSION CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Extension.

  • IO CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IO.

  • IPC CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа IPC.

  • LOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа Lock.

  • LWLOCK CORR: коэффициент корреляции между операционной скоростью и количеством ожиданий типа LWLock.

Сравнительный анализ корреляций

  1. Коэффициент корреляции с событиями ожидания тяжеловесных и легковесных блокировок практически не изменился.

  2. При проведении нагрузочного тестирования без использования индексов, возникает сильная отрицательная корреляция с ожиданиями взаимодействия с другим процессом и слабая отрицательная корреляция с ожиданиями подсистемы ввода-вывода.

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

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

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

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

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

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

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

  6. LWLock / WALWrite: Ожидание при записи буферов WAL на диск.

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

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

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

  1. IO / RelationMapSync Ожидание помещения файла отображений отношений в надёжное хранилище.

  2. IO / DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

  3. IO / DataFileRead Ожидание чтения из файла данных отношения.

  4. IO / DataFileImmediateSync Ожидание немедленной синхронизации файла данных отношения с надёжным хранилищем.

  5. IPC / BufferIO Ожидание завершения буферного ввода/вывода.

  6. IPC / ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

  7. IPC / BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

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

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

  10. LWLock / BufferMapping Ожидание при связывании блока данных с буфером в пуле буферов.

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

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

  13. LWLock / SyncScan Ожидание при выборе начального положения для синхронизированного сканирования таблицы.

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

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

При использовании индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост
Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

Без использования индекса

Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост
Корреляционный анализ PostgreSQL - ожидания СУБД при отсутствии индексов Субд, Postgresql, Производительность, Корреляция, Тестирование, Длиннопост

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

С использованием индекса

select custom_test( $1 )

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

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

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

Без использования индекса

select custom_test( $1 )

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

  1. IO / DSMFillZeroWrite Ожидание заполнения нулями файла, применяемого для поддержки динамической общей памяти.

  2. IO / DataFileRead Ожидание чтения из файла данных отношения.

  3. IPC / BufferIO Ожидание завершения буферного ввода/вывода.

  4. IPC / ParallelFinish Ожидание завершения вычислений параллельными рабочими процессами.

  5. IPC / BgWorkerShutdown Ожидание завершения фонового рабочего процесса.

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

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

  8. LWLock / BufferMapping Ожидание при связывании блока данных с буфером в пуле буферов.

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

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

  11. LWLock / SyncScan Ожидание при выборе начального положения для синхронизированного сканирования таблицы.

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

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

  1. Результаты корреляционного анализа нагрузочного тестирования с использованием индексов являются нормальной картиной штатной работы СУБД в условиях массовых обновлений данных .

  2. Корреляция с ожиданиями IO и IPC - с высокой долей уверенности , свидетельствует о неоптимальном плане выполнения SQL запроса.

Показать полностью 23
[моё] Субд Postgresql Производительность Корреляция Тестирование Длиннопост
0
kznalp
kznalp
3 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

Основная гипотеза корреляционного анализа ожиданий СУБД⁠⁠

Взято с основного технического канала Postgres DBA

Основная гипотеза корреляционного анализа ожиданий СУБД Субд, Корреляция, Статистика, Postgresql

Коэффициент корреляции Пирсона

Входные данные

Статистически обработанные значения количеств ожиданий по типам (wait_event_type) , событиям ожиданий (wait_event) на уровне кластера и SQL выражений , за заданный период анализа.

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

  • Очень слабая корреляция: [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

Показать полностью
[моё] Субд Корреляция Статистика Postgresql
0
kznalp
kznalp
3 месяца назад
Postgres DBA
Серия СУБД PostgreSQL

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

Взято с основного технического канала Postgres DBA

Корреляционный анализ ожиданий для сценариев нагрузочного тестирования СУБД PostgreSQL Субд, Postgresql, Исследования, Производительность, Корреляция, Анализ данных

Анализировать и сравнивать можно только вычисляемые сущности.

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

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

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 Исследования Производительность Корреляция Анализ данных
0
Посты не найдены
О Нас
О Пикабу
Контакты
Реклама
Сообщить об ошибке
Сообщить о нарушении законодательства
Отзывы и предложения
Новости Пикабу
RSS
Информация
Помощь
Кодекс Пикабу
Награды
Команда Пикабу
Бан-лист
Конфиденциальность
Правила соцсети
О рекомендациях
Наши проекты
Блоги
Работа
Промокоды
Игры
Скидки
Курсы
Зал славы
Mobile
Мобильное приложение
Партнёры
Промокоды Biggeek
Промокоды Маркет Деливери
Промокоды Яндекс Путешествия
Промокоды М.Видео
Промокоды в Ленте Онлайн
Промокоды Тефаль
Промокоды Сбермаркет
Промокоды Спортмастер
Постила
Футбол сегодня
На информационном ресурсе Pikabu.ru применяются рекомендательные технологии