RDB (custom storage) support

Asked by Nikolay Baluk on 2012-06-08

Hi there.
Now I'm doing support for supply points values from relational database.
The database stores just timestamps of some events. Now I aggregate them to points with SQL-query like
"SELECT COUNT( * ) AS point_value, MAX( time ) AS time FROM `Events` WHERE `time` BETWEEN '2012-05-01 00:00:00' AND 2012-06-01 00:00:00' GROUP BY EXTRACT( DAY FROM TIME ) ORDER BY `time`"
where `time` column is column with timestamps. I also fill zero-fields (days when was not events) with zeroes in code. So I get a list of points (sum of events per day) and some step (a day in this case) and it works.
But! But this way is not universal. First of all I need to determine the step manually and I need to edit mysql query for each period (minutes, hours, days, years).

So I have a question: what is the best way to determine which step (secondsPerPoint) should be used and maybe graphite/whisper/carbon already have it (I can't find this code, just the code for step from whisper-file's header).
Also now I think that maybe graphite can make point's value for a hour from every-minute-point? If it is right, then another things will be very simple. You can just select from database with "GROUP BY EXTRACT( MINUTE FROM TIME )" and give it to some function witch gives you pointValues with a hour step.

Question information

Language:
English Edit question
Status:
Solved
For:
Graphite Edit question
Assignee:
No assignee Edit question
Solved by:
Michael Leinartas
Solved:
2012-06-10
Last query:
2012-06-10
Last reply:
2012-06-10
Best Michael Leinartas (mleinartas) said : #1

The time step is merely determined by the storage format - Whisper forces you to pre-configure the granularity of data and this is where the step originates. For any given data series requested, Whisper will send data from the highest-precision retention definition that will satisfy all the points and include the relevant step (the first part of that retention definition) so the display logic knows what to expect.

In your case, I think it makes sense to keep the step always at 1 second. This will simplify things on your end and I don't think there's really a reason to consolidate the data to a larger step - there are indeed functions which can do the consolidation to larger steps at display time if needed (summarize() for instance).

Nikolay Baluk (kolyaflash) said : #2

Thanks Michael Leinartas, that solved my question.