Openhab2 and InfluxDB (on Cloud)

[bechte (Stefan Bechtold)]

• Platform information:
  • Hardware: Raspberry PI4
  • OS: OpenHabian, newest version
  • Java Runtime Environment: OpenJDK8
  • openHAB version: 2.5
• Issue of the topic: InfluxDB performs to slow

Hey Guys,

I was struggling with my OpenHab2 setup in regard to SD card performance. I had to deactivate my OpenHab logging in order to run my system in production. I have connected our solar inverter and our heating via network / modbus and both produce a lot of value changes which I would like to keep track within my influxdb.

Since every change was recorded as an event and basically got logged into openhab event log, plus triggered a persistence action into influxdb, plus was stored and processed within influxdb, the sd card was on its limits. As I said, I could solve the issue by turning off the logging, but this isnt the ideal solution.

Now, running this setup for quite a while (5 month now), the days get shorter, the nights get longer and I start to think about an alternative. Now I do have made up a list of requirements and I would like to get some feedbacks from you guys:

• I would like to have all my values from the solar inverter, the sensors for temperature, humidity and sunlight stored within a cloud based influxdb on AWS. This also serves as my “backup” for the values.
• I would like to have the values visibile from within the openhab sitemap (ideally) or at least a grafana dashboard. E.g. this could also mean, that I use the kostal binding for displaying values within openhab sitemap, but not storing them elsewhere.
• I wish to have a local “buffer” for values, that will store the values if the internet connection is unavailable.
• I am not interested in using the influxdb persistence for restoring data after restart, for this I simply use a mapdb with the latest values

Currently, I set up a prototype and use telegraf for reading the values from the kostal solar inverter into the influxdb cloud. This seems to work ok. I could do the same thing for my heating. Still wonder, if I can read other items from openhab using telegraf or if I would have to use mqtt to send the values from my thermostats and others sensors into the influxdb cloud. This would also give me a local “buffer”, wouldnt it?

Has anyone made up some experiences with such a setup or would you guys direct my into a different direction, e.g. setup everything locally with a attached SSD on my Raspberry PI4 and syncing / backing up data with a nightly cronjob, etc.?

Happy to hear from you.
Bechte

[NoneWhereTo (None)]

Are your concerns with the SD card related to its IO performance? If yes, how much writes per second do you have?
or isiit towards wear?

[rlkoshak (Rich Koshak)]

@bechte

I would like to have all my values from the solar inverter, the sensors for temperature, humidity and sunlight stored within a cloud based influxdb on AWS. This also serves as my “backup” for the values.

As long as OH can reach the server and has the right credentials there is nothing different from the OH perspective of running the DB server in the cloud or locally.

@bechte

I would like to have the values visibile from within the openhab sitemap (ideally) or at least a grafana dashboard. E.g. this could also mean, that I use the kostal binding for displaying values within openhab sitemap, but not storing them elsewhere.

I’m not sure I understand this one. openHAB is the originator of the values that get sent to InfluxDB. Items change states and that gets stored in InfluxDB. So if you put those Items on your sitemap, you will have that information displayed.

@bechte

I wish to have a local “buffer” for values, that will store the values if the internet connection is unavailable.

Doesn’t exist, cannot be done with the current OH software.

@bechte

I am not interested in using the influxdb persistence for restoring data after restart, for this I simply use a mapdb with the latest values

Than you’ve done nothing to solve your original SD card problem as MapDB stores the data locally. And if you are using it for restoreOnStartup you need to save at least everChange.

@bechte

Still wonder, if I can read other items from openhab using telegraf or if I would have to use mqtt to send the values from my thermostats and others sensors into the influxdb cloud. This would also give me a local “buffer”, wouldnt it?

What you are describing is confusing to me, perhaps there isn’t an understanding of how openHAB works. openHAB has Items. Items change state. One can configure persistence to save the values of Items to a database given a strategy (eveyMinue, everyChange, etc.). InfluxDB is one of the supported databases that can be used for persistence.

You cannot directly populate the state of an Item from the database except through restoreOnStartup. openHAB only queries the database for past data when generating charts (if you are not using Grafana) or in rules where you can ask for the state of an Item at a given time or summary of the values (e.g. sumSince, maxSince, avgSince).

This does not seem to match with what you are asking about. So it seems that what you are asking for cannot be done. But there is enough ambiguity that I might be misunderstanding what you are after.

To my knowledge, there is no way to directly save data to InfluxDB using MQTT. But if there were, yes this could give you a local buffer if and only if:

• both the publishers and the subscribers use QOS 1 or QOS 2 for the messages/subscriptions
• the broker is configured with persistent storage

@bechte

e.g. setup everything locally with a attached SSD on my Raspberry PI4 and syncing / backing up data with a nightly cronjob, etc.?

• Moving everything to an SSD or HDD attached to the RPi is pretty popular.
• Moving the logging to a ram or zram disk. openHABian comes with zram now. That puts the logs into RAM instead of on the SD card so writes do not wear anything out.

[bechte (Stefan Bechtold)]

It’s about IO performance. I cannot tell how much it it in detail, but as I set Log Level to WARN only, it start working again. So, to me it has something to do with IO.

[bechte (Stefan Bechtold)]

Misleading description, I guess, so let me add more detail:

My primary goal here is, that I would like to have a backup of my local influxdb. As I couldnt find a way to easily handle this, I though it might be cleaver to move the influxdb to the cloud. I looked for the AWS service, but I could get it to work with openhab, as it requires more authentication parameters.

Then I stumbled upon influxdata.com and saw that they use telegraf for data integration. And in the next step I found out, that telegraf already supports modbus. Wow, so I dont need to route the state changes through openhab and can directly monitor my solor power plant. Nice!

Then the idea was born, that I could use the telegraf on the raspberry pi to log the data. Said, done. Works.

But: Now, how do I get the data from my items also stored into the cloud influxdb? And here I am… asking for ideas and now you might also understand my first requirement a little bit better:

1. I would like to have all my values from the solar inverter, the sensors for temperature, humidity and sunlight stored within a cloud based influxdb on AWS. This also serves as my “backup” for the values.

=> For the Items within openhab, I would like to know if I could use the persistence provider influxdb to log data to a cloud instance (which requires a token for auth) or if I have to store them locally and sync them somehow else (mqtt?)

2. I would like to have the values visibile from within the openhab sitemap (ideally) or at least a grafana dashboard. E.g. this could also mean, that I use the kostal binding for displaying values within openhab sitemap, but not storing them elsewhere.

=> This closely relates to the values which are not represented as items in openhab, because I log the via telegraf directly into the influxdb cloud. For those, is there an easy way to achieve this?

3. I wish to have a local “buffer” for values, that will store the values if the internet connection is unavailable.

=> So, run a local influxdb and sync this with the cloud influxdb would be a valid solution, then.

4. I am not interested in using the influxdb persistence for restoring data after restart, for this I simply use a mapdb with the latest values

=> I just wanted to state here, that whatever the solution may be, I do not rely for the influxdb values for restoreOnStartup. I use it very spare, just in cases where it is essential for rules to work.

5. Still wonder, if I can read other items from openhab using telegraf or if I would have to use mqtt to send the values from my thermostats and others sensors into the influxdb cloud. This would also give me a local “buffer”, wouldnt it?

What I was trying to say: Let’s image I run openhab without influxdb persistence. In order to log and protocol value, is it possible to use telegraf to read values from openhab and to send them to the influxdb cloud server? And if not, I may use mqtt to route the state changes via mqtt topic to telegraf, which is able to listen to mqtt topics. With that scenario, I would need openhab to deal with influxdb anyway, because I could simply publish all necessary events (state/value changes) through mqtt topics to telegraf which sends it over the influxdb in the cloud and I would be (almost) happy.

===

So, having said that, and while I was rethinking about everything while answering, I believe that adding a local ssd will simply be much more easier to accomplish and solve my issues, and for the backup, I will simply set up a typical backup script for openhab and the influxdb itself.

But, there might be different ideas, still? Would be happy to discuss anyway.

Bechte

[rlkoshak (Rich Koshak)]

@bechte

My primary goal here is, that I would like to have a backup of my local influxdb. As I couldnt find a way to easily handle this, I though it might be cleaver to move the influxdb to the cloud.

There are lots of really simple approaches to this.

I run InfluxDB in a container with folders mounted to the host operating system instead of Docker volumes. So all I need to do is run a cron job once a day that tar and gzips up that folder and puts it on my backup folder on my NAS. If InfluxDB is installed, you could do the same by storing /var/lib/influxdb, and maybe /etc/influxdb as well. NOTE: after about four years my total backup file size is around 150 MB.

If you want a backup that is more portable you can use the influxdb command line tool to create a dump of the database that can then be imported on another instance.

If all you are after is backup, there are many simpler approaches than Telegraf and AWS services and such.

@bechte

For the Items within openhab, I would like to know if I could use the persistence provider influxdb to log data to a cloud instance (which requires a token for auth) or if I have to store them locally and sync them somehow

If the current binding doesn’t support token based authentication you can file an issue to see if that could be added.

As for syncing them, just using MQTT isn’t enough. You need something that can receive MQTT messages and save the messages to InfluxDB. As far as I know there is nothing built into InfluxDB nor Telegraf that would support that.

@bechte

This closely relates to the values which are not represented as items in openhab, because I log the via telegraf directly into the influxdb cloud. For those, is there an easy way to achieve this?

No unless you write something yourself to poll the database for updates and post them to openHAB using it’s REST API (or MQTT or whatever).

@bechte

So, run a local influxdb and sync this with the cloud influxdb would be a valid solution, then.

I imagine there are ways to bridge multiple InfluxDB instances.

@bechte

is it possible to use telegraf to read values from openhab and to send them to the influxdb cloud server?

There is no openHAB add-on. But there appears to be an MQTT plugin so, assuming that there are no limitations in that plug-in or openHAB to make them incompatible that would probably be the best approach. But be aware that you will be doing a whole lot more individual wiring of the data source and InfluxDB database “tables” (I can’t remember what InfluxDB calls them, series?). You won’t get any of that for free from my understanding from scanning their github readme. You will need to configure openHAB to publish messages in the format that Telegraf expects.

@bechte

a local ssd will simply be much more easier to accomplish and solve my issues,