13

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?

5
  • 8
    In software world there isn't "the best"; there are only trade-offs.
    – Random42
    Aug 16, 2013 at 10:51
  • @Thijs van Dien, this comment should be an answer- it deserves at minimum an upvote for it's simplicity, if not being the accepted answer!
    – Vorac
    Aug 23, 2013 at 9:22
  • As you wish. :) Aug 23, 2013 at 12:05
  • What advantage do you believe using a database for this provides?
    – JimmyJames
    Dec 9, 2020 at 15:54
  • @Random42 It's actually a general rule in life. Everything is a trade off, there's nothing that is absolute Jan 19, 2022 at 20:01

6 Answers 6

10

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.

1
  • Very good advice. Speaking from experience I'm guessing. Sort of hit home too. Jun 23, 2019 at 18:16
11

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.

4
  • The other nice thing about using a simple text file is, if you split it by day, you can use the same technique to zip up old files using a cron job, saving disk space. Like this
    – Peter K.
    Aug 16, 2013 at 12:29
  • Yup, text file with one line per second. Simple, durable and expandable (if/when I add more sensors/quantities, they will just be appended on the same line). Here is a more general discussion of my system and here is the source code under work. I'll post an answer with screen-shots in that other answer, when functional.
    – Vorac
    Aug 23, 2013 at 9:26
  • Importantly, a text file can be flagged for append-only mode using chattr +a effectively protecting the contents from overwriting. That cannot be done with sqlite.
    – Federico
    Oct 28, 2018 at 14:29
  • Unstructured logging is brutal though, because it's impossible to extract any useful information except through NLP (or manual reading)
    – Alexander
    Dec 9, 2020 at 17:44
5

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.

3

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.

1
  • I considered your answer very deeply. However, I came to the conclusion that creating a new file every month adds significant logic to the project. Consequently, I have decided to use one hudge file. It will be archived once a month, however, for which I give you +1 .
    – Vorac
    Aug 23, 2013 at 9:20
1

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.

2
  • You would say that one record per second is "high speed"? Aug 16, 2013 at 11:18
  • no, not really, but SQLite still isn't the tool for this. Inserting into the middle of a table with 63 million records isn't going to be super fast. A text file is still a more efficient option.
    – gbjbaanb
    Aug 16, 2013 at 13:36
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.