Storing data into database frequency

Post by **Richard** » Tue Sep 12, 2006 7:59 pm

Hello to all,

I've a short question, how often do you guys store your date from your 1-wire network / x10 network into the/a database, offcourse IF you store it? and can you tell me why you make up this decision.

At this moment i'm thinking all things all over, how often do I want to store my 1-wire network data (voltage, humidty, temperature) into my database. right now for testing purposes i write it once a minute, nice... but with one 'nasty' consequence, the db is growing extremely fast and becomes a huge thing

. (oke, my motto would be... so what... it's just a database, if he don't like it then he never had to become a database).

Your opinion's please.

Ps. a real motivation for storeing it once a minute i don't have at this moment.

TIA,

Richard

Post by **Bwired** » Wed Sep 13, 2006 12:09 am

Richard
For temperature and humidity I log one record a day. In this record I store current with time, Highest with time and lowest with time and times received.
For some temperatures I log all the data depending on what you want to do with it. Btw if you have a sql database like Mysql don't be afraid about the amount of records, I have millions of them stored now and still running smoothly!
[img]images/icon_paperclip.gif[/img] templog.gif

Pieter Knuvers
www.bwired.nl Online House in the netherlands. Domotica, Home Automation.

Post by **Richard** » Sun Sep 17, 2006 3:39 pm

Pieter,

thanks for the answer, i thought about it and i take the following approach for now (in testing stadium).

I store each minute every sensor data into a couple of tables, to know: SensorNo, SensorID, SensorValue and a TimeStamp. During the parsing i know wich kind of data i'm dealing with, so Temperature Data goes into a TmpTemperature table, Voltage Data goes into a TmpVoltage table and so on. On every 5 samples (minutes) i collect the data and summerize it (sommeer in dutch). In this way i have a Minimum, Average and Maximum value of each sensor, this data is inserted into the "real" datatables eg, Voltage, Humidity, Temperature. After inserting it into their final tables i clear the tmp tables and the process is starting all over again.

So in this way i 'bring down' my record count with a factor 5 (against the original idea of storing it every minute) and i have more data availeble on a flexible way.

I'm not really afraid of storing lots of data, but if i took my orignal idea i come to the following:
1 sensor, storing data every minute = 525600 records a year. so we are talking about a ~1/2 million records per sensor per year. And offcourse we don't have one sensor, but the idea is to get about 20+ sensors, so then i would say that ~10 million records a year "can" be a serious load for my DB (i don't use MySQL or MSSQL) so i don't know how the DBEngine it takes (never had the change to test it with this amount of data), and besides... this construction would make my tables grow very fast. And because i have the idea to buy some pc (as posted in a post somewhere on this forum) with een Embedded Win OS and no Hard drive (flash card or something) i can imagine that the storage capacity could be 'little' a problem.

So for now, the Temp08 data parser is running in a test/beta statium to find out if everything goes wel.

If i look at the reply count of this post i almost think that there a not so many HA guys that uses a Midon device to collect their data, is this correct?

Any feedback is appreciated.

With Regards,

Richard

Post by **Bwired** » Sun Sep 17, 2006 8:16 pm

Hi Richard,
I'm not sure how many have Midon, I think not so many!
I also check if the data is changed on the first decimal. So if its unchanged I don't store the data. Then I can also check in my home automation system if for some time the data has not changed.... there could be an error or something.
Pieter

Post by **Digit** » Mon Sep 18, 2006 1:33 am

Richard,

You won't be interested in the temperature in your living room on a particular date/time a long time ago, i guess.

However, if you're interested in how much your hot water usage contributes to your total gas usage over a year, you're gonna need a lot more than just daily records in your database.

So, in a way, the level of detail in your data storage is very personal.

What i want to say is, it all comes down to what you want to do with your data in the future. That should be your guide in your database design/data storage. And then choose an appropriate hardware/software platform for it. If not, you'll end up with either an oversized or a 'useless' database in the end.

Maybe good normalisation could help keeping table sizes acceptable. Although total database size will normally increase with the use of more tables, it can also increase performance quite a bit.

In fact, too many variables to give you 'the one and only right answer'. Design the database you need and store in it what you need...for now, and in the future.

Regards, Digit.

(who hasn't figured out yet what to store either...)

Post by **Niknik** » Sat Jun 16, 2007 1:40 pm

Sorry to revive this "old" topic.

I'm facing this same question myself, and would like to know how you're doing it right now (and possible any "drawbacks", or "optimizations" you would like to have if you were doing it today).

I'm waiting to receive my Energy meter logger from rfxcom, and thinking about about how I'm going to store the data.

My thoughts:

1) Logging data every 1 minute (ok, it is overkill, but it will help track down and test devices to see if they use too much power in the beginning).

2) Then, after a preset time (1 month or so) have that data "compacted" into "1 hour" blocks. This would greatly reduce the number of records per year while still providing valuable "historic" data.

Another question is: do you "log" all you sensor data in a single table, or do you use separate tables like: "Electricity", "Water", etc?

Thanks.
Carlos

Post by **Snelvuur** » Sat Jun 16, 2007 2:00 pm

Dont know which platform, but you could use rrd for this. I dont know exactly how they do it but you can read more at http://oss.oetiker.ch/rrdtool/doc/rrdtool.en.html

Basicly it allows you to store for instance 20000 entry's for 3 months, and add an extra value that stores 5000 entry's for 1 year. But both entry's use the same data. So in this case you would need 25.000 entry's for a year, with the last 3 months being really accurate. Correct me if i'am wrong

Post by **Niknik** » Sat Jun 16, 2007 2:24 pm

Thanks for you reply.

I was talking about the general concept and "architecture" for the DB structure and records.

I'll be coding it myself - in C# or vb.net and MySQL.

The idea is exactly, keep a "accurate" reading for a short time frame, and less accuracy for older months.

This can easily be done in SQL, I was just asking how people are doing it in their own homes

Post by **Willem4ever** » Sat Jun 16, 2007 2:57 pm

Hi Niknik,

I think Pieter describes how he does it, but here is my approach, every minute I take a sample and store it in my database. I pre-allocated 60 slots (0,1,2...59) On the hour i calculate the low/high/average/total and store it in the database. This allows me to always look back in detail over the last 60 minutes, and keep the average for the hour as long as i wish. This will give you 24*365 slots per year, which isn't to bad for a database. Again you could create pre-allocated slots per year as well. Anyway, i think you get the idea.

How this helps, Willem.

Post by **Niknik** » Sat Jun 16, 2007 4:02 pm

Willem, yes, that's exactly the sort of thing I was talking about.

I'll be setting up a PC to do all the home automation tasks in the next week. I'll keep you guys posted

Post by **Digit** » Sat Jun 16, 2007 5:34 pm

FWIW, 'compression' of data is not my cup of tea. You never know when details become important (in the future) for data mining or something similar. And since i've just started data collection, i don't know what will and will not be usefull to store in detail.

I just store all the data of which i think can be useful in the future. For example, my gas usage is stored every hour. Looking at the past 9.5 months, this means 4233 rows of data. For a modern DBMS, a table with that number of rows, is still 'nothing'.
I'm using a single table for storage of historical data. Currently this table has >150000 rows. Currently, I only store gas usage and temperature data in it. And much more to come...

The only tip here: don't store anything when there are no changes in the data.

Problems normally arise not when storing data, but executing query's retrieving stuff. This is where you can 'help' your DBMS a lot:

- Think carefully about the querys you execute.
- Make sure the tables have the right indexes.

Whenever my DBMS becomes sluggish, that's when i'm going to think about compression.

Regards,
Robert.

Post by **Niknik** » Sat Jun 16, 2007 7:20 pm

Hi Robert, thanks for your input.

I agree with you completely - when I said "compressing" it, was in fact reducing it to a more manageable size while keeping it valuable. That is only an issue when considering logging data every minute - which, although it might be useful for "test" purposes, doesn't really makes much sense for years of data.

Like you said, having 1h "resolution" is more than enough for water/gas/power usage, even more if you care to store "total/highest/average/lowest" for each hour.

That would make it go from 525,600 (once per minute) records per sensor per year to a more "manageable" 8760 (per hour).

I'm just considering the enhanced 1min resolution to be used in the first weeks/months, until I get a sense of what's going around the house, what's using more power, etc. etc. That way I can power up some devices, and get instant feedback in a couple of minutes, instead of waiting 1hr.

Post by **Digit** » Sat Jun 16, 2007 9:40 pm

Hi Carlos,

Well, yes, the 1 hour resolution is enough. For now. But, one example that pops up into my head once in a while, where this resolution will probably not be sufficient is: how much gas is used for heating and how much for hot water? In that case, i would need to know the destination for every measurable unit of used gas. That would mean a whole different approach... Or, to get an answer to that question, i would have to somehow 'program' the answer into the way data is stored. And actually, that's something i want to avoid. Questions always come afterwards, right?

So as i said earlier, the way you store data is very personal, in a way. I like big databases... [:D]

Regards,
Robert.