Nifty queries over time-series data using PostgreSQL
I'm currently looking into loads of maths stuff for Fluxmon, so I eventually reached the point where I'd like to try a few things. I've even collected quite a nice amount of data by now, so I have something to go with. But how to store and analyze it?
On the Chemnitz Linux Days, Hans-Jürgen Schönig held a talk named PostgreSQL: Daten superschnell analysieren which I had attended, so I wanted to get the data into PostgreSQL and try out some of the fancy stuff he showed. Googling for postgres time series, I stumbled upon this blog post showing a coupl'a nice things too, and since the schema used by this guy is radically simple, I just went ahead.
First of all, I created a table that links into FluxMon's check and variable tables and allows me to store results:
CREATE TABLE "monitoring_checkmeasurement" ( "id" serial NOT NULL PRIMARY KEY, "check_id" integer NOT NULL REFERENCES "monitoring_check" ("id") DEFERRABLE INITIALLY DEFERRED, "variable_id" integer NOT NULL REFERENCES "monitoring_sensorvariable" ("id") DEFERRABLE INITIALLY DEFERRED, "measured_at" timestamp with time zone NOT NULL, "value" double precision NOT NULL );
Absolutely no magic there. Then I imported values from my RRDs using a Python script to parse the output of rrdtool fetch, extended FluxMon's result processing to record results in that table from now on, and by the time I'm writing this, the table has 187373 entries so I can try out some stuff.
First of all, I'm actually astonished that even on this small VM the performance footprint is hardly measureable (FluxMon reports the average IOPS on the server have increased by 2 — two — which means the box is now only slightly less idle), and all the queries I'm running return without me noticing any processing time or anything. I've never regretted moving this system to a netcup vserver because the performance is just plain awesome.
But when we now start actually working with the data, this is where the fun starts.
First of all, this is what some our data looks like (I joined the name column in from the monitoring_sensorvariable table and filtered for a single check):
check_id | measured_at | name | value
----------+------------------------+-------------+---------------------
287 | 2015-08-16 14:25:03+02 | wr_ticks | 0.171345577611925
287 | 2015-08-16 14:25:03+02 | rd_merges | 0
287 | 2015-08-16 14:25:03+02 | wr_merges | 0
287 | 2015-08-16 14:25:03+02 | rd_ticks | 0.0232418721651974
287 | 2015-08-16 14:25:03+02 | rq_ticks | 0.194627384265379
287 | 2015-08-16 14:25:03+02 | wr_sectors | 29445.2423249549
287 | 2015-08-16 14:25:03+02 | ios_in_prog | 0
287 | 2015-08-16 14:25:03+02 | wr_ios | 268.662597618636
287 | 2015-08-16 14:25:03+02 | rd_sectors | 145.015438354903
287 | 2015-08-16 14:25:03+02 | rd_ios | 2.39606929538118
287 | 2015-08-16 14:25:03+02 | tot_ticks | 0.113347389167615
287 | 2015-08-16 14:30:02+02 | wr_ticks | 0.173318147797764
287 | 2015-08-16 14:30:02+02 | rd_merges | 0
287 | 2015-08-16 14:30:02+02 | wr_merges | 0
287 | 2015-08-16 14:30:02+02 | rd_ticks | 0.0142595933940384
287 | 2015-08-16 14:30:02+02 | rq_ticks | 0.187737961342297
287 | 2015-08-16 14:30:02+02 | wr_sectors | 27165.3231784757
287 | 2015-08-16 14:30:02+02 | ios_in_prog | 2
287 | 2015-08-16 14:30:02+02 | wr_ios | 249.696428706562
287 | 2015-08-16 14:30:02+02 | rd_sectors | 88.2278962058103
287 | 2015-08-16 14:30:02+02 | rd_ios | 1.22167864752295
287 | 2015-08-16 14:30:02+02 | tot_ticks | 0.137322020653263
287 | 2015-08-16 14:35:03+02 | wr_ticks | 0.146852936089821
287 | 2015-08-16 14:35:03+02 | rd_merges | 0
287 | 2015-08-16 14:35:03+02 | wr_merges | 0
287 | 2015-08-16 14:35:03+02 | rd_ticks | 0.0101310021180631
287 | 2015-08-16 14:35:03+02 | rq_ticks | 0.157063814702902
287 | 2015-08-16 14:35:03+02 | wr_sectors | 29537.1896174472
287 | 2015-08-16 14:35:03+02 | ios_in_prog | 0
287 | 2015-08-16 14:35:03+02 | wr_ios | 273.367319635791
287 | 2015-08-16 14:35:03+02 | rd_sectors | 72.9272399510508
For starters, I had recently wondered how many IOPS this system would be capable of at 100% load. (Of course this is only theoretical because it will never actually reach 100%, but just for the fun of it.) This should be easily achievable by dividing wr_ios through wr_ticks, since the latter is a fraction of a second, and thereby we calculate what IOPS would roughly be necessary in order to yank the wr_ticks up to 1.0. But in order to do that, we first need to somehow extract wr_ios and wr_ticks, and preferably those with the same measured_at timestamp.
Well, Postgres can do that pretty easily using the filter syntax for aggregate functions:
select cm.check_id, cm.measured_at, avg(cm.value) filter (where sv.name = 'wr_ios') as wrios, avg(cm.value) filter (where sv.name = 'wr_ticks') as wr_ticks from monitoring_checkmeasurement cm inner join monitoring_sensorvariable sv on variable_id=sv.id where cm.check_id=287 group by cm.check_id, cm.measured_at order by cm.measured_at ;
This statement works by first grouping all the values by the check they belong to and the time they were measured. Then I use the avg aggregate function to calculate the average measured value, not because I need the average (there's only one single IOPS value in the grouped time period anyway), but because it allows me to use the filter keyword to tell Postgres which value instances I'm interested in. Namely, the one in the row that belongs to the wr_ios metric. Then I do the same thing with wr_ticks.
This yields the following result:
check_id | measured_at | wr_ios | wr_ticks
----------+------------------------+------------------+-------------------
287 | 2015-08-16 14:25:03+02 | 268.662597618636 | 0.171345577611925
287 | 2015-08-16 14:30:02+02 | 249.696428706562 | 0.173318147797764
287 | 2015-08-16 14:35:03+02 | 273.367319635791 | 0.146852936089821
287 | 2015-08-16 14:40:02+02 | 253.674634889183 | 0.203743783943429
287 | 2015-08-16 14:45:03+02 | 270.205780314176 | 0.189478882778515
So now that I have the values I need, I can of course do calculations on them, like for instance, divide them:
select cm.check_id, cm.measured_at, avg(cm.value) filter (where sv.name = 'wr_ios') as wr_ios, avg(cm.value) filter (where sv.name = 'wr_ticks') as wr_ticks, avg(cm.value) filter (where sv.name = 'wr_ios') / avg(cm.value) filter (where sv.name = 'wr_ticks') as max_ios from monitoring_checkmeasurement cm inner join monitoring_sensorvariable sv on variable_id=sv.id where cm.check_id=287 group by cm.check_id, cm.measured_at order by cm.measured_at ;
Which yields:
check_id | measured_at | wr_ios | wr_ticks | max_ios
----------+------------------------+------------------+-------------------+------------------
287 | 2015-08-16 14:25:03+02 | 268.662597618636 | 0.171345577611925 | 1567.95758234929
287 | 2015-08-16 14:30:02+02 | 249.696428706562 | 0.173318147797764 | 1440.68253601417
287 | 2015-08-16 14:35:03+02 | 273.367319635791 | 0.146852936089821 | 1861.50394343215
287 | 2015-08-16 14:40:02+02 | 253.674634889183 | 0.203743783943429 | 1245.06686770683
287 | 2015-08-16 14:45:03+02 | 270.205780314176 | 0.189478882778515 | 1426.04693648117
287 | 2015-08-16 14:50:02+02 | 246.400630315261 | 0.129145522335015 | 1907.93010752688
287 | 2015-08-16 14:55:03+02 | 264.348570000643 | 0.192318868983909 | 1374.53267792855
So the data seems to indicate that theoretically, this box should be maxed out at somewhere between 1200 and 1900 IOPS.
But there's much more to be seen.