Means of sensor and device value storage

Pop your questions regarding Home automation software here.....
Post Reply
airox
Member
Member
Posts: 214
Joined: Sat May 15, 2010 10:42 pm

Means of sensor and device value storage

Post by airox »

Hi everyone,

In a lot of domotica software we save our data from sensor readings and device switches to the database. In a usual household this is getting a large number of readings very fast. I calculated mine to be like 8 million readings a year. In those values you can notice several types of values. Having a year of history seems the minimal. I use this for checking my gas usage bills and electricity bills, etc.

When these values are available in the database it's quite easy to generate graphs from it. These graphs deliver a lot of information which you can use for optimizing energy and other stuff around your house. Like turning off the heating in the evening and knowing the temperature will only drop below a comfortable value when you are already in bed.

I was wondering if people over here who have written their own software have ever thought about a efficient way of saving this huge amount of values. My last optimization by seperating types of values in different database tables for example was one way of getting things faster. This resulted in my usage costs page not taking 30secs to generate but only 3 secs. Presenting all the current values of my house now only takes 0.3 seconds instead of the 7 seconds it was before.

What are your experiences and ways of handling and saving this much of information? Does your domotica software become slower the further the year it is. Do you archive data outside of the system or only save aggregated values inside the system?

Looking forward for the experiences.

Greetings from Arnhem, The Netherlands

Gijs
Digit
Global Moderator
Global Moderator
Posts: 3388
Joined: Sat Mar 25, 2006 10:23 am
Location: Netherlands
Contact:

Re: Means of sensor and device value storage

Post by Digit »

Hi,

I've made my own software and have been collecting data for a few years now. To give you an idea, here some numbers:

DBMS: MS SQL Server 2003 x64
Database Size : 785 MB
Nr of tables : 22
Timestamp oldest data row : 01-09-2006

I save all the historical data into 1 table (besides some "specials" like phonecalls, ledbar messages etc.).
The rest of tables are for defining events, devices, interfaces, etc. etc. and relatively small (max. 500 rows per table), in fact defining how the software should operate.

The table that holds all the historical data now contains almost 507000 rows and I don't see any degredation in performance with this ever growing database. Some historical values are stored on a hourly basis, some every 10 minutes, depending on what I want to do with it.

I don't store everything permanently; for instance, things like temperatures of less important rooms are only stored for 30 days and after that they are deleted again. Others are stored permanently (power, water, gas and some more); all this is configurable on device value level.

When I look at the home page of my website, it needs 64 queries to retrieve all the information that's being shown from the database; this is done within 0.2-0.25 seconds the last time I checked, so I think that's ok.

A database needs maintenance just like the rest; if you just throw everything in and don't carefully think of how, what and how much you store it can get pretty messy; for example, every night a series of queries is automatically executed to keep it 'healthy' - mostly to delete old data I don't need anymore. And when I create new graphs or queries, I always check if the db engine can find a suitable index to speed things up; if not, I create a new one.

I don't archive data outside this database and also don't aggregate.

Greetings from Rheden :wink:
User avatar
structor
Member
Member
Posts: 125
Joined: Tue Sep 22, 2009 8:12 pm
Location: Netherlands

Re: Means of sensor and device value storage

Post by structor »

I am currently building my own home automation system and from my guestimate my current historic table will have something like 27k on records per year (extrapolating from 2 months of statistics). This will probably be more when i will have more ('virtual') devices. In my historic value provider i have exclusion lists what to log and what not. This feature keeps my tables more lean.
One of the items on my todo list is also energy aggregation. I have not thought about howto implement this yet. So perhaps i will also get some trouble there, but 30secs feels long to me. Anyhow interesting thread hope to learn something here...

O i am currently using sql-lite as my db, but i use nhibernate so i can swap easily to another db engine.
airox
Member
Member
Posts: 214
Joined: Sat May 15, 2010 10:42 pm

Re: Means of sensor and device value storage

Post by airox »

Rheden? That's pretty close from here :-)

A current view of my data gives these statistics:

888086 temp 19,45%
770616 moisture 16,87%
670504 elecusage 14,68%
575824 gasmeter 12,61%
569323 gasusage 12,47%
109518 motion 2,40%
71958 rain 1,58%
71070 speed 1,56%
43934 int 0,96%
35414 pressure 0,78%
20101 direction 0,44%
14851 lux 0,33%
10054 open 0,22%
8640 kilometer 0,19%
6569 switch 0,14%
2905 lightdark 0,06%
1807 button 0,04%
1444 vailable 0,03%
104 battery 0,00%
56 boolean 0,00%
33 dimlevel 0,00%
3,864 gps position

This gives some perspective on what types are generating a lot of readings. I could delete data occasionally, but I still want to be able to abstract information for it. You never now what you would want to look into is my opinion :-)
User avatar
structor
Member
Member
Posts: 125
Joined: Tue Sep 22, 2009 8:12 pm
Location: Netherlands

Re: Means of sensor and device value storage

Post by structor »

How many temperature / moisture sensors do you have? Or are they polling just so often?
airox
Member
Member
Posts: 214
Joined: Sat May 15, 2010 10:42 pm

Re: Means of sensor and device value storage

Post by airox »

Three temp and three moisture sensors (updates every 30 secs). A single gas and electricity sensor (updates every 30 secs). A weatherstation with wind speed, etc (updates every minute).
And a lot of switches.
Digit
Global Moderator
Global Moderator
Posts: 3388
Joined: Sat Mar 25, 2006 10:23 am
Location: Netherlands
Contact:

Re: Means of sensor and device value storage

Post by Digit »

Gijs,

Ever thought about only storing (relevant) changes in values? Cause I get the feeling you store historical data even when there's no change in value?

When we don't use water in our house (like when we're away for a weekend or on vacation), no water usage data is written to the database; because it doesn't provide any information. Another trick is being able to store changes based on the number of significant digits (decimals). For example, you could consider only storing new temperature values when the 1st decimal changes; are you really gonna use that 2nd decimal?
Combined with a maximum storage interval, cause that makes life easy for graphs :)
airox
Member
Member
Posts: 214
Joined: Sat May 15, 2010 10:42 pm

Re: Means of sensor and device value storage

Post by airox »

A lot of sensor types only store changes. Those that are "gliding" I store every reading. The following sensors I consider "gliding":
temp, moisture, elec usage, gasmeter, gasusage, pressure, lux, kilometer. The reason I store the values is that when a reading comes in I am sure the reading is valid for the given time. But indeed ... it is rather odd to save values when they aren't that different. Do you reduce the number of readings with this strategy significantly?

Looking at the contents of the elec and gas sensors .. these are the main sensors which fluctuate a lot.. and those are the one's I will get more and more (when Qees releases their products.... :-( ).
Digit
Global Moderator
Global Moderator
Posts: 3388
Joined: Sat Mar 25, 2006 10:23 am
Location: Netherlands
Contact:

Re: Means of sensor and device value storage

Post by Digit »

Regarding the reduction of stored data I did 2 tests.

I counted the number of rows in the database for the temperature in the livingroom, which is stored every 10 minutes if there has been a change.
The outcome: for the last 24 hours, 24 rows were stored instead of 6*24:

Code: Select all

2011-03-25 23:10:00.000    21.200000
2011-03-25 23:40:00.000    21.100000
2011-03-26 00:10:00.000    21.000000
2011-03-26 00:50:00.000    20.900000
2011-03-26 01:20:00.000    20.800000
2011-03-26 01:50:00.000    20.700000
2011-03-26 02:20:00.000    20.600000
2011-03-26 02:50:00.000    20.500000
2011-03-26 04:20:00.000    20.400000
2011-03-26 05:50:00.000    20.300000
2011-03-26 08:40:00.000    20.400000
2011-03-26 09:30:00.000    20.500000
2011-03-26 10:00:00.000    20.600000
2011-03-26 11:00:00.000    20.600000
2011-03-26 11:10:00.000    20.700000
2011-03-26 13:20:00.000    20.800000
2011-03-26 13:50:00.000    20.900000
2011-03-26 15:50:00.000    21.000000
2011-03-26 17:40:00.000    21.100000
2011-03-26 17:50:00.000    21.100000
2011-03-26 18:20:00.000    21.100000
2011-03-26 19:10:00.000    21.100000
2011-03-26 21:10:00.000    21.000000
However, if I do the same for the outside temperature I get 127 rows! (with the same storage rules as for the livingroom)
Only 144-127=17 rows saved...

So it's hard to give a simple answer to your question...
Bwired
Administrator
Administrator
Posts: 4704
Joined: Sat Mar 25, 2006 1:07 am
Location: Netherlands
Contact:

Re: Means of sensor and device value storage

Post by Bwired »

For every temp sensor (weather sensor) i have 1 record a day which also holds high, low etc.
For important sensors, or need graphs, i log the flow if the value changes
Im logging for more then 10 years now, so lost of records, luckyly MYSQL stores this efficiently.
Post Reply

Return to “Questions & Discussions Forum”