Делаем быстрый поиск по турам на основе ClickHouse26.03.2017 22:47

В чем сложность поиска по турам

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

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

Откуда брать данные

Набор полей

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

• tour_id — uint64 — уникальный ID предложения
• package_id — uint64 — ID соответствующего пакета туров (пакеты добавляются и отзываются целиком)
• date_start — uint8 — дата прилета — число дней с определенного момента
• stars — uint8 — количество звезд у отеля + специальные значения, например для апартаментов
• pansion — uint8 — тип питания (Bed & Breakfast, All Inclusive, etc)
• nights — uint8 — продолжительность пребывания (количество ночей)
• region_id — uint16 — ID региона отеля
• hotel_id — uint32 — ID отеля
• airport_id — uint8 — ID аэропорта прилета
• price — uint16 — цена предложения, в долларах

Код для генерации пишется за 10 минут любым программистом, я приведу пример на go:

package main

import (
	"encoding/csv"
	"flag"
	"math/rand"
	"os"
	"strconv"
)

var n = flag.Int("n", 10000000, "How many entries to generate")

func main() {
	wr := csv.NewWriter(os.Stdout)
	defer wr.Flush()

	wr.Write([]string{
		"tour_id",
		"package_id",
		"date_start",
		"stars",
		"pansion",
		"nights",
		"region_id",
		"hotel_id",
		"airport_id",
		"price",
	})

	for i := 0; i < *n; i++ {
		wr.Write([]string{
			strconv.FormatInt(int64(i), 10),
			strconv.FormatInt(rand.Int63(), 10),
			strconv.FormatInt(rand.Int63n(90), 10),
			strconv.FormatInt(rand.Int63n(10), 10),
			strconv.FormatInt(rand.Int63n(10), 10),
			strconv.FormatInt(rand.Int63n(30), 10),
			strconv.FormatInt(rand.Int63n(1000), 10),
			strconv.FormatInt(rand.Int63n(1000000), 10),
			strconv.FormatInt(rand.Int63n(10), 10),
			strconv.FormatInt(rand.Int63n(10000), 10),
		})
	}
}

Критерии поиска

Реализация на MySQL

Попробуем использовать 3 немного разных подхода: простой InnoDB, InnoDB + primary key (date_start, id) и MyISAM. Структура таблицы будет одинаковой для всех 3 подходов и отличия будут только в движке и в том, какие индексы мы накладываем:

CREATE TABLE `tours` (
  `tour_id` bigint(20) NOT NULL,
  `package_id` bigint(20) NOT NULL,
  `date_start` tinyint(4) NOT NULL,
  `stars` tinyint(4) NOT NULL,
  `pansion` tinyint(4) NOT NULL,
  `nights` tinyint(4) NOT NULL,
  `region_id` smallint(6) NOT NULL,
  `hotel_id` int(11) NOT NULL,
  `airport_id` tinyint(4) NOT NULL,
  `price` smallint(6) NOT NULL
)

InnoDB

Вставим наши данные в дефолтную инсталляцию MySQL 5.7.17 (innodb_buffer_pool_size=128 Мб, остальные настройки см. под спойлером):

LOAD DATA LOCAL INFILE 'tours.csv' INTO TABLE tours FIELDS TERMINATED BY ','

Query OK, 10000000 rows affected, 11 warnings (1 min 35.09 sec)
Records: 10000001  Deleted: 0  Skipped: 1  Warnings: 11

Получившийся размер таблицы — 490 Мб данных и 162 Мб индексов. В процессе выполнения запроса MySQL записал на диск 3,5 Гб и загрузка CPU была в районе 100% (утилизировано 1 ядро из 4 на тестовом ноутбуке).

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

InnoDB + PRIMARY KEY (date_start, tour_id)

Давайте посмотрим, как обстоят дела со вставкой:

Query OK, 10000001 rows affected, 10 warnings (1 min 13.34 sec)
Records: 10000001  Deleted: 0  Skipped: 0  Warnings: 10

Размер таблицы — 538 Мб данных и 0 Мб индекса (вторичных индексов нет, а PRIMARY KEY записывается является частью данных в InnoDB)

MyISAM

Query OK, 10000000 rows affected, 11 warnings (40.39 sec)
Records: 10000001  Deleted: 0  Skipped: 1 Warnings: 11

ClickHouse

Сначала создадим таблицу:

CREATE TABLE tours
(
    tour_id UInt64, 
    package_id UInt64, 
    date_start UInt8, 
    stars UInt8, 
    pansion UInt8, 
    nights UInt8, 
    region_id UInt16, 
    hotel_id UInt32, 
    airport_id UInt8, 
    price UInt16, 
    date Date MATERIALIZED toDate(date_start)
) ENGINE = MergeTree(date, date_start, 8192)

ClickHouse, вставка данных

$ cat tours.csv | pv | clickhouse --client --query 'INSERT INTO tours FORMAT CSVWithNames'
524MiB 0:00:04 [ 123MiB/s]

Можно видеть, что вставка csv-файла произошла за 4 секунды со скоростью вставки порядка 100 Мб/сек (как и заявлено авторами). При вставке сервер потратил 7 секунд процессорного времени.

Размер данных составляет 378 Мб, и на диск было записано тоже 378 Мб (по всей видимости, весь объем был отсортирован в памяти и записан одним куском).

К сожалению, поскольку наши тестовые данные случайны, у нас не получилось извлечь значимую пользу от компрессии столбцов. Реальные данные должны сжиматься лучше, поэтому итоговый размер должен быть ещё меньше.

Времена вставки данных, сравнение

Выборка

-- 1. «Горящие туры»
SELECT * FROM tours ORDER BY price ASC LIMIT 20;

-- 2. «Горящие туры» в интервале 10 дней (1/9 всех записей)
SELECT * FROM tours WHERE date_start BETWEEN 40 AND 50 ORDER BY price ASC LIMIT 20;

-- 3. 5 ночей с ценой от 500 до 600 долларов
SELECT * FROM tours WHERE nights = 5 AND price BETWEEN 500 AND 600 ORDER BY price ASC LIMIT 20;

-- 4. цена от 500 до 600 долларов, максимальная продолжительность и число звезд
SELECT * FROM tours WHERE price BETWEEN 500 AND 600 ORDER BY nights DESC, stars DESC LIMIT 20;

-- 5. конкретный отель в определенные даты
SELECT * FROM tours WHERE date_start BETWEEN 10 AND 20 AND hotel_id = 767036 ORDER BY price LIMIT 20;

Итак, результаты:

Анализ

Поскольку ClickHouse является колоночной базой, запрос вида SELECT * для него является не очень удобным — это можно видеть по времени исполнения запросов. Если есть возможность, нужно выбирать только те колонки, которые нужны (мета-информация для tour_id в любом случае должна дублироваться ещё где-то в надежном хранилище, и можно оттуда её доставать вместо выборки напрямую из ClickHouse). Также, ClickHouse автоматически распараллеливает исполнение запросов на все доступные ядра, а также сканирует только нужные колонки, поэтому работает очень быстро при прогретом кеше. В случае холодного старта особенно заметно, почему предпочительней выбирать не все колонки, а только нужные.

Однако, даже при холодном старте, ClickHouse лидирует по времени исполнения запросов во всех случаях без исключения.

Заключение

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

P.S. Если вы можете затюнить MySQL и ClickHouse, чтобы результаты были лучше и опубликуете свои бенчмарки, то пишите обязательно в комментариях, это будет полезным дополнением к статье, поскольку мои цифры лишь примерны, и правильные настройки могут изменить результаты в несколько раз для некоторых видов запросов.

Ссылки

1. ClickHouse
2. Генератор данных для туров
3. Собранные бинарники ClickHouse под macOS

Комментарии (12)

• oxidmod

  25 марта 2017 в 17:28

  –2

  ↑

  ↓

  Но ведь никто в здравом уме не будет для такого поиска использовать мускуль. есть же Еластик

• RFL

  25 марта 2017 в 19:08

  –1

  ↑

  ↓

  Спасибо за Ваш труд, интересная информация по тестам! Только лучше было бы все же предварительно настроить MySQL, это было бы больше похоже на реальные испытания.

• wildraid

  25 марта 2017 в 22:49

  0

  ↑

  ↓

  Ради интереса, сгенерировал tours.csv и положил в Exasol (1 node, бесплатный который).
  Размер в сжатом виде получился 180.06 Мб.

  По времени выполнения, все запросы идут 0.2 — 0.5 секунд, из которых львиная доля уходит на EXECUTE \ COMPILE. Просто на то, чтобы взлететь и понять, что нужно сделать. Данных совсем мало чтобы получить заметную разницу.

  Вот бы взять ~1Тб туров, колоночек сделать штук пятьдесят и запрос чуть посложнее. Например, «топ 50 туров, которые искали люди, похожие на вас».

  • Suncheez

    26 марта 2017 в 00:33

    0

    ↑

    ↓

    А давайте посчитаем косты на Exasol и ClickHouse для работы с терабайтной базой туров?

    Из своего опыта могу сказать, что 250 отелей*60 размещений*18 вариантов срока пребывания*200 доп опций*сезон в 7 месяцев = примерно 30ГБ в БД Мастер-тур.
    Вся Италия вместе с Сардинией будет как раз около ТБ.
    На рынке есть моно-операторы, работающие только в одном направлении, но их мало уже осталось. Большинство крупных операторов работают минимум на всю Европу, но (!) у себя держат не полные данные, а примерочные, а полные данные дают по запросу клиента, запрашивая из внешних баз.

    Вот здесь нам рассказывают, что для более-менее приемлемой аналитической работы с базой в 85ТБ нужно 8 20-головых нод с 5,6ТБ суммарной оперативки и 64ТБ хранилищем.

    Самая крупная база, с которой имел дело лично я, весила 3,5ТБ. Это объём данных оператора в средне-тяжёлом весе за полный год. Обычно, хранят «под рукой» данные за два-три года.

    Если предположить, что для работы с такой базой нужна будет одна нода в Exasol из примера Baidoo, против обычного 2-голового ксеона с 256ГБ оперативки в топе под Click-House, то выбор становится очевидным, правда?

    • wildraid

      26 марта 2017 в 00:50

      +1

      ↑

      ↓

      Именно на терабайтную базу косты одинаковые будут. 1Тб сырых данных как раз примерно ужмутся в 200Гб сжатых и целиком влезут в память бесплатной версии. И как раз это всё заведётся на машинке с 256Гб памяти.

      Тут вопрос немного в другом. При росте объёма данных те проблемы и острые углы, которые есть у любого продукта, начинают проявляться намного ярче. По шести сотням мегабайт и fullscan можно сделать, сохранив адекватное время ответа. А вот хотя бы на пятистах гигах ClickHouse должен оторваться от MySQL уже на много порядков. Особенно на запросе чуть посложнее WHERE + ORDER BY.

• vbarinov

  26 марта 2017 в 10:12

  +1

  ↑

  ↓

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

  
  

  Потом у самого кликхауса на сайте есть бенчмарки против MySQL, и там прекрасно видно их сравнение.

  
  

  Было бы гораздо интереснее посмотреть сравнение против Elastic Search, Splunk, Druid…

• hatarist

  26 марта 2017 в 18:53

  0

  ↑

  ↓

  че-т не очень искали :)

  https://github.com/excitoon/ClickHouse/releases — собранные бинарники
  https://github.com/excitoon/homebrew-clickhouse — репо для хомбрю с клиентом
  https://github.com/hatarist/homebrew-clickhouse — репо для хомбрю с сервер+клиентом

  • youROCK

    26 марта 2017 в 18:58

    0

    ↑

    ↓

    Чо-то как-то так себе сборка, я вам скажу:

    > ~/Downloads/clickhouse-client
    dyld: Symbol not found: __ZSt14__once_functor
      Referenced from: /Users/yuriy/Downloads/clickhouse-client (which was built for Mac OS X 10.12)
      Expected in: /usr/local/opt/gcc/lib/gcc/6/libstdc++.6.dylib
     in /Users/yuriy/Downloads/clickhouse-client
    fish: '~/Downloads/clickhouse-client' terminated by signal SIGABRT (Abort)
    

    • hatarist

      26 марта 2017 в 19:01

      0

      ↑

      ↓

      Пардон, я со свежезареганного аккаунта, поэтому править комменты не могу. Исправленный:

      https://github.com/excitoon/ClickHouse/releases — собранные бинарники (уже старые)
      https://github.com/excitoon/homebrew-clickhouse — репо для хомбрю с клиентом (тоже уже старое)
      https://github.com/hatarist/homebrew-clickhouse — репо для хомбрю с сервер+клиентом (стараюсь обновлять, но может быть лениво)

      в последнем случае всячески приветствуются:
       — автоматизация тестов и обновления пакета (как brewbot’ы делают для bottle’ов),
       — исправления для того, чтобы пройти в homebrew-core репу. дело было давно, но отклонили. см. https://github.com/Homebrew/homebrew-core/pull/7222
       — банальные обновления ссылки/хеша/тестирование того, что пакет собирается, с последующими пуллреквестами

      ссылка на идею сделать официальный homebrew-репозиторий на стороне яндекса: https://github.com/yandex/ClickHouse/issues/235

      Тоже сначала плевался от докера, оверхед от virtualbox/xhyve по I/O очень большой, что критично при тесте кликхауса локально, в макоси. Но на время тестов, да и пока нет нормальной линуксовой машины, докер вполне можно потерпеть. ¯\_(ツ)_/¯
      

    • hatarist

      26 марта 2017 в 19:05

      0

      ↑

      ↓

      , а насчет excitoon’овских ничего не могу сказать, он действительно собирал для 10.12, и официально сборка для макоси поддерживается с сиерры (раз, два), но я сидел на El Capitan, и с ним сначала все было ОК, потом сильно сломалось, а теперь снова все ОК, если динамически библиотеки линковать.
      Впрочем, может там опять что-то изменилось, я перестал следить :(

      • youROCK

        26 марта 2017 в 19:10

        0

        ↑

        ↓

        Так я собственно для macOS Sierra и собрал. И недостающие либы тоже выложил.

• DOLARiON

  26 марта 2017 в 19:04

  0

  ↑

  ↓

  Может еще Tarantool? Насколько знаю, он уже умеет держать в оперативке не все данные.