show volume of Indigo data being ingested by Influx?

Posted on
Sat Jan 05, 2019 10:56 am
dduff617 offline
Posts: 659
Joined: Jul 05, 2006
Location: Massachusetts, USA

show volume of Indigo data being ingested by Influx?

I'm thrilled overall with useful info I am able to glean from Grafana. I've been using it for a while and have over time added and tweaked settings in terms of included devices and included states.

What I'd like to be able to see is how many total updates Indigo is adding to Influx for different Devices/States. Anyone have a clever influx query (or even better, a Grafana dashboard) that would show the volume of updates and summarize it somehow by device and/or by state? I confess sometimes it seems like I don't completely have my head fully around influx concepts and terminology - sometimes things that seem should be easy (in "normal" relational db's) turn out not to be so in influx...

Anecdotally... I suspect I have added some devices/states in my experimentation that are eating tons of resources but providing no real value - e.g., where there's a field like "time since last update" or something that increments continuously. I little poking around I've done seems to indicate that certain devices (Nest thermostats, for example) seem to generate a HUGE number of updates - orders of magnitude beyond anything that corresponds to real-world state changes.

It occurred to me it would be super useful to be have a dashboard that showed # of updates in the last hour by device type or by state or both. I see commands that look enticing like "show stats" or "show tag cardinality" and the like, but figured I'd ask here first to see if someone has already gone down this path and is willing to share their findings...

Posted on
Sat Jan 05, 2019 1:33 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: show volume of Indigo data being ingested by Influx?

+1. I would find this really useful to try and optimise the limited resource on my Mac mini.


Sent from my iPhone using Tapatalk

Posted on
Sun Jan 06, 2019 10:37 am
vtmikel offline
Posts: 628
Joined: Aug 31, 2012
Location: Boston, MA

Re: show volume of Indigo data being ingested by Influx?

Pretty cool idea. I had not thought of this.

I can toy around to see if I can figure out the queries that would be needed. I also am not very skilled in Influx queries, and have relied heavily on the Grafana visual query creator, which I've become intimately skilled at.

My advice is to avoid adding devices to the include list. In particular, plugin devices that have a lot of custom states. I much prefer adding specific states/properties. My own August plugin has a custom state that holds the number of minutes that the lock was unlocked, so this is updated every minute. Plugins like Timed Devices or Fing are even more dangerous. It can be tempting to add a device to the Include list without realizing this effect it will have.

Also, be weary of the Minimum Update Frequency (bottom of the config dialog). This sends a update to Influx even if a change to the state/property has not occurred. Mine is set to 10 minutes, which is the second most frequent. I find this setting helpful because it corrects the Discrete panels so that you dont have missing data, but it comes at a cost to having more data sent that is unnecessary.

Overall, the plugin's performance doesn't change much even if you have a very chatty interaction between Indigo and Influx. However, the InfluxDB can become large quickly, and you'll see queries take longer to perform.

I'll see what I can come up with.

Posted on
Sun Jan 06, 2019 11:29 am
dduff617 offline
Posts: 659
Joined: Jul 05, 2006
Location: Massachusetts, USA

Re: show volume of Indigo data being ingested by Influx?

vtmikel wrote:
My blind advice is to avoid adding devices to the include list. I much prefer adding specific states/properties. My own August plugin has a custom state that holds the number of minutes that the lock was unlocked, so this is updated every minute. Plugins like Timed Devices are even more dangerous. It can be tempting to add a device to the Include list without realizing this effect it will have.


That's a point of view I am also coming around to, but with a caveat that sometimes I don't know what I want or what's interesting/useful until I look at what's there and poke around at it. It has been very valuable for me to use GHD to help determine things like:
  • which states have names that sound like what you are looking for but in fact contain no useful data.
  • the set of values that a state takes on over time so I can figure out what they mean.
  • which states are merely "cosmetic" human-readable presentations of other states, and of those which are actually useful.
  • which states actually serve as more universal representation of a state across different devices, allowing you to simplify data access vs. other more model-specific states.

Also, I note that currently GHD configuration provides a way to INCLUDE a device, INCLUDE a state (globally by state name), and to EXCLUDE a device. Some other things ideas:
  • Include device by type (or model). E.g., include all devices with model "Motion Sensor", including for a plugin-based model like "Nest Thermostat Module"
  • Exclude a state, globally.
  • Include (or exclude?) a state, "locally", meaning when it occurs on instances of a device type or a specific device. E.g., I want state "observationTime" from my Weather Locale devices (but not from my rain sensor that happens to have the same state name).
  • Include (or exclude) a device based on regex name.

I realize these things would add complexity and things could certainly get "messy"... Something to be dealt with is how these different rules for inclusion/exclusion layer on top of each other. E.g. include all devices of type "foo" but exclude the specific device named "bar". Globally include state "x" but exclude it when it occurs in device type "y". This sounds like something that would require a config file - writing a UI to create/edit these rules would certainly be difficult.

I think the regex-based inclusion/exclusion seems enticing for me, especially since so much of how I use Grafana lately is already pivoting around the use of regexes to partition my Indigo devices/data into useful classes. For example, I have numerous dashboards that show a group of devices based on matching a pattern in name (e.g. /.*Motion$/ for enumerating all my motion detectors, and of course there's already so much regex leverage built-in to Grafana, that it seems natural to consider extending this to GHD config as well.

Posted on
Tue Jan 08, 2019 9:40 pm
dduff617 offline
Posts: 659
Joined: Jul 05, 2006
Location: Massachusetts, USA

Re: show volume of Indigo data being ingested by Influx?

I have not yet come up with a simple/direct query to see the number of state updates that are getting stored in InfluxDB for each device or state. However, I have done some manual exploring and I I think have confirmed that such a query would be valuable and in the process have exposed some extreme inefficiencies in my own system.

An obvious problem in my config stemmed from my way Nest Protect devices are implemented in the Nest Home plugin. There are numerous states that get updated quite frequently but which contain no actual useful information. Examples of these states are: time_since_last connection (example value: "1 days 19 hours 7 min 39 seconds"), minutes_since_last_connection, etc. I was seeing multiple hundreds of updates per hour from each of these devices.

Some time early in my GHD exploration, I had added by Nest Protect devices to the GHD "Include Devices" list without giving it much thought. Tonight I removed Nest Protect devices from the Include list and instead added only a smaller set Nest Protect states that contain useful information to the "Global Include States" list. I'm pretty sure I've reduced the number of fields updated in Influxdb to less than 10% of what it was, yet lost absolutely no real functionality.

I have more work to do in fixing Nest Thermostats, which have similar problems as the Nest Protect's, but unfortunately are harder to repair because the set of useful states that have to be manually added is much larger. If GHD provided a way to globally exclude states, it would make things easier in this case.

Posted on
Wed Jan 09, 2019 5:23 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: show volume of Indigo data being ingested by Influx?

dduff617 wrote:
I was seeing multiple hundreds of updates per hour from each of these devices.


I think I need to do the same as you.. I know on some of the plugins I use for heating and alarm that there are a lot of custom states that probably update frequently that offer no value in GHD. How did you see the number of updates each device was producing?

Posted on
Wed Jan 09, 2019 8:12 am
dduff617 offline
Posts: 659
Joined: Jul 05, 2006
Location: Massachusetts, USA

Re: show volume of Indigo data being ingested by Influx?

siclark wrote:
dduff617 wrote:
I was seeing multiple hundreds of updates per hour from each of these devices.
How did you see the number of updates each device was producing?


Honestly, I felt like I was poking around in the dark, learning InfluxDB query interface, learning the GHD Influx schema, and learning about the vagaries of my specific devices/plugins/states all at the same time.

I spent a few minutes and tried things like:
Code: Select all
select count("id") from device_changes where time > now() - 10m group by "name"
to get an idea of which devices were generating a lot of updates, then in some cases for specific suspect devices, I did for example
Code: Select all
select *  from device_changes WHERE "name" = 'FR Nest Protect' and time >= now() - 10m


These were imperfect methods - I had to look at one Influx measurement at a time. I couldn't seem to figure out how to add "order by" and "limit n" clauses to those queries. The "Select *" type queries are messy in that they essentially return a very wide table with the union of all fields present in all devices in the measurement (100's in my case). I went back and forth using menu items Grafana Home Dashboard>Explore Device and Explore State to dump out description to the logs for specific devices/states, then using directed Influx queries to count the number of records in a given series per day or per hour.

So that's basically a restatement of the motivation for my original post - I'm hoping someone with more familiarity with InfluxDB query language would have more luck with a concise and revelatory query. Either that or the plugin could be instrumented to dump stats on updates periodically (e.g. every 5 mins) with an additional debug-logging flag in preferences.

Posted on
Wed Jan 09, 2019 8:36 am
dduff617 offline
Posts: 659
Joined: Jul 05, 2006
Location: Massachusetts, USA

Re: show volume of Indigo data being ingested by Influx?

I just noticed (duh!) that there already are some useful-looking settings relevant to tuning up data flow to InfluxDB. I'm experimenting with turning on "InfluxDB update decisions debug" and "InfluxDB update decisions L2 debug (not recommended)" flags.

I think these do not cause additional lines to go to the main Indigo logs, but instead (I think) to /Library/Application Support/Perceptive Automation/Indigo 7.2/Logs/com.vtmikel.grafana/plugin.log.

I hadn't seen that log previously. It looks promising. Will report if I come up with any better methodology for tuning.

Posted on
Thu Jan 10, 2019 11:28 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: show volume of Indigo data being ingested by Influx?

Disclaimer first, I haven't used this myself yet, however

I came across this on OpenHab forum discussing Grafana and Indigo where they had a bit more work involved in the setup

https://github.com/CymaticLabs/InfluxDBStudio

Might be an easy way to check on the data in influx? Volumes, number of fields stored etc. Maybe even drop unnecessary tables or fields?


Sent from my iPhone using Tapatalk

Posted on
Thu Jan 10, 2019 12:06 pm
vtmikel offline
Posts: 628
Joined: Aug 31, 2012
Location: Boston, MA

Re: show volume of Indigo data being ingested by Influx?

With a bit of fooling around, I was able to come up with a graph for the number of updates per device. I was not, however, able to come up with a graph for the number of updates per state (across devices). It appears that you cannot do the latter in Influx. I could track this in the plugin itself, though I'd have to think about it some more.

Here is the main query to use: SELECT count("name") FROM "device_changes" WHERE $timeFilter GROUP BY time(15m), "name"

If this helps, I've attached my dashboard JSON. It requires the Pie Chart plugin which is not currently included in my plugin distribution of Grafana.

Pie Chart: https://grafana.com/plugins/grafana-piechart-panel
put the folder from the zip file here: /Library/Application Support/Perceptive Automation/Indigo 7.2/Plugins/Grafana Home Dashboard.indigoPlugin/Contents/Server Plugin/servers/grafana/data/plugins

Code: Select all
{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": null,
  "graphTooltip": 0,
  "id": 17,
  "links": [],
  "panels": [
    {
      "aliasColors": {},
      "breakPoint": "50%",
      "cacheTimeout": null,
      "combine": {
        "label": "Others",
        "threshold": ".01"
      },
      "fontSize": "80%",
      "format": "short",
      "gridPos": {
        "h": 9,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "id": 4,
      "interval": null,
      "legend": {
        "show": true,
        "sort": "current",
        "sortDesc": true,
        "values": true
      },
      "legendType": "Under graph",
      "links": [],
      "maxDataPoints": 3,
      "nullPointMode": "connected",
      "pieType": "pie",
      "strokeWidth": 1,
      "targets": [
        {
          "alias": "$tag_name",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "name"
              ],
              "type": "tag"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "measurement": "device_changes",
          "orderByTime": "ASC",
          "policy": "default",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "name"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "count"
              }
            ]
          ],
          "tags": []
        }
      ],
      "title": "% of changes by device",
      "type": "grafana-piechart-panel",
      "valueName": "current"
    },
    {
      "aliasColors": {},
      "bars": false,
      "dashLength": 10,
      "dashes": false,
      "datasource": "Indigo",
      "fill": 1,
      "gridPos": {
        "h": 10,
        "w": 24,
        "x": 0,
        "y": 9
      },
      "id": 2,
      "legend": {
        "alignAsTable": true,
        "avg": true,
        "current": false,
        "max": false,
        "min": false,
        "rightSide": true,
        "show": true,
        "sort": "total",
        "sortDesc": true,
        "total": true,
        "values": true
      },
      "lines": true,
      "linewidth": 1,
      "links": [],
      "nullPointMode": "null",
      "percentage": false,
      "pointradius": 5,
      "points": false,
      "renderer": "flot",
      "seriesOverrides": [],
      "spaceLength": 10,
      "stack": false,
      "steppedLine": false,
      "targets": [
        {
          "alias": "$tag_name",
          "groupBy": [
            {
              "params": [
                "15m"
              ],
              "type": "time"
            },
            {
              "params": [
                "name"
              ],
              "type": "tag"
            }
          ],
          "measurement": "device_changes",
          "orderByTime": "ASC",
          "policy": "default",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "name"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "count"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": [],
      "timeFrom": null,
      "timeRegions": [],
      "timeShift": null,
      "title": "Number of updates per device (15 minute interval)",
      "tooltip": {
        "shared": true,
        "sort": 0,
        "value_type": "individual"
      },
      "type": "graph",
      "xaxis": {
        "buckets": null,
        "mode": "time",
        "name": null,
        "show": true,
        "values": []
      },
      "yaxes": [
        {
          "format": "short",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": true
        },
        {
          "format": "short",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": true
        }
      ],
      "yaxis": {
        "align": false,
        "alignLevel": null
      }
    }
  ],
  "schemaVersion": 16,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-24h",
    "to": "now"
  },
  "timepicker": {
    "refresh_intervals": [
      "5s",
      "10s",
      "30s",
      "1m",
      "5m",
      "15m",
      "30m",
      "1h",
      "2h",
      "1d"
    ],
    "time_options": [
      "5m",
      "15m",
      "1h",
      "6h",
      "12h",
      "24h",
      "2d",
      "7d",
      "30d"
    ]
  },
  "timezone": "",
  "title": "Performance",
  "uid": "pu_OneUiz",
  "version": 1
}
Attachments
Screen Shot 2019-01-10 at 12.54.14 PM.png
Screen Shot 2019-01-10 at 12.54.14 PM.png (1.04 MiB) Viewed 3837 times

Posted on
Thu Jan 10, 2019 12:11 pm
vtmikel offline
Posts: 628
Joined: Aug 31, 2012
Location: Boston, MA

Re: show volume of Indigo data being ingested by Influx?

dduff617 wrote:
I just noticed (duh!) that there already are some useful-looking settings relevant to tuning up data flow to InfluxDB. I'm experimenting with turning on "InfluxDB update decisions debug" and "InfluxDB update decisions L2 debug (not recommended)" flags.

I think these do not cause additional lines to go to the main Indigo logs, but instead (I think) to /Library/Application Support/Perceptive Automation/Indigo 7.2/Logs/com.vtmikel.grafana/plugin.log.

I hadn't seen that log previously. It looks promising. Will report if I come up with any better methodology for tuning.


I thought about telling you about this, but the debug is VERY chatty and can easily become overwhelming. It was designed to make sure I've implemented the decision making correctly on what to log. I've even found it can saturate the log window and freeze up a copy of the Indigo client when running on a separate machine from the server. It was not really intended to be a debugger for others to use in this way, but it certainly could help if you are persistent enough to review the logs.

Let me know if you have questions.

Posted on
Thu Jan 10, 2019 1:38 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: show volume of Indigo data being ingested by Influx?

The log was very useful to show me which devices were sending updates the most frequently. The main one was my alarm panel which I don't need at the moment so could disable that.
I opened in text wrangler on another mac and it was fine.


Sent from my iPhone using Tapatalk

Posted on
Fri Jan 11, 2019 1:20 pm
vtmikel offline
Posts: 628
Joined: Aug 31, 2012
Location: Boston, MA

Re: show volume of Indigo data being ingested by Influx?

So.....


As I was building my own graph showing the update volume, I noticed what dduff617 mentioned - the NEST devices have a lot of updates. It raised my eyebrow, but I didn't investigate until now.

I've investigated further and I found two things:

1. The NEST plugin makes unnecessary state changes to the device states. Also, the case of the string variables seem to change. for example, my Nest protects will have a ui_color_state of "green" then "Green".

2. Even when the case is the same for a string, under some conditions, my plugin will still send an update to Influx even though it didn't change.

These two bugs, combined, were resulting in an update being sent to InfluxDB every 45 seconds or so for my NEST devices, for a single included state.

I've now corrected both of these items in version 1.0.8 of the plugin. The plugin will now compare the previous value to the current value, and for strings it will ignore case.

Additionally, I've added a "Smart" option for the Minimum Update Frequency. This configuration option will send minimum update frequencies of 5 minutes only at the beginning and end of each day (between 23:45 and 00:15), and will not send any updates throughout the middle of the day. Since most time ranges that you graph in Grafana will start/end at the beginning or end of the day, this should cover when it's needed the most.

I'm going to test it a bit further before posting the final version to github. The beta will be posted shortly.

Posted on
Fri Jan 11, 2019 2:49 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: show volume of Indigo data being ingested by Influx?

My alarm panel has a state that shows current time! It was updating every couple of seconds! I've now removed the device and will add the required states if I need to.


Sent from my iPhone using Tapatalk

Posted on
Sat Jan 12, 2019 10:07 am
vtmikel offline
Posts: 628
Joined: Aug 31, 2012
Location: Boston, MA

Re: show volume of Indigo data being ingested by Influx?

Here's a view of the reduction in the number of updates sent to Influx after my 1.0.8 changes. My nest devices went from 30 updates in a 15 minute period to much less. Only my power meter stays constant, the other devices have bursts of updates when the sensors report, as they should . I need to test my "smart" minimum frequency updates a bit further before I publish a non beta version.
Attachments
Screen Shot 2019-01-12 at 11.05.39 AM.png
Screen Shot 2019-01-12 at 11.05.39 AM.png (598.34 KiB) Viewed 3717 times

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 0 guests