Историческое предисловие

Как известно, основная задача DBA - обеспечить наиболее эффективную и производительную работу вверенной ему в сопровождение СУБД. Для выполнения задачи одно из основных требований - умение определить насколько производительно/эффективно СУБД справляется с получаемой нагрузкой и выдает требуемый результат. Для этого необходимо определить такое понятие как производительность СУБД. Потому, что очень важно, для начала, хотя бы обеспечить мониторинг и иметь возможность сразу сказать - в каком состоянии СУБД - минимальная загрузка, оптимальная, перегруз, авария. Однако, как выясняется, общего понятия "производительность СУБД" до недавнего времени не существовало. Каждый DBA понимал под производительностью, то , что лично ему нравится - количество запросов в секунду, количество зафиксированных транзакций, среднее время отклика СУБД и даже процент утилизации CPU+RAM или вывести на экран десяток другой графиков мониторинга и каким то мистическим образом определить хорошо работает СУБД или плохо.

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

Для начала надо определиться с определением и ответить на главный вопрос - что есть производительность СУБД ?

Вспоминая физику , можно использовать базовое понятие:

В физике производительность — это величина, которая обозначает объём работы, выполняемый за единицу времени (например, за час или за день). По-другому её можно назвать скоростью выполнения работы

Данное определение, было взято за основу. Осталось уточнить - что такое объем работы выполняемой СУБД ?

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

Самый первый вариант расчета метрики

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

А может быть производительность СУБД это вектор: (N1, N2, N3), где:

N1 - количество активных сессий

N2 - количество транзакций

N3 - количество запросов к СУБД в секунду.

https://habr.com/ru/posts/787966/

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

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

Аномалия учета ожиданий

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

Причина- количество активных сессий учитывает не только сессии выполняющие запросы , но и находящиеся в состоянии ожидания.

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

Второй вариант расчета метрики

Было принято решение изменить методику расчета, используя вектор:(N1, N2, N3, N4, N5), где:

• N1 - количество страниц shared_buffer , прочитанных в секунду

• N2 - количество страниц shared_buffer, записанных в секунду

• N3 - количество страниц shared_buffer, измененных в секунду

• N4 - количество завершенных запросов в секунду

• N5 - количество зафиксированных транзакций в секунду

https://habr.com/ru/posts/804899/

Этот вариант проработал дольше . И обеспечил хорошую базу для работ по статистическому анализу производительности СУБД.

• Корреляционный анализ для решения инцидентов производительности СУБД / Хабр (habr.com)

• Нагрузочное тестирование СУБД в облачной среде — часть 1 / Хабр (habr.com)

• Нагрузочное тестирование СУБД в облачной среде — часть 2. Итоги и результат / Хабр (habr.com)

• Статистический анализ результатов нагрузочного тестирования СУБД в условиях облачной инфраструктуры / Хабр (habr.com)

• Применение статистического анализа для определения причин деградации производительности СУБД / Хабр (habr.com)

• Применение статистического анализа для определения причин деградации производительности СУБД — часть 2 / Хабр (habr.com)

• Статистический анализ результатов benchmark PostgreSQL / Хабр (habr.com)

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

Аномалия изменения плана выполнения запроса.

Для того, что бы обнаружить аномалию достаточно было провести очень простой эксперимент:

1. Создаем большие таблицы: родитель-потомок.

2. В таблицах не создаем индексы.

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

4. Выполняем несколько итераций, фиксируем время выполнения запроса и показатель производительности СУБД.

5. Создаем индексы для таблиц.

6. Выполняем итерации того же самого запроса.

7. Фиксируем время выполнения запроса и показатель производительности СУБД.

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

Причина: При выполнении индексного доступа к данным количество обработанных страниц shared_buffer существенно уменьшается. А при использовании метода доступа Index Only Scan вообще будет нулевым. В результате значение метрики производительности уменьшается.

Третий вариант расчета метрики

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

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

Полезными операциями(результатами) работы СУБД являются:

1. Количество строк выданных пользователю.

2. Количество запросов выполненных пользователем.

3. Количество зафиксированных пользователем транзакций.

Разделив количество на количество секунд (DB Time), которые потребовались на выполнения операций СУБД в изменяемый промежуток получаем - вектор , определяющий операционную(результативную) скорость:

• QPS: Количество запросов в секунду.

• TPS: Количество транзакций в секунду.

• RPS: Количество строк в секунду.

Для того, что бы иметь одну цифру используется модуль вектора ( QPS , TPS , RPS ).

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

Объемная скорость

Работа СУБД заключается в обработке блоков информации:

1. Прочитанные разделяемые блоки

2. "Загрязнённые" разделяемые блоки

3. Записанные разделяемые блоки

4. Прочитанные локальные блоки

5. "Загрязнённые" локальные блоки

6. Записанные локальные блоки

7. Прочитанные временные блоки

8. Записанные временные блоки

Подробнее о разделяемых блоках: WAL в PostgreSQL: 1. Буферный кеш / Хабр (habr.com)

О локальных и временных блоках , пока не нашел. Найду , добавлю.

Таким образом, применив тот же подход , что и для расчета операционной скорости получим- вектор, определяющий объёмную скорость :

1. RSBS : Прочитанные разделяемые блоки в секунду.

2. DSBS : "Загрязнённые" разделяемые блоки в секунду.

3. WSBS : Записанные разделяемые блоки в секунду.

4. RLBS : Прочитанные локальные блоки в секунду.

5. DLBS : "Загрязнённые" локальные блоки в секунду.

6. WLBS : Записанные локальные блоки в секунду.

7. RTBS : Прочитанные временные блоки в секунду.

8. WSBS: Записанные временные блоки в секунду.

Аналогично, для получения значения используем модуль вектора ( RSBS , DSBS , WSBS , RLBS , DLBS , WLBS , RTBS , WSBS ).

Полученное значение и будет объемной скоростью.

Расчет метрики "производительность СУБД"

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

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

Другими словами - данная метрика показывает - насколько эффективно СУБД выдаёт результат, обрабатывая объем информации .

Т.е. если план запроса изменился так, что запрос стал выполняться быстрее и читать меньше блоков (стоимость запроса снизилась) , то в этом случае значение метрики - увеличится .

Для удобства , обозначим производительность СУБД как CPI. Тогда производительность СУБД в момент времени t , есть значение дискретной функции CPI(t).

Для сглаживания графика и исключения выбросов используется медианное сглаживание: Вопрос о скользящей средней и медиане / Хабр (habr.com)

Временной/исторический анализ производительности

Задача анализа производительности СУБД сводится к анализу временного ряда, сформированного из значений функции CPI(t) , для значений t от начала до окончания анализируемого периода .

Например: Корреляционный анализ для определения причин деградации производительности СУБД / Хабр (habr.com)

Параметрическая оптимизация производительности

Задача по оптимизации производительности СУБД сводится к задаче оптимизации функции CPI(t) при изменении набора конфигурационных параметров СУБД .

Задача - определить комбинацию параметров дающих наибольший прирост производительности .

На текущий момент, первое, что сразу приходит в голову - использовать метод покоординатного спуска (в данном случае - подъёма )

https://habr.com/ru/posts/848496/

Для анализа результатов каждой итерации применяется Статистический анализ результатов benchmark PostgreSQL / Хабр (habr.com) .

Примечание

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

В комментариях к предыдущей статье было предложено использовать не Евклидову, а Манхеттенскую метрику для расчета модуля векторов операционной и объёмной скорости . Поскольку , в общем случае, размерности векторов нельзя считать независимыми . В настоящий момент , тема в проработке. Возможно смена метрики позволит избежать каких то еще аномалий , которые пока не проявились.