База данных за один вечер и три бутылки пива

Для начала – о том, как делать не надо. Вот представьте, что вам надо сохранять в какой-нибудь базе данных показания каких-нибудь датчиков, чтобы потом показывать их в модном веб-приложении. Вы уже хотите написать что-то в таком духе?

CREATE TABLE measurements (
    measurementID int NOT NULL AUTO_INCREMENT,
    sensorID int NOT NULL,
    time datetime NOT NULL,
    value double NOT NULL,
    PRIMARY KEY (measurementID)
)

Это очевидно неправильно – имел удовольствие несколько лет подряд наблюдать за эволюцией небольшой SCADA-системы, где хранение данных организовали примерно так. Пока датчиков было немного – оно работало, но примерно на сотне-другой обновляющихся примерно раз в 5-10 секунд датчиков потребовало для работы некоторых костылей. Для начала – в БД перестали записывать “небольшие” изменения данных – скажем, меньше 5% (величина была выбрана довольно произвольно – не надо спрашивать, соответствует ли такой подход требованиям технического задания :) ) от предыдущего сохраненного значения. Затем – по мере накопления данных соорудили чудовищную систему из триггеров и хранимых процедур, которая автоматически создавала “архивные” таблицы сравнительно небольшого размера. Впоследствии, конечно же, выяснилось, что эта система временами глючит и иногда новую таблицу для архива надо создавать вручную.

Наконец, на одном из объектов в дополнение к привычным “медленным” газоанализаторам поставили два десятка акселерометров – и тут все повалилось уже серьезно. Во-первых… нет, для начала в нулевых. Не надо говорить мне, что datetime в SQL неспособен хранить данные с “разрешением” меньше секунды, а опрашиваемый раз в секунду акселерометр бесполезен – тут речь шла уже о том, чтобы записать в базу хоть что-то. А теперь во-первых – показания акселерометров меняются сравнительно быстро и под фильтр “небольших” изменений данных не подпадают, и во-вторых – отказы БД стали чуть ли не ежесуточными. Считайте сами – в сутках 86400 секунд, и при двух десятках пишущих что-то датчиков мы влегкую получаем 1,7 миллиона записей в сутки. Да, часть из них отфильтруется – но и миллиона строк хватит, чтобы популярные СУБД с SQL хорошо и надежно “легли”.

Признаться, я списывал многое из этих проблем на невысокую квалификацию разработчиков этой системы, но после доклада Сергея Аксенова “Антипаттерны разработки программных комплексов для интернета вещей” на Inothings++ понял, насколько героические люди используют SQL для хранения такого рода данных. Про выбор СУБД в видео – с 4:28:

Идея понятна – реляционные СУБД расчитаны на количество строк порядка миллионов (десятков миллионов – если у вас поблизости есть грамотный администратор баз данных), а как легко посчитать – это всего лишь суточный архив сравнительно небольшой системы. Впрочем, случай “Стрижа” еще более-менее прост – частота опроса каких-нибудь ЖКХшных счетчиков относительно небольшая, всего несколько раз в сутки. А что делать, если хочется хранить, скажем, данные по 16 каналам АЦП с небольшой в общем-то частотой дискретизации в 16 тысяч выборок в секунду? Нет, я прекрасно представляю, как это сделать “на файлах”, и это совсем не сложно – простенький формат хранения данных можно полностью реализовать за неделю неспешной работы (собственно, что-то наподобие edflib, но работающее на микроконтролере с сотней килобайт памяти я написал в прошлом году где-то за несколько дней):

Implementing EDF takes a week. EDF+ takes a few weeks but is better and more powerfull. If you still decide to start with EDF, it is wise to adopt the 12 simple additional EDF+ specs.

Не смотрите, что он предназначен для записи всяких физиологических данных – я в таком виде пробовал всякие обороты двигателя, расход воздуха и прочее время впрыска записывать, они в этот формат прекрасно вписываются :)

Еще из полезного чтения – статья Luca Deri о системе, названной им tsdb – построенной поверх Berkeley DB системе, обеспечивающей хранение миллионов (!) временнЫх рядов (time series – собственно, это общее название для такого рода данных). Решение не совсем подходящее для моего случая – но опять же содержащее несколько полезных идей.

Для начала – система, многократно превосходящая по производительности решения на основе SQL, реализована всего лишь в 1000 или около того строк кода на Си. Именно это и вдохновило меня на написание собственного решения – 1000 строк это совсем немного, а следовательно, должно быть довольно просто. Второе – данные желательно каким-либо образом объединять – если в tsdb это сделано довольно произвольным образом (в базе много редко обновляемых временных рядов), то в моем случае я просто решил писать “секундные” (или соответствующие какой-то доле секунды) записи, как в EDF.

В общем, осознав эти “три источника и три составные части”, я взял пивка и засел за Visual Studio – и примерно за вечер написал прототип базы данных, оптимизированной под мои требования – десятки-сотни одновременно записываемых временных рядов с частотой дискретизации от единиц до тысяч Гц. Собственно, по факту получился тот же EDF, только записанный в Berkeley DB. Выбор довольно произвольный (прежде всего – ее используют в tsdb) – но эта, так сказать, “встраиваемая СУБД” поразила меня своей простотой. Немного не радует лицензия – хотя при желании могу выложить код на какой-нибудь гитхаб для очистки совести.

Что могу сказать? Не прилагая никаких усилий в части оптимизации, на собственном ноутбуке я “из коробки” получил скорость записи в базу данных около 40-50 Мбит/с (похоже, упираясь в производительность жесткого диска) – причем, судя по всему, падения производительности с ростом числа записей в БД не происходит. Например, в БД размером около 11,5 Гб – это чуть меньше 1,5 миллионов записей по 8 килобайт (в каждой из записей – по 4000 “точек”, каждая из которых – двухбайтовое целое) никакого снижения скорости записи или чтения я не наблюдал (дальше просто стало жалко жесткий диск). Что забавно – тесты “на чтение” демонстрируют полезность кеширования – сначала 10 000 записей читаются за долгие 8 секунд, а затем, если повторить тот же тест “не отходя от кассы” – за 0,22 секунды. Не видел бы – не поверил.

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

PS А если вам все же нравится SQL – почитайте, как правильно организовывать хранение time series в реляционной БД:

https://blog.timescale.com/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c

И все-таки хочу напомнить, что решение “на коленке” практически не уступает приведенному в статье по ссылке в плане производительности (и да, надо бы проверить его с четырехгигабайтным кешем :) ).

3 комментария

  1. Sergey пишет:

    Спасибо за видео и статью. Но так и не понял, зачем было городить какую-то базу, когда достаточно просто писать последовательно (размер данных фиксированный?). А потом на ПК сконвертировать в какую угодно базу.

    • Ну собственно никто этого не запрещает (ту же tsdb сначала делали на memory-mapped files), но:

      - это тяжело переносится между *nix и Windows;
      - есть некоторые проблемы с параллельным доступом;
      - сложно учитывать, скажем, нарушенную по каким-то причинам последовательность пакетов с данными – с устройства на компьютер по некоторым причинам приходится передавать по UDP, а он в этом плане может дурить.

    • Немножко поподробнее напишу. В планах вообще довольно гибкое решение, в первом приближении выглядящее примерно так: имеется “регистратор” – девайс с АЦПшкой, SD-карточкой и WiFi (аппаратная платформа – TI CC3200 или 3220); данные с АЦП (ну или не с АЦП даже, а “подслушанные” с автомобильной диагностической шины) он чуть-чуть обрабатывает и пишет на SD, а заодно передает по WiFi на телефон с Android или какой-нибудь Raspberry Pi. Тут тоже можно обойтись записью в файлы, особо не заморачиваясь. Дальше хочется передавать это все в интернет (используя телефон или Raspberry только в качестве промежуточного звена; в принципе, можно и сразу цеплять CC3200 к точке доступа – если проблем со связью не предвидится). Ну и вот тут уже хочется иметь что-то более интересное, чем тупо запись в файлики; заодно хотелось понять, какие пределы по скорости и количеству одновременно работающих устройств будут у такой системы.

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

Ответить

Или воспользуйтесь входом по OpenID: