Непрямой контроль производительности приложения через генерируемый SQL и его характеристики

Привет, Хабр! В настоящее время используются не только SQL решения для работы с данными, тем не менее, на долю SQL приходится значительная часть систем. Также нередко бывает, что приложение генерирует SQL в зависимости от действий пользователя, например, при выборе полей или применении фильтров в отчетах, иными словами, есть динамический SQL, а не статический. Также часто для приложения есть тесты, например, соответствующие типичным активностям пользователей, и каждой активности соответствует один или несколько SQL, причем в тестах проверяется именно правильность результатов выполнения SQL.

Далее, для относительно сложных вариантов генерации SQL при изменениях бизнес-логики (или других изменениях) может измениться и сам динамически генерируемый SQL, причем результаты выполнения SQL могут остаться неизменными, однако производительность поменяется. В таких случаях изменения в производительности можно уловить при помощи бенчмарков, однако прогон бенчмарков может занимать значительное время, в связи с этим актуальна задача быстрого анализа изменений в производительности динамически генерируемого SQL. Интересны особенности анализа производительности приложения на основе SQL — добро пожаловать под кат 🙂

Видно, при изменениях в бизнес-логике, которые потенциально могут привести к изменению динамически сгенерированного SQL, возникают задачи:

1) детектирование изменений в производительности (без прогона бенчмарков);

2) при наличии изменений — отображение обобщенной картины (позитивные или негативные изменения);

3) при наличии изменений — возможность получить детальную информацию, что изменилось (в каком тесте).

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

Рассмотрим для примера пять тестов и допустим, мы получили пять SQL, по одному SQL для каждого теста. Будем считать, что условно имеем дело с PostgreSQL. Рассмотрим таблицы sales, customer, product. Предположим, что поле sales.customerid является обязательным и есть проверка в sales на существование ключа в customer.id.

Условной характеристикой качества SQL запроса будем считать количество INNER JOIN в нем (хотя, конечно, можно учитывать любые типы JOIN и любые другие характеристики, например, другие виды JOIN, длину запроса, количество параметров и т.д., также можно рассмотреть и не SQL запросы вовсе, а API запросы, подход остается один и тот же).

Возьмем пять тестов для проверки результатов выполнения SQL и пять соответствующих SQL, ниже приведены названия тестов и текст SQL:

Detailed Sales Report: SELECT sales.ordernumber, sales.amount, customer.name, product.name FROM sales INNER JOIN customer ON sales.customerid = customer.id INNER JOIN product ON sales.productid = product.id Total Sales: SELECT SUM(sales.amount) FROM sales Total Products: SELECT COUNT() FROM product Total Customers: SELECT COUNT() FROM customer Sales by Products: SELECT sales.ordernumber, product.name FROM sales INNER JOIN product ON sales.productid = product.id GROUP BY product.name

Считаю, что есть смысл сразу привести результат, к которому можно стремиться. Смоделируем изменения в бизнес-логике, которые привели к изменениям в генерации SQL — добавим лишний INNER JOIN customer в пятый тест:

SELECT sales.ordernumber, product.name   FROM sales   INNER JOIN customer ON sales.customerid = customer.id   INNER JOIN product ON sales.productid = product.id GROUP BY product.name

Для исходных пяти тестов и после изменений в бизнес-логике построены два Markdown файла, видны изменения Total суммарно по всем тестам — количество INNER JOIN увеличилось с 3 до 4, что может говорить о возможной деградации производительности.

Также видно и имя теста Sales by Products, в который мы добавили лишний INNER JOIN, а также видно, что в SQL для Sales by Products количество INNER JOIN увеличилось с 1 до 2.

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

Будем рассчитывать для каждого теста хеш каждого SQL и количественные характеристики производительности SQL запроса (в данном случае просто количество INNER JOIN в нем), также для всех тестов расчитаем общий инкрементальный хеш и общее количество INNER JOIN. Такую таблицу условно можно назвать performance fingerprint, т.к. за счет рассчитанного хеша по всем запросам в Total мы «фиксируем» SQL и анализируем его общие тенденции при помощи суммарного количества INNER JOIN, а за счет хешей по тестам можно найти все тесты с изменениями производительности для детального изучения, а также изменения количественных характеристик по тестам (т.е. количество INNER JOIN в каждом тесте).

К слову говоря, такой подход применим не только к SQL, а к анализу любых других метаданных или запросов, влияющих на производительность.

Реализация возможна на любом языке, можно рассмотреть на C# и .NET 9.

using System.Security.Cryptography; using System.Text; using System.Text.RegularExpressions; const string FullJoinString = "FROM sales INNER JOIN customer ON sales.customerid = customer.id INNER JOIN product ON sales.productid = product.id"; const string SalesProductJoinString = "FROM sales INNER JOIN customer ON sales.customerid = customer.id"; List<(string testName, string sql)> queries = [    ("Detailed Sales Report", "SELECT sales.ordernumber, sales.amount, customer.name, product.name " + FullJoinString),    ("Total Sales", "SELECT SUM(sales.amount) FROM sales"),    ("Total Products", "SELECT COUNT() FROM product"),    ("Total Customers", "SELECT COUNT() FROM customer"),    ("Sales by Products", "SELECT sales.ordernumber, product.name " + SalesProductJoinString) ]; var innerJoinTotalCount = 0; using SHA256 sha256 = SHA256.Create(); using IncrementalHash incrementalHash = IncrementalHash.CreateHash(HashAlgorithmName.SHA256); Console.WriteLine($"| Test Name | INNER JOIN Count | Hash |{Environment.NewLine}|---------:|-----------------:|------|"); for (int i = 0; i < queries.Count; i++) {    var bytes = Encoding.UTF8.GetBytes(queries[i].sql);    incrementalHash.AppendData(bytes);    int innerJoinCount = Regex.Matches(queries[i].sql, @"\bINNER JOIN\b", RegexOptions.IgnoreCase).Count;    innerJoinTotalCount += innerJoinCount;    var hash= sha256.ComputeHash(bytes);    Console.WriteLine($"| {queries[i].testName} | {innerJoinCount} | {Convert.ToHexString(hash).Replace("-", "").ToLower()} |"); }  var hashValue = incrementalHash.GetHashAndReset(); Console.WriteLine($"| Total | {innerJoinTotalCount} | {Convert.ToHexString(hashValue).Replace("-", "").ToLower()} |");

Используем SHA256 для хеша каждого SQL, и IncrementalHash для хеша всех SQL запросов. В результате выполнения получаем необходимую таблицу со значениями хешей.

Видно, что можно проверить начилие изменений в производительности можно через хеш Total. По количественным характеристикам Total (количество INNER JOIN в Total) можно понять, какое было изменение (хуже/лучше).

Далее по изменению SQL из теста можно понять, что изменилось, что ответственно за изменение. Наконец, по характеристикам для изменившегося теста можно понять, что изменилось в конкретном тесте (стало хуже/лучше).

Можно также сказать пару слов о выборе хеш функции. Нужно, чтобы она не зависела от данных времени выполнения приложения. В итоге, требования к хеш-функции — детерминированность и кросс-платформенность, чтобы можно было спокойно сравнивать файл обычным diff и там были одинаковые хеши для одинаковых SQL для любой платформы.

Теперь изменим SalesProductJoinString на FullJoinString для SQL в последнем тесте — моделируем изменения в бизнес‑логике, которые привели к изменениям в генерации SQL, добавляем лишний INNER JOIN customer:

("Sales by Products", "SELECT sales.ordernumber, product.name " + FullJoinString)

После изменений в бизнес-логике генерации SQL получили для пяти тестов следующие SQL:

Detailed Sales Report: SELECT sales.ordernumber, sales.amount, customer.name, product.name FROM sales INNER JOIN customer ON sales.customerid = customer.id INNER JOIN product ON sales.productid = product.id Total Sales: SELECT SUM(sales.amount) FROM sales Total Products: SELECT COUNT() FROM product Total Customers: SELECT COUNT() FROM customer Sales by Products: SELECT sales.ordernumber, product.name FROM sales INNER JOIN product ON sales.productid = product.id GROUP BY product.name

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

Результаты выполнения SQL не изменились, однако в тесте Sales by Products SQL стал менее оптимальным, что видно по увеличению количества INNER JOIN с 1 до 2 (лишний INNER JOIN с таблицей customer). Сумеет ли СУБД это исправить оптимизаторами — другой вопрос, предполагаем, это изменение действительно снижает произодительность.

При ручной проверке всех SQL кейсов даже для пяти тестов случаев это занимает неоторое время, чтобы найти лишний sales INNER JOIN customer в тесте Sales by Products. Поэтому, например, в реальных условиях для 5000 тестов и для 5000 SQL (причем, намного более сложных SQL), и изменений в нескольких тестах возможность обработки вручную всех SQL стоит под вопросом. Однако предложенный подход позволяет быстро решить подобную задачу (в рамках описанных возможностей и ограничений), может помочь в планировании задач и принятии решений (решения вида «нужно посмотреть возможную деградацию из теста Sales by Products», или решение вида «все стало только лучше, уменьшился SQL», если всё проверить вручную слишком трудозатратно).

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

С точки зрения workflow, для очередного коммита достаточно просто проверить общий хеш. Если он изменился, то сразу по общим характеристикам понять, стало лучше или хуже. По детальным характеристикам за счет хеша теста понять, в каком тесте изменилось, и в каком тесте стало лучше или хуже по характеристикам. Дальше уже другими средствами получить детальный SQL для теста, дебажить тест при необходимости. При многочисленных изменениях и невозможности/трудоемкости проверки каждого кейса — принять решение на основе общей таблицы.

Надеюсь, описанный подход будет интересен или может вдохновить на новые решения 🙂