Is SQLite a sensible option for data logging?

I am trying to setup a small data-logging application under linux. The data arrives via serial port, and from there it should be pumped to logging and to graphical display.

The logging functionality is there for initialization purposes of the graphing functionality, after a reboot.

The data arrives every second and is composed of 1 number of 5 decimal digit precision, and should be logged for 2 years. My current free space is about 15GB.

The languages of the project so far are bash and python.

My objectives are to make simple but robust (maybe some live backups to a flash drive?) logging system, that is also fast and easy to read from python and write from bash.

Is SQLite suitable for this (I have never used it)? Is a text file in my home directory suitable for this?

Both options seem valid to me. In such cases, a useful rule to apply is to do the Simplest Thing That Could Possibly Work. Text files are easier to get started with and are expected to work reasonably well at least in the beginning. Once requirements arise that are better satisfied using a database, it will be trivial to import them. Using this strategy, you postpone design decisions as long as possible (but not longer than that). As such, you don’t do unnecessary work. When, if ever, it will be needed, you will have a much better understanding of what exactly it is you need. Hence, you are more likely to build the Right Thing and not waste time building the Wrong Thing.

I would say that given the requirements for robustness and longevity that a plain old text file is a better choice. As others have noted, your data rate is not high; also, your post-processing is all offline from the logging system so you need not write your data in the most efficient manner because you can read it back at leisure.

A filesystem is a database (although a degenerate one), and appends to text files are very light on system resources. It would be hard to beat

echo data >> mylogfile

for minimal system call activity. Also text files are much more robust in the face of internal corruption than a sqlite db and especially a pickle file.

I’m guessing that your application is a remote, unattended sensor, somewhere not easy to get an operator to. In that case, the lowest tech solution should be preferred.

Yes. If you set the WAL, synchronous=NORMAL pragma, and batch your inserts, you can write to an NVM drive at about the same speed as a flat file.

DefaultEntryStoreBenchmark.benchmark  thrpt  20  803206.197 ± 54816.464  ops/s

That’s roughly 803 inserts per millisecond. For comparison, a file appender with immediateFlush=false is roughly ~1789 ops/ms. See SQLite section in this article for more details.

For what you’re doing, it sounds like Sqlite µLogger might be a good fit.

As @gbjbaanb pointed, SQLite “is not designed to insert very many records at high speed.”

I’d say 1 number / 1 second is not high speed if you keep the connection to the database open. And that should work reasonably fine, but logs are usually made on files, that solution is more conventional and maybe elegant due to that.

I’d use text files and I would create a new one every month. You can compress and/or delete old files after some time that may suit for you if the HDD space is a problem.

If “The logging functionality is there for initialization purposes of the graphing functionality, after a reboot.” and you only need a time window with the last n elements then you can add a finally block to your code and use pickle to dump some data structures and load them after the reboot. But make sure before that the reboot is actually allowing you to do what you need.

That is probably the solution that requires less resources and fits more tightly to your requirements.

No. SQLite is a great tool, but it is not designed to insert very many records at high speed.

The best solution here is something that can be appended to quickly, and that’s a file. Such a file can be located anywhere you like, on Linux they are usually put in /var/log (as some admins prefer to put /var on a separate partition so if a rogue app write a lot of data, it won’t crash the OS when it fills the disk up).

Once the data is written, you can write a tool that reads the log files, imports them into a SQLite DB and uses that for reporting.

Alternatively, use an existing tool such as Nagios. Write a plugin for it (if you can’t configure it to recognise your data directly in the log file) and use that for monitoring and reporting.

Yes. SQLite is just a binary file. You just need to have an empty skeleton for your database once, then you are able to just append data to that binary file to fill up the “database”.

In a former company we have implemented this for the Symbian OS (Using the c++ language), doing some kind of logging too. Unfortunately this was a while ago and I can not remember the details.

Filed under: softwareengineering - @ 15:08

Thẻ: logging, python, sql