Scrubbing InfluxDB database

Configuration
1

Is there any easy way, preferably a GUI, to scrub or maintain the InfluxDB database?
It has grown to a considerable size (>1GB) and would like to prune old data. Tried to setup a Retention policy, but unsuccessful. Any tips or manual to follow?

Cheers, Mark

2

That’s the way to do it.
In what way was it unsuccessful?

1 Like
3

Unable to put a working Continuous query and or to verify it’s operation.

Example: SHOW RETENTION POLICIES on “home_assistant”
returns

name duration shardGroupDuration replicaN default
autogen 0s 168h0m0s 1 TRUE
2y_5m 17520h0m0s 168h0m0s 1 FALSE
5y_15m 43800h0m0s 168h0m0s 1 FALSE

Which is fine.

But ideally would like to downsample all data.
So tried the following command: SHOW CONTINUOUS QUERIES

measurement name query
home_assistant 2y_5m CREATE CONTINUOUS QUERY 2y_5m ON home_assistant BEGIN SELECT mean(value) AS value INTO home_assistant.2y_5m.temperature FROM home_assistant.autogen.temperature GROUP BY time(5m), entity_id fill(previous) END
home_assistant cq_5m_temp CREATE CONTINUOUS QUERY cq_5m_temp ON home_assistant BEGIN SELECT mean(value) AS value INTO home_assistant.2y_5m.temperature FROM home_assistant.autogen.temperature GROUP BY time(5m), entity_id fill(previous) END

But unable to verify it’s operations… ;-/ Could not find any example to downsample the data.
Or cleanup data from old / deleted sensors (but first fix this :wink:

4

Well, you should have data in home_assistant.2y_5m.temperature:wink:

So you don’t have a retention policy on the data sent by HA right now…

6

So a retention policy on should be set? What duration should be set then?

7

Whatever duration you see fit.
But, to get back to your original concern, that’s the only way to delete data to reduce the size of your db.

Probably best approach is to first set it far, check, decrease the retention period, check, rinse repeat until your DB has a size that fits you. It should then stay more or less at the same size.

1 Like
8

Thanks, I’ve set it to 6 months now. Will check over the course of the next days and weeks

9

MMm, it’s scrubbing data alright. But the Continuous Queries do not deposit any data in the databases ;-(
Any suggestions?

10

No clue. I assume you tested the queries out of continuous context and they work?

11

No, I copied examples from a website as my SQL knowledge is not that great. Tried to run the examples above but no indeed no output. Where to find a working examples :roll_eyes: ?

12

Nobody knows your influxdb setup :wink:
You have to have the main query working before making a continuous query out of it…

I.e. make the hereunder return values:

SELECT mean(value) AS value FROM home_assistant.autogen.temperature GROUP BY time(5m), entity_id fill(previous)

Random example of mine from grafana, on homeassistant.autogen

SELECT mean("value") FROM "°C" WHERE ("entity_id" = 'mijia_out_temp' OR "entity_id" = 'exterieur_temperature') AND $timeFilter GROUP BY time($__interval) fill(linear)