Аналитические платформы стареют одинаково: данных больше, запросов больше, отклик длиннее. У заказчика поиск по реестрам уходил в минуты, и очевидное решение лежало на поверхности — сервер помощнее. Мы не спешили его покупать. Сначала разобрались, что именно тормозит, обкатали гипотезу на тестовом стенде, и дело оказалось не в железе. Тормозил оператор «содержит»: поиск вхождения подстроки идёт мимо индекса, полным перебором миллионов строк, пока парсер каждую секунду дописывает в ту же таблицу. Тот же запрос через «равно» отрабатывал за миллисекунду. Можно было отделаться правкой на 5 часов, но нагрузка росла структурно, поэтому вместо нового сервера базу развели на два контура: оперативный MySQL и аналитическую реплику на ClickHouse. Поиск теперь занимает 3–4 секунды вместо минут.
Сводка
| Отрасль | Сертификация продукции, B2B-аналитика рынка |
| Конечный клиент | «Аналитика сертификатов» |
| Формат сотрудничества | Регулярное сопровождение — реестры, целостность данных и архитектура базы |
| Тип проекта | Подключение справочных реестров, диагностика производительности и переделка архитектуры базы под рост нагрузки |
| Объём работ | Реестр аккредитованных лиц с приватными списками по компаниям; база новых участников рынка на ClickHouse + BI; развод базы на MySQL и аналитическую реплику ClickHouse через контейнер-репликатор; восстановление целостности дат; разбор инцидентов с экспортами |
| Дата проекта | январь 2022 — июнь 2024 |
| Трудозатраты | ~253 часа по ТЗ (80 — аккредитованные лица · 100 — база новых участников · 73 — архитектура базы), не считая абонентских часов на инциденты |
| Команда | Антон Херсун (руководитель проекта) и разработчик аналитической панели — он ведёт направление БД и реестров с первого дня и до сегодня |
| Технологический стек | MySQL (основная) · ClickHouse (аналитическая реплика) · контейнер-репликатор · Laravel · Sencha/ExtJS · Rocket BI и технические панели мониторинга |
| Сдано | Двухсерверная архитектура: основная MySQL и аналитическая реплика на ClickHouse с инкрементальным репликатором. Поиск по тяжёлым таблицам — 3–4 секунды вместо минут под SLA «20 потребителей в минуту, отклик до 10 секунд, запас по росту 2–3×». В продакшене с 5 июня 2024. |
Постановка задачи
К 2024 году платформа упёрлась в предел одной MySQL-базы, на которой работала с самого начала. Поиск по декларациям ТР ТС РФ «совсем плохо работал», по словам заказчика: по любому столбцу искал долго и в большинстве случаев отдавал ошибку. Первая реакция понятная — нужен сервер мощнее.
Прежде чем считать новое железо, мы посмотрели, на чём именно теряется время. Оказалось, часть пользователей ищет ОГРН с оператором «содержит». Такой поиск идёт мимо индекса: базе приходится перебирать строки подряд, проверяя вхождение подстроки в каждую. Тот же поиск с оператором «равно» опирается на индекс и отрабатывает за миллисекунду. Мы написали заказчику прямо: дело не в мощностях и не в новом сервере, а в том, как формируются запросы. Исправление оценили в 5 часов: поставить по умолчанию оператор «равно» на колонки ОГРН.
Заказчик уточнил картину, и она оказалась жёстче. По большинству текстовых полей оператор «содержит» нужен по делу, да и после переноса CRM-виджета на Битрикс24 число запросов через него вырастет кратно — пользователей станет заметно больше. Структурная нагрузка на тяжёлые таблицы будет расти независимо от того, как ищут люди. Так задача переехала с «ускорить запрос» на «вынести аналитическую нагрузку с оперативной базы». В отдельном ТЗ заказчик зафиксировал требования цифрами:
- беспрерывный доступ при 20 потребителях в минуту,
- отклик не более 10 секунд,
- архитектура должна допускать рост числа пользователей в 2–3 раза,
- CRM-виджет должен отдавать данные без задержек.
Что в этом сложного. Переоптимизировать так же легко, как недооптимизировать. Перейти на ClickHouse целиком значит переписать половину Laravel-кода и потерять транзакционность ORM. Оставить всё на MySQL и тянуть индексами не выход: через полгода начнётся то же самое, материализованные представления конфликтуют с непрерывной записью парсера, а read-реплика MySQL не спасает, потому что нагрузка на запись тоже растёт. Поэтому выбрали промежуточную схему: оперативные операции (запись парсера, транзакции) остаются в MySQL, аналитика и тяжёлый поиск уходят в реплику на ClickHouse, между ними контейнер-репликатор. Цена компромисса — задержка данных в аналитике на считанные минуты от записи. Для B2B-аналитики приемлемо.
Как мы это сделали
1. Сначала диагностика, потом архитектура.
Вслепую архитектуру не переделывают. Сначала мы нашли причину тормозов (оператор «содержит», который идёт мимо индекса), назвали её заказчику и предложили обойтись малой кровью: правкой оператора по умолчанию. К переделке перешли, только когда выяснилось: структурная нагрузка будет расти при любом операторе. Эксперименты с новой структурой шли на тестовом стенде, домашнем сервере: «если успешно, предложу решение». Через сутки исследование завершилось положительно, тестовый контур подключили к одному руту, чтобы заказчик сам пощупал скорость, и только потом расписали ТЗ и стоимость.
2. Двухсерверная архитектура: MySQL основной и реплика на ClickHouse (73 ч по ТЗ).
Основной сервер (4 ядра, 8 ГБ оперативной памяти, 80 ГБ диска) держит MySQL с парсерами. Второстепенный (8 ядер, 12 ГБ, 100 ГБ) отдан под контейнер-репликатор и ClickHouse — базу, заточенную под быстрое чтение и поиск. Репликатор инкрементально переносит на второстепенный сервер четыре самые массивные таблицы (две по РФ и две по реестрам ЕАЭС), синхронизация раз в час. Один инженерный нюанс мы проговорили с заказчиком заранее: таблица добавляется в репликатор вручную и только если в ней есть числовой идентификатор ID. По нему репликатор понимает, сколько новых записей появилось, и забирает только их. Ограничение сознательное: четыре тяжёлые таблицы получают высокую скорость, остальные работают на прежнем механизме, а новую таблицу можно переключить на ClickHouse «галочкой», когда понадобится.
3. Поиск с минут до 3–4 секунд.
На тех же четырёх таблицах средний запрос стал занимать 3–4 секунды вместо прежних минут. Заказчик подтвердил: «отклик действительно очень быстрый, и поиск работает корректно». Побочные эффекты вылезли по дороге: новая база оказалась чувствительна к регистру и оставляла служебные символы в строках — и то, и другое вычистили до выката. Решение ушло в продакшен 5 июня 2024 года. Бонусом ClickHouse дал статистику запросов по таблицам, которой на MySQL не было: видно, что и как ищут пользователи.
4. Реестр аккредитованных лиц с приватными списками по компаниям (80 ч по ТЗ).
Источник: Росаккредитация, реестр аккредитованных лиц, порядка 30 тысяч записей. Парсинг по тому же принципу, что и документов; полный переобход справочника идёт еженедельно, чтобы ловить изменения. Поверх общего справочника заказчик попросил редактируемый список «нужных» аккредитованных лиц — отдельный для каждой компании клиента, без доступа между компаниями. Это разграничение видимости на уровне строки. Приватные списки пересобираются каждую ночь, и одна компания не видит отметок другой. Модуль сдан 23–30 ноября 2022 года.
5. База новых участников рынка на ClickHouse, за год до основной миграции (2023, 100 ч по ТЗ).
Отдельная задача: находить заявителей, которые впервые появились в реестрах, чтобы менеджеры могли их прозвонить. Цель не аналитическая: база для обзвона. Наивный путь отвергли сразу: гонять на каждый новый документ поиск по всей базе по его ОГРН парализовало бы и аналитику, и CRM на часы, потому что документы приходят почти непрерывно. Вместо этого собрали ту самую схему, которая через год станет основной. Вся база инкрементально перетекает на параллельный сервер с ClickHouse, запрос группирует документы по ОГРН и смотрит дату первого появления: если старых документов ноль, ОГРН считается новым участником. Для отчётов поставили BI-надстройку (Rocket BI). По сути это был боевой прототип двухсерверной архитектуры — к моменту основного ТЗ мы уже знали, что связка репликатор + ClickHouse работает на этих данных. Модуль выкатили досрочно, закрыли в сентябре 2023 года.
6. Целостность данных как часть архитектуры.
Быстрый поиск бесполезен, если в таблицах мусор. Ещё в январе 2022 года разобрали историю «сломанных дат»: около 26 тысяч записей за 2018 год хранили испорченные даты — день превратился в год из-за старого алгоритма, который когда-то копировал даты в базу неправильно. Хранение перевели на штатный тип «дата», без алгоритмов копирования. Не перенёсшиеся значения восстановили вручную. Старые данные за 2017–2018 годы (около 170 тысяч записей) отправили в архив, и таблица заодно стала компактнее и быстрее. Позже, в 2024-м, нашли источник дублей в базе новых участников: дубли появлялись, когда у документа не удавалось определить страну. Исправление на будущее: нет страны — считаем Россией. Нашли 20+ тысяч исторических дублей и предложили заказчику честно: обработать их около 16 часов либо оставить как есть, зная природу. Заказчик выбрал оставить, раз новых дублей больше не возникает. Корень проблемы (отсутствие адреса) он держал под отдельное ТЗ.
Результаты
| Метрика | Значение |
|---|---|
| Поиск по тяжёлым таблицам | 3–4 секунды вместо минут |
| Целевой SLA | 20 потребителей в минуту · отклик до 10 секунд · запас по росту 2–3× |
| Архитектура | Основная MySQL (запись парсера, транзакции) + аналитическая реплика ClickHouse (поиск, аналитика) · контейнер-репликатор, синхронизация раз в час |
| Конфигурация серверов | Основной (4 ядра, 8 ГБ, 80 ГБ) + второстепенный (8 ядер, 12 ГБ, 100 ГБ) |
| Реестр аккредитованных лиц | ~30 тыс. записей · еженедельный полный переобход · приватные списки по компаниям, пересборка ежедневно |
| База новых участников | Инкрементальная репликация в ClickHouse + Rocket BI; боевой прототип архитектуры за год до основной миграции |
| Восстановление целостности | ~26 тыс. испорченных дат исправлено, ~170 тыс. устаревших записей в архив |
| Продакшен | 5 июня 2024 |
| Эксплуатация спустя 2 года (06.2026) | 102 активных пользователя за 2 месяца, отклик не просел (разбивка — в тексте ниже) |
Что получилось: основная MySQL обслуживает запись парсера и транзакции, реплика на ClickHouse берёт тяжёлый поиск и аналитику, между ними круглосуточно работает инкрементальный репликатор. Поиск по четырём самым массивным таблицам ускорился с минут до 3–4 секунд. Видимость реестра аккредитованных лиц разнесена по компаниям клиента на уровне приложения. База новых участников живёт в отдельном контуре и не мешает основной работе. Архитектура держит требуемый SLA с заявленным запасом по росту.
Результаты мы ощутили и сразу, и через 2 года. Статистика входов за апрель–июнь 2026: в системе 167 учётных записей, за 2 месяца активны 102 пользователя, еженедельно работают 66–79 человек, и эта цифра не проседает ни на одной из 9 недель. Ядро из примерно 55 сотрудников работает в системе регулярно, самые активные заходят по 10–12 раз в день. Архитектура, рассчитанная по ТЗ на рост нагрузки в 2–3 раза, держит этот режим без деградации отклика.
Процесс и хронология
| Этап | Период | Результат |
|---|---|---|
| Восстановление целостности дат | январь 2022 | ~26 тыс. записей исправлено, ~170 тыс. в архив |
| Реестр аккредитованных лиц | октябрь–ноябрь 2022 | 80 ч по ТЗ |
| База новых участников рынка (ClickHouse + BI) | июль–сентябрь 2023 | 100 ч по ТЗ |
| Диагностика «содержит» и инцидент с экспортами | январь–март 2024 | Найдена истинная причина тормозов; введён лимит и автоочистка экспортов |
| Переделка архитектуры базы (MySQL + ClickHouse) | май–июнь 2024 | 73 ч по ТЗ, продакшен 05.06.2024 |
| Исправление дублей в базе новых участников | октябрь 2024 | Исправление на будущее; чистку истории заказчик отложил |
Команда
- Антон Херсун, Xaver Pro, руководитель проекта: диагностика производительности, выбор двухсерверной схемы, формализация SLA, ТЗ и согласование
- Разработчик аналитической панели: модуль связи платформы с ClickHouse, конфигуратор, работа с репликатором. Он ведёт направление БД и реестров с первого дня и до сегодня: подключение справочных реестров 2022 года, прототип на ClickHouse 2023-го и переезд аналитики на ClickHouse 2024-го вёл один человек. Знает все исходные схемы и историю запросов.
- Инфраструктурная команда под лидом Антона: серверы, репликатор, мониторинг и восстановление после аварий хостинга.
Скриншоты и материалы
Будут добавлены отдельным проходом: схема архитектуры «парсер → MySQL → репликатор → ClickHouse → виджет», панель статистики запросов.
Если ваша MySQL умирает под одним отчётом, и при этом туда же пишет парсер — пришлите EXPLAIN самого долгого запроса и схему. Скажем, что развести по двум серверам, а что лечится оператором и индексом. Разбор ничего не стоит.