BTMON, Influxdb, and Grafana help needed

[adamall]

I am looking for someone who might be able to help make some changes to the BTMON python script.

The problem: In grafana it is difficult to group by month or year because of limitation in influxdb. If you want to show exactly what your house used for the month of January you have to approximate by using the last 30.5 days or something similar. Basically their is no way to group by year, month, week, or other timed based grouping in influxdb. This is a limitation in influxdb and is on there list of things to implement in the future.

My thought to get around this would be to add tags to each value that is inserted into influxdb. I think assigning the following tags based on the current timestamp would allow grouping in Grafana.

example of tags
Tagname=tagvalue
Year=current.year.value
month=current.month.name
day=date.value
daytype=day.name
hour=hour.value

Right now in BTMON's influxdb processor you can add tags that get inserted for each reading, but they are static and don't change. To do what I suggest above would require some programming changes that could read the current time stamp and then assign the tag values as appropriate for each reading. The problem is I am not able to make those changes as my python skills are not that great. Is there someone who might be able to make those changes?

I think it would make influxdb and grafana even more useful than it already is.

[wci68]

Essentially that would just be storing the timestamp, with one-hour granularity, as a tag set. That would in turn increase cardinality considerably in an unbounded fashion, which in turn would cause an increase in memory usage. This would not be a good idea.

Perhaps I'm missing the detail of the issue you noted. Exactly how are you looking to use groupings and what is the actual issue? I follow influxdb discussions regularly and am not familiar with this particular aspect, at least not how it might apply here.

If you really want to do this you should be able to set up a CQ (continuous query) to add the tags you desire, though you could potentially use a CQ to derive and store the summarizations you want directly. These would be configured directly in influxdb and wouldn't need any python skills.

[adamall]

Here is a link to the problem I am referring to, https://github.com/influxdata/influxdb/issues/3991

What I want to do in Grafana is show how much energy was used for a particular month. Right now the largest grouping you can do in influxdb is by day, so I group by 30.5 days. It would be better if I could group by actual month and as far as I can tell there is no easy way to do this in influxdb.

So that is why I thought adding these tags would be useful. I also thought it might be useful to see things like how much energy I use on Saturday's and things like that.

I am not sure I understand how these tags would increase the cardinality. If I am storing each component as a separate tag, I would essentially have 12 different month tags, 31 day tags, 7 day type tags, and, 24 hour tags. The only tag that would keep increasing is the year tag, but that wouldn't happen that often.

[wci68]

The cardinality that becomes the issue is for the composite set of tag values. The issue isn't so much being the addition of tags, it is that the values of those combinations of tags would increase without bound on a steady trend. Adding tags to track time is not really the right answer.

Perhaps the issue is indeed how UIs such as Grafana are creating queries. Not everyone agrees that 30 days equals a month. And is a year 365 days, 12 months, 52 weeks, or not something that is determinate? While you may not like to have to use a query for 30.5 days, how would you propose a TSDB solve this differently? It does get very complex very quickly.

If what you really want is a summary for each calendar month then a summary for "one month" is not going to provide what you want regardless of where it is implemented. If a summary based on arbitrary 30-day periods is fine then that is how those queries can be written (as you are already doing). This is a topic that had existed long before TSDBs were even a concept, and even in the early days of RRD was discussed.

For these cases it is often better to use something like a CQ to maintain the desired summaries, which would provide what it appears you are seeking. These would be more performant, would be more accurate, and could be maintained longer with less storage impact (IE: you can keep a decade of daily, monthly, and yearly summaries in a fraction of the space needed to maintain all of the collected datapoints for that same time period)

In fact, this is one of the reasons CQs exist in the first place.

[adamall]

I was never proposing a summary by a generic month and apologize if I confused things. What I want to be able to do is group by calendar month, such as the month of June or September. And to further that, it would be good to group by all Saturdays or Tuesdays. Eventually I would expand that to group by a given year (don't quite have enough data for that yet). I get that it can be very complex to implement in TSDB which is why I figure it will take awhile if ever for influxdb to implement it and why I am trying to find a solution.

My understanding of tags seems to be different than yours. Maybe I am confusing something or not completely understanding. My understanding of tags comes from this page https://www.influxdata.com/blog/tldr-in ... r-15-2016/
Which I interrupt to mean if I have tags keys, Year (values of 2016 - ~), month(values of 1-12), day(values of 1-31), daytype(values of 0-6), and hour (values of 1-24) that the year is the one tagkey that can increase unbounded. But that will take awhile as it will only happen once a year.

I would be fine using continuous queries, but I am not sure that solves my problem of being able to group by calendar month or certain days of the week. I am not familar with setting up CQ but understand how they can lessen the amount of storage I need.

To give you an idea of what I collect and how it is stored in influxdb, see the attached file. I am essentially collecting: amps, differential watt hours, watts, and watt hours for each of the GEM's 32 channels. Each of these points are stored in influxdb with the exact same time stamp about every 30 seconds. I currently have no tag values, only time and field columns.

Thanks for you help and explanations of things, I only mess around with this stuff occasionally so it is good to talk this through with someone.

[wci68]

The issue is not with the tags. The concern over cardinality is that memory use increases with each combination of tag values and not just with the individual tag values themselves. So even though you may have twelve months, seven days, and thirty one dates, for each year you add the total memory used will continue to increase as you've added another new combination of values.

Using the article you referenced, the cardinality of your tag set would be based on years * months * days * hours. Your 'daytype' would not change cardinality since a given combination of month/day in each year will only ever have a single value itself.

You can create summaries using CQs as you see fit. You'll likely need multiple CQs to create the various summaries you want.

If you are looking to do queries for something like a summary of usage on tuesdays that really is no longer a time-series query though it is often misconceived to be such. Similar for something like summaries by calendar month. If you wanted a rolling 30-day summary that would be - it can be confusing since there is a subtle but significance between the two. That is why this is something that can't be done with a standard time-series query.

Not directly related, but as a complementary example there was another discussion related to a top-x-by-y type of query such as "top five consumers for the past week". This really wasn't a time-series query though it was based on a time period. A time series query could provide the top-X series for each measurement over a period Y, but could not accumulate and sort those to present the top-X over the entire period. Instead support was added to the query language to be able to create CQs that would perform such summaries as this was not something that could readily be implemented with a single query.

[ben]

Have you considered trying out Grafana and BTMon with MySQL instead of InfluxDB? You should be able to get everything mentioned in this thread via MySQL queries.

[adamall]

That thought has crossed my mind. Though what I am leaning towards doing is adding a tag for month, day, and day type. I don't know that I really need hour or year. That should keep the unique tags from getting out of control.