Hello, this is my first post on this forum. I need some help cleaning up my influxDB instance which runs as an addon in HomeAssistant HAOS.
It is v1 and runs for around 5 years now survived many updates. the DB was recently 50GB bid and that is an issue (for backup etc.).
I am not a DBA in any form but technical.
I started to read what can be done and I did a few things.
I changed from “log all” to “log specific”.
Connect via InfluxDBStudio-0.2.0. I deleted may entities and some measurements. This freed up around 5GB and now my DB is 45GB. (yellow arrows of things I deleted (not there anymore in this screenprint).
Tried to ask many AI machines for help, but none are reliable for me…
What I want to achieve is:
a. to cleanup the DB readings for entities that I deleted. From what I understand is that although deleting entities does not necessary delete readings or measurements.
b. set a retention policy where now it is “never”.
Picture in next post as I am new.
rp_6m_2y: Keeps 5-minute averages for data between 6 months and 2 years.
rp_2y_inf: Keeps 15-minute averages for data older than 2 years.
Run the following commands in the InfluxDB CLI:
-- Retention Policy for data between 6 months and 2 years
CREATE RETENTION POLICY "rp_6m_2y" ON "home_assistant" DURATION 18M REPLICATION 1
-- Retention Policy for data older than 2 years
CREATE RETENTION POLICY "rp_2y_inf" ON "home_assistant" DURATION INF REPLICATION 1
The autogen policy remains active and retains raw data for up to 6 months.
2. Create Continuous Queries
Now set up Continuous Queries (CQs) to automatically compress data and store it in the appropriate Retention Policies.
Compression for data between 6 months and 2 years:
CREATE CONTINUOUS QUERY "cq_5m" ON "home_assistant"
BEGIN
SELECT mean(value) AS value, mean(crit) AS crit, mean(warn) AS warn
INTO "home_assistant"."rp_6m_2y".:MEASUREMENT
FROM "home_assistant"."autogen"./.*/
WHERE time > now() - 2y AND time <= now() - 6M
GROUP BY time(5m), *
END
Compression for data older than 2 years:
CREATE CONTINUOUS QUERY "cq_15m" ON "home_assistant"
BEGIN
SELECT mean(value) AS value, mean(crit) AS crit, mean(warn) AS warn
INTO "home_assistant"."rp_2y_inf".:MEASUREMENT
FROM "home_assistant"."autogen"./.*/
WHERE time <= now() - 2y
GROUP BY time(15m), *
END
3. Verify the Configuration
After creating the RPs and CQs, verify everything is set up correctly:
To check your RPs:
SHOW RETENTION POLICIES ON "home_assistant"
To list your CQs:
SHOW CONTINUOUS QUERIES
Summary
Raw data (last 6 months): Stored in autogen.
Compressed data (5-minute intervals, 6 months to 2 years): Stored in rp_6m_2y.
Compressed data (15-minute intervals, older than 2 years): Stored in rp_2y_inf.
a. to cleanup the DB readings for entities that I deleted. From what I understand is that although deleting entities does not necessary delete readings or measurements.
I’m not sure what you mean by that, if you delete something it actually gets deleted… the cleanest delete is the measurement drop, which is quite extreme… but you can also delete data points by filtering on specific tags and time (useful for anomalies)
b. set a retention policy where now it is “never”.
That’s just a simple command, which can look like that
ALTER RETENTION POLICY "_RP_" ON "_DB_" DURATION 180d SHARD DURATION 1d DEFAULT
Every X time influx will enforce it and delete the data outside retention. If you want to keep the oldest data do not run this before moving/aggregating those data to a new RP. (and that’s done via a one-shot manually run query, not via CQs)
To CREATE new RPs the command is almost the same, I would discourage using names like rp_2y_inf, putting duration and aggregation frequency in the name itself will make no sense if tomorrow you decide to change it (but that’s a personal consideration)
About the CQs, I doubt the one you posted will work for several reasons:
it filters data already outside the retention or in the best case borderline into retention
not considering the previous point, every time the query runs it will aggregate ALL the data and update the already existing “snapshots”… that’s not needed and is also bad performance-wise
generally speaking, CQs do not work that way, by default, you generate the aggregated data immediately, practically if I run a CQ every 5m, it creates the aggregate of the last 5m of data (meaning that I’ll have the “current” data in 2 RPs, with different precision), you can use a wider window (CQ advanced syntax) to re-generate more than one interval, useful if your data are subject to delays
Have a look at the docs linked above to have a better understanding of the possibilities and behavior of CQs
Hi, thanks for taking the time to react and respond. Much appreciated!
I have read the docs, but I am unsure of the correct order and exact syntax of the commands.
I hoped there was a simple instruction of steps. Now I am still very unsure what to do with those RPs. I have 1 now… what do I nee dto do where to get 3 and automatically have data moved over? And how do the RP syntax look? And where to apply them?
Ok, I am going in circles here. I have read the docs and conceptually I get them.
But…
As described in the 1st post I am not a DBA. I have some guts though, but since this might potentially break my entire DB (even later discovered) I asked for help on the actual steps and commands.
So how I understand the docs is 2 fold:
create the Retention Policies (RPs).
create the Continuous Queries (CQ).
For 2 I think I read that if these are not there data is not automatically changed to another RP and if there would be 3 RPs all data will end up in the autogen RP and stay there forever (correct?).
Data will only be moved to another RP if there are proper QCs (correct?).
Lets first focus on step 1 than to create the 3 RPs as I proposed.
STEP 1
1. Define Retention Policies
Three RPs:
autogen: Keeps raw data for the last 6 months.
rp_1: Keeps 5-minute averages for data between 6 months and 2 years.
rp_2: Keeps 15-minute averages for data older than 2 years.
Run the following commands in the InfluxDB CLI:
-- Retention Policy for data between 6 months and 2 years
CREATE RETENTION POLICY "rp_1" ON "home_assistant" DURATION 18M REPLICATION 1
-- Retention Policy for data older than 2 years
CREATE RETENTION POLICY "rp_2" ON "home_assistant" DURATION INF REPLICATION 1
The autogen policy remains active and retains raw data for up to 6 months.
I had to fight a little with cahtgpt to specify my question but then I got this answer.
Is that correct for STEP 1 ???
Here’s how to configure retention policies in the InfluxDB Admin GUI, where there is a button for “Add Retention Policy” and fields for “Retention Policy” and “Duration”:
Instructions for the GUI:
1. For autogen (raw data for the last 6 months):
Retention Policy: autogen (This is pre-filled when editing the default autogen policy.)
Duration: 26w (26 weeks = 6 months.)
Click Save or Apply after entering the values.
2. For rp_1 (5-minute averages for data between 6 months and 2 years):
if above 3 new RPs are in place. Would Ik only have to enter the following SQL commands? Are they correct?
Set Up Continuous Queries (CQs)
Continuous Queries (CQs) in InfluxDB are used to automatically aggregate data from one retention policy (RP) into another. This ensures your existing raw data in the autogen RP is processed and stored appropriately in the new RPs.
a) For rp_1 (5-minute averages for data between 6 months and 2 years):
This CQ will calculate the average of all measurements every 5 minutes and write the results into the rp_1 retention policy.
CREATE CONTINUOUS QUERY "cq_rp1" ON "home_assistant"
BEGIN
SELECT mean(*) INTO "home_assistant"."rp_1".:MEASUREMENT
FROM "home_assistant"."autogen"./.*/
GROUP BY time(5m), *
END
What this does:
Takes all measurements (.*) from the autogen RP.
Calculates the 5-minute averages (mean(*)).
Writes the results into the corresponding measurements in rp_1.
b) For rp_2 (15-minute averages for data older than 2 years):
This CQ will take 5-minute averages from rp_1 and further aggregate them into 15-minute averages for rp_2.
CREATE CONTINUOUS QUERY "cq_rp2" ON "home_assistant"
BEGIN
SELECT mean(*) INTO "home_assistant"."rp_2".:MEASUREMENT
FROM "home_assistant"."rp_1"./.*/
GROUP BY time(15m), *
END
What this does:
Takes data already processed into 5-minute averages in rp_1.
