MariaDB will not purge

Configuration
1

My home assistant database has grown from 148mb to 902mb in seven months. I didn’t pay too much attention at first as I assumed it was the impact of long term statistics. As the backups were taking progressively longer to run, I decided to check my purge settings.

In configuration.yaml:

recorder:
  db_url: mysql://hass:xxxxxxx@core-mariadb/homeassistant
  purge_keep_days: 10

I then ran Recorder:Purge through developer tools:

service: recorder.purge
data:
  apply_filter: false
  keep_days: 10
  repack: true

This appeared to run with no errors in the log and CPU rising from 5% to around 50% for about 5 minutes. However, an entity history enquiry shows data is still present back to 5th June 2022 and the database size hasn’t changed. I’m wondering if this could be connected to the 2022.6 release as it references database improvements?

Apart from the increasing database size my system runs perfectly with no log errors or problems.

I’m hoping someone can suggest how I could go about diagnosing and correcting this problem as I would rather not delete the database and lose my long term statistics.

2

For anyone else experiencing this issue, I found a work-around to get the size of the database down.

Deleting one domain at a time using “Recorder: Purge Entities” completely removed the history for each domain. After this, I used the phpMyAdmin add-on Web UI to execute SQL queries against the homeassistant database:

optimize table events

and then

optimize table states

to release the space. I’m not sure if this will have any impact on the regular recorder purges which had been failing for many months, but at least the backup files are now a far more managable size (700 mb less) and I can always do this again when the size becomes too large.

2 Likes