Best practices for influxdb?

Support
7

Good points @overeasy . I was worried about overloading the IotaWatt with calculations that I could do elsewhere. Is it possible to have “too many” outputs/scripts that slow down the scan time of the IotaWatt?

Have been reading about InfluxDB and have setup various inputs from the IotaWatt, but I’m only getting the basic watts from each input.

Is there a way to use the tag-sets on the IotaWatt to send Pf with the input data (Watts + Pf), or do I have to create an entry for each input that sends Pf. I can do this by setting up (14) Pf entries, but was wondering if there’s a way to send it all with a single tag-set?

Also, does “Upload history From” (in the influxdb service) mean it will go back and send historical data if I change a configuration. For example, I set “upload history from” to 11/25 (a day before I started logging), and added Total_Pf to the influx config (just now)…

totals

But only get the most recent values…

select * from Total_Pf
name: Total_Pf

time iotawatt value
1543775520000000000 Home 0.89
1543775530000000000 Home 0.891
1543775540000000000 Home 0.891
1543775550000000000 Home 0.891
1543775560000000000 Home 0.891

Should it go back to 11/25 and send all those values also?

8

You really can’t overload the IoTaWatt with calculations, the processor is pretty respectable and these calculations are relatively trivial. That said, the actual data posting to influx is a very verbose command line interface and you can dim the lights a little sending a lot of measurements very often. Some basic tips are to keep the measurement and tag names short, use 10 second or longer interval, and bulk send 2 or 3 intervals at a time. In the future I intend to write a codec to compress the payload to influx which should help immensely.

I have another user that is hung-up on power-factors as well. I can see being concerned about them if you are monitoring industrial machinery, but my experience is that typical household loads have a power-factor profile that you can get a look at with the IoTaWatt local grapg capability or even the status-display. You are billed on real-power, so PF is irrelevant to cost. I know my heatpump is about .90 when it cranks and about .82 when it’s not working too hard. That’s not gonna change no matter how long I monitor it or how much I upload the power-factors. I can see this with a local graph:

image
image1305×653 112 KB

Upload history will overide if there is more recent data. You can drop the existing series, either in total or after a specific date with a “where time” clause. Then IoTaWatt will reload from the “upload history” date.

You may have mixed up your terminology. All of the measurements have the same tag-set, although the tag-values can be different if variables are used. But what I think is the intent of the question: It is possible to send multiple values for a single measurement. They will be aggregated by influx at the other end and can be queried as such.

9

Sorry, I tend to want to squeeze everything I can from a new toy. So if I can log Pf or Hz going from 59.97 to 60.05, I’ll want to do it. Even if there is no value in doing so. Have to rethink my philosophy of “capture everything”.

So for clarification, the tag set is only for defining your IotaWatt device (if you have multiple IotaWatts) which stores an InfluxDB tag for every value? I’m guessing tag sets are related to the inputs because the first one (Iotawatt=Home) gets tagged with every record. What else are tag sets (in the Iotawatt config) used for? How do the variables work? Has this already been explained somewhere (link)?

We did get some best practices out of this thread : )

  • Keep measurement & tag names short
  • Use 10 second or longer interval
  • Only bulk send 2-3 intervals at a time

The upload history doesn’t seem to be working for me. When I delete the measurement in InfluxDB (DELETE FROM Total_Mains). The Iotawatt just picks up with the next record/sample. I also tried stopping the service (from the status page), then delete the measurement, then restart the InfluxDB service.

This is what I see in my log…

12/01/18 19:13:07 Updater: service started. Auto-update class is MINOR
12/01/18 19:13:07 dataLog: service started.
12/01/18 19:13:07 dataLog: Last log entry 12/1/18 19:12:55
12/01/18 19:13:08 historyLog: service started.
12/01/18 19:13:08 historyLog: Last log entry 12/1/18 19:12:00
12/01/18 19:13:08 EmonService: started. url=192.168.208.3:80, node=IotaWatt, interval=10, encrypted
12/01/18 19:13:08 influxDB: started, url=192.168.208.1:8086, db=iotawatt, interval=10
12/01/18 19:13:09 EmonService: Start posting at 12/1/18 19:13:00
12/01/18 19:13:24 Updater: Invalid response from server. HTTPcode: -11
12/01/18 19:13:28 influxDB: Start posting at 12/1/18 19:10:30
12/01/18 19:14:35 EmonService: Stopped. Last post 12/1/18 19:14:30
*** I Stopped using EmonCMS ***
12/02/18 11:31:04 WiFi disconnected.
12/02/18 11:32:07 WiFi connected. SSID=gg, IP=192.168.208.5, channel=1, RSSI -72db
*** I deleted the Influx records here, but noticed only new records were being sent ***
12/02/18 15:00:12 influxDB: Stopped. Last post 12/2/18 14:59:20
*** Then I stopped the service, deleted InFluxDB records, then restarted - same result ***
12/02/18 15:00:33 influxDB: started, url=192.168.208.1:8086, db=iotawatt, interval=10
12/02/18 15:00:35 influxDB: Start posting at 12/2/18 14:59:30

I believe historical records were working when I was messing with EmonCMS, but for InfluxDB the IotaWatt seems to start on the next record (not starting with the Upload History date). I’ve tested this on both calculated data and direct inputs.

Untitled

Anything else I can check? I guess it’s not a big deal, I just have to wait for Influx to get new data instead of using the old data in the IotaWatt.

Thanks,

— edit —
Forgot to mention I deleted my logs once I finalized my panel/CT setup (on 11/28), so everything starts logging here…
Untitled

10

IoTaWatt looks for the most recent measurement of all the measurements that are defined and begins posting for all measurements the next interval after that. So deleting individual measurements isn’t going to help you.

I’d suggest you spend some quality time with the influx documentation so that you better understand the basic concepts of measurements, series, tags, values etc. Play around with it using the CLI to influx. Then armed with that knowledge, the various options available with IoTaWatt output will make more sense. We are really talking about influx best practices here, and they get into that in their documentation.

11

The influxdb documents are quite good. Tag sets describe a measurement, values are the data. Having rich tag sets allows you to combine data from different sources, which can be very useful or totally meaningless if the data are not related.

Tag sets make it easier to get multiple lines on a single Grafana plot, but you can do it without them. There are, of course, performance differences between the different choices, but I don’t know how much they really matter.

12

I’ve been down this road and configured half a dozen IotaWatts to use InfluxDB (and Grafana) in a variety of ways.

The short version of the outcome is here. As mentioned above, there are a number of tradeoffs and some gotchas, mainly to do with making things easy to graph in Grafana, and not overloading InfluxDB.

Specifically, my experiences suggest:

  • Use in built IotaWatt calcs as much as possible, as Bob suggests
  • Use Continuous Queries in InfluxDB to create energy (from power)
  • Use tags in InfluxDB, and don’t think of it as “SQL with time”
  • Read the docs first, to understand InfluxDB, Grafana and RegEx-with-tags. It saves time in the long run! (a gotcha for me!)
  • Understand what you’re asking InfluxDB to provide to Grafana - the query inspector is great
  • Understand timezones and specify explicitly were necessary (a gotcha for me!)

The long (and messy) version is here. Good luck!

8 Likes
13

Thanks for the write-up it is helpful.

14

Thanks for the detailed explanation. This helps a lot (especially your website articles).

15

Your short version page seems down, can you provide the iotawatt config?

1 Like
16

@MxMLssRI I’ve updated the link to the short version.

17

Looks like the link has changed, I thinks it’s this now: Iotawatt: Single Tenant InfluxDB and Grafana Setup – Brett Beeson

18

Just wondering, did you ever get time to work on that? Or when configuring InfluxDB should I still assume that the command is pretty bulky? And if you did get to compress this right down, is it feasible to be pushing the payload every 5 seconds to whatever is using the InfluxDB data can be as granular and up to date as Graph+?

And when it comes down to it, I really should be able to have all the data IoTaWatt has if I just send Influx each CT’s Wattage, and each VT’s Volts and Frequency, since those are the actual “real” measurements from the hardware, right?
If so, if I want to figure out my WattHours, Power Factor, Amperage etc, I can just have that minimum of data sent from the IoTaWatt to InfluxDB to keep the work on the IoTaWatt packing it all up to a minimum, and then do the math for those other measurements either

  • in InfluxDB if I want them written to disk for easy retrieval (reducing memory usage for Influx on large queries like per day or month stuff), or
  • in the queries if I’m happy just having those numbers on the fly when viewing a graph (which saves on disk space if my Influx host has a ton of RAM)

And I’m pretty sure I have my head around it but could you confirm for me, if I want my InfluxDB to have the exact same data sent to it that IoTaWatt is measuring from the CTs and VTs, would I set “post interval” to 5 seconds and then have “bulk send” at 6 so IoTaWatt is only needing to upload the values once every 30 seconds?
And then as above, I’d just need to have the measurements set to my VT’s Volts and Hz, and my CT’s Watts?
I’m sitting on 7 CTs and a single VT

19

That’s a lot of questions Tony. Maybe just give you the basics and you can figure out what makes sense for your needs.

Without getting into how, it stores Voltage and frequency for VTs, and it stores Watts and VA for CTs. To get Amps it divides VA by voltage. To get PF it divides Watts by VA.

I have not done anything with compression, but have added code that combines like measurements in some situations. The size of the post isn’t really that much of an issue at 5 second intervals. The problem is if it gets behind because of a communication or server outage and must upload history. That can take a long time with a lot of measurements at 5 second intervals.

In theory you would get slightly more accurate results uploading longer intervals because rounding errors would be reduced. But practically speaking it doesn’t matter.

Uploading with bulk send probably makes more sense if you are uploading only a small number of measurements where the ratio of overhead (headers etc) to data is high. If you are going to send a lot of measurements, it might be best to leave bulksend at 1. In the event it gets behind, for any reason, it will send multiple frames to catch up.

20

Heh, sorry, kinda got away from me as I went :stuck_out_tongue:

I set it that way and had about 3 days of data for it to catch up on, which dropped the AC samples per second to 20-24 but otherwise didn’t seem to effect much. Now it’s done the cycles are back at 30-35. I dunno if that’s really a useful measure of performance, but it’s all I have to go on :joy:

I’ve played with bulk a little and it seems not to make a difference, at least on the IoTaWatt side. Cycles stay the same right down to 1 and up as far as 12 with my number of inputs.

One (I promise!) final question.
I had already hit go on Watts, Volts and Hz when you replied, and only selected VA for all my CTs on its own afterwards. I figured with the upload from date set to 2021-01-17 (day before I set up the IoTaWatt, and same date I entered to get it to upload the other field-sets historically), that would upload the new field-sets for VA including the historical data, but it has only uploaded the VA readings since I added it to the InfluxDB entry, nothing from prior. Is that expected behaviour?

Everything is under one measurement called “readings” with each unit a field-key, and the circuit for each is added as a tag.

Edit: reading that last bit back… it’s only gonna be checking “last uploaded” based on the “measurement” entry, isn’t it? :joy::sob:

21

Yes, functioning as designed. IoTaWatt queries all of the measurements and restarts uploads of all after the date of the most recent.

22

Thanks for your always stellar guidance and insight Bob. Up and running, pumping the data over like a champ. For the amount I watched, it seems to be able to transfer about 2 minutes of 5-second intervals over in about 5 seconds, if it ever has to play catchup

23

When integrating energy data in Influx (2.x), is it best to create a pivot table with all outputs on a single row, or keep the raw format of a single measurement per row?

I would like to be able to do math operations between measurements for a specific window, and I am struggling with how to best pull that off with the Influx documentation.

24

I think its fair to say that many users are struggling with the flux query language. These types of questions might better be raised in the influx forum where the participants may be more immersed in it and have a better understanding.

25

What are you trying to do, what math?
I started using Flux several months ago to enable doing math across measurements (in InfluxDB) because I have two IotaWatts and wanted to aggregate the data.

I also use Grafana. Grafana offers some math too, that can work across measurements.

I have my data going to InfluxDB in one measurement. The data is tagged with the device and channel. This was based on the earlier recommendations.

Using Flux to aggregate data is hard (syntax is confusing and not easy to remember, would likely be be better with more examples, there were very few when I did this). Flux seems like it might be slower integrating W data into WH. It is okay for short periods, but quite slow for long periods. It is also really slow for finding a maximum over a long period. (Think finding the maximum amps used over the last 6 months. It can do it, but it hits the one virtual CPU core I give it very hard for as much as min. I am running it on something much more powerful than an RPI 4).

So, what insights are you hoping to get from the data. That will help determine what math you need and then how to set it up. If you don’t know yet, just follow the standard way I listed. It works well with both InfluxDB and Grafana.

26

@frogmore I am struggling with a few of the basic concepts for now. I export volts and watts from IoTaWatt to Influx in a bucket called iotabucket from my three meters. (I have a main panel that sub-feeds two other panes, as well as the PV disconnect and three mini-split air conditioners.) The main meter also exports PF for the utility feed. In addition to the CT loads directly, I also export calculated “import”, “export”, and split-phase loads— watts only.

I want to integrate the individual loads into hourly energy values, presumably in a second bucket. Part of me contemplates also calculating min/max Watts for each hour. Some preliminary testing makes it look like the integration does not always generate valid results, especially for my import/export calculated values.

For Grafana, I would like to be able to do some kind of histogram or series of pie charts that help understand where energy consumption is going, and also some graphs that help me understand real-time how I am doing with self-consumption of PV, and the status of my net-metering on a day/week/month perspective. Separately, I want to track my EV charging and mileage, and how much of my EV charging is self-consumption. I don’t have a great sense of how I will display the information yet, but the key point is that I will need data from all three units for some graphing operations. My basic understanding is that to do that I need to pivot data so that I have a single time row, and value columns for each meter. I am unsure if this is a persistent table, or dynamically created for the graph.

I’ll keep trying to do more digging to better understand Influx and Grafana, but I want to make sure my data is structured in a way that it works well for the future.

1 Like