Маркетолог прислал мне скрин дашборда и одно слово: «пусто».
На скрине — панель «Количество пришедших по реферальной ссылке за период». Ноль. Под ней — график регистраций. Тоже ноль. Ссылка, по которой он пришёл, вела на дашборд с фильтром по конкретному slug — тому самому, который он неделю крутил в закупке.
Я открыл ту же ссылку, поменял в урле from=now-6h на from=now-30d и увидел 16 регистраций.
Дефолтный интервал в Grafana — 6 часов. У бота ночью в этой гео никого нет. Аналитика работала идеально и показывала абсолютно честный ноль.
Это самая безобидная из историй, которые тут будут. Дальше — про то, как мы строили продуктовую аналитику для Telegram-бота с AI-персонажами на ~300K MAU, ~75 RPS в пике и ~1.5M событий в день: без ClickHouse, без Amplitude, без единой event-таблицы. Двенадцать дашбордов в Grafana поверх боевой MySQL. Три из них какое-то время врали, и это выяснилось не сразу.
Главный парадокс всего проекта помещается в одну строку: через систему проходит полтора миллиона событий в день, и ни одно из них не записано как событие. Есть только строки в продуктовых таблицах и их created_at. Всё, что вы прочитаете ниже, — следствие этого факта.
Статья — не туториал «подключите MySQL к Grafana за 10 шагов». Это разбор того, что происходит, когда продуктовые метрики приходится доставать из схемы, которую проектировали под продукт, а не под аналитику, — и когда по этим метрикам прямо сейчас решают, лить трафик дальше или нет.
Дисклеймер: проект под NDA, поэтому названия, точные объёмы и часть деталей изменены. Порядок величин, схема и сами проблемы — настоящие.
Что за система
Telegram-бот с AI-персонажами. Пользователь открывает ссылку, попадает в бота, выбирает персонажа, переписывается. Часть переписки — текст, часть — генерация картинок. Есть платежи, есть реферальные ссылки под закупку трафика, есть локали (бот многоязычный).
Порядок величин, чтобы дальше было с чем соотносить цифры:
-
~300K MAU, из них заметная дневная активность;
-
~75 RPS в пике — это живой OLTP, по которому мы теперь ещё и гоняем тяжёлые
GROUP BY; -
~1.5M событий в день — сообщения, коллбэки, генерации, платежи. Всё это ложится строками в продуктовые таблицы и нигде не агрегируется.
Стек до аналитики:
Telegram ──► Laravel-бэкенд ──► MySQL 8 │ ├──► очередь (jobs / failed_jobs) │ └──► воркеры генерации: text / image │ └──► внешние LLM- и image-провайдеры
Схема — обычная продуктовая. Никакого event sourcing, никакой таблицы events, никаких user_id в привычном смысле. То, что есть:
|
Таблица |
Что это на самом деле |
|---|---|
|
|
диалог человека с ботом. Ближайшее, что есть к «пользователю» |
|
|
диалог с конкретным персонажем внутри одного |
|
|
сообщения внутри |
|
|
сырые сообщения из Telegram, привязаны к |
|
|
персонажи и версии их промптов, |
|
|
реферальные ссылки, поле |
|
|
платежи, |
|
|
состояния генераций, |
|
|
стандартная очередь Laravel |
Всё. Из этого нужно было получить воронку, retention, метрики по каналам, топ персонажей и техничку — на объёме, который растёт на полтора миллиона строк в сутки.
Почему Grafana поверх прода, а не нормальный аналитический контур
Короткий ответ: потому что нормальный контур стоит месяц работы и денег на инфраструктуру, а метрики были нужны позавчера — трафик уже закупался, и решение «лить дальше или нет» принималось на глаз.
Длинный ответ — список того, что мы сознательно приняли как долг:
-
Читаем боевую базу. Отдельной реплики под аналитику сначала не было. Все запросы ниже — с прода, который в этот момент держит 75 RPS живой нагрузки.
-
Нет слоя агрегатов. Каждое открытие дашборда — это полный пересчёт по
messages, а она прибавляет по миллиону с лишним строк в день. -
Нет событий. Всё, что мы можем измерить, — это состояния строк и их
created_at. -
Нет истории изменений. Если строку обновили, прошлое значение мы уже не узнаем.
Первые два пункта решаются деньгами и временем. Третий и четвёртый — нет, и именно из-за них дальше начинается интересное.
Grafana тут выиграла у Metabase/Redash по одной причине: она уже стояла для инфраструктурных метрик, у неё есть переменные дашборда ($ref_id, $character) и макросы времени. Продуктовые дашборды просто приехали в ту же инсталляцию.
Проблема 1: в базе нет пользователей
Первый же вопрос — «сколько у нас пользователей» — не имеет ответа в схеме. Есть telegram_chats, есть chats. Разница между ними — множитель.
Один человек, который поговорил с пятью персонажами, — это:
-
1 строка в
telegram_chats -
5 строк в
chats -
N строк в
messages
Если считать «пользователей» по chats, вы завышаете аудиторию ровно во столько раз, сколько персонажей в среднем пробует человек. У нас это было далеко не 1.0 — то есть на 300K реальных людей приходилось ощутимо больше «пользователей», если считать наивно.
Поэтому во всех продуктовых панелях «пользователь» — это строго telegram_chats.id, и считается он только так:
SELECT COUNT(DISTINCT tc.id) AS `Пользователи`FROM messages mJOIN chats ch ON ch.id = m.chat_idJOIN telegram_chats tc ON tc.id = ch.telegram_chat_idJOIN characters c ON c.id = ch.character_idWHERE m.role = 'user' AND $__timeFilter(m.created_at);
COUNT(DISTINCT tc.id), а не COUNT(*) и не COUNT(ch.id). Выглядит очевидно ровно до того момента, пока не появится вторая панель, где кто-то (я) написал иначе.
Правило, которое стоило дороже всего: определение сущности «пользователь» должно быть записано словами до того, как написан первый SQL. У нас оно появилось после третьего расхождения в цифрах.
Проблема 2: две таблицы сообщений и два разных топа персонажей
В базе две ветки сообщений:
-
telegram_messages— сырьё из Telegram. Знаетtelegram_chat_id, не знает персонажа. -
messages— сообщения внутри диалога с персонажем. Знаютchat_id,role, черезchatsзнаютcharacter_id.
Панель «Топ персонажей» была написана по первой:
WITH base AS ( SELECT DISTINCT m.telegram_chat_id, ch.character_id FROM telegram_messages m JOIN chats ch ON ch.telegram_chat_id = m.telegram_chat_id WHERE $__timeFilter(m.created_at))SELECT c.code, COUNT(base.telegram_chat_id) AS "Количество пользователей"FROM base JOIN characters c ON c.id = base.character_idGROUP BY c.codeORDER BY 2 DESC;
Найдите ошибку. Она в JOIN.
telegram_messages не знает, какому персонажу адресовано сообщение. Джойн идёт по telegram_chat_id — то есть каждое сырое сообщение пользователя склеивается со всеми его диалогами. DISTINCT спасает от размножения строк, но не от смысла: если человек написал персонажу A, а с персонажем B у него просто открыт пустой диалог, — B получает этого человека в свой топ.
Персонажи, которых показывали первыми в списке выбора, собирали пустые диалоги и уезжали наверх в топе. Топ мерил не популярность, а позицию в UI.
Правильная версия джойнит через messages и фильтрует роль:
FROM messages mJOIN chats ch ON ch.id = m.chat_id -- ключ диалога, не пользователяWHERE m.role = 'user' -- считаем людей, а не бота
Две панели в двух дашбордах отвечали на один вопрос и давали разные ответы. Заметили это, когда пришёл вопрос, почему персонаж из топа-3 не приносит ни одного платежа.
Вывод: если в схеме две таблицы про одно и то же — выберите каноническую для аналитики и запретите себе вторую. У нас канон — messages, telegram_messages осталась только там, где нужны сырые тайминги (о них ниже).
Проблема 3: воронка без событий
Классическая воронка требует событий: bot_opened, character_selected, first_message_sent. Событий нет — хотя их полтора миллиона в день. Есть только строки и их created_at.
Пришлось собирать воронку из косвенных признаков существования:
-- Этап 1: новые (появилась строка в telegram_chats)SELECT DATE(tc.created_at) AS time, COUNT(*) AS new_usersFROM telegram_chats tcWHERE $__timeFilter(tc.created_at)GROUP BY DATE(tc.created_at);-- Этап 2: активированные (появился хотя бы один диалог с персонажем)SELECT DATE(tc.created_at) AS time, COUNT(DISTINCT tc.id) AS activated_usersFROM telegram_chats tcJOIN chats ch ON ch.telegram_chat_id = tc.idWHERE $__timeFilter(tc.created_at) AND $__timeFilter(ch.created_at)GROUP BY DATE(tc.created_at);-- Этап 3: стартовавшиеSELECT DATE(tc.created_at) AS time, COUNT(DISTINCT tc.id) AS started_usersFROM telegram_chats tcJOIN chats ch ON ch.telegram_chat_id = tc.idJOIN messages m ON m.chat_id = ch.idWHERE $__timeFilter(tc.created_at) AND m.role = 'user' AND m.text LIKE '/start%'GROUP BY DATE(tc.created_at);
Да, третий этап — это LIKE '/start%' по тексту сообщения. Скан по тексту вместо поля event_type. Мне не нравится, вам не нравится, но другого следа от команды /start в базе не осталось.
Два неприятных свойства такой воронки:
-
Этапы считаются по дате регистрации, а не по дате события.
$__timeFilter(tc.created_at)во всех трёх запросах. Человек, зарегистрировавшийся 1-го и написавший 3-го, попадёт в оба этапа на 1-е число. Для когорты это правильно, для «что было вчера» — нет. Мы выбрали когорту, и это надо помнить, читая график. -
Порядок этапов — предположение. Мы не знаем, что
chatsсоздаётся послеtelegram_chatsпо бизнес-логике, — мы это просто знаем из кода. Схема этого не гарантирует.
Если бы у меня был один голос за одно изменение в бэкенде — я бы потратил его на таблицу events(telegram_chat_id, type, payload, created_at). Всё остальное в этой статье — следствие её отсутствия.
Проблема 4: главная метрика оказалась не воронкой, а кривой дожития
Самая полезная панель во всём проекте называется «Вовлечённость» и выглядит как таблица:
|
Этап (X) |
Пользователи (остались) |
Доля (%) |
|---|---|---|
|
0 сообщение(й) и более |
‹все› |
100.0 |
|
1 сообщение(й) и более |
… |
… |
|
2 сообщение(й) и более |
… |
… |
|
5 сообщение(й) и более |
… |
… |
Это не воронка по этапам продукта. Это кумулятивное распределение: сколько людей дошли до N-го сообщения. Считается так:
WITH msg_counts AS ( SELECT ch.telegram_chat_id AS chat_id, COUNT(*) AS msg_cnt FROM messages m JOIN chats ch ON ch.id = m.chat_id WHERE m.role = 'user' AND $__timeFilter(m.created_at) GROUP BY ch.telegram_chat_id),eligible_chats AS ( SELECT DISTINCT tc.id AS chat_id FROM telegram_chats tc WHERE $__timeFilter(tc.created_at)),msg_distribution AS ( SELECT COALESCE(mc.msg_cnt, 0) AS msg_cnt, COUNT(*) AS users_count FROM eligible_chats ec LEFT JOIN msg_counts mc ON mc.chat_id = ec.chat_id -- нули не теряем GROUP BY COALESCE(mc.msg_cnt, 0)),cumulative AS ( SELECT md.msg_cnt, md.users_count, SUM(md.users_count) OVER (ORDER BY md.msg_cnt) AS cum_users, SUM(md.users_count) OVER () AS total_users FROM msg_distribution md)SELECT CONCAT(msg_cnt, ' сообщение(й) и более') AS "Этап (X)", (total_users - cum_users) + users_count AS "Пользователи (остались)", ROUND(100 * ((total_users - cum_users) + users_count) / total_users, 1) AS "Доля (%)"FROM cumulativeORDER BY msg_cnt;
Ключевая деталь — LEFT JOIN в msg_distribution. Без него из знаменателя пропадают все, кто не написал ни одного сообщения, — то есть ровно те, чей отвал вы и пытаетесь измерить. Метрика становится красивой и бесполезной: конверсия «из написавших в написавших ещё раз».
Именно эта таблица дала самый прикладной результат всего проекта: обрыв стоит не там, где его искали. Основная масса отваливается не на десятом сообщении (усталость от продукта), а между первым и вторым (не понял, что происходит). Это меняет приоритет: не «улучшать персонажей», а «чинить онбординг». На аудитории в 300K это разница не косметическая — это разные статьи бюджета.
Отдельная история — как это писалось. Первая версия считала кумулятиву коррелированным подзапросом:
(SELECT SUM(d2.users_count) FROM msg_distribution d2 WHERE d2.msg_cnt <= d1.msg_cnt) AS cum_users
Работает, но это квадрат по числу различных длин чата — а их со временем становится больше. MySQL 8 умеет оконные функции, и SUM(...) OVER (ORDER BY msg_cnt) делает то же самое за один проход. В репозитории до сих пор живут обе версии в разных дашбордах — идеальная иллюстрация того, как копипаста расползается быстрее, чем правки.
Проблема 5: сессии, которых нет в базе
«Сколько раз в день человек возвращается в бота» — вопрос, на который в схеме нет ни одного поля. Сессий никто не пишет.
Зато есть тайминги сообщений, и работает старый приём из веб-аналитики: новая сессия начинается там, где разрыв между соседними сообщениями больше 30 минут.
SELECT ROUND(AVG(sess_cnt), 2) AS valueFROM ( SELECT chat_id, SUM(is_new_session) AS sess_cnt FROM ( SELECT m.telegram_chat_id AS chat_id, CASE WHEN LAG(m.created_at) OVER (PARTITION BY m.telegram_chat_id ORDER BY m.created_at) IS NULL OR TIMESTAMPDIFF( MINUTE, LAG(m.created_at) OVER (PARTITION BY m.telegram_chat_id ORDER BY m.created_at), m.created_at ) > 30 THEN 1 ELSE 0 END AS is_new_session FROM telegram_messages m JOIN telegram_chats tc ON tc.id = m.telegram_chat_id WHERE $__timeFilter(m.created_at) ) t GROUP BY chat_id) s;
LAG(...) IS NULL в первом условии — это не защита от NULL, это и есть первая сессия: у самого первого сообщения предыдущего нет, значит оно открывает сессию.
Здесь мы сознательно взяли telegram_messages, а не messages: сессия — это про присутствие человека в боте вообще, а не про конкретного персонажа. Переключение между персонажами внутри получаса не должно порождать вторую сессию.
Порог в 30 минут — не истина, а соглашение, унаследованное из GA. Для бота с AI-персонажами он, скорее всего, неправильный: диалоговая сессия тут длиннее веб-сессии. Правильный порог достаётся из гистограммы межсообщенческих интервалов — по «локтю» распределения. Мы этого не сделали и честно живём с числом, которое сравнимо само с собой во времени, но не имеет абсолютного смысла.
Проблема 6: мультиселект в Grafana + MySQL = REGEXP и дыра
Дашборд по рефералам должен уметь фильтроваться по одной ссылке, по нескольким или по всем сразу. Переменная:
SELECT slug FROM telegram_links ORDER BY slug;
Дальше начинается специфика. У Grafana для SQL-источников нет честного биндинга списка в IN (...) — переменная разворачивается текстовой подстановкой до отправки запроса. Общепринятый костыль выглядит так:
WHERE tc.telegram_link_id IS NOT NULL AND $__timeFilter(tc.created_at) AND ( '${ref_id:raw}' = '' -- ничего не выбрано → без фильтра OR l.slug REGEXP CONCAT('^(', '${ref_id:pipe}', ')$') -- выбрано → em0311|abc123|... );
Форматтер :pipe превращает мультиселект в a|b|c, а ^(...)$ не даёт REGEXP матчить подстроки: без якорей slug em03 поймал бы em0311, и отчёт по маленькому каналу тихо включил бы в себя большой.
Это работает и решает задачу. Но признаем прямо, что тут происходит: значение переменной подставляется в текст SQL как есть. Это конкатенация строк в запрос — то самое, за что бьют по рукам в любом другом месте кодовой базы.
Здесь это терпимо ровно потому, что:
-
значения переменной не вводятся руками, а приходят из
SELECT slug FROM telegram_links; -
дашборд закрыт и доступен только своим;
-
у datasource-пользователя Grafana права только на
SELECT.
Убери любое из трёх — и получишь SQL-инъекцию через URL дашборда, потому что var-ref_id= живёт в адресной строке и подставляется в запрос. Проверьте прямо сейчас, что ваш аналитический пользователь в MySQL — read-only. Это тридцать секунд и один GRANT.
Проблема 7: $__timeFilter по двум таблицам через OR
В части панелей нужно было брать «пользователей, которые либо зарегистрировались в периоде, либо были активны в периоде». Написалось это буквально:
eligible_chats AS ( SELECT DISTINCT tc.id AS chat_id FROM telegram_chats tc LEFT JOIN chats ch ON ch.telegram_chat_id = tc.id LEFT JOIN messages m ON m.chat_id = ch.id WHERE $__timeFilter(tc.created_at) OR $__timeFilter(m.created_at))
Две проблемы, и обе не про SQL как язык.
Производительность. OR между условиями на разные таблицы через LEFT JOIN не даёт оптимизатору использовать индекс ни по одной из них: нельзя сузить telegram_chats по дате, потому что строка может пройти по второй ветке условия. На проде это разворачивается в полный проход по messages — самой большой таблице в базе, которая растёт на миллион с лишним строк в сутки. Дашборд с большим интервалом открывался долго и держал соединение к базе, у которой в этот момент 75 RPS боевой нагрузки. Один открытый на месяц дашборд — и OLTP чувствует. Лечится разделением на UNION двух дешёвых запросов, каждый со своим индексом.
Смысл. Знаменатель метрики теперь — «новые ИЛИ активные», и это разные когорты, склеенные в одну. Retention по такой когорте нельзя сравнить с retention по когорте «только новые», а на соседней панели — как раз она. Две таблицы рядом, обе называются «вовлечённость», знаменатели разные.
Оба варианта — WHERE $__timeFilter(tc.created_at) и WHERE ... OR ... — живут в репозитории до сих пор. Разница между ними нигде не подписана, и это самая настоящая мина: цифры расходятся, обе панели правы.
Проблема 8: час пик, которого не было
Панель «Временные паттерны активности» показывает почасовую активность. Бакеты в ней считаются руками:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(m.created_at) - (UNIX_TIMESTAMP(m.created_at) % 3600)) AS time, COUNT(DISTINCT ch.telegram_chat_id) AS valueFROM messages m ...GROUP BY time
Приём рабочий: округление unix-времени вниз до часа. Но он обходит стороной $__timeGroupAlias(...), а вместе с ним — и всю работу Grafana с таймзонами. Бакет получается в таймзоне сервера MySQL, а дашборд открыт с timezone=browser.
Для одного часового пояса это косметика. Для бота, у которого локали размазаны от Европы до Азии, — это метрика, которая отвечает на вопрос «когда активны наши пользователи» временем моего сервера. «Пик в 23:00» не значит ничего, пока не сказано, чьи это 23:00.
Правильно — либо честный $__timeGroupAlias(m.created_at, 1h) и один явный часовой пояс на весь дашборд, либо (что для «когда людям удобно писать боту» куда полезнее) считать час в локальном времени пользователя, раз уж locale в telegram_chats есть.
Мы пока живём с первым вариантом и подписью на панели. Второй — в бэклоге и, судя по всему, там и останется.
Проблема 9: реферальная атрибуция, которой хватает ровно на один вопрос
Реферальные ссылки — это telegram_links.slug и счётчик visited, который инкрементит приложение. Плюс telegram_chats.telegram_link_id, который проставляется при создании чата.
Отсюда получается ровно одна честная панель:
SELECT l.slug AS ref_slug, l.visited AS visits, -- клики, счётчик приложения COUNT(DISTINCT tc.id) AS chats_count -- реально дошли до ботаFROM telegram_links lLEFT JOIN telegram_chats tc ON tc.telegram_link_id = l.idGROUP BY l.id, l.slugORDER BY l.visited DESC;
Разрыв между visits и chats_count — это и есть верхняя часть воронки канала, и по нему каналы отлично сортируются: одинаковый объём кликов при разной конверсии в старт видно сразу.
Дальше — стена, и стена архитектурная:
-
visited— счётчик, а не события. У него нет истории. Нельзя посмотреть клики за прошлый вторник, нельзя построить их динамику, нельзя нормально сравнить с регистрациями в том же интервале.$__timeFilterк нему неприменим в принципе: у числа нет даты. На панели «Визиты по ссылке» его и нет — это единственная панель в проекте, которая игнорирует таймпикер, и в этом её главная ловушка для читателя. -
Атрибуция только first touch, и только навсегда.
telegram_link_idпишется один раз при созданииtelegram_chats. Человек, пришедший по ссылке A, потом по B, потом купивший, — до конца жизни числится за A. Ни last touch, ни multi-touch на этих данных не построить, и никакой SQL этого не исправит: данных просто нет. -
Мёртвые джойны. В доброй половине запросов живёт
LEFT JOIN telegram_links l ONl.id= tc.telegram_link_id, гдеlдальше нигде не используется. Наследство копипасты из реферальных дашбордов в персонажные. Оптимизатор такой джойн обычно выкидывает, но читателю запроса он врёт: выглядит как «тут учтены рефералы», а не учтено ничего.
Вот эта строчка про first touch — самая дорогая в статье. Пока трафика мало, кажется, что и так сойдёт. Когда каналов становится пять и они начинают пересекаться, выясняется, что ретаргет вообще не может быть оценён: он по определению приводит людей, у которых telegram_link_id уже занят.
Проблема 10: A/B персонажей, который невозможно посчитать
У персонажей есть версии: characters → character_versions, актуальная лежит в characters.active_version_id. Промпты правятся, версии обновляются.
В аналитике это выглядит красиво:
names AS ( SELECT c.id AS character_id, c.code AS character_code, COALESCE(chv.name, c.name) AS character_name FROM characters c LEFT JOIN character_versions chv ON chv.id = c.active_version_id)
COALESCE тут — потому что имя может жить и в версии, и в самом персонаже, а актуальной версии может не быть вовсе.
Проблема глубже: в messages нет character_version_id. Сообщение знает персонажа, но не знает, какой промпт на него отвечал. Из этого следует, что:
-
нельзя сравнить вовлечённость до и после правки промпта;
-
любая метрика персонажа — это среднее по всем его версиям за период;
-
откатили неудачную версию — метрика «размазалась» и выглядит просто как шум;
-
вся панель «Уникальность и релевантность персонажей» с её
relevance_scoreсчитает популярность персонажа-как-идеи, а не персонажа-как-текущего-промпта.
Это ровно тот случай, когда одно поле в одной таблице отделяет «у нас есть аналитика персонажей» от «у нас есть A/B-платформа для контента». Поле стоит одну миграцию. Мы его не добавили, потому что в момент, когда это стало понятно, версии уже жили без него достаточно долго, и половина истории всё равно была бы пустой.
Проблема 11: техничка, которая измеряет не то, что написано на панели
Дашборд «Техничка» — про инфраструктуру: очередь, падения, время генерации. Самая невинная на вид панель:
SELECT DATE(gs.created_at) AS "Дата", AVG(TIMESTAMPDIFF(SECOND, gs.created_at, gs.updated_at)) AS "Среднее время (сек)", COUNT(*) AS "Количество"FROM generation_states gsWHERE gs.type = 'text' AND gs.updated_at IS NOT NULL AND $__timeFilter(gs.created_at)GROUP BY DATE(gs.created_at);
«Среднее время генерации текста». Считается как updated_at - created_at.
updated_at — это ORM-поле. Оно обновляется при любом апдейте строки. Ретрай, смена статуса, дозапись метаданных постфактум — всё это уезжает в «время генерации». Метрика меряет не длительность генерации, а время жизни строки до последнего касания.
Что это значит на практике: чем хуже работает провайдер, тем сильнее метрика врёт — причём в какую сторону, предсказать нельзя. Прошёл ретрай — время выросло вдвое, хотя пользователь ждал столько же. Дописали статус через час — в среднем появился час.
И ровно на этой панели держится решение «менять провайдера или нет».
Чинится не в SQL, а в схеме: started_at / finished_at, выставляемые воркером явно. До тех пор надо хотя бы смотреть не на AVG, а на медиану и p95 — среднее по такому распределению не значит вообще ничего.
Две соседние панели, кстати, честные и полезные:
-- сколько задач ждёт прямо сейчасSELECT COUNT(*) AS `Текущий размер очереди`FROM jobs WHERE (reserved_at IS NULL OR reserved_at = 0);-- где именно падаетSELECT COALESCE(queue,'default') AS `Очередь`, COUNT(*) AS `Ошибок`FROM failed_jobs WHERE $__timeFilter(failed_at)GROUP BY 1 ORDER BY 2 DESC;
Мораль в том, что на одном дашборде рядом стоят метрики с совершенно разной степенью доверия, и внешне они неотличимы. Размер очереди — факт. Среднее время генерации — оценка с неизвестной ошибкой. Обе нарисованы одинаковым шрифтом.
Где мы наврали сами себе
Отдельный раздел про баги, которые нашлись при подготовке этой статьи, — то есть заметно позже того, как по этим цифрам принимались решения о закупке трафика на аудиторию в сотни тысяч человек.
Из одиннадцати проблем выше в продакшн уехали три реально неверных числа. Два из них уже разобраны (топ персонажей, мерящий позицию в UI, и дефолтный now-6h из начала статьи). А вот третье не всплывало нигде — и оно худшее:
«Платящие активные пользователи по персонажам» — декартово произведение.
WITH base AS ( SELECT ch.telegram_chat_id, ch.character_id FROM messages m JOIN chats ch ON ch.id = m.chat_id WHERE m.role = 'user' AND $__timeFilter(m.created_at) -- ← строка на КАЖДОЕ сообщение)SELECT c.code, COUNT(DISTINCT b.telegram_chat_id) AS "Платные пользователи", COUNT(m.id) AS "Сообщений", ROUND(COUNT(m.id) / COUNT(DISTINCT b.telegram_chat_id), 2) AS "Среднее сообщений на пользователя"FROM base bJOIN paid_users p ON p.telegram_chat_id = b.telegram_chat_idJOIN characters c ON c.id = b.character_idJOIN messages m ON m.role = 'user' AND m.chat_id IN (SELECT ch.id FROM chats ch WHERE ch.telegram_chat_id = b.telegram_chat_id) AND m.created_at BETWEEN $__timeFrom() AND $__timeTo()GROUP BY c.code;
В base нет DISTINCT — там по строке на каждое сообщение. Затем messages джойнится второй раз, по всем диалогам пользователя, без привязки к персонажу. Результат: COUNT(m.id) — это примерно (сообщения персонажу) × (все сообщения пользователя), и колонка «Среднее сообщений на пользователя» показывает величину, у которой нет физического смысла.
COUNT(DISTINCT b.telegram_chat_id) при этом остаётся правильным. То есть панель одновременно содержит корректную колонку и колонку-выдумку. Это худший из возможных вариантов: если бы врало всё, заметили бы сразу.
Общее у всех трёх багов: ни одна цифра на дашборде не была ни разу сверена с ручным пересчётом по базе. Панель написана, панель показывает правдоподобное число, панель уходит в продакшн. Правдоподобность — не проверка.
Что бы я сделал иначе
По убыванию отношения пользы к затратам:
-
Таблица
eventsс первого дня.(telegram_chat_id, type, payload JSON, created_at). Одна миграция и один хелпер в коде. Снимает разделы 3, 9 и 10 этой статьи целиком. На 1.5M событий в день она же станет и главным кандидатом на отдельное хранилище — но это уже следующий шаг. -
Словарь метрик до первого SQL. Один markdown-файл: что такое пользователь, что такое активный, что такое сессия, какая таблица каноническая. Не «архитектурная документация», а десять строк, которые не дают двум панелям разойтись.
-
Явные тайминги вместо
updated_at.started_at/finished_atвgeneration_states. Одна миграция превращает выдуманную метрику в настоящую. -
character_version_idвmessages. Одно поле, которое превращает контент-аналитику в A/B. -
Read-only реплика + read-only пользователь. Второе — прямо сегодня, даже без первого. Особенно если у вас в дашборде живёт
REGEXP CONCAT('^(', '${var:pipe}', ')$'). При 75 RPS живой нагрузки реплика перестаёт быть роскошью примерно сразу. -
Сверка каждой новой панели руками. Один раз, на маленьком интервале, ручным
SELECT. Ловит ровно те баги, которые нашлись сильно позже. -
Подпись на каждой панели. Что именно считает знаменатель и в какой таймзоне бакеты. Grafana умеет описания панелей — все их игнорируют, включая меня.
Чеклист для тех, кто собирается делать так же
-
[ ] Определение «пользователя» записано словами и одинаково во всех запросах
-
[ ] Выбрана каноническая таблица событий/сообщений, вторая под запретом
-
[ ]
LEFT JOINтам, где в знаменатель должны попадать нули (иначе retention врёт в плюс) -
[ ] Кумулятивы — оконными функциями, не коррелированными подзапросами
-
[ ] Нет
$__timeFilter(a.x) OR $__timeFilter(b.y)— вместо этогоUNIONдвух запросов -
[ ] Все временные бакеты — через
$__timeGroup*, таймзона дашборда зафиксирована -
[ ] Мультиселект-переменные — только через
:pipeи с якорями^(...)$ -
[ ] Пользователь datasource в MySQL имеет только
SELECT -
[ ] Дефолтный интервал дашборда соответствует реальному циклу данных, а не
now-6h -
[ ] Метрики, построенные на ORM-полях (
updated_at), помечены как приблизительные -
[ ] Каждая панель хотя бы раз сверена с ручным пересчётом
Аналитика на голом проде — это нормальное инженерное решение, когда решения нужно принимать сейчас, а бюджета на отдельный контур нет. Она даёт большую часть пользы за малую часть стоимости, и я бы сделал так же ещё раз даже на 300K MAU.
Только у неё есть свойство, о котором не пишут: она не падает. Сломанный запрос не бросает исключение — он рисует красивое число не той величины, и это число неделями ходит по чатам как факт, пока по нему закупают трафик. Единственная защита — руками пересчитать каждую цифру ровно один раз, в самом начале.
Ну и поменять дефолтный таймрейндж.
ссылка на оригинал статьи https://habr.com/ru/articles/1078480/