Unreliable InfluxDB size sensor

[tom_l]

This sensor I have for monitoring my InfluxDB size:

- platform: influxdb
  host: a0d7b954-influxdb
  port: 8086
  username: homeassistant
  password: !secret influxdb_pssword
  queries:
    - name: InfluxDB DB Size
      unit_of_measurement: MB
      value_template: '{{ (value|float / 1024 /1024)|round(1) }}'
      group_function: sum
      measurement: '"monitor"."shard"'
      database: _internal
      where: 'time > now() - 10s'
      field: diskBytes

Is rather unreliable, often reporting 0 MB.

Is there a better way to do this?

[petro]

So, I don’t use influx but that where clause looks suspect. On the surface it looks like it’s only seeing the last 10 seconds of information, then it looks like you sum that information?

[tom_l]

I thought it was summing all the shards (whatever they are).

I copied it from an old Reddit post.

[petro]

I’m only going off what I know in other queries. I could be wrong about the syntax and what it means.

[tom_l]

I found the original source for the sensor, it was Frenk but it seems to have been botched by internet Chinese whispers since then:

config warning InfluxDB sensor

[tjntomas (Tomas Jansson)]

I am using a simple folder sensor instead since I could never get a similar approach working.

sensor:
  - platform: folder
    folder: /mysql_data

I then mapped the influxdb data path as a volume named mysql_data in my HA docker config. This will of course only work if you are able to map the path to a volume, i.e. if influxDB is on the same machine as your HA instance.
See here: https://www.home-assistant.io/integrations/folder/

[tom_l]

Thank you but I’m not sure I can do that with HassOS and the InfluxDB addon?

[tjntomas (Tomas Jansson)]

I’m running HA Core in Docker, so I can’t help with the mapping, but if you are running HassOS, it means that your influxDB folder is on the same system, so it seems doable. You just need to figure out where the influxdb data folder is, and how to map a volume.

[tom_l]

It’s in a seperate container from the home assistant container and HassOS is a pretty minimal OS.

[tjntomas (Tomas Jansson)]

Ok, so it might be difficult. If you can ssh out of the container to the host with a command line sensor, you might be able to do a simple
du -s
to get the folder size, if you can find the folder.

[tom_l]

Is there a way to use this script:

#!/bin/bash

docker exec influxdb du -sh /var/lib/influxdb/data/home_assistant | awk '{print $1; }' | cut -d'M' -f1 > /docker/homeassistant/file_sensors/ha_influx_db_size.txt

With the command line sensor?

Command line Sensor

Or maybe:

Shell Command

This shell command does not work:

get_influxdb_size: "bash docker exec influxdb du -sh /var/lib/influxdb/data/homeassistant | awk '{print $1; }' | cut -d'M' -f1 > /config/file_sensors/ha_influx_db_size.txt"

No errors in the log.

[tom_l]

Some progress.

This works in the influx container:

du -s /data/influxdb/data/homeassistant

It gives me a nice machine readable number of KB (no unit as occurs with the human readable -h option).

According to portainer the container name is

addon_a0d7b954_influxdb

However running this from the CLI

bash docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant

Gives me a bunch of command not found errors. So it was more than a bit doubtful that this would work:

- platform: command_line
  name: InfluxDB Size Test
  command: "bash docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant"
  unit_of_measurement: "MB"
  value_template: '{{ value / 1024 }}'

And as expected, it does not.

Command failed: bash docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant

[tom_l]

This works from the SSH ad Web Terminal Addon with protection mode disabled:

docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant

However according to Frenk there is no way yo access that from within home assistant. So dead end and the only option for Home assistant users is the unreliable and (and frankly incorrect) shard method. Which is also not an option if you disable the internal database - as recommended by InfluxDB themselves.

Only solution: If you want a reliable sensor choose another install method that allows access to docker container files.

[tom_l]

It has stopped working altogether after updating to HassOS 4.14

EDIT: restarting the addon rectified it.

[e-raser]

Just a backlink as I was looking for the same.

InfluxDB information (database size) as sensor in Home Assistant

Hi, as many others (searched the forum, few requests with different information but all without a solution) I struggle to successfully define an InfluxDB sensor to be used in Home Assistant. I want to get the current InfluxDB database size of homeassistant . My query is: SELECT sum("diskBytes") FROM "_internal".."tsm1_filestore" WHERE time >= now() - 7d AND "database"='homeassistant' GROUP BY time(30s), "database" What does the sensor definition in my configuration.yaml need to look like? I…

In case anyone got a better solution… let us know

[tom_l]

Abusing the init_commands in the Web Terminal SSH addon (with protection mode off) I have added the config:

init_commands:
  - >-
    watch -n 10 docker exec addon_a0d7b954_influxdb du -s
    /data/influxdb/data/homeassistant >
    /config/file_sensors/ha_influx_db_size.txt

Which is logging to the file!

It’s a mess but it is logging.

e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:55:12

352808	/data/influxdb/data/homeassistant
e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:55:22

352808	/data/influxdb/data/homeassistant
e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:55:32

352808	/data/influxdb/data/homeassistant
e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:55:42

352808	/data/influxdb/data/homeassistant
e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:55:52

352808	/data/influxdb/data/homeassistant
e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:56:02

352808	/data/influxdb/data/homeassistant
e[He[JEvery 10.0s: docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant                                                                                                                                                          2021-01-25 18:56:12

352808	/data/influxdb/data/homeassistant

Using the file sensor I should be able to get this into home assistant as a sensor. I’m going to increase the watch time to 3000 seconds, 10 was just to test. The only downside is that the web terminal no longer works with ingress. Guess I’ll have to use the core SSH addon for access.

The confusing thing is that redirecting the command output with > should overwrite the file but instead it is appending to the file.

Any Linux gurus know why?

[JZhass]

@tom_l have fun

init_commands:
  - 'while [ 1 = 1 ]; do du -s /etc > /backup/test.txt && sleep 10; done'

[tom_l]

Thanks but while this works:

docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant > /config/file_sensors/ha_influx_db_size.txt

This:

while [ 1 = 1 ]; do docker exec addon_a0d7b954_influxdb du -s /data/influxdb/data/homeassistant > /config/file_sensors/ha_influx_db_size.txt sleep 10; done

Returns:

du: cannot access '10' : No such file or directory

EDIT: I see my missing &&.

Edit 2: So the configuration is changed to this on saving:

init_commands:
  - >-
    while [ 1 = 1 ]; do docker exec addon_a0d7b954_influxdb du -s
    /data/influxdb/data/homeassistant >
    /config/file_sensors/ha_influx_db_size.txt && sleep 300; done

And while it is overwriting this 352808 /data/influxdb/data/homeassistant to the file, I still get 502: Bad Gateway when attempting to open the web terminal. Seems an awful waste of this addon.

[JZhass]

@tom_l don’t give up hope on that amazing plugin… there’s a simple solution to your issue. Just throw ampersand at the end which turns the Linux command into a “background” task. It should just look like the below just pay attention to that last ampersand.

init_commands:
  - 'while [ 1 = 1 ]; do du -s /etc > /backup/test.txt && sleep 10; done &'

[tom_l]

For a moment there I thought we had cracked it. It was logging to file every 300 sec and I could still log into the SSH terminal.

Then I noticed my system temps.

It seems the backgrounded sleep function caused my processor use to spike and my relatively stable cpu temp to go from 50°C to above 80°.

Is it possible to set a cron job instead?

Would that be more efficient?