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: