Join Order Benchmark (JOB)

Join Order Benchmark (JOB) нагружает оптимизатор запросов 113 аналитическими запросами к реальному, сильно коррелированному набору данных (снимку IMDb). С момента своего появления бенчмарк JOB стал фактическим стандартом для оценки производительности оптимизаторов запросов реляционных баз данных, включая оценку мощности и оптимизацию порядка JOIN. В отличие от синтетических бенчмарков, предполагающих равномерные и независимые данные, JOB использует реальные данные с перекосами и корреляциями, что делает его сложным тестом для оптимизации порядка JOIN и оценки мощности.

Набор данных содержит около 74 миллионов строк в 21 таблице и занимает в ClickHouse около 1,15 GiB в сжатом виде.

113 запросов организованы в 33 семейства (1–33). Запросы внутри одного семейства (a, b, c, …) имеют один и тот же граф JOIN, но различаются предикатами отбора.

Ссылки

• Насколько хороши оптимизаторы запросов на самом деле? (Leis et al., VLDB 2015)
• Репозиторий Join Order Benchmark

Набор данных JOB — это снимок IMDb, содержащий 21 таблицу. Определения таблиц доступны в init_cloud.sql в репозитории ClickHouse.

В каждой таблице используется движок MergeTree с сортировкой по столбцу первичного ключа id, что соответствует исходной схеме PostgreSQL, где в каждой таблице объявлен id integer NOT NULL PRIMARY KEY. Столбцы PostgreSQL, допускающие NULL, сопоставляются с типами Nullable(...).

Создайте таблицы:

curl -O https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/tests/benchmarks/job/init_cloud.sql
clickhouse client --query "CREATE DATABASE IF NOT EXISTS job"
clickhouse client --database job --queries-file init_cloud.sql

Данные берутся из исходного снимка IMDb, используемого в JOB, и распространяются по одному CSV-файлу на таблицу (aka_name.csv, title.csv, …). В этих CSV используется семантика PostgreSQL COPY с ESCAPE '\': символ обратной косой черты экранирует кавычку только внутри поля в кавычках, а вне кавычек обратная косая черта считается обычным символом. ClickHouse ожидает CSV в формате RFC 4180 (удвоенные кавычки, без экранирования обратной косой чертой), поэтому файлы сначала нужно перекодировать.

convert_csv.py выполняет это преобразование. Он читает исходный CSV из stdin и записывает стандартный CSV в stdout, удваивая вложенные кавычки и сохраняя пустые поля без кавычек (которые ClickHouse сопоставляет с NULL для столбцов Nullable).

Чтобы создать таблицы из исходных CSV:

• Создайте таблицы (см. выше).
• Скачайте набор данных IMDb в виде файла imdb.tgz, следуя инструкциям из репозитория Join Order Benchmark.
• Преобразуйте и импортируйте данные:

set -euo pipefail

for table in aka_name aka_title cast_info char_name comp_cast_type company_name \
             company_type complete_cast info_type keyword kind_type link_type \
             movie_companies movie_info movie_info_idx movie_keyword movie_link \
             name person_info role_type title; do
    echo "Loading ${table} ..."
    python3 convert_csv.py < "${table}.csv" > "${table}.clean.csv"
    clickhouse client --database job --query "INSERT INTO ${table} FORMAT CSV" < "${table}.clean.csv"
done

После заполнения таблиц их можно экспортировать в Parquet, чтобы ускорить последующий повторный импорт, например: clickhouse client --database job --query "SELECT * FROM title ORDER BY id FORMAT Parquet" > title.parquet.

Подробные размеры таблиц:

(Размеры в сжатом виде в ClickHouse взяты из system.tables.total_bytes и рассчитаны на основе приведённых выше определений таблиц.)

113 JOB-запросов можно найти здесь в репозитории ClickHouse. Настройки, используемые для их запуска, находятся в settings.json. Сведения об известных проблемах и примечания к отдельным запросам см. в README.

В запросах таблицы указаны по именам, поэтому запускайте их в базе данных job (например, с помощью clickhouse client --database job).

Пример запроса (1a):

SELECT MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_title,
       MIN(t.production_year) AS movie_year
FROM company_type AS ct,
     info_type AS it,
     movie_companies AS mc,
     movie_info_idx AS mi_idx,
     title AS t
WHERE ct.kind = 'production companies'
  AND it.info = 'top 250 rank'
  AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
  AND (mc.note LIKE '%(co-production)%'
       OR mc.note LIKE '%(presents)%')
  AND ct.id = mc.company_type_id
  AND t.id = mc.movie_id
  AND t.id = mi_idx.movie_id
  AND mc.movie_id = mi_idx.movie_id
  AND it.id = mi_idx.info_type_id;

ClickHouse отслеживает производительность запросов JOB во всех выпущенных версиях. На странице бенчмарка версий ClickHouse можно посмотреть время выполнения всех 113 запросов JOB и проследить, как менялась производительность с течением времени.