Clickhouse Yandex log analyzer. Создаем анализатор логов на базе Clickhouse. Clickhouse vs mysql. Делаем систему аналитики

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

Статистика сайта на базе 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 синтаксисом построения запрос для анализа. Вариантов запросов может быть целая куча.
Посмотрим динамику ошибок на сервере

Посмотрим динамику ошибок на сервере

Найдем самые медленные ответы с учетом IP адреса клиента
Посмотрим динамику ошибок на сервере

Определим топ 10 ip медленных клиентов
IP c медленным интернетом

Вам может понравиться