Hi, I’m looking for some guidance as how to query sensor data in the MariaDB
I’m familiar with recorder purging, yet I’d like a little more insight as to what’s going on
Usecase:
- I have 200+ devices around the house, which generate A LOT of sensor data.
- I’ve dialed this down somewhat by curbing what Recorder actually will store,
- Nevertheless I still have a 2.3GB Maria DB and in periods it’s really getting pummeled, although holding up fine. Btw using Dbeaver as a console for the db
Specifically I’m looking to:
a) Query top x entity data volume, i.e. which sensors are spamming the most. This would allow me to go back and tweak my recorder config accordingly.
b) Query dormant/stale data. I understand that HA eventually will purge stale sensor data, yet i’d like to identify data in the db which hasn’t been updated for x amount of time
c) Is there a way to monitor purge activity? let’s say I’ve kicked off a manual purge after making changes, where can I see the progress, or just if it’s actually finished? I checked both supervisor and core logs yet curiously nothing there mentions a purge.
If somebody’s been grappling with similar issues, and perhaps have wrangled some SQL queries into existence, I’m all ears! Thank you.
Anyone?