Добрый день, Хабр! 

Меня зовут Олег, я являюсь Backend-разработчиком в IT-компании «Философт» последние полтора года. Мы занимаемся разработкой платформы для жителей, подключённых к нашей системе, которая призвана помочь взаимодействовать с различными «умными» устройствами, коммуницировать с управляющей компанией, оплачивать счета ЖКХ и т.п.

Изначально разработка велась силами подрядчиков, оставивших после себя крайне посредственного качества монолит, который мы с коллегами приводим в приличный вид, постепенно распиливая на модули, а также занимаемся внедрением новых возможностей и интеграций. Одной из областей взаимодействия с пользователями является отображение показаний приборов учёта (электричество, водоснабжение и т.п.) Не так давно эта область кода была подвергнута достаточно масштабному рефакторингу и переработке, в рамках чего и состоялось наше знакомство с БД Clickhouse. 

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

Преамбула

База MariaDB – таблица почасовых показаний приборов учёта за несколько лет размером в 1,5 миллиардов записей и 150 ГБ на накопителе, что как бы достаточно много. В таблице хранятся как автоматически переданные данные, так и внесённые жителями вручную. В связи с этим назрела идея подобрать более оптимальное хранилище без ущерба для производительности.

Изначальные требования выглядели так:

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

• управляющая компания должна иметь возможность получать последние на выбранный момент времени показания счётчиков

Начало пути

Основной идеей стало использование другой БД, которая обеспечила бы более компактное сжатие. Так как у команды имелась некая экспертиза в области Postgres, первым вариантом было использовать именно её. Выгрузили порядка 100 миллионов записей, загрузили, прикинули объём на полном датасете, прикинули потенциальный рост, нашли данный вариант недостаточным.

Вторым шагом в нашем поиске стал отказ от более привычных БД. Немного поизучав альтернативы, решили было попробовать колоночную Clickhouse. Аналогичные шаги (выгрузили, закинули, оценили) показали, что даже с алгоритмами сжатия мы по умолчанию получаем выгоду примерно в 10 раз относительно объёма в MariaDB, тем более что запас по дальнейшему улучшению был весьма неплохим. Опять же, в пользу этого решения сыграл знакомый синтаксис запросов, так что необходимые данные удавалось получить не только быстро, но и легко. 

В результате вся работа с приборами учёта из монолита переехала в отдельный микросервис; была написана необходимая обвязка, и всё вроде было хорошо. Но нет.

Прилет птички

Релиз данного функционала приближается, код переезжает на stage-сервер для окончательного тестирования, в базу заливаются данные со всех счётчиков («приключение» часов на 30-40), размер растёт в рамках ожидаемого. Всё с виду неплохо. Казалось бы, что может пойти не так?

Первый же тест показывает, что не так пошло практически всё – буквально все запросы к базе банально валятся по тайм-ауту. Выдача показаний по одному счётчику за нужное время занимает больше 30 секунд. Соответственно, результата работы каких-то более сложных агрегаций можно было и не ждать. То, что идеально работало на датасете в 100 миллионов записей, наглухо дохло, когда речь шла о миллиарде. Оптимизировать запросы дальше уже некуда. Время искать альтернативы.

Дубли

Напомню: данный функционал и данные не трогала новая команда, и всё это было, по сути, наследством, доставшимся нам от предков. Полезли изучать данные. Оказалось, что в таблице не то что имеются дублирующиеся записи, а их количество составляет чуть не две трети от общего числа. Прореживать старую таблицу и удалять из неё дубли, учитывая объёмы, было занятием неблагодарным; поэтому в данном случае мы сказали большое спасибо разработчикам Clickhouse за движок ReplacingMergeTree.

Для незнакомых с ним – краткая справка: данный движок автоматически удаляет дубликаты записей по ключу сортировки (не путать с PrimaryKey!). Производится, правда, это не при вставке, а впоследствии (приблизительно раз в 4 часа).

Проблема дублей решена, размер ещё меньше; вроде бы, наступило счастье? Нет. Не помогло ни на грош. Единственное, что отсюда вынесено: не стоит верить предшественникам, свои данные следует знать лучше.

Партиции

И вот тут мы долгими окольными путями подошли к ключевому моменту, а именно – грамотному определению партиций. За просвещение хочется сказать отдельное спасибо профильному Telegram-каналу: за отзывчивость коллег, проконсультировавших и подсказавших наилучшее решение.

Итак, в чём же суть: фактически мы полностью пересоздали структуру всех таблиц для оптимального для нас разбиения данных на отдельные партиции (PARTITION BY toYYYYMM(created)). В итоге большинство выборок затрагивали данные только в одной партиции, что значительно ускорило выполнение запросов.

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

We need to go deeper

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

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

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

Какой же из всего этого нам удалось вынести урок? Всегда надо уточнять реальные необходимости и требования.

Эпилог

В совокупности данная история тянулась несколько месяцев, отбирая время и силы команды. При этом большинства подводных камней можно было бы избежать, заранее уточнив требования и более тщательно изучив данные и механизм работы Clickhouse. Да, время на это было потрачено; но благодаря пройденному пути – пусть в процессе и было набито немало шишек – удалось вынести немало бесценного опыта и определить новые точки роста команды. Нам же остаётся надеяться, что данный материал поможет кому-то не наступить на те же грабли, по которым прошлись мы.