DB size - please explain the history graph

Configuration
1

I got a strange history graph for this sensor:

sensor:
  - platform: filesize
    file_paths:
      - /config/home-assistant_v2.db
    scan_interval: 300

изображение
изображение1846×515 28.7 KB

Recorder settings:

recorder:
  auto_purge: true
  purge_keep_days: 7

Since “ purge_keep_days: 7” the maximum history length is 7 days.

First 2 days the DB size is not changing.
Probably this happened because there were no new entities added, the history was being overwritten and this did not change the DB size.

Next the DB size is changing only at night till 04:12.
And this is not clear for me…
These days I did create new entities, this should cause the DB’s growth.
But why this growth was happening at night till 04:12 ???

2

This looks like pretty “normal” based on what I see for the filesize sensor for my home-assistant_v2.db:

grafik
grafik695×517 17.7 KB

04:12 seems to be the daily default purge time if auto_purge: true is set or nothing is set. I set auto_purge: false and I’m running the recorder.purge service from an automation so it better fits my HA usage time and backup concept as well having a bit more control in general etc.

Full recorder settings (default SQLite database), perhaps they are helpful to you:

recorder:
  #db_url: sqlite:////home/user/.homeassistant/config/home-assistant_v2.db
  db_max_retries: 10
  db_retry_wait: 3
  auto_purge: false
  purge_keep_days: 14
  commit_interval: 10

file size sensor:

  - platform: filesize
    scan_interval: 3600
    file_paths:
      - /config/home-assistant_v2.db
3

As far as I understand the “purge” procedure it should get rid of old data (kept for more than 7 days).
And this should cause a decreasing of DB size.
But in my case the DB size is increasing each night and after 04:12 it stops changing.

In your case it looks similar…

4

Yes and No.

  • Yes, it does remove old data from the database.
  • But No, filesize does not decrease when purging data.

Purging does not change the actual file size of the SQLite database. For this a repack is necessary. I run that one manually too, once a week. See the decrease on the left part of my history screenshot.

See Recorder - Home Assistant (whole page quite interesting).

Therefore I think everything’s alright on your side :slight_smile:

5

This also came up on this thread.

I think this may be something relatively new. My DB used to sit at the same size for weeks. Now it grows a little each morning at that magic 4:12 time. It seems to have been introduced around the time Energy Management was.

Doing a purge with repack seems to work. I think I’ll have to automate that, too.

6

Oh, I know nothing about the repack…
Left for learning.

7

I bet it’s another thing introduced at the same time: long-term statistics (which is a prerequisite for the Energy Dashboard). Those stay in the statistics table forever and make a database grow, no matter what the purge keep days setting for the database (does not affect statistics table) is.

That’d be my explanation.

1 Like