Thanks Sean. Yes, that sounds like what I need. Although it isn't clear to me what scope nested functions are applied at: what would SUM(MEAN) do? Would MEAN be separately applied within each series, and then SUM applied across series?
In the general case, we really need InfluxDB to support different functions being applied at different configurable scopes. Another example is hosts that report CPU usage on a per-core basis. I get CPU metrics reported with a tag indicating the core and a tag indicating the host. Ideally I'd like to average across cores in a host (to get a total CPU usage for the host) and then take the max of those figures across hosts (so that I can chart the most heavily utilised host and see if e.g. one of my hosts is nearing 100% CPU). Here I want to do something like "MAX (MEAN value GROUP BY host, core, time(5s)) GROUP BY time(5s)" I come from a Splunk background where their query langauge is generic enough to express pretty much anything of this nature. I recognise that this particular example could be solved with a CQ but that really feels like an undesirable work around to the underlying issue (and it comes with significant usablility and performance hits). On Monday, 27 June 2016 19:04:16 UTC+1, Sean Beckett wrote: > Thanks for the context, Mat. What you need really requires SUM() to accept > nested functions, which it doesn't currently do. The original issue is > https://github.com/influxdata/influxdb/issues/834 which was folded into a > meta-issue (https://github.com/influxdata/influxdb/issues/5930) for query > system enhancements. > > > It's not fundamentally hard to add but it is not trivial. I don't know where > it falls on the current roadmap. > > > On Mon, Jun 27, 2016 at 12:16 AM, Mat and Helen <[email protected]> wrote: > > Hi Sean, > > > Sorry but I don't think that works either in the general case as it relies on > knowing in advance that I am going to want to graph this with 1h resolution. > In reality, when someone is viewing data for the last 10m I will want to > graph with 5s resolution, when they're viewing the last 24h I might want to > graph with 10m resolution etc. > > > > I really need the ability to calculate the mean within each series > dynamically at a scope that matches the resolution I am graphing over and > then to sum across series. At the moment I'm thinking that I might have to > move away from InfluxDB to Graphite which provides this kind of flexibility. > I don't know anything about the implemention of InfluxDB -- is the kind of > capability that I need here something that would fundamentally be hard to add? > > > Thanks! > > > Mat > > > > > On 26 June 2016 at 22:34, Sean Beckett <[email protected]> wrote: > > > > So you want the sum of the mean? > > > First write a continuous query to calculate the MEAN, as in my prior query, > and write it back to a new measurement. Then you can query that measurement > for the sum. > > > E.g. > > > CREATE CONTINUOUS QUERY foo ON mydb REPEAT EVERY 15m FOR 2h BEGIN > SELECT MEAN(value) INTO sipMessagesMean FROM sipMessages GROUP BY time(1h), * > END > > > Then > > > SELECT SUM(mean) FROM sipMessagesMean WHERE time > now() -7d GROUP BY > time(1h), tag_message_type > > > > > On Sat, Jun 25, 2016 at 4:43 AM, Mat and Helen <[email protected]> wrote: > > > > > Thanks Sean. Unfortunately that's not what I need -- I need the TOTAL rate > that SIP messages are being processed across the deployment. So I need to > SUM across the nodes. I think your formula gives me the average rate per > node? > > > Mat > > > > > > On 24 June 2016 at 21:17, Sean Beckett <[email protected]> wrote: > > > > > It seems that > > > SELECT MEAN(value) FROM sipMessages WHERE time > now() -7d GROUP BY time(1h), > tag_message_type > > > would return what you want. That will give the average of the "value" field > per hour, per message_type, but across all nodes. If that isn't what you > want, can you clarify why not? > > > On Fri, Jun 24, 2016 at 2:57 AM, <[email protected]> wrote: > I've hit an issue that I think, sadly, is going to prevent us from deploying > InfluxDB. The following is an example of this issue, but in reality it > manifests in different ways in many of our use cases. > > > > I have a set of nodes each reporting the rate at which differnet SIP messages > are being received (in terms of messages per s) > > > > timestamp | tag_node | tag_message_type | value > > --------------------------------------------------- > > | node1 | INVITE | 1728 > > | node1 | BYE | 1051 > > | node2 | INVITE | 1521 > > | node2 | BYE | 1241 > > > > Each node reports writes these stats to InfluxDB roughly every 5s. > > > > I want to draw charts showing the total rate of different types of SIP > messages being processed across my deployment, split by message_type. > > > > Ideally I would do this like so: > > > > SELECT sum("value") FROM "sipMessages" WHERE WHERE time > now() - 7d GROUP > BY time(1h), "tag_message_type" > > > > The problem here is that this sums across different values of node, but also > sums multiple points within each series. So the number I get returned is > ~720 times higher than it should be. If I knew that there were always going > to be exactly 720 measurements in each time interval I could divide by 720, > but this 720 is only an approximation. [it is also the case that using > Grafana to draw the graphs this "1h" period changes automatically based on > the period that I'm graphing over] > > > > What I really need to do is to run an average aggregation within each series > over each GROUP BY time period (so that for each node / message_type > combination I have one data point per time period), and then sum the results > of those aggregations across the different series. > > > > I don't believe this is possibe though, and I can't see any way to work > around it? > > > > What I don't understand is why this isn't a critical issue for lots of users > as this seems like a very standard use case. Is there a reason that other > users are able to avoid this issue? > > > > Would adding this capability (to do per series aggregation within "GROUP BY > time" intervals) be technically difficult? > > > > -- > > Remember to include the InfluxDB version number with all issue reports > > --- > > You received this message because you are subscribed to the Google Groups > "InfluxDB" group. > > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/influxdb. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/influxdb/c7be9920-759c-49e5-a23f-a4800c18b979%40googlegroups.com. > > For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > > Sean Beckett > Director of Support and Professional Services > InfluxDB > > > > > > > > -- > > Remember to include the InfluxDB version number with all issue reports > > --- > > You received this message because you are subscribed to a topic in the Google > Groups "InfluxDB" group. > > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/influxdb/uXdYy9JA6_E/unsubscribe. > > To unsubscribe from this group and all its topics, send an email to > [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/influxdb. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/influxdb/CALGqCvPvaBsKuruQ5j1mXtftTo70R7LjYm%2BTNq43QBwCRtzwZA%40mail.gmail.com. > > > > For more options, visit https://groups.google.com/d/optout. > > > > > > > > -- > > Remember to include the InfluxDB version number with all issue reports > > --- > > You received this message because you are subscribed to the Google Groups > "InfluxDB" group. > > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/influxdb. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/influxdb/CAKyPxTQQsnLyC%3DV_KOJTdRvSA9n1fNtGwm7v63TmH7mScy0YBw%40mail.gmail.com. > > > > For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > > Sean Beckett > Director of Support and Professional Services > InfluxDB > > > > > > -- > > Remember to include the InfluxDB version number with all issue reports > > --- > > You received this message because you are subscribed to a topic in the Google > Groups "InfluxDB" group. > > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/influxdb/uXdYy9JA6_E/unsubscribe. > > To unsubscribe from this group and all its topics, send an email to > [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/influxdb. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/influxdb/CALGqCvPvTOgZb2mmwPrXZYrnEBETeTh%3DRwq3f6pPmJ4hc4RiBA%40mail.gmail.com. > > > > For more options, visit https://groups.google.com/d/optout. > > > > > > > > -- > > Remember to include the InfluxDB version number with all issue reports > > --- > > You received this message because you are subscribed to the Google Groups > "InfluxDB" group. > > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/influxdb. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/influxdb/CAKyPxTQ13ExjcmTtcs-E3Segf0QBqgCxSbWjW4MT%3Dpbs6jktfg%40mail.gmail.com. > > > > For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > > Sean Beckett > Director of Support and Professional Services > InfluxDB -- Remember to include the InfluxDB version number with all issue reports --- You received this message because you are subscribed to the Google Groups "InfluxDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/a4a540c0-11bc-463f-a58e-13a06907b4f5%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
