Logging 1-wire readings to a database

[MN]

I'd avoid relational databases of any sort.

The problem is that even with my small set up (with 20 sensors) I get over 500k records (after compression) per month.  A yearly analysis take s a few seconds – without a relational DB my guess is it would be in the minutes / hours.

I may be wrong

MN

[wyleu]

That is easily put on the other side of a client, my django head likes relational for doing all that permissions stuff that seems to keep people on their toes nowadays.
The actual state stored on the event_manager ( for want of a better name ) is much simpler.
I don't think we should cache except as a client. Keep the static data readings out of the core.

[MN]

Did You transfer the old data from Access to the new system? I don't think I would...

Yes I did. 

Why wouldn't you? 

[SimonHa]

Well, I hadn't expected such a debate (especially not relational vs no SQL!). I just thought this was going to be a thin layer to sit on top of owfs writing readings to a noddy database.

As this system will have to run 24/7, I would have thought that there is a strong requirement for it to run on a very lightweight server - a Slug, something Atom-based or the current crop of nippy ARM/Kirkwood devices (Guruplug, Buffalo LS-XHL NAS, etc) - which will "only" use about 10W power. Hence my suggestion of SQLite (which I've never actually used but sounds simple & attractive).

[wyleu]

SQLite is a no brainer for python types it comes installed. I'll stick with what I know on first branch. Keep asking me where it is, and smite accordingly!

I've built a base git repository. Anyone got any visible space they'd like to clone it to?

[ericw]

As this system will have to run 24/7, I would have thought that there is a strong requirement for it to run on a very lightweight server - a Slug, something Atom-based or the current crop of nippy ARM/Kirkwood devices (Guruplug, Buffalo LS-XHL NAS, etc) - which will "only" use about 10W power. Hence my suggestion of SQLite (which I've never actually used but sounds simple & attractive).

If you didn't need instant access to the most recent data, you could be writing it to an SD card with a readily available Arduino based system consuming milliwatts and transfer it into a database running on a real machine if and when you wanted to update it.
 

[EccentricAnomaly]

I've built a base git repository. Anyone got any visible space they'd like to clone it to?

I haven't but github has.

[wyleu]

I know, I know but I was trying to land a Wookey but one doesn't like to say too much...

[wookey]

Yes. I have space on wookware.org. And am already running a git server on the machine (but not that domain) and an svn server on that domain. mail me the pull address and I'll set something up.

re couchDB - It is very cool, but my (limited - I've not actually used it for anything) understanding is that would not be well suited to the very small record sizes we will be generating. It is excellent when your data entries are more 'web-page' sized. Although I just asked a man across the room (I'm in a conference with 150 geeks) and they thought it wasn't such a daft idea (apparently it provides disconnected operation for free), but they'd just use SQL or a text log.

I think in fact that the 'correct' answer is that basic logging only needs to be done to a very simple format (e.g. text log file), but that data needs to end up in a reasonably efficiently interrogatable form (e.g. an SQLite database) if you want to do interrogation, such as for graphing, historical analysis etc. And that the buffering aspect is important for when the net connection goes away.

Some sums on just how much space per month a reasonably large system might use if it's all bunged in various formats would be helpful. And we do need to consider very long-term use, where monthly or annual chunking is a good idea.

[SimonHobson]

Coming into this a bit late, but I'm not too clear what people are using the database for. A 'DB' option not mentioned is an RRD database.

If you're not used to such a beast, it can be hard getting your head around it, but it does all the work that many applications are after.

It does NOT store arbitrary data at arbitrary times - stick with a conventional DB if you want that. What it does do, and does very well, is to consolidate and store data over a specific time with s specific resolution. For example, you could set it up to store :
A sample for each 5 minute period over a couple of days.
Consolidated values over 1/2 hour periods for a week or so.
Consolidated values for 2 hours periods over a couple of months.
And daily values over several years.
The consolidations can include min, avg, and max values - so you can easily draw a graph with a line for average, and shaded area to show min and max.

RRD tools take care of all the normalisation and consolidation - all you have to do is keep stuffing values in. The file size is fixed when you create it - so no DB files that keep growing and need pruning from time to time.

I think for some of the things being discussed here, it could be the right tool.

[roscoe]

I'm rather spoiled in the commercial field, things like Osisoft pi or Ge Proficy Historian.

These guys kinda reckon relational databases are pants for process data like temperatures or even solar energy.  Why, well compression mainly, so if the temperature does not change but more say 0.2 oC then why log the same value every x secs or at night for solar the same -6w for hours on end.  The benefit is less stored so less to write and less to retieve, in simple terms a change of state logger, nothing fancy.  End result rapid response less to store and backup.

So any open source offerings ?

My feeling is no, because the community is microscopically small and there are so many databases that do a fair but not excelling job.  I recall a few custom change of state offerings but they never managed to offer all the expected external interfaces.

In summary use a RDB for relational data and use a decent change of state logger for process data.  Of course many disagree, but over time data quantities do spoil things, keeping things compressed but realistic is worthwhile, especially for the long term.