Проектирование большой БД. Чешу репу

[Osolemio]

Добрый день всем. Прошу прощения, что только на форум и создаю новую тему. Может кто поможет посмотреть в правильную сторону

Проектирую БД. Основная парадигма: облако. Принимает пакетами данные от устройств юзеров (user_id /unique/) и раскладывает в БД. У одного юзера может быть несколько устройств (device_id /unique/).
Естественно, потом все это на веб-фейс + раздача через сервисы: будет выборками выплевывать.

В чем беда: юзеров > 1000. И со временем вполне за 10 000. Записи посекундные. Т.е. за секунду в таблицу добавляется, скажем 4 000 ... 40 000 записей.
Нормализацию первого порядка я могу сделать
user_id device_id timestamp ... а вот дальше идут вполне себе уникальные аналоговые данные. Обращаю внимание, что timestamp здесь уже не уникален.

Что получается: Если я пихаю все это безобразие в одну таблицу, тогда при среднегодовом объеме данных на одно пользователеустройство = 4ГБ, годовой объем таблицы, в т.ч. по записям удручающий.
К примеру, 3600 с * 24ч * 365 дней * 1000 пользователей = почти 6 млрд записей по 20 полей
Придется включать партиционирование и время от времени переколбашивать таблицы.В итоге все сводится к модели один ко многим. Выборки через некоторое время будут весьма тяжелыми.

Есть второй вариант: делаю таблицу соответствия user_id + device_id -> table id и делаю 1000 одинаковых таблиц, по одной на каждое устройство. В плане удобства и объема каждой таблицы - веселее. Плюс: при удалении пользователя/устройства дропается таблица за секунду и привет. Но тут подводные камни СУБД и системы. Mysql будет держать тысячи открытых файлов. В принципе, такие подходы есть и работают весьма успешно. Да и для InnoDB можно запретить соответствие файл-таблица. Остальные заморочки вроде тоже как победимы.

Что-то я никак не определюсь : и первый вариант не нравится. И второй не по феншую. Секундный интервал я могу увеличить до 5 - 10с, но это в целом на картину не влияет, да и не хотелось бы. Это как резерв ставки. Сразу сокращает объем данных в 5 раз. Но снижает точность.

У кого есть на этот счет какие соображения? Заранее благодарен
P.S. Для большей точности: СУБД Маришка. Ибо Центос + Ларавель

[Alex11223]

Сообщение от Osolemio

Для большей точности: СУБД Маришка. Ибо Центос + Ларавель

Ибо что? Они поддерживают и кучу других.

[Osolemio]

Сообщение от Alex11223

Ибо что? Они поддерживают и кучу других.

Видите ли, я с Центос дружу наверное с года эдак с 2005 и совершенно, знаете ли, об этом не догадываюсь. Ни одной другой БД под Центом я не знаю Если вы решили так мне помогать, то мне такая помощь не нужна. Спасибо большое

А почему "ибо"? Да потому что штатная СУБД в центе с недавних пор мария, когда они испугались оракловской лицензии. А в тексте я писал о mysql
Если у меня все упрется в движок БД, поверьте, я ее легко поменяю. Но пока вот так я поставил спарку и хотел бы ее и оставить. В этом тоже есть много причин. А задавая вопрос, я решил дать точные вводные. Вот и все. Если ответ на мой вопрос сведется к умничанию старожилов, увы, мне будет грустно, что я сюда зашел. Я хотел бы все же услышать мнение опытных людей именно по заданному мною вопросу. Если это сложно или невозможно, немножко мне помочь, и на том спасибо. Пойду поищу еще где-нибудь подсказки.

[ADSoft]

Похоже вы чего -то с геопозиционировнием мутите)))
ИМХО - раз в секунду ну очень не нужный интервал.... туда сюда траф гонять....
у вас канал быстрее забьется (а так же число воркеров исчерпается) чем встанут проблемы производительности БД....

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

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

кароче нужно знать - что за задача, и исходя из нее решать

P.S. Пишу не просто так, сам делал и наступал на грабли - приложение для Такси, с геопозиционированием в реалтайме..... макс чистота отправки на которой хоть что-то работало и не падало - было порядка 5 сек

P.P.S - ах, да... еще на nix ах хорошо Postgress идет .... и тоже беспатен

[Osolemio]

Сообщение от ADSoft

Похоже вы чего -то с геопозиционировнием мутите)))
ИМХО - раз в секунду ну очень не нужный интервал.... туда сюда траф гонять....
у вас канал быстрее забьется (а так же число воркеров исчерпается) чем встанут проблемы производительности БД....


P.S. Пишу не просто так, сам делал и наступал на грабли - приложение для Такси, с геопозиционированием в реалтайме..... макс чистота отправки на которой хоть что-то работало и не падало - было порядка 5 сек

Ох. Спасибо. Ну я же не про канал спрашивал Так и знал, что не о базе разговор пойдет, а о том, что канал забъется. Не забъется.
Я весьма себе сертифицированный инженер по сетям в прошлом.
Передача идет пакетами. Раз в минуту. Сжатыми LZF. Всего-лишь 5-10кБ/пакет.
Приемная сторона написана на Golang. 500 пакетов (читай - 500 соединений) съедает за доли СЕКУНДЫ. И это на тестовой виртуалке

Это уже распакованные записи содержат посекундный шаг. К геопозиционированию нет, не имеет отношений. Телеметрия.

Возвратимся к архитектуре БД, пожалуйста

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

Сообщение от ADSoft

P.P.S - ах, да... еще на nix ах хорошо Postgress идет .... и тоже беспатен

В курсе, спасибо. Только пока не вижу в нем преимуществ именно для моей задачи

ADSoft,

У меня вообще дурацкая идея была: прямо в таблицу поминутно складывать LZF-сжатые данные. А распаковывать уже из выборки и выбирать нужные из JSON-массивов. Места будет уходить намного меньше, БД легче. Основные накладные расходы переместятся на обработчик и парсер. Только вот у меня ощущение, что идея не рабочая: или оперативы не хватит, или по процессору завалится

[ADSoft]

ну еще можно вообще не реляционные базы юзать ... типа MongoDB итд
довольно шустро должно получится, либо часть оперативную - в NoSQL... часть в обычную БД ...

В любом случае для нормального вывода нужно всю задачу целиком осознавать, какие данные, может как то группировать можно итд ....
Например записывать в БД сразу весь ваш минутный Пакет,

непонятно в чем вопрос просто....

У меня вообще дурацкая идея была: прямо в таблицу поминутно складывать

да не дурацкая, надо тока реализацию продумать ... я встречал такие решения

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

[Osolemio]

Сообщение от ADSoft

ну еще можно вообще не реляционные базы юзать ... типа MongoDB итд
довольно шустро должно получится, либо часть оперативную - в NoSQL... часть в обычную БД ...

В любом случае для нормального вывода нужно всю задачу целиком осознавать, какие данные, может как то группировать можно итд ....
Например записывать в БД сразу весь ваш минутный Пакет,

непонятно в чем вопрос просто....

Уже думал в сторону не реляционных. Тогда уж лучше не Mongo, а тот же PostgreSQL с JSONB. У меня данные прибывают в JSON. Весьма удобно. И "кольцо" поддерживается. Можно ограничить пользователя временем хранения. Правда, JSON строка займет хренову тучу места, в сравнении с голыми байтами. И это тоже минус. Но не очень сильный. Дисковое пространство все же один из недорогих ресурсов.

Задача. Да "простая". Устройства, имеющие рабочие параметры: температура, ток, напряжение, скорость ветра, влажность, энергия... всякая хрень. Эти данные пакуются и уходят в облако. Там хранятся. И при необходимости запрашиваются, либо по выборкам пользователя отображаются в неком виде на вебе. Это таблицы, графики, анализ и пр, и пр. Причем, по каким данным приспичит пользователю посмотреть выборку и за какой период - никому не известно. А пользователи, они ж такие: ждать не хотят обработки запросов. Да еще и RT графики вынь да положь.
Все, что можно было выкинуть и сгруппировать, я уже выкинул и сгруппировал. Т.е. реально длину строки уменьшил раза в 2. Остались тупые аналоговые данные. В основном float
Ну могу там еще пару полей byte сшить в 4-байтный int, но это уже на скорость ветра не сильно
Вот и вся штуковина
На клиентах я использовал раньше mysql, потом свой кольцевой движок сделал. Разбив на посекундную часть с хранением 24ч и месячную усредненных 5-секундных. И все положил в ОЗУ. Добился т.о. офигенной производительности (клиент мелкий - микро-ПК встроенный)

Сообщение от ADSoft

Елси объем не так важно, а главное в реалтайме там чето отображать постоянно, и желательно побыстрее - тогда думать надо )

Вот именно это

[ADSoft]

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

p.s - ну и как бы не извращались при таких объемах - железо должно быть на уровне, на стареньком компе в кладовке вряд ли получится все быстро и красиво )

[Osolemio]

Сообщение от ADSoft

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

Практика показала, что выше 5с усреднение уже совсем пропускает пики. Т.е. скачки тока, напряжения, и т.п. не видны уже совсем.
Т.е. минутная детализация уже очень и очень плоха
И есть еще дискретные данные: типа состояния реле. Его не усреднишь. Только выкинуть можно
Я уже даже подумал вчера о переработке данных и визуализации интервалами по несколько минут по принципу японских свечей. Тогда и экстремумы видны, и вход-выход из интервала. Короче, я с вашими идеями солидарен и думаем мы в одном направлении. Это радует. Спасибо. Буду тогда думать дальше

[ADSoft]

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