PG_HAZEL : Анализ инцидентов производительности СУБД

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

Задача

1. Провести корреляционный анализ инцидентов производительности продуктивной СУБД.
2. Выявить проблемный запрос.
3. Провести оптимизацию запроса.
4. Оценить результат и влияние оптимизации отдельного SQL запроса на производительность СУБД.

Обнаружение проблемного запроса/запросов для оптимизации

Одним из результатов сводного отчета по инцидентам производительности за неделю является информации о участии SQL запросов в инцидентах:

SQL запросы участвующие во всех инцидентах производительности

Подробнее о сводных отчетах:

Результат

Первым SQL запросом для начала оптимизации является запрос с queryid = 1214551160677155501.

Ожидания СУБД

Диаграмма Парето по типам ожиданий

80% ожиданий по СУБД имеют тип IO и IPC

Результат

При анализе ожиданий по SQL запросам наибольшее внимание необходимо уделить ожиданиям типа IO и IPC.

Корреляционный анализ ожидания для SQL-запроса

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

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

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

Количество и корреляция по ожиданиям типа IPC

Результат

Количество ожиданий типа IPC существенно выше. При анализе плана выполнения необходимо обратить основание внимание на событие ожидания BgWorkerShutdown.

• BgWorkerShutdown: Ожидание завершения фонового рабочего процесса.
• ExecuteGather: Ожидание активности дочернего процесса при выполнении узла плана Gather.
• ParallelFinish: Ожидание завершения вычислений параллельными рабочими процессами.

Оптимизация SQL-запроса

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

SELECT

"attribute1",

"attribute2",

…

"attribute14"

FROM

"table1"

WHERE

"table1"."attribute2" IN ('ххх-ххх')

ORDER BY "attribute5" DESC

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

QUERY PLAN

------------------------------------------------------------------------------------------

Sort (cost=12287.34..12287.35 rows=2 width=233)

Sort Key: "attribute5"DESC

-> Gather (cost=1000.00..12287.28 rows=2 width=233)

Workers Planned: 3

-> Parallel Seq Scan on "table1" (cost=0.00..11287.08 rows=1 width=233)

Filter: ("attribute2" = 'yyy-yyy-yyy'::uuid)

(6 rows)

Результат

Большое количество ожиданий типа IPC при выполнении SQL-запроса вызвано параллельным выполнением операций Parallel Seq Scan.

Аналогичная проблема обнаруженная в тестовых экспериментах

Рекомендация по оптимизации проблемного SQL-запроса

Создать индекс по "attribute2" для таблицы "table1".

Оценка результата оптимизации SQL-запроса

Количество инцидентов снижения производительности

До создания индекса

После создания индекса

Результат

После добавления рекомендованного индекса количество инцидентов снизилось с 5 до 3.

Участие SQL-запроса в инцидентах снижения производительности

До создания индекса

После создания индекса

SQL-запрос с queryid = 1214551160677155501 в инцидентах снижения производительности не участвует.

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

До создания индекса

После создания индекса

Результат

Количество ожиданий существенно снизилось.

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

Синяя линия графиков - без индекса.

Красная линия графиков - с индексом.

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

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

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

Итог

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