How to reduce your database size and extend the life of your SD card

True, but that’s relatively easy to add in the spreadsheet: search-and-replace "- " for " - ". I could do this quicker than the time it takes to restart HA, and I haven’t had to mess around with my config files.

Feel free to add it as an option. But please don’t delete the file notification version.

… ashamed to admit I had not realized we can set the history items too… I am all in to minimizing what’s been recorded, and have meticulously set logbook and recorder (always thought recorder set the history items…duh)

will add immediately, so thanks for this!

fwiw, I excluded these, and only add the individual entities in include :wink:

exclude:
  event_types:
    - service_removed
    - service_executed
    - platform_discovered
    - homeassistant_start
    - homeassistant_stop
    - feedreader
    - service_registered
    - call_service
    - component_loaded
    - logbook_entry
    - system_log_event
    - automation_triggered
    - script_started
    - timer_out_of_sync

  domains:
    - alert
    - automation
    - binary_sensor
    - camera
    - climate
    - counter
    - customizer
    - device_tracker
    - group
    - input_boolean
    - input_datetime
    - input_number
    - input_select
    - input_text
    - light
    - media_player
    - proximity
    - scene
    - sensor
    - script
    - sun
    - switch
    - persistent_notification
    - person
    - remote
    - timer
    - updater
    - variable
    - weather
    - zone
1 Like

Not seen it mentioned but sometime around 110 they added a new command option to tune the commit interval on the DB.

All the above is still relevant to cut down on what is stored in the DB, but by upping the commit interval you can batch commit which will lead to far fewer writes.

I use a 15 second interval which has drastically reduced the number of writes to the DB, and confirmed by monitoring with iostat

commit_interval: 15

1 Like

I always thought so. Maybe it is just the docs that are misleading ?

I read this:

If you only want to hide events from your history, take a look at the history integration. The same goes for the logbook. But if you have privacy concerns about certain events or want them in neither the history or logbook, you should use the exclude/include options of the recorder integration. That way they aren’t even in your database, you can reduce storage and keep the database small by excluding certain often-logged events (like sensor.last_boot).

which is in the recorder docs just above https://www.home-assistant.io/integrations/recorder/#service-purge

That anything excluded in the recorder settings, is excluded from the DB completely, and wont be seen by logbook or history either.
One can further narrow down what is used I either logbook or history, by excluding entities that are recorded, but you don’t need to show up on logbook and history.

1 Like

Or, inside the Template page click inside the result set and SELECT ALL, e.g. Ctrl+A and then paste to a text/code editor. There will be extra lines at the top which can easily be deleted.

Or, click just to the left of the very first result and then hit SHIFT key until you get to the end, followed by SHIFT+Click to select just the result set.

Paste all into a suitable text/code editor like Visual Studio Code to easily manage the indentation.

I’ve used both the methods to help trim my database down, thank you for the extensive write up.
:beers:

1 Like

@tom_l glad you brought the original content back!

One more thing to possibly add to the DB solution — while old-school nerds may love command-line, a GUI is sure pretty to look at. I’ve been loving HeidiSQL for this purpose and actually stayed away from command-line for a while just due to the appeal. It’s a Windows UI and eliminates the need for something like phpMySQLAdmin — which was the UI choice before finding HeidiSQL and the ease of use.

My DB is 1/3 the size now… thanks for prompting me to re-visit this subject!

Can’t take all the credit. I was away for a few days and due to finding a few requests on my return was about to put it back “by popular demand” but mbuscher had beaten me to it.

Put the DB in memory and stop ALL writes to the SD card…

recorder:
   db_url: 'sqlite:///:memory:'

Simple as that…, I have a 1 day purge setting and cut the recorder down to what I actually need to see, which makes it about 100MB. This also produces huge performance gains vs. any other kind of DB implementation.

PS, Set your log level to critical and further reduce writes

4 Likes

Doesn’t that cause your database to be totally wiped out if you reboot your server or restart Home Assistant? From the doc: “The database ceases to exist as soon as the database connection is closed.”

Yes but he’s also set his purge to 1 day. Likely logging relevant data to influxDB, just a guess here.

Yes, the database is wiped on restart, but so what ? The DB contains only historical log data and absolutely nothing relevant to the operation of the system (in my case at least). I use the logbook for debug purposes only and the history view is interesting, but not functionally necessary.

I switched to mariadb and can’t find the db file either with Samba or ssh. I would like to find it before I start trying to reduce its size. I used the default settings to install mariadb. Thanks

It is not easy to find. There is a topic about it :

Found it using add on phpmyadmin. Also if you have the ssh / terminal add on and have protection mode disabled you can find it by the following:

  1. docker ps you need to find the id of the maria container
    2.docker exec -it fbd81dccedd4 /bin/bash
  2. cd data/databases
    4 . du -h homeassistant - this gives the size of the folder where the database is stored
  3. or cd homeassistant
  4. ls -hal - this gives the individual files in the folder.
    Type "exit " to get out of the bash.

If you get a chance, please keep track of your database size before updating to 0.112 and then check again 11 days later to compare.

If you have a large database > 16GiB, please be aware that it may take a while for the 0.112 update to complete the database schema changes.

Any idea when this will be released ?

Tomorrow if all goes well.