Best practices for influxdb?

The docs page is very light on suggestions for how to configure it. I have seen several examples of people asking for different ways of doing it in the issues. I am sure I can figure something out, but it would certainly help to see how others have done it and why?

I have a basic understanding of influxdb and Grafana and know how to make stuff show up, but have not tried it with iotawatt, yet.

Influx is very powerful and flexible. It is schema-less so it just stores everything you throw at it. You can upload your data as a flat file, or index it as many ways as you please with keys.

So it all depends on what you want to do. I’ve tried to make the configuration somewhat versatile with the wildcard substitution capability. For my own purposes, I use the default settings for measurement and field-key.

The nice part is that you can upload a weeks worth of history with one configuration and play around with it (using whatever compatible visualization tool you fancy). You can then upload the same data as a different series and play with that. When you get what you want, upload as much of your history as you want and continue to post from there.

I want to store & run everything locally (on my own server) and wasn’t sure which storage option was “better”, EmonCMS or InfluxDB.

I played around with EmonCMS for a few days. While it was easy to setup with the IotaWatt, and EmonCMS is energy focused, it was difficult to figure out for my needs. Not sure if it’s my lack of knowledge, or bugs in the EmonCMS interface. Couldn’t get the detailed & flexible stats I wanted.

So I’m now trying InfluxDB/Grafana (also installed locally). Installing was relatively easy (on Debian). I obviously have a lot of reading to do, but was hoping for some guidelines how to “best” setup the IotaWatt for InfluxDB. I realize anything is possible, but it seems like there is a best way to get started.

One confusing IotaWatt config is the tag set entries. The description is too technical for me (I’m just not getting it).
Untitled

“IoTaWatt assumes the first tag is unique to this measurement and is referenced to determine the time of last measurement during restart recovery. In that way, an IoTaWatt can maintain context and continuity where multiple data sources are writing to a single database.”

I’ve also looked here for help, but have only seen fixed string entries like “Iotawatt = Home”. Does this tag every measurement inserted into the InfluxDB, or does the paragraph above mean the IotaWatt is storing this locally to figure out where to resume measurement uploads (“restart recovery”)? Would this setting only be used for multiple IotaWatts or is it necessary to resume uploads when wifi goes down/up?

Tag sets are also available as $device, $name, $units variables, but I don’t understand how to use them. Are these used to send outputs to InfluxDB?

Should setting up all your inputs to be sent to InfluxDB be the “best” option, then everything else can be calculated from the inputs in Grafana? That way there is no additional space wasted logging something that could be calculated from the inputs(?). i.e. Don’t waste space storing something that can be calculated.

Does each input upload both input values (Vrms + Hz, or Watts + Pf)?

Apologies if I’m asking questions I should be able to easily figure out. Trying to learn IotaWatt, InfluxDB, & Grafana all at once. Like I said, I have a lot of reading to do.

Have been studying this thread. A lot of useful info there that probably answers a lot of my questions (I have to keep studying it). I’m hoping it’s still accurate with recent firmware updates.

It is confusing because there are MANY ways to do things. I don’t have an answer for you since I haven’t done it for iotawatt yet.

I have used emoncms for some time now and it is pretty capable, but doesn’t do some simple things well, ie show you what is happening now or for the last little while.

Grafana does a really good job of displaying just about anything you want, but is complicated and/or tedious to set up. It has great ability to show various time periods, including the last little bit of time.

I am standardizing on Grafana. It has different ways that it can work. It can work with individual measurements or it can do things that are more templated. If you are willing to spend the time to configure every graph/component in Grafana, it is less critical how you set things up.

One thing to keep in mind is that doing math across measurements is not possible/easy with influxdb (as far as I know). This means that you really need to think about what you want to be able to visualize and then you can figure out what data you need to get that.

As an example, I want to see the following:

  1. Voltage over time
  2. Power over time for each circuit
  3. Energy over time for each circuit

That is the very basic use case. Now if I want them in individual graphs there are a couple of ways to set up the data. Using templating, I can create a definition that gets all/many with a single Grafana measurement. The problem with that is the labels don’t look great, too long. But, doing it without templates, while allowing nice names for each line, takes some time to configure.

Read up on templates fof Grafana and figure out what you want your dashboard to look like, then you will have some idea of what data you need to store in influxdb, which will give you insight into what you need to configure in iotawatt.

I believe several people have been using influxdb for some time and maybe they would share what they chose and why.

I use Grafana to keep track of my compost pile. I first used tags to keep track of each sensor, with a templated query to display the results with one measurement definition in Grafana. It works fine.

I then added the same sensors to OpenHAB and it also has an influxdb persistent store. But, it stores each data point in a unique measurement. I then created a new graph to display the results. But, this time I had to create a measurement/line for each sensor. This both good and bad. The good part is that I can configure everything and get nice names for each line. The bad part is that I have to do it for each one. So, both work. Since you don’t have to use templated queries, having tags that allow you to do so is a good thing. Influxdb is smart about the data that it stores, so I don’t believe there is a large cost to having several tags for each data point. Having them allows you to combine data from multiple measurements (this sounds like a contradiction of my statement about multiple measurements, but that was about more complex math than summation/average). When I set it up, I will probably use a rich tag set, so I can see utilization across meaningful multiples of circuits easily, like all first floor, all kitchen, all laundry, all second floor, all bedrooms. The choices that make sense depend on the way the circuits are laid out.

Hope that helps.

1 Like

Thanks for the feedback.

On EmonCMS I was trying to create virtual feeds based on the IotaWatt inputs, but found that they only worked in various places and not in others (graphs, dials, apps, etc.).

I was hoping InfluxDB/Grafana would be able to convert power to energy and sum up inputs/circuits in various ways (1st floor, 2nd floor, bedrooms, etc.) which may overlap each other (e.g. bedrooms on multiple floors). Will have to take the time to research.

Of course these things could be discussed on other forums. So I guess I/we should focus on the IotaWatt part of the equation here.

“For now” I’m just dumping inputs to InfluxDB. I guess I’ll figure out if I need to dump more data depending on my needs.

Thanks again,

Not familiar with how virtual feeds work in emonCMS. The basic input->process list->feed system works pretty well to filter and produce higher level data - like combining mains in a poly-phase system and netting solar and mains to get use. In my emoncms I also subtract all of my loads from the mains to produce a “misc” feed completes the picture with everything that’s not directly measured.

The dashboards and various visualization apps in emonCMS work pretty well, and the “My Electric” and “MySolar” are used by many as they are easy to set up and reliable.

influx can produce energy data from a series of power measurements using the integral function. One caveat is that it reportedly doesn’t work properly with negative data like an import/export series. Although I’m not familiar with the specifics, there are other visualization tools that can be used with influDB as well. If you look at their documentation (influx), they talk about the “TICK” stack, where I think one of the components is their own visualization tool.

Don’t overlook the capabilities of the IoTaWatt native scripting and the “calculator” user interface to create scripts. You can use it to produce complex outputs to influx or emoncms, and you can define local outputs that are available through the IoTaWatt native graph visualization tool. You can also use port-forwarding on your router to expose the IoTaWatt web server to the internet, and with a dynamic-dns service, you can access those visualizations from anywhere. There is a password feature to authorize access.

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?

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:

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.

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

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.

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.

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

Thanks for the write-up it is helpful.

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

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

1 Like

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

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

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

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.

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: