Предисловие
Сводная статья для подготовки докладов на конференциях PgConf и Heisenbug по теме статистического анализа производительности СУБД PostgreSQL по состоянию на май 2025 .
Теоретическое предисловие
Корреляционный анализ ожиданий СУБД PostgreSQL - презентации по докладам, не попавшим на конференции PGConf.СПб 2025:
Эпиграф
Вначале любая оригинальная теория признается абсурдной,
потом — верной, потом — самоочевидной и незначительной,
и, наконец, столь важной и самобытной, что бывшие критики присваивают ее себе.
Вопросы, на которые будут ответы
- Зачем нужно считать производительность СУБД?
- Как рассчитать метрику производительности СУБД?
- Какая практическая польза от результатов расчётов?
- Какие есть ограничения для метода?
Вопросы, на которые не будет ответов
· Как сделать серебряную пулю? (“Теорию всего” еще не придумали)
· А как это все устроено внутри и какие конкретные формулы расчётов? (Постоянно всё меняется, наличие “know-how”, исходники будут опубликованы позже.)
· А какие математические формулы используются? (“каждая дополнительная формула уменьшает цитируемость статьи на 28% по сравнению с работами, которые содержат меньше выкладок”[1])
[1] Proceedings of the National Academy of Sciences.
Зачем нужно считать производительность СУБД?
Если вы можете измерить то, о чем говорите,
и выразить это в цифрах – значит,
вы что-то об этом предмете знаете.
Но если вы не можете выразить это количественно,
ваши знания крайне ограничены и неудовлетворительны.
Может это начальный этап,
но это не уровень подлинного научного знания.
Как известно, для решения задач существует два метода:
• Дедуктивный метод, также известный как анализ, подразумевает движение от общего к частному.
• Индуктивный метод, или синтез, предполагает восхождение от частного к общему.
Применительно к ситуации, "СУБД работает медленно", разный подход к решению проблемы выглядит следующим образом.
Анализ
Разделяем проблему на компоненты и, разрешая каждую из них, стремимся повысить общую эффективность СУБД:
• Фиксируется проблема (снижение производительности).
• Производится сбор данных о состоянии и метриках СУБД.
• Определяются факторы, снижающие производительность СУБД.
• Формируется перечень действий, направленных на устранение этих факторов.
Для проведения анализа и выработки решений необходимо ответить на следующие важные вопросы:
1. Идентификация события, классифицируемого как "инцидент, влияющий на производительность": это может быть уведомление от системы мониторинга, жалоба от пользователя или необычные отклонения на графиках мониторинга.
2. Какие статистические данные следует регистрировать, с какой периодичностью и с использованием каких инструментов? Важно определить, какие метрики наиболее релевантные для оценки производительности системы.
3. Каким образом будет устанавливаться первопричина возникшей проблемы? Необходимо разработать методологию, позволяющую эффективно выявлять источник инцидента.
Синтез
Повышаем эффективность отдельных компонентов, полагая, что это приведет к общему улучшению быстродействия базы данных:
• Выявление "проблемных" запросов
• Оптимизация "проблемных" запросов
Однако, прежде чем оптимизировать ресурсоемкий запрос, оказывающий негативное влияние на общую производительность базы данных, необходимо его обнаружить.
Таким образом, возникают два связанных вопроса:
• Какой запрос можно считать проблемным?
• Как обнаружить проблемные запросы?
Существует множество докладов и подробных материалов, посвященных методам интеграции при оптимизации производительности СУБД.
Например, отличным и достаточным материалом для начала практической работы по этой теме является доклад, представленный на конференции PGConf-2024:
Дело о пропавшей производительности в PostgreSQL: руководство по поимке и обезвреживанию проблемных запросов (Екатерина Соколова) | PGConf.СПб 2024 | PGConf.Russia, 2024[2]
Поэтому в данном докладе, метод синтеза не рассматривается.
В любом случае, для оптимизации производительности СУБД необходимо мониторить состояние (как минимум) и метрики производительности СУБД.
Мониторинг производительности СУБД – необходим.
Как происходит процесс решения инцидентов производительности, если производительность базы данных не мониторится:
Заказчик -” у нас все плохо, долго, сделайте нам хорошо”
Инженер-” плохо это как?”
Заказчик –” вот как сейчас (час назад, вчера, на прошлой неделе было), медленно”
Инженер – “а когда было хорошо?”
Заказчик – “неделю (две недели) назад было неплохо. “ (Это повезло)
Заказчик – “а я не помню, когда было хорошо, но сейчас плохо “ (Обычный ответ).
Кто виноват и что делать?
На первую часть вопроса ответить легче всего — виноват всегда инженер DBA, потому, что “Мы уперлись в СУБД.” ©, “А с приложением у нас все хорошо.” ©.
На вторую часть ответить тоже не слишком сложно — нужно внедрять систему мониторинга производительности базы данных.
Возникает главный вопрос (вообще говоря, не относящийся к теме доклада) — что мониторить?
Путь 1. Будем мониторить ВСЁ
Загрузку CPU, свободное место SWAP, количество операций дискового чтения/записи, размер выделенной памяти, и еще мегатонна разных счетчиков, которые любая более-менее рабочая система мониторинга может предоставить.
В результате получается куча графиков, сводных таблиц, и непрерывные оповещения на почту и 100% занятость инженера - решением кучи одинаковых тикетов, впрочем, как правило со стандартной формулировкой — “Temporary issue. No action need”. Зато, все заняты, и всегда есть, что показать руководству — работа кипит.
Нужен более эффективный путь.
Путь 2. Мониторить только то, что нужно, а, что не нужно, не нужно мониторить.
В данной ситуации необходимо мониторить производительность СУБД. А для этого нужно рассчитать метрику производительности СУБД.
Как рассчитать метрику производительности СУБД?
В каждой естественной науке заключено столько истины,
сколько в ней есть математики
Вспоминая физику, можно использовать базовое понятие:
Производительность – это объем работы, выполняемый за единицу времени (например, за час или за день).
По-другому, скорость выполнения работы.[3]
Данное определение, было взято за основу. Однако необходимо уточнить - что такое объем работы, выполняемой СУБД.
Сначала необходимо определить – какими свойствами должна обладать метрика производительности.
Метрика производительности СУБД - это дискретное скалярное выражение, обладающее следующими свойствами:
· Величина определена для временного интервала, отличного от нуля.
· Значение всегда больше или равно нулю.
· Находится в прямой зависимости от числа операций, произведенных пользователем в СУБД за конкретный промежуток времени.
· Не содержит известных отклонений(аномалий) от нормального функционирования.
[3] Задачи на работу и производительность | YouClever
Самый первый вариант расчета метрики
Производительность СУБД — модуль вектора: (N1, N2, N3),
где:
· N1 - количество активных сессий за отрезок времени.
· N2 - количество завершенных транзакций за отрезок времени.
· N3 - количество запросов к СУБД за отрезок времени.
Данные легко можно получить из систем системных представлений СУБД и представления pg_stat_statements одноименного расширения.
Однако, как можно понять - метрика в общем то не совсем производительность считает.
Причина - "количество активных сессий», и тут возможна первая аномалия.
Аномалия учета ожиданий
Возможна ситуация - особенно при продуктивной нагрузке - работоспособность СУБД падает, а метрика растет.
Причина- количество активных сессий учитывает не только сессии выполняющие запросы, но и сессии, находящиеся в состоянии ожидания.
Второй вариант расчета метрики
Производительность СУБД — модуль вектора :(N1, N2, N3, N4, N5),
где:
•N1 - количество страниц shared_buffer, прочитанных в секунду
•N2 - количество страниц shared_buffer, записанных в секунду
•N3 - количество страниц shared_buffer, измененных в секунду
•N4 - количество завершенных запросов в секунду
•N5 - количество зафиксированных транзакций в секунду
Аномалия изменения плана выполнения запроса.
Для того, чтобы обнаружить аномалию достаточно было провести очень простой эксперимент:
•Создаем большие таблицы: родитель-потомок.
•Не создаем индексы.
• Создаем запрос, использующий фильтрацию. Поскольку индексов нет, в плане выполнение используется последовательное чтение.
•Выполняем несколько итераций, фиксируем время выполнения запроса и показатель производительности СУБД.
•Создаем индексы для таблиц.
•Выполняем итерации того же самого запроса.
•Фиксируем время выполнения запроса и показатель производительности СУБД.
Аномалия заключается в том, что запрос стал работать на порядки быстрее, стоимость запроса кардинально снизилась, следовательно, эффективность резко возросла, но значение метрики - уменьшается.
Причина: При выполнении индексного доступа к данным, количество обработанных страниц shared_buffer существенно уменьшается. А при использовании метода доступа Index Only Scan вообще будет нулевым. В результате значение метрики производительности уменьшается.
Третий вариант расчета метрики
Для решения проблемы аномалии изменения плана выполнения запроса, расчет метрики был изменен. Необходимо было ввести новые определения.
Операционная(результативная) скорость
Полезными операциями(результатами) работы СУБД являются:
• Количество строк, выданных пользователю.
• Количество запросов, выполненных пользователем.
• Количество зафиксированных пользователем транзакций.
Разделив количество на количество секунд (DB Time), которые потребовались на выполнения операций СУБД в изменяемый промежуток получаем - вектор, определяющий операционную(результативную) скорость:
• QPS: Количество запросов в секунду.
• TPS: Количество транзакций в секунду.
• RPS: Количество строк в секунду.
Модуль вектора (QPS, TPS, RPS) и будет считаться операционной скоростью.
Объемная скорость
Работа СУБД заключается в обработке блоков информации:
•Прочитанные разделяемые блоки
•"Загрязнённые" разделяемые блоки
•Записанные разделяемые блоки
•Прочитанные локальные блоки
•"Загрязнённые" локальные блоки
•Записанные локальные блоки
•Прочитанные временные блоки
•Записанные временные блоки
Таким образом, применив тот же подход, что и для расчета операционной скорости получим- вектор, определяющий объёмную скорость:
•RSBS: Прочитанные разделяемые блоки в секунду.
•DSBS: "Загрязнённые" разделяемые блоки в секунду.
•WSBS: Записанные разделяемые блоки в секунду.
•RLBS: Прочитанные локальные блоки в секунду.
•DLBS: "Загрязнённые" локальные блоки в секунду.
•WLBS: Записанные локальные блоки в секунду.
•RTBS: Прочитанные временные блоки в секунду.
•WSBS: Записанные временные блоки в секунду.
Модуль вектора (RSBS, DSBS, WSBS, RLBS, DLBS, WLBS, RTBS, WSBS) и будет объемной скоростью.
Полученное значение и будет объемной скоростью.
Отношение операционной скорости к объемной скорости и будет принято считать, как производительность СУБД.
Как видно, производительность СУБД в течение заданного промежутка времени прямо пропорционально объёму полученного результата и обратно пропорциональна объёму обработанной для получения результата информации.
Другими словами - данная метрика показывает - насколько эффективно СУБД выдаёт результат, обрабатывая объем информации.
Т.е. если план запроса изменился так, что запрос стал выполняться быстрее и читать меньше блоков (стоимость запроса снизилась), то в этом случае значение метрики - увеличится.
Аномалия деградации производительности.
Аномалия третьего варианта расчета метрики производительности проявилась при проведении нагрузочного тестирования – нагрузка на СУБД (количество одновременных выполняемых тестовых запросов) растет, а значение метрики сначала уменьшается, потом резко возрастает.
Причина – с ростом нагрузки операционная скорость снижается, но объемная скорость также снижается.
Поэтому после некоторого значения нагрузки значение отношения “операционная скорость” / “объемная скорость” начинает возрастать.
Текущий (возможно не окончательный) вариант расчета производительности СУБД
В ходе исследований, были перепробованы и другие методы расчета метрики производительности. Однако, все они показали наличие аномалий.
Ключевой момент, который необходимо учитывать: вычислить точную продуктивность системы управления базами данных (СУБД) реально лишь при условии, если известен объем информации, полученный от СУБД в ответ на запрос. К сожалению, на данный момент для PostgreSQL не существует способа узнать- какой объем данных был возвращен в результате запроса. Мы можем определить количество строк в ответе, но точный размер, переданных данных в SQL-запросе остается недоступным (по крайней мере, на текущий момент).
В связи с этим, вместо прямого расчета производительности СУБД, принято решение – использовать метод оценки производительности СУБД, на основании косвенных оценок показателей метрик производительности СУБД.
Основной метрикой для оценки производительности СУБД является модифицированная метрика операционной скорости:
Операционная скорость – это комплексная метрика, определяемая путем сложения общего количества исполненных SQL-запросов и общего количества строк, обработанных или измененных командами за конкретный период времени.
Инструменты мат. статистики, используемые при расчетах
Медиана
Медиа́на (от лат. mediāna «середина») или серединное значение набора чисел — число, которое находится в середине этого набора, если его упорядочить по возрастанию, то есть такое число, что половина из элементов набора не меньше него, а другая половина не больше. Другое равносильное определение: медиана набора чисел — это число, сумма расстояний (или, если более строго, модулей) от которого до всех чисел из набора минимальна.[4]
[4] Медиана (статистика) — Рувики: Интернет-энциклопедия
Сглаживание выбросов
Принципиальное отличие в сглаживании скользящей средней и скользящей медианой хорошо заметно на данных реальной нагрузки на СУБД.
Подробнее о скользящей средней и медиане.[5]
[5] К вопросу о скользящей средней и скользящей медиане
Проблема среднего арифметического при расчёте среднего времени выполнения запроса.
В ходе нагрузочного тестирования была обнаружена следующая проблема:
· Имеется 2 виртуальные машины в облачном хранилище - версия СУБД одинаковая, гипервизор один, других ВМ в гипервизоре - нет.
Метрика производительности СУБД в ходе нагрузочного тестирования
Среднее время выполнения тестового запроса
В результате - производительность СУБД практически не отличается, а среднее время выполнения тестового запроса отличается кардинально. Как такое возможно?
Причина: Использование при расчета значение mean_exec_time среднего арифметического.
Среднее арифметическое не всегда является идеальным показателем. Например, если ваши данные содержат очень высокие или низкие значения, они могут сильно исказить среднее. В таких случаях рассмотрите использование других статистических мер.[6]
Решение проблемы: Использование в качестве среднего значение для времени выполнения тестового SQL-запроса – медианы.
[6] Как найти среднее арифметическое
Коэффициент корреляции
Корреля́ция (от лат. correlatio «соотношение»), или корреляцио́нная зави́симость — статистическая взаимосвязь двух или более случайных величин (либо величин, которые можно с некоторой допустимой степенью точности считать таковыми), при этом изменения значений одной или нескольких из этих величин сопутствуют систематическому изменению значений другой или других величин.[7]
[7] Корреляция — Рувики: Интернет-энциклопедия
Отрицательная корреляция
отрицательная корреляция — корреляция, при которой увеличение одной переменной связано с уменьшением другой. При этом коэффициент корреляции будет отрицательным[8].
[8] Корреляция — Рувики: Интернет-энциклопедия
КОЭФФИЦИЕНТ КОРРЕЛЯЦИИ -0,192
Положительная корреляция
Положительная корреляция в таких условиях — это такая связь, при которой увеличение одной переменной связано с увеличением другой переменной.[9]
[9] Корреляция — Рувики: Интернет-энциклопедия
КОЭФФИЦИЕНТ КОРРЕЛЯЦИИ 0,9115
Линия регрессии
Метод наименьших квадратов — такой способ проведения регрессионной линии, чтобы сумма квадратов отклонений отдельных значений зависимой переменной от неё была минимальной.[10]
[10] Линейная регрессия, метод наименьших квадратов
Метрики и индикаторы производительности СУБД
Источником данных для дальнейшей статистической обработки и расчетов метрик производительности СУБД являются представления расширения pgpro_stats:
Расширение pgpro_stats предоставляет возможности для сбора статистики планирования и выполнения всех обрабатываемых сервером SQL-операторов.[11]
Операционная скорость
Как было указано выше, для расчета операционной скорости необходимы следующие исходные данные:
1) Количество выполненных запросов за отрезок времени.
2) Количество обработанных или изменённых строк за отрезок времени.
[11] pgpro_stats
Для получения необходимых для расчетов данных используются представления расширения pgpro_stats:
1)Представление pgpro_stats_statements
Статистика, собираемая модулем, выдаётся через представление с именем pgpro_stats_statements. Это представление содержит отдельные строки для каждой комбинации идентификатора базы данных, идентификатора пользователя и идентификатора запроса.[1]
2)Представление pgpro_stats_totals
Агрегированная статистика, собранная модулем, выдаётся через представление pgpro_stats_totals. Это представление содержит отдельные строки для каждого отдельного объекта БД[2]
Используемые столбцы:
· calls Счётчик выполнений данного оператора
· rows Общее число строк, полученных или затронутых оператором
Данные собираются по СУБД в целом (pgpro_stats_totals) и по каждому SQL (pgpro_stats_statements) в отдельности.
Периодичность сбора = 1 минута.
[13] pgpro_stats_totals
Для исключения влияния выбросов на результаты используется метод медианного сглаживания:
Основное преимущество медианного сглаживания, в сравнении со сглаживанием, скользящим средним, состоит в том, что результаты становятся более устойчивыми к выбросам (имеющимся внутри окна). Таким образом, если в данных имеются выбросы (связанные, например, с ошибками измерений), то сглаживание медианой обычно приводит к более гладким или, по крайней мере, более "надежным" кривым, по сравнению со скользящим средним с тем же самым окном.[14]
[14] Сглаживание
Для расчета медианного значения, используется встроенная функция PostgreSQL - percentile_cont[15].
Скользящая медиана(t) = медиана отрезка [Y (t - период); Y(t)].
Период сглаживания = 1 час.
Ожидания СУБД
Для получения необходимых для расчетов данных используются представления расширения pgpro_stats:
1)Представление pgpro_stats_statements
Статистика, собираемая модулем, выдаётся через представление с именем pgpro_stats_statements. Это представление содержит отдельные строки для каждой комбинации идентификатора базы данных, идентификатора пользователя и идентификатора запроса.[16]
2)Представление pgpro_stats_totals
Агрегированная статистика, собранная модулем, выдаётся через представление pgpro_stats_totals. Это представление содержит отдельные строки для каждого отдельного объекта БД[17]
· wait_stats Объект типа jsonb, содержащий статистику по событиям ожидания для каждого выполнения запроса по соответствующему плану.
Данные собираются по СУБД в целом (pgpro_stats_totals) и по каждому SQL (pgpro_stats_statements) в отдельности.
Периодичность сбора = 1 минута.
Для исключения влияния выбросов на результаты используется метод медианного сглаживания:
Основное преимущество медианного сглаживания, в сравнении со сглаживанием, скользящим средним, состоит в том, что результаты становятся более устойчивыми к выбросам (имеющимся внутри окна). Таким образом, если в данных имеются выбросы (связанные, например, с ошибками измерений), то сглаживание медианой обычно приводит к более гладким или, по крайней мере, более "надежным" кривым, по сравнению со скользящим средним с тем же самым окном.[18]
Для расчета медианного значения, используется встроенная функция PostgreSQL - percentile_cont[19].
Скользящая медиана(t) = медиана отрезка [Y (t - период); Y(t)].
Период сглаживания = 1 час.
[18] Сглаживание
Угол наклона линии регрессии операционной скорости
Для расчета угла наклона линии наименьших квадратов, используется встроенная функция PostgreSQL regr_slope:
regr_slope ( Y double precision, X double precision ) → double precision
Вычисляет наклон линии, полученной методом наименьших квадратов по парам (X, Y).[20]
Выборка X = точки наблюдения в отрезке [t - период; t].
Выборка Y = значение операционной скорости в отрезке [t - период; t].
Период 1 час.
Угол наклона линии регрессии ожидания
Для расчета угла наклона линии наименьших квадратов, используется встроенная функция PostgreSQL regr_slope:
regr_slope ( Y double precision, X double precision ) → double precision
Вычисляет наклон линии, полученной методом наименьших квадратов по парам (X, Y).[21]
Выборка X = точки наблюдения в отрезке [t - период; t].
Выборка Y = значение ожиданий в отрезке [t - период; t].
Период 1 час.
Индикатор снижения операционной скорости
Для расчета коэффициента корреляции, используется встроенная функция PostgreSQL corr[22].
Условие возникновения события “Инцидент снижения скорости СУБД”:
Если угол наклона линии регрессии операционной скорости <0,
И
угол наклона линии регрессии ожиданий> 0
ТО
Создать оповещение мониторинга "Инцидент деградации производительности".
В качестве уровня важности оповещения, можно использовать абсолютное значение коэффициента корреляции:
<0.7: низкий уровень
>= 0.7: высокий уровень.[23]
[23] "Индикатор снижения скорости" как сигнал для начала корреляционного анализа ожиданий СУБД.
Условие завершения события “Инцидент снижения скорости СУБД”:
Если угол наклона линии регрессии операционной скорости >= 0,
ИЛИ
угол наклона линии регрессии ожиданий <= 0
ТО
Создать оповещение мониторинга "Инцидент деградации производительности завершен"[1]
[24] "Индикатор снижения скорости" как сигнал для начала корреляционного анализа ожиданий СУБД.
Ожидания СУБД
Когда процесс (системный или обслуживающий) не может выполнять свою работу и ждет чего-либо, это ожидание можно увидеть в представлении: столбец wait_event_type показывает тип ожидания, а столбец wait_event — имя конкретного ожидания.[25]
[25] Блокировки в PostgreSQL: 4. Блокировки в памяти / Хабр
Гипотеза
Сокращение периода, когда серверный процесс, обрабатывающий SQL-запрос к системе управления базами данных (СУБД), сталкивается с событием ожидания, напрямую влияет на скорость выполнения запроса. Чем меньше времени процесс простаивает в ожидании, тем быстрее завершается SQL-запрос.
При многократном повторении запросов, минимизация времени ожидания приводит к увеличению количества выполненных запросов за единицу времени. В конечном итоге, это позволяет предоставить клиенту больший объем полезной информации за тот же период. Таким образом, снижение задержек в обработке SQL-запросов ведет к повышению общей производительности системы.
Следствие из гипотезы - необходимое условие определения причины снижения производительности СУБД.
Для оптимизации производительности СУБД необходимо определять и оптимизировать SQL запросы в ходе выполнения которых возникают ожидания имеющие наибольшую корреляцию со снижением операционной скорости СУБД.
Какая практическая польза от результатов расчётов?
Теория — это когда все известно,
но ничего не работает.
Практика — это когда все работает,
но никто не знает почему.
Мы же объединяем теорию и практику:
ничего не работает…
и никто не знает почему!
Вся предыдущая часть доклада служит только одной цели – практическое использование корреляции ожиданий СУБД для анализа и оптимизации производительности СУБД PostgreSQL.
Использовать методику корреляционного анализа ожиданий СУБД можно для двух основных практических тем:
1. Анализ результатов нагрузочного тестирования СУБД
2. Оптимизация производительности СУБД в ходе решения инцидентов производительности СУБД.
Анализ результатов нагрузочного тестирования СУБД
Для создания рабочей нагрузки используется штатное средство PostgreSQL:
pgbench — это простая программа для запуска тестов производительности Postgres Pro. Она многократно выполняет одну последовательность команд, возможно в параллельных сеансах базы данных, а затем вычисляет среднюю скорость транзакций (число транзакций в секунду). По умолчанию pgbench тестирует сценарий, примерно соответствующий TPC-B, который состоит из пяти команд SELECT, UPDATE и INSERT в одной транзакции. Однако вы можете легко протестировать и другие сценарии, написав собственные скрипты транзакций.[26]
[26] pgbench
Нагрузочный тест состоит из последовательности итераций с увеличивающейся нагрузкой. Нагрузка имитируется увеличением значения параметра --client для каждой итерации.
--client=клиенты
Число имитируемых клиентов, то есть число одновременных сеансов базы данных. Значение по умолчанию -1.[27]
[27] pgbench
Рост нагрузки – экспоненциально.
Сценарии нагрузочного тестирования определяются и используя параметр --file:
--file=имя_файла[@вес]
Добавить в список выполняемых скриптов скрипт транзакции из файла имя_файла.
Дополнительно можно задать целочисленный вес после @, меняющий вероятность выбора этого скрипта относительно других. По умолчанию вес считается равным 1.[28]
Среда нагрузочного тестирования – виртуальная машина в облаке.
Комбинируя сценарии или используя новые сценарии нагрузочного тестирования можно получить оценить предельную нагрузку и характерные ожидания коррелированные со снижением скорости СУБД.
[28] pgbench
Сценарии нагрузочного тестирования
Сценарий “Select only”
Задача сценария
Определение предельных нагрузок и характерных ожиданий СУБД при читающей нагрузке на СУБД.
Тестовый запрос сценария
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = max_i / 2;
select br.bbalance
into test_rec
from pgbench_branches br
join pgbench_accounts acc on (br.bid = acc.bid )
where acc.aid = current_aid ;
Пример результатов нагрузочного тестирования по сценарию “Select only”
Корреляция и ожидания
Результат нагрузочного тестирования
Предельной нагрузкой для данной ВМ для сценария “Select only” можно считать значение 16 одновременных клиентских подключений.
Характерным типом ожидания для данного сценария является ожидание легковесных блокировок LWLock.
Характерным событием ожидания является ожидание при обращении к общим структурам данных в рамках процесса ProcArray.
Сценарий “Select + Update”
Задача сценария
Определение предельных нагрузок и характерных ожиданий СУБД при выполнении массовых запросов и обновлений в одной транзакции.
Тестовый запрос сценария
current_delta = (ROUND( random ())::integer)*10 + 1 ;
SELECT MIN(aid) INTO min_i FROM pgbench_accounts ;
SELECT MAX(aid) INTO max_i FROM pgbench_accounts ;
current_aid = max_i / 2;
UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE aid = current_aid ;
SELECT abalance INTO test_rec FROM pgbench_accounts WHERE aid = current_aid ;
SELECT MIN(tid) INTO min_i FROM pgbench_tellers ;
SELECT MAX(tid) INTO max_i FROM pgbench_tellers ;
current_tid = max_i / 2;
UPDATE pgbench_tellers SET tbalance = tbalance + current_delta WHERE tid = current_tid ;
SELECT MIN(bid) INTO min_i FROM pgbench_branches ;
SELECT MAX(bid) INTO max_i FROM pgbench_branches ;
current_bid = max_i / 2;
UPDATE pgbench_branches SET bbalance = bbalance + current_delta WHERE bid = current_bid ;
Пример результатов нагрузочного тестирования по сценарию “Select + Update”
Корреляция и ожидания
Результат нагрузочного тестирования
Предельной нагрузкой для данной ВМ для сценария “Select + Update” можно считать значение 16-21 одновременных клиентских подключений.
Характерным типом ожидания для данного сценария является ожидание тяжеловесных блокировок Lock и легковесных блокировок LWLock.
Наиболее характерным событием ожидания являются ожидания:
· transactionid Ожидание завершения транзакции.
· tuple Ожидание при запросе блокировки для кортежа.
· LockManager Ожидание при чтении или изменении информации о «тяжёлых» блокировках.
· BufferContent Ожидание при обращении к странице данных в памяти.
· ProcArray Ожидание при обращении к общим структурам данных в рамках процесса
Сценарий “Insert only”
Задача сценария
Определение предельных нагрузок и характерных ожиданий СУБД при пишущей нагрузке на СУБД.
Тестовый запрос сценария
SELECT MAX(aid) INTO current_aid FROM pgbench_accounts ;
SELECT MAX(tid) INTO current_tid FROM pgbench_tellers ;
SELECT MAX(bid) INTO current_bid FROM pgbench_branches ;
FOR counter IN 1..1000
LOOP
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES ( current_tid , current_bid , current_aid , 10 , CURRENT_TIMESTAMP );
END LOOP;
Пример результатов нагрузочного тестирования по сценарию “Insert only”
Корреляция и ожидания
Результат нагрузочного тестирования
Предельной нагрузкой для данной ВМ для сценария “Insert only” можно считать значение 21 одновременных клиентских соединений.
Характерным типом ожидания для данного сценария является ожидание легковесных блокировок LWLock , ожидание тяжеловесных блокировок Lock и дисковой подсистемы IO.
Наиболее характерным событием ожидания являются ожидания:
· BufferMapping Ожидание при связывании блока данных с буфером в пуле буферов.
· WALInsert Ожидание при добавлении записей WAL в буфер в памяти.
· BufferContent Ожидание при обращении к странице данных в памяти.
· MultiXactGen Ожидание при чтении или изменении общего состояния мультитранзакций.
· ProcArray Ожидание при обращении к общим структурам данных в рамках процесса
· LockManager Ожидание при чтении или изменении информации о «тяжёлых» блокировках
· Extend Ожидание при расширении отношения.
· DataFileExtend Ожидание расширения файла данных отношения.
Пример 1: анализ влияния изменения конфигурационного параметра СУБД slru_buffers_scale_size.
Задача
Получить качественную и количественную оценку влияния изменения конфигурационного параметра slru_buffers_scale_size на производительность СУБД по сценарию “Insert only”.[29]
Сравнительные эксперименты
· Эксперимент-1: slru_buffers_scale_size = 2
· Эксперимент-2: slru_buffers_scale_size = 5
Результаты нагрузочного тестирования
[29] PG_HAZEL : Влияние изменения параметра slru_buffers_scale_size на скорость и ожидания СУБД.
Результат
· Скорость выполнения тестового запроса в Эксперименте 2, менялась разнонаправлено от -1.5% до 1.5%.
· Время выполнения тестового запроса в Эксперименте 2, менялась разнонаправлено от -1% до 1%.
Пример 2: анализ влияния изменения конфигурационного параметра СУБД max_worker_processes.
Задача
Получить качественную и количественную оценку влияния изменения конфигурационного параметра max_worker_processes на производительность СУБД по сценарию “CPU Load”.[30]
Сравнительные эксперименты:
· Эксперимент-1: max_worker_processes = 8
· Эксперимент-2: max_worker_processes = 4
Результаты нагрузочного тестирования
[30] PG_HAZEL: Влияние изменения параметра max_worker_processes на скорость и ожидания СУБД.
Результат
· Скорость выполнения тестового запроса в Эксперименте 2 увеличилась до ~3%
· Время выполнения тестового запроса в Эксперименте 2 уменьшалась до 4%, затем относительная разница медианного начала снижаться.
Оптимизация производительности СУБД в ходе решения инцидентов производительности СУБД
Как было указано выше, решение задачи методом анализа состоит из основных этапов:
1) Фиксируется проблема (снижение производительности).
2) Производится сбор данных о состоянии и метриках СУБД.
3) Определяются факторы, снижающие производительность СУБД.
4) Формируется перечень действий, направленных на устранение этих факторов.
Пример: отсутствие индексов
Этап 1 – Идентификация/фиксация проблемы
Этап 2 – Сбор данных
Определение типа ожидания с наибольшей корреляцией
Типы и события ожиданий за время инцидентов
Этап 3 – Определение факторов, снижающих производительность СУБД
Диаграмма Парето по типам ожидания
Запросы c наибольшей долей ожидания
Корреляция по событиям ожидания по проблемному запросу
Этап 4 – Формирование перечня действий для устранения причины
Анализ текста и плана выполнения проблемного запроса
Текст запроса
SELECT
"id"
FROM
table1 AS "Task"
WHERE
"Task"."id1" = 721148 AND
"Task"."id2" = 2 AND
"Task"."id3" = 2
План выполнения запроса
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather (cost=1000.00..155830.18 rows=1 width=8)
Workers Planned: 5
-> Parallel Seq Scan on task "Task" (cost=0.00..154830.08 rows=1 width=8)
Filter: (("id1" = 721148) AND ("id2" = 2) AND ("id3" = 2))
Результат анализа
1. Использование метода доступа Parallel Seq Scan
2. Использование параллельных процессов в плане выполнения
Рекомендации по оптимизации проблемного запроса
Основываясь на результатах эксперимента:
PG_HAZEL: ожидания СУБД PostgreSQL при отсутствии индексов[31].
Дана рекомендация - добавить в таблицу table1 индексы по полям:
· id1
· id2
· id3
[31] PG_HAZEL: ожидания СУБД PostgreSQL при отсутствии индексов
Какие есть ограничения для метода?
Нельзя объять необъятное.
Основное и принципиальное ограничение метода корреляционного анализа ожиданий СУБД – снижение скорости СУБД, вызванное внешними причинами, в первую очередь CPU.
Пример: Утилизация CPU и снижения скорости СУБД
Симптомы
Аномальная утилизация CPU сервера СУБД
Пример: Утилизация CPU и снижения скорости СУБД
Наблюдаемая проблема
Снижение операционной скорости СУБД
Корреляционный анализ
Отрицательная корреляция между снижением операционной скорости и ростом ожиданий - отсутствует.
Корреляционный анализ - не применим. Причина снижения скорости СУБД - внешняя.
Для дальнейшего анализа используется pgpro_pwr[31].
Причина аномальной утилизации CPU и снижения операционной скорости СУБД.
Массовый вызов хранимой функции, требующей для выполнения высоких вычислительных ресурсов.[32]
[31] pgpro_pwr — отчёты о нагрузке
[32] Утилизация CPU и снижения скорости СУБД -определение причины с использованием pg_hazelи pgpro_pwr.
Контакты
Сунгатуллин Ринат Раисович
Дзен канал “Postgres DBA”
Телеграмм канал “PG_HAZEL”:
E-mail: