Recorder, 400 MB database file for 3 days, even if i exclude stuff

Configuration
1

Why do i have such a big .db file? i already configured that less events are recorded, but it always grows big

if i delete the file , its again more the 100 MB after 1-2 days

recorder:
  purge_keep_days: 3
  purge_interval: 3
  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
2

Hi

Do you really mean 400 GB or is it 400 MB? 400 sounds right.

Until I started excluding events I had a size around 1 GB. At the moment I can keep it around 200 MB.

I have this in mine. The 4 first entities relates to a public transportation integration for Denmark. It alone recorded bus arrival times like there was no tomorrow

recorder:
  purge_keep_days: 5
  exclude:
    entities:
      - sensor.vanlose_9a
      - sensor.glostrup_busses
      - sensor.glostrup_s_train
      - sensor.byparken
      - sensor.home_assistant_uptime
    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
3

indeed, its 400MB :slight_smile: corrected the subject
indeed must be somekind of sensor that eats data
but how to find out?

4

It really depends on how you have your DB setup.

For example I’m using MariaDB and I can look at the database contents using ‘PHPMyAdmin’ for example and there are a lot of things in there.

THere is probably something similar for whatever DB you are using.

5

i am just using the default setup that creates the home-assistant_v2.db file

wondering how i can open it to actually see what data is in it? that i can now what entity is filling the dabatase so quick

6

I think the default setup used to use SQLite for the DB.

If this is still the case, then look up how to view and display the contents of a SQLite database.

7

ok, gonna install this tool then : https://sqlitebrowser.org/

8

ok, seems the “states” and the “events” table is quite big, the events is the biggest

anyone that can tell me some kind of query to identity rows that are eating the table?

9

I use a whitelist rather than an exclude list. Makes it easier to only store the things you really want.

My db size is about 250-300MB in size now, quite stable and purging data older than 30days every 2 days. Using a MariaDB is recommended over sqlite though, the speed difference is significant.

10

yes , thats indeed another approach

seems in the events table, there are lot of rows that are type state_changed

gonna exclude that one also

11

Hi, can I ask where you managed to get this list of event types?

I’ve looked thru the recorder docs multiple times here: Recorder - Home Assistant

And it shows no examples of how to exclude events from the DB let along a list of all the possible events that can be excluded.

Or is it the list of events in the ‘dev-tools/events’ tab as my event list looks very different to yours.

Here’s what I have:

homeassistant_close (6 listeners)
entity_registry_updated (136 listeners)
homeassistant_stop (12 listeners)
device_registry_updated (1 listeners)
* (1 listeners)
time_changed (35 listeners)
core_config_updated (3 listeners)
state_changed (131 listeners)
component_loaded (3 listeners)
platform_discovered (12 listeners)
alexa_media_**************@***** (52 listeners)
tileboard (1 listeners)
service_registered (1 listeners)
service_removed (1 listeners)
panels_updated (1 listeners)
themes_updated (1 listeners)
persistent_notifications_updated (1 listeners)
12

More information available in this thread:

1 Like
13

Thanks, I’ve not seen that before, I’ll try using the examples in the thread. :slight_smile: