Для многих коммерческий проектов встает вопрос сбора статистики для внутренних нужд. Чем больше показателей мы собираем, тем больше проблем возникает в дальнейшем.

Статистика сайта на базе mysql

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

Через полгода работы проекта мы уперлись в производительность SATA диска и заменили его на ssd. Еще через год индексы перестали помещаться в 32Gb ОЗУ. Следующий шагом для рост был новый сервер с 64Gb ОЗУ и 2x512 SSD. При росте объема накопленных данных встала проблема расширения собираемых данных. Те кто работает с mysql и таблицами больше 5G знают насколько затратной становится операция добавление нового поля. Для обеспечения приемлемой скорости выборки данных для отчета мы добавляем индексы - а это вызывает увеличение занимаемого дискового пространства.

Так 600_000_000 записей в год у нас занимают около 500Gb на ssd.

При росте данных требуется увеличение производительности железа. (скорость диска, размер ОЗУ).
Расширение функционала по сбору данных затруднена особенностями работы mysql c большими таблицами. Сейчас добавление новой колонки в основную таблицу занимает несколько суток

ClickHouse для статистики

Летом 2016 года Яндкес открыл в OpenSource базу данных под названием ClickHouse. База данных используется в системе сборки статистики metrika.yandex.ru

Доклад о ClickHouse можно посмотреть в записи одной из встреч проводимых яндексом начало на 2h36m23s

При разработке системы яндекс решил проблемы озвученные в первой половине статьи. И если вы еще используете для хранения mysql или postgresql, то вы можете существенно ускорить отзывчивость системы отчетов без вложений в железо

ClickHouse - это колоночая база данных, что позволяет без труда расширять количество собираемых данных. При хранение данных используется сжатие данных. Основная таблица статистики в mysql занимает ~500Gb. После экспорта данных в Clickhouse данные заняли ~30Gb

SQL запросы применяемые в системе на базе mysql практически без изменений запустились на ClickHouse и показали увеличение производительности от 10-100 раз.

Практическая часть для тестирования

Быстрый способ опробовать работу clickhouse это загрузить access log и сделать разнообразные выборки. Для сравнения можно загрузить логи в mysql

Формат nginx лога
Создадим структуру таблицы для экспорта логов в базе clickhouse

Для экспорта логов мы воспользуемся сжатыми логами за неделю. Воспользуемся небольшим Perl скриптом читающем данные из лога и отправляющего в clickhouse

Установим небольшую библиотеку для обработки времени в perl

apt-get install -y 	libdatetime-perl

perl load_access_log.pl access.log.1.gz $(hostname)

#экспорт нескольких логов
for i in {1..7}; do perl load_access_log.pl access.log.${i}.gz $(hostname)& done

После экспорта в нашей базе логи сервера за последние 7 дней. Мы получили инструмент с SQL синтаксисом построения запрос для анализа. Вариантов запросов может быть целая куча.
Посмотрим динамику ошибок на сервере