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

Installation Home Assistant OS
1

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.

2

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.

image
image384×594 19.8 KB

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.

3

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!

1 Like
4

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…

5

@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:password@core-mariadb/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?

6

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…

7

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.

8

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
9

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

1 Like