Производительность СУБД — расчет метрики, временной анализ, параметрическая оптимизация
Историческое предисловие
Как известно, основная задача DBA — обеспечить наиболее эффективную и производительную работу вверенной ему в сопровождение СУБД. Для выполнения задачи одно из основных требований — умение определить насколько производительно/эффективно СУБД справляется с получаемой нагрузкой и выдает требуемый результат. Для этого необходимо определить такое понятие как производительность СУБД. Потому, что очень важно, для начала, хотя бы обеспечить мониторинг и иметь возможность сразу сказать — в каком состоянии СУБД — минимальная загрузка, оптимальная, перегруз, авария. Однако, как выясняется общего понятия »производительность СУБД» до недавнего времени не существовало. Каждый DBA понимал под производительностью, то , что лично ему нравится — Количество запросов в секунду, количество зафиксированных транзакций, среднее время отклика СУБД и даже процент утилизации CPU+RAM или вывести на экран десяток другой графиков мониторинга и каким то мистическим образом определить хорошо работает СУБД или плохо.
Ситуацию надо было менять , ибо , как говорится — для того, что бы чем то управлять и улучшать надо это для начала измерить.
Для начала надо было определится с определением — что есть производительность. Вспоминая физику , можно использовать базовое понятие:
В физике производительность — это величина, которая обозначает объём работы, выполняемый за единицу времени (например, за час или за день). По-другому её можно назвать скоростью выполнения работы
Данное определение, было взято за основу. Осталось уточнить — что такое объем работы выполняемой СУБД ?
Методы расчета метрики производительности
Самый первый вариант расчета метрики
На этапе нагрузочного тестирования одного большого проекта, возникла необходимость оценить степень влияния изменений вносимых разработчиками на эффективность работы СУБД. Тогда и возникла впервые идея — надо считать метрику и строить графики мониторинга.
А может быть производительность СУБД это вектор: (N1, N2, N3), где:
N1 — количество активных сессий
N2 — количество транзакций
N3 — количество запросов к СУБД в секунду.
https://habr.com/ru/posts/787966/
В принципе метрика вполне себе работала и показывала вполне ожидаемые результаты — 99% изменений не оказывали вообще никакого влияния на работоспособность СУБД. Было сохранено очень много рабочего времени на ненужно объяснений о неэффективности предложенных изменений.
Однако, как можно понять — метрика в общем то не совсем производительность считает. Очень важный момент — «количество активных сессий» , и тут возможна первая аномалия.
Аномалия учета ожиданий
Возможна ситуация — особенно при продуктивной нагрузке — работоспособность СУБД падает, а метрика растет.
Причина- количество активных сессий учитывает не только сессии выполняющие запросы , но и находящиеся в состоянии ожидания.
Второй вариант расчета метрики
Было принято решение изменить методику расчета, используя вектор:(N1, N2, N3, N4, N5), где:
N1 — количество страниц shared_buffer, прочитанных в секунду
N2 — количество страниц shared_buffer, записанных в секунду
N3 — количество страниц shared_buffer, измененных в секунду
N4 — количество завершенных запросов в секунду
N5 — количество зафиксированных транзакций в секунду
https://habr.com/ru/posts/804899/
Этот вариант проработал пока, дольше. И обеспечил хорошую базу для работ по статистическому анализу производительности СУБД.
Однако, благодаря обратной связи в комментариях к статьям (чуть позже найду этот комментарий, добавлю), была обнаружена еще одна аномалия .
Аномалия изменения плана выполнения запроса.
Для того, что бы обнаружить аномалию достаточно было провести очень простой эксперимент:
Создаем большие таблицы: родитель-потомок.
В таблицах не создаем индексы.
Подготавливаем запрос. Поскольку индексов нет , используется последовательное чтение.
Выполняем несколько итераций, фиксируем время выполнения запроса и показатель производительности СУБД.
Создаем индексы для таблиц.
Выполняем итерации того же самого запроса.
Фиксируем время выполнения запроса и показатель производительности СУБД.
Аномалия заключается в том, что запрос стал работать на порядки быстрее, стоимость запроса кардинально снизилась , следовательно эффективность резко возросла.
Причина: При выполнении индексного доступа к данным количество обработанных страниц shared_buffer существенно уменьшается. А при использовании метода доступа Index Only Scan вообще будет нулевым. В результате значение метрики производительности уменьшается.
Третий вариант расчета метрики
Для решения проблемы аномалии изменения плана выполнения запроса, расчет метрики был изменен. Для расчета метрики вводятся новые понятия .
Операционная (результативная) скорость
Полезными операциями (результатами) работы СУБД являются:
Количество строк выданных пользователю.
Количество запросов выполненных пользователем.
Количество зафиксированных пользователем транзакций.
Разделив количество на количество секунд (DB Time), которые потребовались на выполнения операций СУБД в изменяемый промежуток получаем операционную (результативную) скорость:
QPS: Количество запросов в секунду.
TPS: Количество транзакций в секунду.
RPS: Количество строк в секунду.
Для того, что бы иметь одну цифру используется модуль вектора (QPS, TPS, RPS).
Полученное значение и будет считаться операционной скоростью.
Объемная скорость
Работа СУБД заключается в обработке блоков информации:
Прочитанные разделяемые блоки
«Загрязнённые» разделяемые блоки
Записанные разделяемые блоки
Прочитанные локальные блоки
«Загрязнённые» локальные блоки
Записанные локальные блоки
Прочитанные временные блоки
Записанные временные блоки
Подробнее о разделяемых блоках: WAL в PostgreSQL: 1. Буферный кеш / Хабр (habr.com)
О локальных и временных блоках , пока не нашел. Найду , добавлю.
Таким образом, применив тот же подход , что и для расчета операционной скорости получим:
RSBS: Прочитанные разделяемые блоки в секунду.
DSBS: «Загрязнённые» разделяемые блоки в секунду.
WSBS: Записанные разделяемые блоки в секунду.
RLBS: Прочитанные локальные блоки в секунду.
DLBS: «Загрязнённые» локальные блоки в секунду.
WLBS: Записанные локальные блоки в секунду.
RTBS: Прочитанные временные блоки в секунду.
WSBS: Записанные временные блоки в секунду.
Аналогично, для получения значения используем модуль вектора (RSBS, DSBS, WSBS, RLBS, DLBS, WLBS, RTBS, WSBS ).
Полученное значение и будет объемной скоростью.
Расчет метрики «производительность СУБД»
Отношение операционной скорости к объемной скорости и будет считаться производительностью СУБД (CPI).
Временной анализ
Задача анализа производительности СУБД сводится к анализу ряда, сформированного как значения дискретной функции CPI (t), для значений t от начала до окончания анализируемого периода.
Например: Корреляционный анализ для определения причин деградации производительности СУБД / Хабр (habr.com)
Для сглаживания графика и исключения выбросов используется медианное сглаживание. Чуть подробнее: Вопрос о скользящей средней и медиане / Хабр (habr.com)
Параметрическая оптимизация
Задача по оптимизации производительности СУБД сводится к задаче оптимизации дискретной функции CPI (t) при изменении исследуемого набора конфигурационных параметров СУБД.
Задача — определить комбинацию параметров дающих наибольший прирост производительности .
На текущий момент, первое, что сразу приходит в голову — использовать метод покоординатного спуска (в данном случае — подъёма)
https://habr.com/ru/posts/848496/
Для анализа результатов каждой итерации применяется Статистический анализ результатов benchmark PostgreSQL / Хабр (habr.com) .
Примечание
В настоящее время в стадии сбора данных несколько экспериментов по параметрической оптимизации. Результаты будут опубликованы после сбора и анализа.