DB Self prune

[ur7x]

My System became unstable once my DB got over 4GB.

I would be helpful if the OS could be set to manage the size of the DB, or if the DB could be set to purge old entries.

[CaptTom (Tom)]

It does purge old entries. It’s just that the defaults aren’t always right for everyone, and the options aren’t that specific.

How to keep your recorder database size under control

Note: This guide was written for an earlier version of Home Assistant (around 2021). Since then, changes have happened, but this guide hasn’t been updated (yet). Please be aware your Home Assistant version can be slightly different, and thus you need to adapt yourself the instructions from this guide. Introduction This is yet another guide on: How to view the size of your database. How to figure out which entities are using too much space. And how to filter them out. I’m running Home Assi…

Recorder Retention Period By Entity

I’d like to be able to set a Recorder keep_days value for each entity. Background: New and non-technical users are steered toward running HA on a RPi with an SD card. Yet, if they take the defaults, every event and state change is recorded in the SQL database on the SD card, leading to a bloated database file and shortened SD card life. Yes, I know there are lots of ways to change these defaults, including using other databases and database locations. But for most beginners, it’s a steep learn…

[ur7x]

Thanks, almost as soon as I posted that I found sample automation that will prune the DB base for “noisy” sensors or based on the number of days.

I wrote a simple automation that will prune the DB every night… I trust/hope that will solve my stability issue.

I’m surprised that this issue isn’t more common.

[CaptTom (Tom)]

@ur7x

I’m surprised that this issue isn’t more common.

I think it’s very common. There’s really no way for a new user to know that they need to delve deep into database management or identifying chatty entities. It’s also not something many beginners are going to be good at.

On the other hand, developers don’t see it as an issue, since they’re much more in tune with how HA works under the hood, and likely have the skills to customize the way Recorder works to meet their specific needs.

So, nothing changes and new users are continually running into problems. Some make it here to ask for help. I suspect others just give up.

[tom_l]

@CaptTom

On the other hand, developers don’t see it as an issue

Incorrect. There has been extensive work done this year to optimise the database. It is now at a point where the previously recommended solution, using the MariaDB add-on instead, is no longer required.

And this work is continuing.

[CaptTom (Tom)]

Yes, I should have added that the database has been tuned to be much more efficient. This is a welcome change!

My point was around the whole issue of what to save, and for how long. This is not something which comes naturally to beginners, and the built-in tools are limited and not obvious. This is one reason I created the FR to put the decision about whether, and for how long, to retain each entity’s data right on the screen where the other properties of each entity are shown.

[ur7x]

@tom_l

Incorrect. There has been extensive work done this year to optimise the database. It is now at a point where the previously recommended solution, using the MariaDB add-on instead, is no longer required.

And this work is continuing.

Thanks for your work on this…

My suggestion, if we know that the DB is somewhat unstable over 3G and completely unstable over 4G then HA should produce a “Notification” or “Repair” message when the DB gets over 2G. There should be a system setting to limit the size of the DB with a warning about sqlite and large DBs

Not sure that I would call myself a beginner either, I’ve been running HA for over 4 years now. When the DB got to about 3G (based on my SAMBA Backup size) I would get a mysterious monthly system crash… And I would wake up to a cold dark house… Checked hardware, configuration, etc… Reboot and it was fine…till next month Figured OK this is a special version of Unix that just isn’t that stable… Then as the DB got to 3.5G I was getting weekly crashes… These all seemed to happened about the same time as my weekly SAMBA backup to my Synology. So I blamed that add on and turned it off. Then starting this week I was getting daily crashes… Where the system would be sortof alive and sortof dead. I could still access the logs, and some system tools, but all of the control of smart devices was dead.

Then there, burred in the logs, was a message that the DB was corrupted.
“Unrecoverable sqlite3 database corruption detected” Followed by a very long list of system python scripts that were failing to talk to the DB.

This solves lots of mysteries for me… For one I could not understand why my weekly backup kept getting bigger and bigger, when I wasn’t changing the system. The size of my system was the same… but the DB kept getting bigger and bigger, and the backups took longer and longer.

Right now my DB seems to be growing about 20M per day… If the system auto prunes around 10 days It should top out at a stable 200M… Not sure how mine got to 4G.

The next frustration is that it is not simple to view system logs after a system boot and most cases of a DB corruption my system was completely unresponsive. It was a fluke that I was able to get into the logs and figure it out.

So a setting to preserve logs boot to boot would also be helpful.

Thanks again for your work on this system (in a previous life I was a DBA and I know what a thankless job that can be)

[arwooldridge]

Can you provide a link to the original sample automation?

[ur7x]

This worked for me

alias: System DB Purge
description: ""
trigger:
  - platform: time
    at: "18:00:00"
condition: []
action:
  - service: recorder.purge
    data:
      repack: true
      keep_days: 3
mode: single