Limit size of home-assistant_v2.db

Configuration
1

Hi there.

When backing up my homeassistant installation I was surprised to see that home-assistant_v2.db has grown to over 3 GB in quite short time.

What is stored in there? Is this data required? Is there a setting to limit the size or will I have to keep buying bigger and bigger hard drives?

Hiran

2

It is used to store the history of your entities. Use the exclude or include options to only record what is important to you.

You can also limit the number of days stored.

3

Just take out media players and you are good.

Besides what does one need media player history for? :smirk:

1 Like
4

Thank you tom_l. The two links you sent are a good explanation. Just wondering that my configuration file only contained a reference to history (and nothing about recorder), and I could not find these two pages by navigating the website. It seems you need to know they exist before you can access them.

What I understand now is that by configuring history I can modify what I want to see, while configuring the recorder would modify what gets stored. Interestingly the default value of keeping records for 10 days did not work for me.

I configured 10 days now explicitly and need to watch what’s going to happen.

5

I’m going to add that I ran into a similar problem, with the Logbook tab not loading and my home-assistant_v2.db growing to be very large (4.2GB in my case).

I believe the problem was the introduction of a “date” field that was of course changing every second of every day. This “blew up” the log. After excluding it from the logbook (and history and recorder) and deleting the old home-assistant_v2.db file and then restarting. The logbook page started working again. Here’s my example config to exclude the date element.

logbook:
  exclude:
    domains:
      - updater
      - media_player
    entities:
      - sensor.ups_status_date
1 Like
6

Thank you @randomstring for bumping up this thread. I just checked my history size and was wondering about 8.6 GB of sqlite data. And this although I had added these lines into my configuration:

recorder:
    purge_keep_days: 10
    purge_interval: 5

Following https://www.home-assistant.io/docs/backend/database/ I connected to the database and ran
select * from states;
Among the result were entries with a timestamp from february 2019 - definitely older than 10 days.

Would that mean the settings in the configuration do not work? What might be missing?

7

I have constantly had issues with my database being corrupted and Im not sure whether its related to running theses commands as your doing above in recorder. Since removing the commands and running keep_days:10 separately in dev-tools > services, followed by repack:true I’ve had no issues.

8

I tried doing the same, and I think I found dev-tools>services pane (my Homeassistant shows all the german labels). But when I choose which service to run I can neither find keep_days or repack. How exactly did you do that?

9

http://ipaddress:8123/developer-tools/service

Select recorder.purge as Services
then enter {“keep_days”: “10”} in service data and click on ‘CALL SERVICE’
and enter {“repack”: “true”} in service data and click on ‘CALL SERVICE’

6 Likes
10

Thank you for pointing it out. So I checked my system again, and my home-assistant_v2.db was 8.8 GB in size.

Then I ran recorder.purge service with { “keep_days”: “10” } and it took a long time (two more files were created, I waited until they were gone). After that home-assistant_v2.db was at 8.9 GB size. Hmmm…

Then I ran recorder.purge service with { “repack”: “true” } and again it took a long time, After that home-assistant_v2.db took 8.9 GB still.

Looks like your workaround does not work for me. The file is just growing.

11

Try reducing the keep days to 5

12

This is getting frustrating. As you suggested I ran the service with { “keep_days”: “5” }, then with { “repack”: “true” } and still my DB is at 8.9 GB. There must be some something we have not yet spotted.

I am running Homeassistant 0.98.4 inside a docker container (my config directory is mounted from outside so I have access to those files regardless of the container).

13

Delete the db file, it may be corrupt and restart home assistant

14

Just before following your hint now I tried one more test by running recorder.purge with
{ “keep_days”: 10 }
and
{ “repack”: true }

(note the missing qutoes around the values. On purpose I passed them as number and boolean rather than string). Unfortunately this also did not help.

So my next step is to assume the file is corrupt and simply remove it.
After a { “repack”=true } with no database file it got recreated and is 4 kB in size. Let’s see how fast it will grow now…

15

I use

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

and have a history card displaying the sensor on my frontend.

3 Likes
16

Thank you for that hint. While restarting I moved to homeassistant v0.98.5. The lines you suggest produce this output:

[homeassistant.components.filesize.sensor] Filepath /config/home-assistant_v2.db is not valid or allowed

This is strange as the file itself exists and should be accesible at that path. However it is a side effect when visualizing my original problem so I won’t bother.

17

Sorry, forgot to say you need to white list the config directory by adding:

  whitelist_external_dirs:
    - /config

to the homeassistant: section in the configuration.yaml file

2 Likes
18

Ah, now it works. I have a sensor now and Homeassistant can even produce a nice graph.
I tried to lookup the documentation for the filesize platform but could not find anything. It seems Homeassistant has many nice features but usability is low if people don’t know about them.

Screenshot%20from%202019-09-16%2009-09-36
Screenshot%20from%202019-09-16%2009-09-361082×616 43.7 KB

19

Hi, i have an unsolved question about the db management:

  1. I want to see some mqtt sensor frequently updated on my frontend (every 10s that is the telemetry time set in the tasmota sensor) but i want to store it in the db every 1-2 minutes. Is this possible?
20

You could create a template sensor which has the state of the mqtt sensor. Then exclude the mqtt sensor for the recorder and only log the template sensor to the db.

sensor:
  - platform: template
    sensors:
      sensor_for_db:
        value_template: "{{ sensor.your_fast_changing_sensor.state }}"
        entity_id: []
        friendly_name: 'Your template sensor'        

automation:
  - alias: 'update_template_sensor'
    trigger:
      - platform: time_pattern
        minutes: '/2'
    action:
      - service: homeassistant.update_entity
        entity_id: sensor.sensor_for_db
2 Likes