Как сравнить? - измерить execution-time конечно! - скажет опытный читатель. И будет совершенно прав: с практической точки зрения эффективнее та СУБД, которая выдаёт больший TPS. Однако иногда нам требуется спроектировать систему, которой ещё нет или сделать прогноз поведения на нагрузках, которые ещё не пришли. В таком случае нам нужна некоторая характеристика, позволяющая выполнить качественный анализ плана или выполнить сравнение пары планов. Обсуждению одной такой характеристики - количество прочитанных страниц данных - и посвящён данный пост.

Наиболее проблемной частью научных статей, посвященных различным аспектам технологий систем баз данных для меня являются разделы "performance evaluation". Врядли нужно говорить, что эта часть исследования зачастую является ключевой разработчика, поскольку обосновывает смысл того, что мы вообще тратим своё время на чтение всего предшествующего текста. Не менее важно также то, что этот раздел должен обеспечивать повторяемость результатов и возможность независимого анализа.

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

Ничего подобного я пока не вижу в нашей области систем баз данных. В разделе посвященном тестированию обычно кратко приводится описание программно-аппаратной части и графики, на которых основной исследуемый параметр - время выполнения запроса (execution-time) или TPS (transactions-per-second).

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

А теперь представим, что мы создаем новый метод оптимизации запросов, и хотим сравнить его с ранее опубликованным. Имея графики времени выполнения запросов по схеме «было» - «стало» с кратким описанием тестовой платформы, как например здесь или здесь, мы упираемся в то, что наши цифры не согласуются с опубликованными результатами ввиду большого количества неизвестных. В таком случае нам важно иметь измеряемый параметр, который исключит влияние других подсистем СУБД, будет более переносимым и доступным для анализа. Уверен, что разработчики, к примеру, нового стораджа также были бы не против исключить влияние оптимизатора в своих бенчмарках.

Пытаясь повторить приводимые в статьях эксперименты или сравнить свой метод, с предлагаемым автором мне каждый раз приходится признавать, что неопределённость общепринятого измеряемого параметра execution-time чересчур высока для того, чтобы делать конкретные выводы - она оценивает скорее эффективность кода в конкретных условиях эксплуатации, а не качество найденного плана запроса.

Execution-time -  это характеристика с большим уровнем шума: даже последовательно выполняя прогон одного и того же теста на одной машине, на одном и том же инстансе, мы можем получить значительный разброс execution-time. Для примера приведу результаты десяти последовательных прогонов всех 113 тестов Join Order Benchmark (JOB). Характерный разброс execution-time на моём десктопе составляет до 50% - и это в идеальных условиях, когда в точности повторяются все параметры эксперимента. А какие отличия получит сторонний исследователь повторив эксперимент и как ему анализировать результаты?

SELECT
  shared_blks_hit+shared_blks_read+local_blks_hit+local_blks_read+
  temp_blks_read AS blocks, total_exec_time::integer AS exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements_reset%';

Насколько хорош этот параметр? В том же эксперименте, что показан на рисунке выше, все десять прогонов теста JOB показали мизерное отклонение количества страниц для каждого запроса от итерации к итерации:

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

Что нам дает такой показатель? Проведу простой эксперимент. Возьмем один запрос теста JOB (10a.sql) и будем последовательно увеличивать количество воркеров, участвующих в обработке запроса. Посмотрим как меняется время выполнения запроса и количество прочитанных страниц данных.

Можно заметить, что хотя время выполнения запроса изменяется, количество прочитанных страниц данных остаётся на том же уровне. Количество страниц изменяется только один раз, при увеличении количества воркеров с 1 до 2 - в два раза увеличивается количество прочитанных страниц. Заглянув в EXPLAIN этих двух кейсов обнаруживается и причина: Если в случае 0 и 1 воркеров из шести джойнов запроса три были типа NestLoop и три - HashJoin, то при двух и более воркерах количество NestLoop JOIN’ов увеличивается на 1, а количество HashJoin’ ов соответственно снижается до двух. Таким образом, изменение количества прочитанных страниц позволило детектировать изменение плана запроса, что было неочевидно с критерием execution-time.

Теперь, используя критерий прочитанных страниц попробуем посмотреть, какой эффект даёт расширение AQO оптимизатора PostgreSQL на запросах теста JOB.

Выполним каждый запрос теста с AQO в режиме learn десять раз. В этом режиме AQO работает просто как память планнера, запоминая кардинальность каждой ноды плана (и количество групп в соответствующих операторах) в конце выполнения и подставляя её на этапе планирования, позволяя оптимизатору отклонить план, если оценки были чересчур оптимистичны. Учитывая то, что оптимизатор Postgres склонен недоoценивать (underestimate) кардинальности джойнов, данная методика выглядит вполне обоснованной.

На рисунке ниже показано (логарифмическая шкала), как изменялось количество прочитанных страниц относительно первой итерации (когда оптимизатор не имеет ещё подсказок о кардинальности нод плана запроса, количестве distinct-значений в колонках и пр.). На последней, десятой, итерации почти все запросы либо улучшают этот показатель, либо оставляют его неизменным - возможно, что в пространстве возможных планов оптимизатор Postgres сразу выбрал наилучший, либо наша методика здесь не сработала.

Однако, остаётся ещё шесть запросов, количество прочитанных страниц в которых увеличилось, по сравнению с первой итерацией. Может быть не хватило итераций, чтобы отсеять неоптимальные планы запроса? Увеличим количество итераций выполнения до 30 и посмотрим, что получится:

На рисунке выше видно, что планы запросов сошлись к некоторому оптимальному решению. Здесь можно заметить, что два запроса (26b и 33b) сошлись к количеству страниц большему, чем на нулевой итерации. Если посмотреть на время выполнения запроса, то оно улучшилось на 15-20%. Эксплейны объясняют причину: количество NesLoop’ов в плане запроса уменьшилось на 1, а HashJoin, выполняя построение hash-таблицы сканирует таблицу полностью и увеличивает количество страниц. При этом, параллельный HashJoin оказывается по времени более эффективным, что и приводит к лучшему времени запроса.

Таким образом, получается, что количество страниц не является универсальным критерием оптимальности запроса. При этом, в условиях одной СУБД этот критерий позволяет зафиксировать точку отсчёта, повторить эксперимент в другом программно-аппаратном окружении, сравнивать различные методы оптимизации и обнаруживать эффекты, которые скрыты за нестабильным execution-time. Вероятно, не следует отказываться от execution-time при публикации результатов бенчмарков. Однако, может быть добавить к нему pages-read?

В итоге, имея график изменения количества страниц, прочитанных в ходе выполнения запроса, скрипт тестового прогона (см. выше) и ссылку на сырые данные, можно независимо воспроизвести эксперимент, оттарировав его по опубликованным данным, выполнить дополнительные исследования или сравнить со своим методом в схожих условиях. Разве это не удобно?

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

THE END.

12 января 2025, Паттайя, Тайланд.