Postgres DBA

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

Начало :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Корреляционный анализ на уровне запросов 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

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

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

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

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

Корреляционный анализ на уровне запросов 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

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

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

Корреляционный анализ на уровне запросов 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

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

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

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

Итог

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-7843470278038126227

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

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

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

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

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

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

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

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

-8198400089192679786

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

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

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

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

Итог

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вывод

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

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

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

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