How much free space for the recorder.purge service to work?

I noticed that my home-assistant_v2.db whas growing and it was already 5 GB. When searching the internet i found out that most people have lots more configuratian then i do, with a way smaller database. I have some buggy lights, so i decided to trim down the database using the recorder include and excludes. Recorder,yaml:

  purge_keep_days: 3
  include:
    domains:
      - device_tracker
      - light
      - sensor
      - zone
      - media_player
      - binary_sensor
  exclude:
    entities:
     - device_tracker.xxxx_ipad
     - light.xxxx
     - light.and_more_of_this
     - sensor.and_more_of_this

I waited for the recorder.purge service schedule to do its job, but nothing happend. Two days later the database just keeps growing slowly. Running the service manually with repack:true ended up in nothing as well.

{
  "keep_days":"3",
  "repack":"true"
}

During this process i saw my disks filling up, also logging confirms the database is out of space. So it seems space is the issue, 5GB of free space wasn’t enough:

Error writing config for core.restore_state: [Errno 28] No space left on device: '/home/xxxx/homeassistant/.storage/tmpw7nkyjp9'
4:26 helpers/storage.py (ERROR)

Saving JSON file failed: /home/xxxx/homeassistant/.storage/core.restore_state
4:26 util/json.py (ERROR) - message first occured at 3:56 and shows up 3 times

I Extended my LVM’s with 5GB and did a manual repack of the database again. It runs for a little while, and during the process in saw the home-assistant_v2.db-wal file reaching 10GB and the .db-shm 70MB. And once again, no more free space left, and the process stops with the same error of no diskspace. The SQL temp files are deleted by itself.

How much space do i need for this to run as expected? Or is something wrong?

Thanks in advance!
pyvarex

The docs say repack needs as much space as the size of the database, ie if you have 6 GB database you need 6GB of free space.

I would do the purge without repack as it needs far more space to run, and apparently doesn’t actually save much.

Thanks for your response!

I know that i need the database size x2 for stuff like this. When not running any purge service i have 40% of disk usage so i guess 60% must be enough for the database. I assumed my instance would do a scheduled purge based on the default settings? Did i assume wrong?

Isn’t a 5.5GB just to big for the database? Default the settings for the recorder is 10 days. With my “new” recorder.yaml (3 Days) i expected the database to trim down a couple of gig’s at least, even without the repack. I started using the repack options because i did not see any results without as well.

Thanks!

Just remember, if you restart HA the purge counter resets, so every time you restart HA the countdown will start again.

Yes, i don’t often restart HA. It usually runs for a couple of weeks. It should be able to just run it’s scheduled task. Also when i check my monitoring, i can see the bump in disk space is happening based on a schedule.

When running:

{
  "keep_days":"3",
  "repack":"false"
}

I see the same behavior and growth in the db-shm and db-wal files.

As disk space is getting an issue, and its just not worth it to run HA on a 30 gig machine just for a 5,5GB (and growing) database. I decided to migrate to a new instance of HA. I will check if the same behavior appears again.

For now in my opinion the recoder isn’t working as designed, or as described in the documentation. Its need more then twice the amount of the original database, and it’s still not enough.

I agree, it sounds like it is not working properly (for you anyway). Have you tried deleting the database and starting from scratch.

Also you can look at the database with a sqlite tool and see what is being added, this might check if the excluding stuff from recorder is actually working, and clarify what is making it grow so fast.

1 Like

Found this old thread.

Assume my DB is 8GB, there is 9GB free space left.
Is it enough for purge+repack?
Asking since the service call causes a depleting of free space to 0, then I get

WARNING (Recorder) [homeassistant.components.recorder.util] Error executing purge: (sqlite3.OperationalError) database or disk is full
[SQL: VACUUM]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

and free space is again as it was before; seems the service call failed.
Funny thing that it is called a “warning” instead of an “error” (registered an issue)…

Need to repack since normally the DB size is ~1GB (this is a test setup), and these extra +7GB were gained due to some glitch (need to find out as well).

Somewhere in Internet read that for repacking a DB it is required to have a free space = 150% of DB size.