Disk on Home Assistant almost full

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

root@homeassistant:~# du -cha --max-depth=1 /var/lib/docker/containers | grep -E "M|G"
61G     /var/lib/docker/containers/8c3460c89bc34266120798a010d18a6957dfd0aa7c75583f5e56e204bf71b22c

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.

truncate -s 0 <logfile>

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.

# Mysql query
DELETE FROM states WHERE created < NOW() - INTERVAL 30 DAY;

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

2019-07-23 04:39:01 WARNING (Recorder) [homeassistant.components.recorder.purge] Error purging history: (MySQLdb._exceptions.OperationalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')
[SQL: DELETE FROM states WHERE states.last_updated < %s]
[parameters: (datetime.datetime(2019, 3, 25, 2, 38, 10, 40477, tzinfo=<UTC>),)]
(Background on this error at: http://sqlalche.me/e/e3q8).

I tried different of solution found on Google…

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation, @@session.transaction_isolation;
+--------------------------------+-------------------------+---------------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation | @@session.transaction_isolation |
+--------------------------------+-------------------------+---------------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         | REPEATABLE-READ                 |
+--------------------------------+-------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> SET innodb_lock_wait_timeout=120;

mysql> SET transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

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!

mysql> use homeassistant
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM states WHERE last_changed < NOW() - INTERVAL 90 DAY;
Query OK, 16151 rows affected (24.90 sec)

Looks like the database just needed some time…?