InfluxDB: query to calculate average of StatsD "executionTime" values

Question

I'm sending metrics in StatsD format to Telegraf, which forwards them to InfluxDB 0.9.

I'm measuring execution times (of some event) from multiple hosts. The measurement is called "execTime", and the tag is "host". Once Telegraf gets these numbers, it calculates mean/upper/lower/count, and stores them in separate measurements.

Sample data looks like this in influxdb:

TIME...FIELD..............HOST..........VALUE
t1.....execTime.count.....VM1...........3
t1.....execTime.mean......VM1...........15
t1.....execTime.count.....VM2...........6
t1.....execTime.mean......VM2...........22

(So at time t1, there were 3 events on VM1, with mean execution time 15ms, and on VM2 there were 6 events, and the mean execution time was 22ms)

Now I want to calculate the mean of the operation execution time across both hosts at time t1. Which is (3*15 + 6*22)/(3+6) ms.

But since the count and mean values are in two different series, I can't simply use "select mean(value) from execTime.mean"

Do I need to change my schema, or can I do this with the current setup?

nipun nipun · Accepted Answer · 2016-01-18T10:32:54

What I need is essentially a new series, which is a combination of the execTime.count and execTime.mean across all hosts. Instead of calculating this on-the-fly, the best approach seems to be to actually create the series along with the others.

So now I have two timer stats being generated on each host for each event:

1. one event with actual hostname for the 'host' tag 2. second event with one tag "host=all"

I can use the first set of series to check mean execution times per host. And the second series gives me the mean time for all hosts combined.

InfluxDB: query to calculate average of StatsD "executionTime" values

2 Answers