Большое кол-во записей в секунду в MySQL. Как читать последний порядковый номер столбца и добавлять к нему +1 при записи?

Question

[Maksim Herasim]

Здравствуйте, не знаю как правильно сформулировать заголовок запроса. Постараюсь отразить в деталях.
Есть некая абстрактная БД на mysql, в которых я по сути заполняю лишь одну колонку - result.

Схема таблицы

Где id = Auto Increment , остальные колонки заполняются инсертами.
Есть некоторое устройство (на самом деле таких устройств может быть десятки), которое может генерировать данные с периодичностью 0.001сек. Периодичность меняется, но я закладываю максимальную скорость генерации.
C устройства передаются данные, которые заполняются в колонки (sessid и result)
Само устройство пишет не напрямую в базу, а передаёт данные в обработчик, на котором я реализовал следующую логику -

->Поступают данные с устройства ->

->выполняется проверка наличия sessid в базе

SELECT
	table.serialnumb
FROM
	table
WHERE
	sessid = $sess.id$
ORDER BY
	base.table DESC
LIMIT 1

Таким образом я получаю наибольшее значение serialnumb для данного sessid->

->если не найдено выполняется -

INSERT INTO `base`.`table`(`sessid`, `serialnumb`, `result`, `numnum`) VALUES ('$sess.id', 0, '0', $numnum)

и отправляется заново на проверку наличия sessid в базе->

->Если найдено - выполняю проверку уникальность result в данном sessid (не должно быть двух одинаковых result для одного sessid). Повторные значения result отбрасываю. ->

->Уникальное значение - беру serialnumb +1 и делаю инсерт

INSERT INTO `base`.`table`(`sessid`, `serialnumb`, `result`, `numnum`) VALUES ('$sess.id$', $sess.newlastnum$, '$rand$', $numnum$)

Собственно, я продебажил на разном железе, но в рамках ВМ (на вм стоит mysql и на отдельной вм стоит обработчик на node) у меня получилось так

Intel Platinum 8380 + ddr4
Самый толстый запрос усреднённо 0.04 сек занимает (select с 100 тыс.строк данных)
Ryzen 7 7800x3D + ddr5
тот же запрос 0.035 сек
Тестовый стенд intel xeon gold 6130
тот же запрос усредненно 0.1 сек

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

Проблемка

Причина-следственная связь ясна, из за большого наплыва информации select отрабатывает раньше у части запросов, в связи с чем отдается устаревший serialnumb, в связи с чем один serialnumb прописывается для нескольких запросов.
В итоговом решение - железо будет использовать энергоэффективное, соответственно вариант с железом отметаем.
Можно оптимизировать запросы + оптимизировать саму БД (т.к. она она стоковая без тюнинга mariadb 10.5) - но не думаю что приведет к исключению проблемы.
Как решение, вижу такой способ. Добавление всех записей во временную таблицу, Где будет 3 колонки (id (auto incremet), sessid, result) и далее уже перенос в актуальную таблицу. Но это может дать лаг при анализе данных на frontend, чего хотелось бы избежать. По этой же причине не хотелось бы делать задержку ожидания на обработчике, (тогда там memory может вспухнуть если будет гигантская очередь).
Подскажите, какие способы оптимизации или решения моего вопроса воспользовались бы Вы? Может я где-то нарушил логическую цепочку ?

Answer 1

[Rsa97]

Какой-то бред.
1. Составной ключ по автоинкрементному полю и обычному смысла не имеет, поскольку автоинкрементное поле id всегда уникально, то добавление к нем sessid ничего, кроме торможения, не добавит.
2. Делать какую-то отдельную проверку на уникальность result внутри sessid смысла не имеет. Достаточно сделать уникальный составной ключ (sessid, result) и использовать INSERT IGNORE.
3. Отдельное поле serialnumb абсолютно не нужно. Выборку всегда можно отсортировать через ORDER BY id и пронумеровать через оконные функции.

Comments

[Maksim Herasim]

1. Скажем так. БД это не совсем моё, я в тему не углублялся, но где то\когда то читал либо коммент, либо статью, что добавление ключа даст буст скорости при выборке по полю. Т.е. если условно мне потребуется получить все записи sessid, то используя его как ключ - это будет быстрее. От этого и поставил ключом. Спасибо, буду вникать на досуге.
2. Тут всё немного сложнее, т.к. в дальнейшем, возможен вариант при которых через каждые X записей serialnumb, может всё таки начать встречаться одинаковый result для sessid. Я пока не приступал к этому вопросу, но понимаю что такая потребность может возникнуть. От этого в рамках вопроса, я написал то, от чего отталкиваюсь на данный момент) Сейчас просто реализую задачу от меньшего сопротивления.
3. Т.к. в данном случае я сам себе начальник, разработчик, наладчик, тестировщик и всё в одном - то в дальнейшем, для вывода данных, мне было бы проще использовать порядковую нумерацию в бд, чтобы облегчить себе работу при визуализации данных.
В любом случае спасибо что прочитали мой бред) Дельные советы, обязательно испробую.

[Rsa97]

Maksim Herasim, 1. Только если это единственное или первое поле индекса. Выборка только по второму (третьему и т.д.) полю не ускоряется.
2.

через каждые X записей serialnumb, может всё таки начать встречаться одинаковый result

Это должно решаться другими способами, записывать в SQL-базу, а потом искать по ней - не самое лучшее решение. IMHO, тут больше подойдёт InMemory NonSQL-база, которая будет хранить последние X записей для каждого sessid.
3. У каждой записи и так есть уникальный идентификатор id. Хотите красиваости на выводе - создавайте вьюшку, которая покажет порядковые номера. Незачем нагружать базу лишними операциями при записи.

[Maksim Herasim]

Rsa97,
1. Воооот, спасибо за расширение кругозора, видимо когда читал - через слово запоминал. Могли бы поделиться в каком случае могло бы быть полезным использование несколько ключей?
2. Тут интересно, спасибо за наводку, про NoSql. Нужно будет вчитаться. Правда у меня опыта только с mysql на запросы хватает, а с mssql и postgre там только на уровне настройки сервера и бэкапирования знаю. Может знаете такие, чтобы команды попроще писать было? Или чтобы был софт на подобии navicat с query builder. Он мне в свое время помог разобраться с логикой построения запросов и немного с синтаксисом.
3. На счёт лишней операции, если я в одном запросе считываю sessid и в довесок получаю serialnumb. А в другой операции я заполняю поля sessid, serialnumb, result то сильно ли исключение result в select и insert упрощает жизнь базе? Специально замеры не проводил, но когда делал инсерт без serialnumb мне показалось что время +- одинаковое было, что на мой взгляд говорит о том, что оно не сильно влияет. Но опять же, не моя специализация и я не глубоко изучал вопрос. Благодарю за потраченное время!

[Rsa97]

Maksim Herasim,
1. Первичный ключ (PRIMARY KEY) - он вообще не про ускорение, а про однозначную идентификацию строки. То, что он ещё используется как индекс, это приятное дополнение. А по составным индексам всё просто. Индекс, составленный из нескольких колонок , ускоряет поиск в случае выборки по префиксу индекса. Например, для индекса (a, b, c) ускорятся запросы

WHERE a = ...
WHERE a = ... AND b = ... 
WHERE a = ... AND b = ... AND c = ...

и не ускорятся

WHERE b = ...
WHERE c = ...

2. Тут ничего конкретного не скажу, поскольку у меня highload'а нет.
3. Проблема не в самом serialnumb, а в том, как вы его получаете. Для избежания состояния гонки вам надо делать транзакцию и использовать блокировку таблицы на чтение, но тогда другие потоки будут ждать её освобождения. При этом поле serialnumb особого смысла не имеет.

[Maksim Herasim]

1.Спасибо за разъяснения по пунктам! Тогда понятно, в моем случае такая выборка не будет производится, поэтому для меня это вреднее получается.
2. Понял, принял.
3. Но если по факту - если я не дергаю отдельными запросами resultid, а этот параметр идет в довесок к select - или когда я провожу запись insert, и в этом insert дополнительно к другим параметрам задаю resultid - сильно ли то влияет на производительность операции? Понятно что я могу это эмпирически выявить и проанализировать, но с Вашей точки зрения, дополнительная колонка при insert сильно ли усугубляет ?

Признаю честно, сначала я начал дорабатывать обработчик, с учетом замечаний с вашей стороны. Отказался от проверки уникальности result (на текущем этапе), отказался от заполнения serialnumb.... Но потом меня осенило и я кажется нашел решение. Мне известно что параметр sessid не возобновляется. Т.е. если этот параметр сменился на устройстве, то в дальнейшем он не будет возвращен, т.к. по сути это будет timestamp+uid кодированный в base64. А решение выглядит следующим образом. Хранение последнего serialnumb для каждой активной sessid в memory. Т.е. в рамках обработчика я буду хранить sessid+serialnumb в переменных и каждое поступление данных я буду смотреть, существует ли такой sessid из переменной а не бд, если существует, то при записи в базу буду читать эту переменную, делать ей +1, и записывать в бд. А если приходит sessid которого еще нет в memory, в этот момент я делаю проверку в БД (на всякий случай) и назначаю значение = 0. Если вдруг в БД будет такой sessid в таком случае я от туда беру последнее значение, и вношу его в переменную обработчика. Этот вариант я уже тоже проверил, у меня получилось что каждая переменная отжирает не более 5 кб, памяти. следовательно для хранения значений sessid:serialnumb для 1000 устройств будет отжор менее 5мб memory . Что меня более чем устраивает. В общем буду развивать пока оба решения, с serialnum и без него, когда подойду к визуализации данных, пойму на сколько это будет критично для меня, и уже приму окончательное решение. Спасибо за Ваш вклад в мой труд.

Answer 2

[mayton2019]

Обычно при стриминге информации разделяют 2 процесса. Первое - это регистрация
событий с устройств. Это обычно запись в Message Hub, Kafka, RabbitMQ, Apache Pulsar e.t.c.
прочие брокеры или системы которые работают с очередью. Тут - главная задача просто успеть
регистрировать событие. С этим системы очередей справляются отлично.

При регистрации событий никакого синхронизма между устройствами нет и в принципе
не может быть. По законам физики и по часовому времени и по прочим сложным процессам
которые идут в мире. Мы не можем вообще гарантировать точного совпадения часов на всех
устройствах поэтому фактор времени мы пропустим. К свойствам дублирования и грязи в атрибутах
на этом уровне можно относиться очень толерантно и не требовать никакой сложной логики фильтрации.
Если допустим 99.9% событий зашло ровными - оно и ладненько. Дальше почистим.

Дальше идет - процессинг. Вот здесь уже может вступать в игру MySQL сервер с его логикой
транзакций. Счетчиков. Уникальностей. Сурогатных или натуральных ключей. Партишенинга
и прочего. Здесь у вас уже может возникнуть лимит на скорость генерации (inserts) но он будет
мягкий потому что очередь уже смягчает удар.

Если у тебя приложение - тайм-критичное и клиент хочет видеть КАЖДОЕ событие как счетчик
в браузере то здесь тебе скорее всего надо отказаться от MySQL и попробовать что-то другое.
Что - я не знаю. Надо исследовать глубже use-case. Обычно разработка таких систем - это
компромисс между одним и другим. Идеала тут не будет.

Comments

[Maksim Herasim]

Спасибо за ответ.
Да про брокер я думал и с RabbitMQ уже давно знаком, но я проводил тестирование своего обработчика, и у меня % доставки всегда был около 100%, при 10 000pps. Понятно что это синтетика, реальные тесты еще впреди.
Но на данный момент я спокоен, что обработчик вывозит обращение от 3 устройств которые генерируют данные каждые 0.001 сек, особо не напрягаясь. Поэтому на текущий момент для меня брокер - как дополнительная точка отказа, а я стараюсь избегать такого. В любом случае спасибо. В дальнейшем может быть полезно, но по сути пока не сильно отличается от того, что я очередь буду формировать на обработчике. Обработчик эту очередь будет пихать в отдельную БД, а доп.обработчик будет переносить из одной бд уже в прод.бд

[mayton2019]

Maksim Herasim, с точки зрения надежности у тебя с большей вероятностью упадет MySQL
чем брокер. У БД - больше слабых мест и больше причин падать.
Ну впрочем как будет угодно. И тут вобщем
нет какого-то математического доказательства что один метод лучше а другой хуже.
Это только твой личный опыт покажет.

[Maksim Herasim]

mayton2019, я вообще рассматриваю все как крайне ненадежное. По этому и стараюсь перестраховаться.
Я Ваш довод услышал, и он мне вполне нравится, и моя практика, так же сходится с Вашим мнением касательно того, что брокер будет более надежным чем sql сервер - у нас брокер в проде не падал ни разу, можно сказать - поставил и забыл. а вот отдельно взятые ноды кластеров sql бывало падали. Но суть тут немного в ином.
Сейчас у меня схема грубо говоря выглядит так -
Устройство - обработчик - бд
При добавлении брокера, мне все равно потребуется процессинг, как раз для него и выступает мой обработчик. Он выполняет некоторые дополнительные действия с данными, помимо работы с бд. Получается, что с добавлением брокера, схема будет выглядеть так -
Устройство - брокер - обработчик - бд
Лично в моем видение и мировозрении это выглядит так: лишняя точка отказа, лишнее звено уязвимости, дополнительные ресурсные затраты. А лишняя она по той причине, по куда обработчик способен обработать требуемые объемы данных, то необходимости в брокере нет. Сейчас я нахожусь на начальной стадии проекта, и первые тесты (синтетические и практические) показали что с обработкой таких объемов, обработчик вполне справляется. Во всяком случае в синтетики симулировал 10 устройств высокой интенсивности и запас еще оставался. Поэтому на данном этапе вопрос по брокеру пока не стоит, хотя его преимущество я знаю, использую в т.ч. и для домашних датчиков. Сейчас же хочу минимальными ресурсами и средствами добиться нужных результатов. Именно по этому я не стал использовать несколько таблиц БД. Не стал вводить дополнительные задержки в обработчике. Поэтому можно сказать так, я вполне рассматриваю брокер, но только в тот момент, когда буду масштабы будут шире

[mayton2019]

Начни с самого простого. Потом всегда усложнить можно.