Порекомендуйте подходящую базу данных?

Question

[Megas]

День добрый.

Хотелось бы получить мнение экспертов по базам данных или кого то, кто уже работал с какими либо базами данных для Big Data.

Дано
Имеется некая аналитическая система которая работает на нескольких сайтах. Она для каждой страницы, для каждого элемента сохраняет некую статистику (к примеру, сколько раз элемент был виден, сколько раз на него кликнули и т.д., но в целом это не так важно). Все данные аггрегируются в памяти, а затем раз в сутки скидываются в табличку в БД. Затем аналитики могут генерировать отчет за определенный период для определенного сайта + определенной страницы + определенного сегмента. Либо за определенный период для определенного сайта + определенной страницы + отдельного элемента.

Структура таблицы примерно такая:
1. ID сайта
2. ID страницы
3. ID елемента
4. Дата (день)
5. ID сегмента пользователя (все пользователи поделены на некое количество разных сегментов)
6+. Все остальные поля с данными

На данный момент мы используем Amazon Aurora (MySQL 5.7) с движком InnoDB
Поля с 1-5 это первичный ключ.
Ежедневно в таблицу записывается порядка 80М строчек (но это не конечная цель, в будущем возможна запись и намного большего количества)
Запись оптимизированна и происходит блоками по 1000 строчек в каждом INSERT запросе.

Проблемы текущего подхода
1. Когда таблица пустая, то запись такого количества данных занимает несколько часов, но уже через месяц, из-за увеличения индекса, скорость падает до примерно 12 часов. Т.е. если данных будет в 2 раза больше, то уже не получится вложиться в 24 часовое окно, когда еще не закончилась запись предыдущего дня, а надо уже писать следующий.
2. Скорость генерации отчет оставляет желать лучшего. Для страницы с большим количеством элементов, генерация репорта за месяц может занимать 2-3 минуты, это слишком долго.
3. Иногда нужно удалить данные для определенного сайта, изза огромного количества данных такая процедура блокирует не только саму таблицу, но и всю БД, в которой еще много других таблиц к которым постоянно идет обращение чтение/запись. Пытались решить данную проблему партицированием, но это не особо помогло.

Какие будут предложения?

Comments

[FanatPHP]

ShlickHouse

[Saboteur]

Без POC тут сложно наверное что-то посоветовать. Надо экспериментировать.
Написать скрипт, который генерирует данных на месяц-два и попробовать на разных базах.

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

[Megas]

FanatPHP, знаю что конкуренты с похожей системой используют ClickHouse. Если других идей от когото не поступит, то наверно и мы будем присматриваться к ней.

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

[Akina]

Во всех описанных выборках/отчётах присутствует, практически как базовый и обязательный, фильтр по дате. Что явно наталкивает на мысль использовать партиционирование. 80М записей в день - это 2,5Г в месяц, так что партиционирование по году-месяцу и субпартиции по, например, домену, как бы напрашивается. И проблемы с увеличением времени вставки должны просто исчезнуть.

Это - для именно MySQL.

Answer 1

[rPman]

которые при генерации отчета как либо аггрегируются.

это чуть ли не наисложнейшая задача для баз данных, 80м записей тем более

Партицируйте прямо по суткам.

Убирайте транзакции, нафиг вам тут innodb когда хватит myisam, оно на запись быстрее, у вас база write once read ... тоже once.

У вас там база данных упирается случайно не в работу с диском? в облаке можно взять несколько дисков, они будут независимыми, раскидай по ним таблицы (myisam штатно поддерживает симлинки), что может дать прирост в скорости в разы только за счет этого, даже если они ssd, например отделить хранение индексов от данных или отделить старые данные от сегодняшних.

На время обработки аналитики можно потюнить файловую систему и отключить flush для файлов таблиц (например ext4 data writeback и можно отключить журнал) - сильно ускоряет именно запись, особенно если много ram, это включает большой риск потери/порчи данных при сбросе ос но с другой стороны вероятность этого очень мала и как я понимаю, данные в базу и так пишутся из какого то другого хранилища, т.е. при проблеме с сервером просто перезапускается обработка за текущие сутки.

Уберите индексы на запись, все, сначала пусть идет вставка данных без их индексации, затем создаете индекс (это на порядок быстрее) и уже потом строите аналитику.

Общая аналитика должна не работать с самими данными, а с их посуточной выжимкой (возможно в результате и хранить их не придется) считай это самодельные индексы. Грубо говоря если в запросе на аналитику стоит count,max,min,.. то достаточно сложить посуточные значения и для глобальных считать уже по ним... само собой если запросы с условиями и сложными группировками, то надо думать но все решаемо.. грубый пример нужно считать агрегацию по часам, вот в индексы и пиши суточные значения по часам, а если надо постранично то для каждой страницы для каждых суток считаешь, потом агрегируешь уже по этим результатам.

Comments

[Adamos]

У ТС поле 4 - день. Там нет никаких "по часам", это и есть сумма за сутки.

[rPman]

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

вся идея партицирования агрегации в том что итоговое значение будешь считать агрегируя уже эту агрегацию по всем суткам, данных мизер

[Megas]

rPman на данный момент данные в таблице агрегированы все по максимуму, дальнейшая агрегация невозможна из-за разницы в отдельных отчетах (гдето надо агрегировать по страницам, а гдето по отдельному элементу).

По поводу MyISAM, направление интересное, попробуем.

[rPman]

Megas,

данные в таблице агрегированы все по максимуму

я говорил про партицирование этой агрегации

сейчас как я понимаю у вас просто набор данных, с индексами и вы этим данным пишете select count() .. where страница, максимум объединив в запросе несколько таблиц, по которым разбиты данные

этот запрос каждый раз будет считать ВСЕ данные, даже если он по индексам гуляет, там логарифм от объема а у вас данных 80м в сутки!?

вот и считайте все то же самое но в пределах суток... затем когда надо, уже по этим итогам считайте по всем данным

По поводу MyISAM, направление интересное, попробуем.

еще индексы на текущей партиции отключай на время записи

[Megas]

rPman на данный момент уже используем партицирование по айди сайта и субпартицирование по дате. Пока точных цифр не скажу, так как только недвно поменяли тип партицирования, но вроде как прироста в производительности не заметили.

сейчас как я понимаю у вас просто набор данных, с индексами и вы этим данным пишете select count() .. where страница, максимум объединив в запросе несколько таблиц, по которым разбиты данные

Совсем нет, там как раз чтото вроде `SELECT SUM(filed6), SUM(filed7), AVG(field8)... FROM table WHERE siteId=x AND page=Y ...`, в отчетах постоянно меняется условие в WHERE, поэтому дальнейшая агрегация невозможна.

[rPman]

продолжай, что меняется в where? каждый новый аналитический отчет считает совсем новые цифры и логику или речь идет об упомянутых выше просто подмена условий фильтрации (по страницам или элементам на странице)?

я про то что если аналитика уже есть и она не меняется, ты ее считаешь персонально для каждой партиции - это создание самодельного индекса (хранится как обычные данные в таблицах)

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

Answer 2

[AndromedaStar]

У вас задача, которая решается с помощью OLAP.
Поэтому копать нужно в эту сторону, решений достаточно много.

Answer 3

[Владимир Олохтонов]

Для OLAP нагрузки в последнее время активно используют Clickhouse — у него довольно высокий порог вхождения, зато бесплатный и производительность того стоит.

Answer 4

[Александр Нестеров]

Может, вам лучше посмотреть в сторону noSQL (mongoDB, firebase и т.д.)?
https://habr.com/ru/sandbox/113232/

Comments

[FanatPHP]

АХАХАХА! монга!
Ну нельзя же так, я кофеем подавился

[Александр Нестеров]

FanatPHP, первое что вспомнилось

[FanatPHP]

я понимаю.
когда не понял вопроса, вообще никогда не касался предметной области, и не понимаешь ни одного слова в своем же ответе, то действительно, всякая ересь только и вспоминается.

[Megas]

AlexNest Все возможно, я не ограничен исключительно реляционными БД. Вопрос в том, а какая другая БД будет работать лучше при таких данных (структура + количeство)? Не хочу потратить кучу времени на переезд, к примеру, на ту же MongoDB, а потом обнаружить, что выиграл от этого 2-3% производительности.

[FanatPHP]

Megas, ну почему же 2-3? тут будет от 2000-3000 до бесконечности.
Только не выигрыш, а наоборот.

Но вообще задавая такой вопрос надо бы конечно хотя бы немного представлять себе варианты решений.
Чтобы правильно задать вопрос, нужно знать большую часть ответа.
Если вы не знаете поляну даже схематически, и для вас все базы на одно лицо, а in-memory БД - это потенциально рассматриваемый вариант, то ответы вам не помогут.

[Megas]

FanatPHP, согласен, на данный момент, как вы и написали, для меня все БД на одно лицо, лично работал лишь маленьким их количеством (MySQL, MS SQL, тот же Mongo и всякая ерунда вроде SQLite), но как то к вопросу надо подходить и поэтому спрашиваю, чтоб получить хоть какие то направления...
На in-memory БД памяти не напасешься с таким количеством данных. На данный момент мы храним данные за текущий день в Redis, а потом от туда скидываем в MySQL.

[FanatPHP]

Megas, как-то не вяжется вместе
- лично работал [c] Mongo
- потратить кучу времени на переезд, к примеру, на ту же MongoDB
- На in-memory БД памяти не напасешься

Какое-то из этих утверждений явно не стыкуется с остальными

Answer 5

[Виктор Таран]

Вам не нужна бд в принципе.
При собеседование почему-то у программистов в 100% случаев возникает тупик в вопросе для чего нужна mysql
и когда спрашиваешь почему именно его используют до вопроса с транзакциями не доходит практически никогда.
А ведь именно из-за них ее используют.
Так вот тут у вас транзакции не нужны. Тупо 1 инсерт
Как следствие вам хватит и обычного лог файла который можно удобно парсить, дабы для этого вообще не требуется никакого ПО. да и того в достатке.
Ротация логов и тд и тп, в общем это до вас придумали.
Так же для ваших целей и яндекса и гугла есть соответствующие инструменты ( особенно у гугла с отчётами и подобной хренью, не понимаю зачем вам вообще держать эти данные локально)

Comments

[FanatPHP]

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

[Виктор Таран]

FanatPHP,

На данный момент мы используем Amazon Aurora (MySQL 5.7) с движком InnoDB

/

[FanatPHP]

я понимаю, осилить несколько комментариев очень сложно.

[FanatPHP]

На данный момент мы храним данные за текущий день в Redis, а потом от туда скидываем в MySQL.

[Виктор Таран]

FanatPHP, Amazon MemoryDB for Redis -не файл,
в тз ничего нет про Redis .
не говоря уже о самой задаче, из текущих потребностей нормально настроенных метрик вполне хватит.
я же всего-лишь предлагаю варианты.

[FanatPHP]

Идет заседание ЦК КПСС.
Министр сельского хозяйства зачитывает доклад о том, что в колхозах болеют коровы. Никто не знает, как лечить.
Брежнев: нарисуйте перед каждой коровой зеленый ромбик.
Ну, издали постановление, во всех коровниках кампания по рисованию.
Следующее заседание. Все равно коровы болеют.
Брежнев: нарисуйте перед каждой коровой фиолетовый треугольник.
Ну, опять кампания, доярки в художниц переквалифицируются...
Опять заседание.
Министр докладывает, что все коровы подохли.
Брежнев: жаль. Я хотел предложить ещё много вариантов...

Answer 6

[Adamos]

Если по полям 6+ на самом деле не производится выборка, а только пишутся-читаются данные, можно попробовать собрать поля 3 и 6+ в один блоб (JSON, например). Уменьшится нагрузка на запись, в отчетах будет больше возни, но меньше обращений к базе...

Comments

[FanatPHP]

с какой это стати уменьшится нарузка на запись?

[Adamos]

FanatPHP, поля 1-3 и 5 индексируются, как я понял.
Причем по 3 - самый большой индекс.

[FanatPHP]

а уменьшится-то почему?

[Megas]

Adamos Не, к сожалению ID элемента из индекса нельзя вытащить, так как иногда нужно генерировать отчет по конкретному элементу.

По поводу JSON для полей 6+, думал о таком варианте, только вот на скорость это никак не отразится. Запись тормозится в первую очередь из за большого индекса (неспроста скорость записи в пустую таблицу в разы выше, чем +1Г строчек в ней). Плюс как вы и сказали, при генерации репорта возникают сложности, в текущий момент я могу сразу в запросе аггрегировать эти данные (суммировать к примеру) и получить готовый результат, а так придется тянуть кучу данных через сеть и аггрегировать на стороне кода. А работа с JSON у самого MySQL крайне неудобная, плюс сомневаюсь, что это хоть как то положительно скажется на скорости.

[Adamos]

Megas, ну, я не претендую на гуру в этой области.
Просто после прочтения вопроса сформулировал для себя проблему так, что у вас БД стала узким местом, особенно вставка и удаление. Убрав самый жирный индекс и собрав вместе строки, данные которых все равно чаще всего используются вместе, можно было бы упростить жизнь базе и перенести проблемы на другие части системы, у которых, возможно, полно простаивающих из-за БД резервов.

Answer 7

[Виктор П.]

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

Answer 8

[mayton2019]

Бигдата и индексы - обычно не дружат друг с другом. Антагонизмы по сути. Поэтому от индексов надо уходить и двигаться в сторону партишенинга, сложного и полностью ориентированного на аналитические выборки.

В идеале - реплицировать все данные в другую БД с другой геометрией таблиц или вообще в систему другого типа.

Любая современная биг-дата будет дешевле по стоимости владения по сравнению с DBMS.