I kindly ask some help about this problem…
My DB (default DB, Raspberry PI3+) size is now about 582Mb (running HA from about 3 months) … but even if I deleted some unwanted events (Shelly EM Power Factor and voltage… which was about 800.000 events)… the DB Size remained the same even after called - many times - the service “Recorder.purge” and “Recorder.purge_events”…
The strange thing is that if I do a Full backup the DB size appears to be half size (about 240Mb)…
How to shrink/compact the Database so it can become smaller?
Thanks in advance
1 Like
Quick question: Have you tried enabling Repack when calling the recorder.purge service?
1 Like
auto_repack is default ‘true’ so unless this was intentionally switched of?
520 Mb is however not that big, depends a but on how many devices you have.
Oh, yeah, if you already have auto_repack set in configuration.yaml. I was on about doing it ‘manually’ under Developer Tools.
And no, 520MB isn’t that much, and besides; it’s nice to have a long history of sensors in HA.
The backup is compressed, so that’ll probably explain why that is smaller in size.
Yes… I enabled the repack switch…
The compressed backup could be an explanation…
But I can’t explain why aftert had deleted about 800.000 events (unused events) - and after the repack - the DB size remained the same… I can’t explain that…
I checked the events records by using the following query
SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 20;
Hmm, I dunno what to suggest.
A while ago I also had problems slimming the database. I was looking at getting the file home-assistant_v2.db down in size. The problem I had, was that I was stupid not connecting to the fact that I actually used MariaDB.
Another time, when I didn’t care about the history, I just deleted the database file. HA rebuilds it at next restart.
EDIT: Found this page which seems to have some good info on optimizing the HA database.
Solved… now the DB size is around 300Mb (as I expected after the events delete)…
Before the Purge wasn’t working because I had an addon (Google drive backup) that was causing high CPU/Memory usage and this was preventing the purge operation to be successfull…
With that addon stopped… the purge really shrinked the DB…
Case closed…