I have an issue with the Recorder. It doesn’t seem to purge any data. As a result my home-assistant_v2.db is now nearly 5,5Gb in size and it’s affecting performance. When I look in the History, oldest records are from 6th of June 2022.
I have already tried to manually start the Recorder: Purge service (keep 10 days and enabling the Repack option) but it doesn’t resolve the problem. I does run I think because when I start the servivce, I see the home-assistant_v2.db-wal file growing in size until about the same size as the .db file and then reduces back to around 6Mb.
Any suggestions how to resolve this?
p.s. I am running all the lastest versions (Home Assistant 2022.9.4, Supervisor 2022.08.6, Operating System 9.0)
Not to deny you issue, but adding info: I am using mariadb for the recorder and I do not have the same issue. Purge keep days is set to 7, commit interval set to 30, db url set to mariadb and no other settings. Also running everything latest on Home Assistant OS on an RPI4. Hope it helps narrowing the cause down.
I have just migrated to MariaDB. Just one problem though… I lost all my Energy Dashboard history. Any thoughts on restoring that from the home-assistant_v2.db?
Hi. To answer your last question, there is a way to restore the energy data from default database. I just did the same thing early october. (From my memory) You can export from the sqlite-database (homassistant_db) using the SQLite addon or another SQLite software and importing a csv trough the phpMyAdmin addon.
Came across this thread while troubleshooting a similar issue, only I am already using MariaDB, so I’m curious to hear if your problems went away after switching to MariaDB or if the problem still occurs.
Home Assistant is eating up my disk space and the culprit seems to be recorder/MariaDB. Now I just added a sensor to track the size so I’m not completely certain, but the DB size is 5.7GB, and the history goes back beyond the purge interval.
My recorder setup does not specify auto_purge or purge_keep_days so that should default to true and 10 days. Even though it should auto purge the history is kept from the day I started using MariaDB (October 6th).
When I try to purge manually with the recorder.purge-service I get error messages in the home assistant log after 10-20 seconds;
Unhandled database error while processing task PurgeTask ... (shortened due to length of message)
Error executing query: (MySQLdb.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: DELETE FROM state_attributes WHERE state_attributes.attributes_id IN (%s, %s, ... (shortened due to length of message)
Unhandled database error while processing task KeepAliveTask(): (MySQLdb.OperationalError) (2002, “Can’t connect to MySQL server on ‘core-mariadb’ (115)”) (Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)
also a couple other errors that seem to indicate that the recorder cannot commit - probably due to a temporary dropped database connection!? These are different every time I try.
Currently on Home Assistant OS running in a VirtualBox VM. I tried backing up and restoring on a supervised installation on Debian 11 to try to fiddle with things without breaking my main system. There I get the same issues when I try to purge the recorder.
On the supervised system I can see that the homeassistant database is 16.4GB (using ncdu /usr/share/hassio/addons/data/core_mariadb/).
Disk usage is increasing around 10GB/month, so I’m hoping to find a solution before I am forced to delete all my data.
I’m having the same issue (I’m using MariaDB). The size of the states table is almost 3 Gigabytes. I have it set to keep 2 days of history but In reality it has data since June 2021.
I’ve tried to call the purge functionality manually to completely empty the history using:
But it doesn’t seem to do anything? Also can’t find any activity/errors in the logs when I do this.
What could I do to empty the history?
Also is there any manual intervention I could do in the meantime? E.g. empty the states table using PHPMyadmin?
I now get info in the logs by activating debug mode using:
homeassistant.components.recorder: debug
The output in the logs doesn’t show it actually deleting data from the huge states table:
2023-01-31 14:12:37.266 DEBUG (Recorder) [homeassistant.components.recorder.core] Processing task: EventTask(event=<Event service_registered[L]: domain=recorder, service=purge>)
2023-01-31 14:14:42.123 DEBUG (Recorder) [homeassistant.components.recorder.core] Processing task: EventTask(event=<Event call_service[L]: domain=recorder, service=purge, service_data=keep_days=0, repack=True>)
2023-01-31 14:14:42.130 DEBUG (Recorder) [homeassistant.components.recorder.core] Processing task: PurgeTask(purge_before=datetime.datetime(2023, 1, 31, 13, 13, 10, 396264, tzinfo=datetime.timezone.utc), repack=True, apply_filter=False)
2023-01-31 14:14:42.332 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2023-01-31 13:13:10+00:00
2023-01-31 14:14:42.343 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purge running in legacy format as there are states with event_id remaining
2023-01-31 14:14:42.370 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 352 event ids to remove
2023-01-31 14:14:42.420 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f9d1d4aabf0> events
2023-01-31 14:14:43.721 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 294 shared event data to remove
2023-01-31 14:14:44.158 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f9d17432b30> data events
2023-01-31 14:14:44.192 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 5 statistic runs to remove
2023-01-31 14:14:44.212 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 436 short term statistics to remove
2023-01-31 14:14:44.227 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f9d22eed090> statistic runs
2023-01-31 14:14:44.286 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f9d17208d00> short term statistics
2023-01-31 14:14:44.286 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2023-01-31 14:14:55.336 DEBUG (Recorder) [homeassistant.components.recorder.core] Processing task: PurgeTask(purge_before=datetime.datetime(2023, 1, 31, 13, 13, 10, 396264, tzinfo=datetime.timezone.utc), repack=True, apply_filter=False)
2023-01-31 14:14:55.521 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2023-01-31 13:13:10+00:00
2023-01-31 14:14:55.540 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purge running in legacy format as there are states with event_id remaining
2023-01-31 14:14:55.555 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 0 event ids to remove
2023-01-31 14:14:55.582 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 1 statistic runs to remove
2023-01-31 14:14:55.590 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 0 short term statistics to remove
2023-01-31 14:14:55.626 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted <sqlalchemy.engine.cursor.CursorResult object at 0x7f9d172193f0> recorder_runs
2023-01-31 14:14:57.055 DEBUG (Recorder) [homeassistant.components.recorder.repack] Optimizing SQL DB to free space
Open a GitHub issue if you are prepared to provide a mysqldump of your database. I can try importing it and doing a manual purge to see what’s in the data that is preventing the purge from working correctly.
Have you manage to fix your issue? I’m having the same problem when the purge service is being called. I’m using the timescaledb addon which uses postgresql.
I installed the MariaDB add-on and started from scratch with an empty database. I did look at migrating some historical data from the default DB to MariaDB but decided not to pursue that.