To solve this properly you would have to calculate min/max/average for longer timeframes – ideal would be to have more then 1 length for these periods (for example 30 min for a week and 1 day for a year).. Other problems are handling periods, when no data is received.
I realized if i continued to use sql this was going to be a quite a big project. And probably quite messy.
I was disappointed. As I am lazy I had a look at Cacti, and thats when I discovered RRDTool – that Cacti uses to store and render the measurements.
What is RRD-Tool?
RRD stands for Round Robin Database. The concept is quite simple.
Defining the Database
Imagine you had 100 simple containers that you can fill with values. You start at the first one, then put the second value into the second one and continue until you reach container 100. After you are there you start at the first one again and fill until you are at container 100 again. Round Robin – you get the idea 😉
This would be quite useless in my case – because I want to store a whole year off weatherdata. Thats were the magic happens.
With rrdtool you can create a database that contains more then one RRA (Round Robin Archives).
The definition for my Database looks like this:
rrdtool create weatherdata.rrd –step 60 \
Lets disect this:
The database will be stored in a weatherdata.rdd and excepts data every 60 seconds.
The lines like [code lang=“bash“]DS:humidity:GAUGE:120:0:100[/code] specify which values should be included in the RRD. In this case its the humidity, which can be between 0 and 100 percent.120 means, that after 120 seconds the value is filled out with an unknown.
The RRA lines define the different Round Robin Archives.
I use an RRA that contains a sample every 60 seconds for 1 week.
Then I have 3 bigger RRA’s that store the weather data for up to one year. Every 30 minutes a new entry in this RRA is created. One for the maximal-value off the 30 samples, 1 for the minimal value and 1 for the average.
Thats quite nice – because RRD-Tool handles all the uglyness for me. For longer periods of time it stores less data and after a year it will not get any bigger.
Putting stuff into the database
After the database file is created you can put data into it.
rrd update weatherdata.rdd N:10.55:11.452:88:20:5:95654
The N means now and the you need to append a „:“-seperated list of values. The order is the same as you ordered your values in the database definition.
The last part is drawing some graphs. I found this part the most confusing one.
There are very nice examples on the rrdtool page, but there is no code next to them. The documentation is kind of confusing for me as a beginner.
rrdtool graph temperature.png \
–height=200 –width=750 \
–start end-1d –vertical-label "°C" –title "Temperatur letzte 24 Stunden" \
But after a little while I managed to draw some basic graphs.
You can view the project on GitHub. After I discovered rrdtool I dropped Rails and replaced it with Sinatra and Rake.
I still use SQL as alternative if I want to have the full resolution of my samples in a few years from now.
The graphs are generated with a cron-job every 5 minutes. With rrdtool a complex problem was reduced to a simple and manageable task. Thanks to the creator for such a wonderfull tool. It will remain in my toolbox, to solve similar problems.
Also I found these ressources quite helpful.