Представитель Шуры Люберецкого в ЖЖ (brat_luber) wrote,
Представитель Шуры Люберецкого в ЖЖ
brat_luber

Categories:

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

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

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

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

Запись опубликована в блоге Шуры Люберецкого. Вы можете оставлять свои комментарии там, используя свое имя пользователя из ЖЖ (вход по OpenID).

Subscribe

  • Что-то к хвостику прилипло

    Цитату я взял из замечательного анекдота, прочитанного в комментариях у ex0_planet. Но он прекрасно отражает сущность некоторых “шилдов…

  • С праздником всех причастных!

    –. . -. .-. .. …. –. . .-. -.-. Запись опубликована в блоге Шуры Люберецкого. Вы можете оставлять свои комментарии там,…

  • Ригонда-102

    Восстанавливаю, хотя это громко сказано, скорее – занимаюсь мелким профилактическим обслуживание – радиолу “ Ригонда-102“,…

  • Post a new comment

    Error

    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments