Disk on Home Assistant almost full

Robert Andresen Uncategorized Leave a Comment

For a long time, the disk on Home Assistant box has been slowly filling up. I found that is was the MySQL DB docker container that took up all the space.

My setup

My Home Assistant runs in docker on an old Dell 7010 with Ubuntu 18.04. Data is recorded in another MySQL docker container.

The HA container is running on host network, with mounted config to local disk. For debugging I used putty to view log files and navigating, and Portainer to manage and run console in docker containers.

Debugging disk-space

The output shows that the MySQL container is using 61GB. I checked the database. States and event had more than 10.000.000 records.

homeassistant.states used 5,7 GB and homeassistant.events used 13,7 GB (both included indexing), which is 19,4 GB. So what is using rest of the 41,6 GB?

I found that the volume attached to the MySQL container was using approximately 20 GB. So the 61 GB was not the data in the database.

Json log-file

Inside the container-folder /var/lib/docker/containers/8c3….b22c, I found a log file: 8c3460c89bc34266120798a010d18a6957dfd0aa7c75583f5e56e204bf71b22c-json.log that took 61 GB of disk space.

Solution

Truncate the file.

Note to self: I should probably look at some log rotation or schedule task to fix this in the future.

Cleaning up the database

My disk-space issue was resolved, but I still don’t like the database to be that big. 19,4GB of states for just 6 months isn’t a good solution for the future.

States and event had more than 10.000.000 records. I tried to delete them – both directly in mysql and with recorder.purge.

Both gave the same error «Lock wait timeout exceeded; try restarting transaction». Example from the Home Assistant log file:

I tried different of solution found on Google…

The error was always the same: «Lock wait timeout exceeded; try restarting transaction»… I checked the process list (show processlist;), to make sure nothing was writing to the database. I tried restarting, turning off home assistant, etc…

After a couple of hours I gave up and went to bed. When trying the next morning… Eureka!

Looks like the database just needed some time…?