Серия «СУБД PostgreSQL»

На текущий момент - 756КБ исходников на bash и PL/pgSQL .

Начало и описание метрик производительности : PG_HAZEL - оперативно-тактический комплекс мониторинга производительности СУБД PostgreSQL .

Продолжение : PG_HAZEL - оперативный уровень анализа метрик производительности.

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

Задачи по анализу производительности СУБД решаемые на тактическом уровне:

1. Какая База Данных оказывает наибольшее влияние на производительность кластера в целом?

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

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

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

Производительность СУБД

Таким образом из графика можно сделать следующий вывод: наибольшее влияние на производительность кластера оказывает нагрузка в ходе нагрузочного тестирования при 33, 58 и 84 клиентских сессий pgbench.

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

Benchmark СУБД

Вывод по анализу производительности СУБД

Нагрузка на тестовую СУБД оказывает влияние на производительность СУБД в целом при количестве клиентов pgbench = 33, 58, 84.

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

Вывод по анализу ожиданий СУБД

При количестве клиентов pgbench = 15 ожидания резко возрастают.

Выводы

По итогам анализа метрик производительности СУБД на тактическом уровне , можно сделать следующие выводы по производительности данной СУБД при данном характере нагрузки :

1. Штатная нагрузка на СУБД составляет 15 клиентов.

2. После 33 клиентов начинается влияние и деградация производительности СУБД в целом.

Ближайшие планы развития оперативно-тактического комплекса "pg_hazel"

1. Сбор и статистический анализ информации по ожиданиям клиентских SQL запросов

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

Начало и описание метрик производительности : PG_HAZEL - оперативно-тактический комплекс мониторинга производительности СУБД PostgreSQL .

Продолжение - анализ метрик производительности в ходе нагрузочного тестирования.

Сценарий нагрузочного тестирования

Стандартный сценарий аналогичный TPC-B.

Рост нагрузки , экспоненциально : --client=клиенты

Число имитируемых клиентов, то есть число одновременных сеансов базы данных.

Продолжительность тестового прохода = 10 минут.

Максимальная нагрузка = 100 клиентов.

Общее число проходов = 20

Результаты нагрузочного тестирования

Нагрузка на СУБД

Операционная скорость тестового SQL запроса

Медианное время работы тестового SQL запроса

Решение задач оперативного уровня

Как было определено в статье PG_HAZEL : оперативно-тактический комплекс мониторинга производительности СУБД PostgreSQL - общее описание.

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

1. В каком состоянии находится производительность СУБД в данный момент времени?

2. Какая тенденция развития производительности СУБД на текущий момент или в прошлом?

3. На сколько снизилась производительность СУБД по сравнению с выбранным промежутком из прошлого?

В каком состоянии находится производительность СУБД в данный момент времени? Какая тенденция развития производительности СУБД на текущий момент или в прошлом?

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

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

На сколько снизилась производительность СУБД по сравнению с выбранным промежутком из прошлого?

Снижение производительности в ходе нагрузочного тестирования составило -20,1969%

Итог

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

Как известно, существует всего два метода для решения задач:

1. Метод анализа или метод дедукции, или от общего к частному.

2. Метод синтеза или метод индукции, или от частного к общему.

Для решения проблемы “улучшить производительность базы данных” это может выглядеть следующим образом.
Анализ — разбираем проблему на отдельные части и решая их пытаемся в результате улучшить производительности базы данных в целом.

На практике анализ выглядит примерно так:

• Возникает проблема (инцидент производительности)

• Собираем статистическую информацию о состоянии базы данных

• Ищем узкие места(bottlenecks)

• Решаем проблемы с узких мест

Узкие места базы данных — инфраструктура (CPU, Memory, Disks, Network, OS), настройки(postgresql.conf), запросы:

Инфраструктура: возможности влияния и изменения для инженера — почти нулевые.

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

Запросы к базе данных: единственная область для маневров.

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

Лирическое вступление или зачем все это надо

Как происходит процесс решения инцидентов производительности, если производительность базы данных не мониторится:

Заказчик -”у нас все плохо, долго, сделайте нам хорошо”
Инженер-” плохо это как?”
Заказчик –”вот как сейчас(час назад, вчера, на прошлой деле было), медленно”
Инженер – “а когда было хорошо?”
Заказчик – “неделю (две недели) назад было неплохо. “(Это повезло)
Заказчик – “а я не помню, когда было хорошо, но сейчас плохо “(Обычный ответ)

В результате получается классическая картина:

Кто виноват и что делать?

На первую часть вопроса ответить легче всего — виноват всегда инженер DBA.

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

Возникает первый вопрос — что мониторить?

Путь 1. Будем мониторить ВСЁ

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

В результате получается куча графиков, сводных таблиц, и непрерывные оповещения на почту и 100% занятость инженера решением кучи одинаковых тикетов, впрочем, как правило со стандартной формулировкой — “Temporary issue. No action need”. Зато, все заняты, и всегда есть, что показать заказчику — работа кипит.

Путь 2. Мониторить только то, что нужно, а, что не нужно, не нужно мониторить

Можно мониторить, чуть по-другому- только сущности и события:

• На которые инженер DBA может влиять

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

Исходя из этого предположения и вспоминая «Философское вступление» с целью избежать регулярного повторения «Лирическое вступление или зачем все это надо» целесообразно будет мониторить производительность отдельных запросов, для оптимизации и анализа, что в конечном итоге должно привести к улучшению быстродействия всей базы данных.

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

Итак, возникает два взаимосвязанных вопроса:

• какой запрос считается тяжелым

• как искать тяжелые запросы.

Очевидно, тяжелый запрос это запрос который использует много ресурсов ОС для получения результата.

Переходим ко второму вопросу — как искать и затем мониторить тяжелые запросы ?

Какие возможности для мониторинга запросов есть в PostgreSQL?

По сравнению с Oracle, возможностей немного, но все-таки кое-что сделать можно.

PG_STAT_STATEMENTS

Для поиска и мониторинга тяжелых запросов в PostgreSQL предназначено стандартное расширение pg_stat_statements.

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

Целевые столбцы pg_stat_statements для построения системы мониторинга:

• queryid Внутренний хеш-код, вычисленный по дереву разбора оператора

• max_time Максимальное время, потраченное на оператор, в миллисекундах

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

Как используется pg_stat_statements для мониторинга производительности PostgreSQL

Для мониторинга производительности запросов используется:
На стороне целевой базы данных — представление pg_stat_statements
Со стороны сервера и базы данных мониторинга — набор bash-скриптов и сервисных таблиц.

1 этап — сбор статистических данных

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

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

2 этап — настройка метрик производительности

Основываясь на собранных данных, выбираем запросы, выполнение которых наиболее критично/важно для клиента(приложения). По согласованию с заказчиком, устанавливаем значения метрик производительности используя поля queryid и max_time.

Результат — старт мониторинга производительности

1. Мониторинговый скрипт при запуске проверяет сконфигурированные метрики производительности, сравнивая значение max_time метрики со значением из представления pg_stat_statements в целевой базе данных.

2. Если значение в целевой базе данных превышает значение метрики – формируется предупреждение (инцидент в тикетной системе).

Дополнительная возможность 1

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

Для хранения истории используется сервисная таблица log_query. Таблица заполняется при анализе загруженного лог-файла PostgreSQL. Поскольку в лог-файл в отличии от представления pg_stat_statements попадает полный текст с значениями параметров выполнения, а не нормализованный текст, имеется возможность вести лог не только времени и длительности запросов, но и хранить планы выполнения на текущий момент времени.

Дополнительная возможность 2

Continuous performance improvement process
Мониторинг отдельных запросов в общем случае не предназначен для решения задачи непрерывного улучшения производительности базы данных в целом поскольку контролирует и решает задачи производительности только для отдельных запросов. Однако можно расширить метод и настроить мониторинг запросы для всех базы данных.

Для этого нужно ввести дополнительные метрики производительности:

• За последние дни

• За базовый период

Скрипт выбирает запросы из представления pg_stat_statements в целевой базе данных и сравнивает значение max_time со средним значением max_time, в первом случае за последние дни или за выбранный период времени(baseline), во-втором случае.

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

А при чем тут синтез ?

В описанной подходе, как и предполагает метод синтеза — улучшением отдельных частей системы, улучшаем систему в целом.

• Запрос выполняемый базой данных – тезис

• Измененный запрос – антитезис

• Изменение состояние системы — синтез

Развитие системы

• Расширения собираемой статистики добавлением истории для системного представления pg_stat_activity

• Расширение собираемой статистики добавлением истории для статистики отдельных таблиц участвующих в запросах

• Интеграция с системой мониторинга в облаке AWS

• И еще, что-нибудь можно придумать…