InfluxDB downsampling

[400bird]

I've got InfluxDB running a database storing data about solar production, battery data, grid load, and weather.
But, I'm no database expert, I can't manage to get the thing to down sample the data. A few of the data points come in at 1.5 second intervals.
Once I get more than a week or month out, I'd be happy with 1 minute or 5 minute data points.

I've got a retention policy set up but can't get the continuous query to work.

Anyone here willing to help me out? I can post my retention policy and query if anyone is interested or has experience.

Thanks!

 

[Symbioquine]

I'm curious to hear what you learn/find. I also have this "problem", but I've just been assuming my metrics db would grow more or less indefinitely since storage is cheap. (My .tar.gz backups are up to ~11 GB now for 3.5 years of data)

 

[ChrisG]

I think you’re going to find that all devices ‘record’ at different rates and times which would make normalizing this down to a fixed data point very difficult. Not sure if the juice is with the squeeze on this one. Assume you’re using HomeAssistant? If so I’ve had no problem with it managing my retention schedule. If not, storage is cheap like said above.

 

[400bird]

Well, I am running it on a Raspberry Pi 4 and as the data base grows influx consumes more and more of the CPU
Here it is right now with about 48g (~65 days) in the database and it is already slowing down.
I'm not recording that many data points, just some of them are far too frequent too keep at full fidelity.
Also, I just checked, it is 300 milliseconds, not 1.5 seconds. Hence me thinking that a continuous query to down sample the data would be a great solution.

Not using Home Assistant.

 

[400bird]

And I just reenabled my 30 day retention policy (so it dumped about 1/2 of the data), I don't get the data retention I'd like, but the Pi can simmer down.
Hmm, the disc usage only shrunk by 1 gig, maybe my issue isn't the database size, but I don't know what else it might be.

 

[Symbioquine]

I do have an ugly script that I use to produce summary metrics - interpolating and adding up data from my batteries/inverters/SCCs/etc. Maybe it will serve as inspiration since you could do something similar for down-sampling purposes: https://gist.github.com/symbioquine/07e8789aed818d93ed51fa48350cb19c (It's not the most beautiful code, but maybe you can decipher what I'm doing if you work with Python and InfluxDB much. Happy to answer reasonable questions.)

I mostly use the resulting 1 minute metrics for this summary dashboard;