SOLVED: Help! Home Assistant 20-30 MINUTE to restart and database at 9GB for some reason

I have no idea what’s going on here.

I have Home Assistant running in a VM with 32 GB hard drive, 4 cores, 4GB Ram

It’s normally lightning fast, but lately my backups have started to get HUGE (home-assistant_v2.db ios at 9GBs?)

And it takes literally 20-30 minutes to start up. During which the VM is reading in the drive at 10-30 MB/s

During this time however, I am able to mount my folders as drives and access the files over SMB.

I have no idea what’s going on.

Any ideas?

When I check the folders I don’t see anything that’s taking up space except the DB, which is also a concern, but something is eating up space like crazy.

I copied my database file over to my mac and used https://sqlitebrowser.org/ to open it.

I then used the execute SQL tab to run

select entity_id,count(*) from states group by entity_id order by count(*) desc;

to find out what entries were making this so huge.

So obviously I shouldn’t have 1.9 MILLION entries for my vacuum :slight_smile:

Now I just need to figure out what to do about it.

Setup the Recorder integration configuration and moved to MariaDB following @Sir_Goodenough’s video

Backed up my old DB just in case and deleted from config folder in HA

Restarting now takes 16 seconds again = MONEY!

You could hav just set the retain interval to 2 days and done a reach of the database although switching to MariaDB is a good idea. Maybe also exclude entities from recorder…

@DavidFW1960 I did some of that as well.

Although I’m not really sure I understand the difference betwen “purge_keep_days” and “purge_interval”

Keep days is how much history I want to keep and purge interval is how often it purges?

Here’s what I config’d:

#Recorder
recorder:
  db_url: mysql://username:[email protected]/homeassistant?charset=utf8
  purge_keep_days: 30
  purge_interval: 1
              # Everything is included by default.  
              # Ensure you are keeping only what you need to keep DB small
  include:    # Include everything you graph and will want to see later
    domains:
      - alarm_control_panel
      - binary_sensor
      - climate
      - cover
      - light
      - person
      - sensor
      - switch
              # Everything is included by default.  
  exclude:    # Eliminate anything that you never graph or refer back to
    domains:
      - automation
      - updater
    entities:
      - sun.sun
      - sensor.time
      - sensor.date_time_iso # Don't record date and time
      - sensor.yr_symbol # Ignore this as well
      - group.weather # Don't save weather 

Thoughts?

I think purge_interval is deprecated nd it will run it daily anyway now… or maybe it overrides… check the docs. What are you doing with the data that you need keep 7 days? I’m using 2 days.
I exclude everything from recorder I am not interested in checking the history for. Just limit it as much as possible…

Well my thinking was I’d like to have a history of things over the last 30 days in case something happens, like someone breaks into my house while I’m gone for a week.

Other than that. I’m not doing anything with it.

Ha! Like you wouldn’t know anyway and would need to check in HA history to check? My alarm notifies me immediately and I really don’t need to check that history from a month ago… Even using mariadb, keeping so much history for no reason will end in tears again and for the same reason as before.

1 Like

The fundamental problem with the schema of the HASS database is that you can run a query like that! :slight_smile:

1 Like