WTH can't I see what is consuming database size?

danps · September 8, 2020, 10:12pm

Occasionally my database has a sudden large increase in size and then settles around a new larger size. For example…

I have filters on history and logbook in an attempt to keep DB size smaller, however it would be really useful if there was some sort of tooling which could be used to show what is consuming DB size.

tinglis1 · September 8, 2020, 10:24pm

I use the MariaDB and myphpadmin addons for this reason. It allows me to do deeper analysis into my db.
It would be goos to have some basic diagnostics for the built in db though.

tinglis1 · September 8, 2020, 10:25pm

Also you should vote for your own post.

tom_l · September 9, 2020, 5:57am

That’s your snapshot size. Why are you assuming the database is to blame?

danps · September 9, 2020, 2:10pm

Good point!
Another good tool would be breaking down the size of the snapshot.
I think most of my add-ons are pretty stable in that time, so I put the snapshot growth down to DB growth.
Also HA takes longer and longer to restart which I also attribute (equally without evidence) to DB growth.

mbuscher · September 9, 2020, 4:10pm

You can take a look at the content of these backups using the following steps.

SSH to your HA instance, e.g. by using the SSH & Web Terminal add-on of a supervised installation.
Go to the /backup directory
Use the tar command to view the content of a given backup file.

tar -tvf somefile.tar

Below a screen capture of such a sequence. I was surprised to discover how large the backup of the AdGuard add-on was in particular. I removed that one from my backups as I have no need for those logs.

cgarwood · September 9, 2020, 11:03pm

I have filters on history and logbook in an attempt to keep DB size smaller

Filters on history and logbook don’t actually prevent data from being written to the database, you want to exclude entities from recorder: instead.

But I agree, some database stats and tools would be helpful.

danps · September 10, 2020, 6:44am

wow! Good call. I now know the culprits…

No idea why these are consuming so much space though.
Will uninstall the dev and beta, and do a reinstall of esphome, and see if tomorrows snapshot is smaller.
Thanks

tom_l · September 10, 2020, 7:05am

Oh I know. Been there done that. The problem is the large number of small files each occupying a muinimum disk allocation unit. e.g.

I also worked out the automated partial snapshot: Automated snapshot optimisation but don;t use it any more. I use the [Smaba Backup Addon](http://Smaba Backup Addon) instead.

basnijholt · September 10, 2020, 10:28am

I have written a little script to easily analyze what is taking up space in your database.

See this on GitHub, just place it in /config/ and run

python3 /config/analyze-db.py

osufnl499 · December 9, 2020, 3:27pm

I’m a little confused. Do I run this as a call service from the python script documentation? What exactly will it output? I tried running it through SSH without luck.