Очень быстро лить в БД 1 млн. строк в секунду и настолько же быстро читать их. Как лучше осуществить?

Question

[Иван Мельников]

Необходимо:
- лить в таблицу (ticker, price, quantity, oper) ежесекундно ~1 млн. строк (каждая строка идет отдельным INSERT-ом),
- при этом 100-1000 раз в секунду производятся SELECT-ы на выбор вновь прибывших строк.
Важно, чтобы не просто работало.
Очень важно:
- минимизировать время от поступления TCP-пакетов с данными на сетевой интерфейс до получения результата SELECT'а с этими же данными,
- иметь какую-то гарантию (возможно с вероятностью ~0.995), что время между поступлением данных по сети до получения результатов SELECT-а с этими же данными будет не больше некого достаточно малого ε .
Вопросы:
Можно ли это осуществить с помощью MySQL, PostgreSQL или другой классической реляционной СУБД?
Будет ли проффит по времени от использования колоночных СУБД (InfluxDB, ClickHouse, что-то ещё)?
Будет ли быстрее забирать данные с сетевого интерфейса напрямую, минуя БД, а уже после обработки во время простоя ресурсов складывать их в БД? Насколько это будет быстрее?

Comments

[Армянское Радио]

1 млн. строк в секунду - это не единица измерения производительности СХД. Правильная единица измерения "1 млн строк длиной X байт"

Также нужно понимать, что даже сотни тысяч IOPS сейчас стоят больших денег.

[Армянское Радио]

Если, что, CEPH был специально создан для того, чтобы лить МНОГА данных с сенсоров Большого Адронного Коллайдера в СХД. И он поддерживает RDMA (это вот когда вы говорите "забирать данные с сетевой карты напрямую", вы наверное это имеете в виду). В тесте аж за 2018 год нарисовано, что ускорение составило почти полгигабайта в секунду, что довольно внушительно. (было 2053 стало 2454).

[Иван Мельников]

Армянское Радио, из описания таблицы (ticker, price, quantity, oper)
уже можно понять о каком размере строк идет речь.

[Армянское Радио]

Иван Мельников, неа, вдруг у вас все в VARCHAR(1024).

Формально, все очень просто. Если мне, как программисту, нужно догадываться, до деталей ТЗ (то есть, делать работу аналитика), я включаю Quad Damage, то есть, беру 4 цены за такую работенку. Это страхует меня от нет, мы имели в виду другое, особенно на собеседовании (или при передаче решения заказчику)

[Иван Мельников]

Армянское Радио, понятно. Я уже оптимизировал поля целевой таблицы:

CREATE TABLE `GLASS` (
	ticker ENUM (...) NOT NULL		# 2 Байта
	price DECIMAL(8,4) NOT NULL		# 4 Байта
	quantity MEDIUMINT NOT NULL		# 3 Байта
	oper TINYINT NOT NULL			# 1 Байт
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

[Армянское Радио]

Иван Мельников, дальше вы на этих данных запускаете бенчмарк и смотрите, а какой компонент системы у вас уходит в полку. Лично мне с дивана кажется, что база на SQL здесь может быть несовсем уместна, но нужно смотреть на характер нагрузки - как часто и как долго.

[Иван Мельников]

Армянское Радио,

И он поддерживает RDMA (это вот когда вы говорите "забирать данные с сетевой карты напрямую", вы наверное это имеете в виду).

RDMA — аппаратное решение для обеспечения прямого доступа к оперативной памяти другого компьютера при помощи высокоскоростной сети. Такой доступ позволяет получить доступ к данным, хранящимся в удалённой системе без привлечения средств операционных систем обоих компьютеров.
У меня другое. У меня банальный QUIK принимает поток информации с мосбиржи и он же может генерить INSERT'ы в мою таблицу.

[Saboteur]

Иван Мельников, непонятно. Например InfluxDB и подобные TSDB не любят текст в принципе. любят только числа. И если oper это varchar(1024) то вообще лучше не трогать такое

[hint000]

Saboteur,

И если oper это varchar(1024)

но откуда? автор пишет:

oper TINYINT NOT NULL # 1 Байт

[Алексей Горбунов]

Если SELECT без условий, а только на новые записи, то лучше все в файл складывать, который в оперативной памяти будет лежать. Просто порциями создаешь файлы в оперативке с рандомными названиями и данными, а уже файлами обработчик занимается. Быстрее записи в файл, лежащий в ОЗУ, еще ничего не придумали.

[smart85]

А не варик ли master-slave replication запилить и селектить на слейве?

[Yury Komar]

А транзакция в MySQL разве не сможет справиться с задачей?

Answer 1

[rPman]

лить в таблицу (ticker, price, quantity, oper) ежесекундно ~1 млн. строк

колись, у какого брокера и за какие деньги ты получаешь эти данные такого объема?

Есть данные типа level2/3 (когда вместе с событиями trade тебе льют depth update, изменения в стакане или сами события в стакане, это данные дорогие, доступ на большом рынке тебе дадут только с машины в датацентре брокера, где надо платить еще и дорогую аренду сервера. В мире криптовалют эти данные пока бесплатны, к примеру один binance (крупнейший поставщик биржевых событий, сравним с ними coinbase точнее gdax остальные в сумме наверное от силы столько же дадут) и тот дает порядка 4 тысяч событий в секунду, максимум что я от них видел.

По теме вопроса, всегда, в первую очередь нужно задавать вопрос не как и где хранить данные а как ты их будешь читать. Судя по теме с высокой вероятностью тебе не нужены отдельные случайные события, а нужны данные блоками, на интервале, поэтому и в базе хранить данные лучше этим блоками (вот тут уже надо считать, проводить бенчи на основе твоих данных и твоих мощностей), скорее всего тебе хватит почасовые массивы, тогда при любом запросе потока на момент времени x-y тебе нужно читать минимум две записи, это сотни миллисекунд, плюс фильтрация, на эту уходят десятки миллисекунд даже на php, если в базе данные удобно сериализованы, дольше передавать и обрабатывать будешь.

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

Так вот, хранить данные можно буквально в файлах, файловая система - отличная key value база данных (дели по файлам и каталогам на основе валютной пары, биржи, и временного интервала, но на время лучше индекс заводить), работать с такой базой неудобно только при обслуживании (backup/restore) но если изначально организовать хранилище в отдельном разделе, то и работать с ним напрямую.

Одно время я хранил данные в gzip json, но недавно открыл для себя igbinary, чудесная вещь, бинарный при этом тоже пакуется, файлы храни на btrfs со включенным сжатием zstd ultra.

Comments

[Иван Мельников]

Судя по теме с высокой вероятностью тебе не нужны отдельные случайные события, а нужны данные блоками

Всё верно.
Изначально предполагалось, что работать будет так.
В таблицу с полями (ticker, price, quantity, oper, action, created) льются данные по ВСЕМ выставленным лимитным заявкам по ВСЕМ тикерам. И 1 раз в 10 мс из С++ или Python- кода делается

SELECT *
FROM orders
WHERE -- условие для выбора строк, поступивших за последние 10 мс --

Далее, уже в коде C++ или Python делается отбор из результатов SELECT-а записей для каждого отдельного тикера, затем прибавление их к массиву уже накопленных значений для данного тикера и в итоге перерасчет фичей и отработка модели.

Так вот, хранить данные можно буквально в файлах

Но как это сделать, если речь идет о QUIK 9? QUIK 9 просто генерит INSERT-ы и UPDATE-ы для каждой отдельной сделки. Знаю, речь шла о заявках, а я теперь говорю о сделках. Но допустим, что заявки экспортируются из QUIK точно так же. Как их дописывать в файл?

[rPman]

Я вроде расписал более чем понятно

У тебя должен быть бакэнд, который принимает(читает) данные quik, исторические данные (старее некого интервала, например час) складирует сериализованными массивами в файлы с именем и каталогом, содержащими информацию о бирже, валютной паре и времени (начало интервала, а точнее abs(t/interval)*interval - эта формула из любого времени выдаст файл, в котором даные о событии), а данные по текущему интервалу хранит в оперативной памяти.

Твои insert и update храни как есть в логе, а в файлы сериализуй уже в своем формате, два типа событий trade (time,price,amount,type) и update (time,price,amount,side) либо (time,{bids:[[price,amount],..],asks:[[price,amount],..]}) amount +- означает увеличение или уменьшение объема по цене, 0 означает удаление, смотри сам, второй вариант компактнее

Если нужна надежность то у тебя должен быть промежуточный слой, складывающий лог событий в файл (его можно продублировать на разных серверах), который читает и обрабатывает бакэнд (очередь FIFO), в случае остановки бакэнда он просто прочитает пропущенные данные из лога.

Этот бакэнд не обязательно должен возвращать данные, так как они локально доступны в файлах, пусть твои приложения запрашивают у бакэнда список имен файлов, которые нужно прочитать чтобы получить запрашиваемые данные, для файла который содержит текущую голову потока пусть возвращает не имя файла а url к бакэнду на получение его.

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

upd. еще советую собирать стакан (текущий список bids/asks) на указанный момент времени на бакэнде, храня собранный на начало периода или периодически, довычисляя его из лога update, это понадобится для тестирования стратегий, использующих стакан для анализа или хорошей визуализации ситуации с ликвидностью (тепловые карты рисовать).

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

[Иван Мельников]

rPman, спасибо! Всё очень по делу.

[rPman]

докину сверху, переделваю сейчас у себя организацию хранения данных логов сделок с криптовалютных бирж (к сожалению данные дырявые, много пропусков из-за ошибок в коде):
* на дешевых впсках крутятся простенькие скрипты, складывающие в текстовые логи дампы событий (что возвращает websocket), файлы нарезаются по времени, пакуются gzip и забираются моим отдельным сервером (раньше он и хранил, жутко неудобно работать с разрозненными данными, плюс там json-ы с лишней информацией, не эффективно хранить, если что один бинанс может выдать в сутки 5-6 гигабайт gzip-ов)
* будет крутиться отдельный бакэнд который будет переводить в общий формат хранения как я описал собранные данные
* итоговые файлы хранятся на отдельном файловом сервере, btrfs, включенное сжатие
* дальнейшей обработкой занимаются остальные машины, подключая по сети файловый сервер и забирая нужные файлы.

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

[Yury093]

похоже kafka + in mem база - ваш вариант

[rPman]

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

Answer 2

[Сергей Горностаев]

Выглядит так, будто вам нужно что-нибудь вроде Kafka.

Answer 3

[Yury093]

Конечно может, вопрос в железе. И микроскопом можно забить гвоздь.
Но на слова "хочу быстро вставлять и быстро читать потоком" так и хочется ответить "а зачем тебе БД?"

Поэтому хотелось бы уточнить у автора: а вот кроме описанного "вставить миллион, считать миллион" - что предполагается делать с данными? Менять их построчно? Искать по какому-то ключу? это все надо? Если нет - я бы все же рекомендовал не использовать БД.

Тут следует понимать что любая нормальная БД это [почти] всегда двойная запись на диск: вы пишите в таблицу И в лог базы данных. Именно поэтому файл или Kafka или иной MQ будет всегда быстрее.

Ну а если БД все равно нужно - ну тогда BULK режимы вам в помощь. Обычно они используются для пакетной инициализирующей загрузки. В некоторых БД они на время своей работы могут отключать какие-то фичи или даже логирование в лог транзакций.
----------------------------
Вообще по всем признакам в вашем случае идеальным будет вариант писать в MQ (RabbitMQ или Kafka или см аналоги), а уже из нее в БД. "Все так делают", по крайней мере в крупных компаниях это довольно типовое решение для подобных вашей задач. Причем БД в этой истории нужна только если вам потом нужно хранить и селектить. Если после первой операции данные вам более не нужны, либо нужен только бэкап, то БД не нужна - пишите в файл, пакуйте в zip (в энтерпрайзе - кидайте файлы в Hadoop в каком нибудь Parquet формате).

Comments

[Crash]

В этом случае данные можно также писать в Redis, я так делал для часто обновляемой инфы. Любой объем и любая интенсивность, главное чтобы оперативки хватало)

Answer 4

[Александр]

ИМХО, про Clickhouse незаслуженно забыли, если нужно хранить и какую-то аналитику использовать. А ведь он реально не тормозит, скорость сравнима с простым сбросом сырых логов на диск. Горячие данные на SSD или вовсе в памяти, во временных таблицах можно держать. Главное, батчами вставлять данные, но мелкие вставки ни одна база по моему не любит. Если для гарантированной доставки кафка или кролик будет юзаться - они нативно поддерживаются, но следует учитывать, что дополнительный слой == дополнительные просадки по времени.

если нужно хранить условно за час, то и вовсе стоит смотреть на Aerospike, да и вообще, если этот же час нужно держать в близком доступе, он будет полезен. его незаслуженно мало упоминают в русскоязычном сообществе, а по факту он на голову превосходит всякие редисы, даже бесплатная версия. А также он умеет в постоянное хранение, комбинированное и даже с диском, как с блочным устройством напрямую работать, без ФС

Инфлюкс - он и вовсе про другое, это временные ряды, метрики. Как и Prometheus и Victoriametrics

P.S.: не ради срача и троллинга, но все же староверов, которые в файловой системе хранить все предлагают, мне хочется спросить: Господа хорошие, а вы свои проекты наверно до сих пор храните в виде Новая_Папка, Новая_Папка1, Новая_Папка2 и т.д.? Нужно все таки смотреть на алгоритмы записи, работы с железом и прочее, они меняются и развиваются. Ваш, да и мой тоже 2007 не вернуть

Answer 5

[ComodoHacker]

>Будет ли быстрее забирать данные с сетевого интерфейса напрямую, минуя БД, а уже после обработки во время простоя ресурсов складывать их в БД?

Скорее всего, так и нужно делать.

Comments

[smart85]

Главное обеспечить целостность данных при таком подходе. Дожидаясь flush в БД, где данные хранить? В памяти? Свопить на диск?

[Yury093]

Kafka (и другие MQ) системы как раз созданы чтобы гарантировать доставку сообщений. Этой их главное предназначение. Flush в БД медленный потому что БД нужно писать данные в две области. Kafka пишет в одну. Но и это не все: там еще кеширование ОС накладывается и вопрос какие диски.
Kafka можно настроить хорошо даже используя HDD (а не дорогие SSD).
Но как именно настроить я не подскажу (я архитектор, админ у нас другой человек)

Answer 6

[ThunderCat]

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

Comments

[Yury093]

Лог она все равно пишет. Все равно х2 будет нагрузки на диск. Не увидите вы разницы на этой задаче с другой БД. Да, Тарантул получше для CPU, но в данной задаче узкое место не CPU, а диск, поэтому разница будет в пределах погрешности изменений.

Для таких (быстро вставить дофига и сохранить) задач специально делали Kafka, если честно. Тарантул делали для других задач (ML). Замечу что Kafka + Tarantool - да, вот это наверное самое то им.

Answer 7

[Afatar]

kafka или elasticsearch

Comments

[Yury093]

лучше "и" - вначале Kafra, из нее - Elasticsearch. Elasticsearch тратит много CPU на индексирование. Kafka позволит нагрузку размазать по времени. В противном случае придется ставить такой CPU который выдержит пик.