Question regarding recorder

Configuration
1

Lately I installed an electric power consumption meter (Shellly EM).
In order to keep track of it’s data I changed the below configuration of recorder to 30 days from 2-3 days it was before (in order to have the monthly data of shelly EM)

Now I see that my database grows bigger and I would like to see what other options I have to deduce it.

According to documents I could add the commit interval integer

are you using it? What could be a possible value that could work effectivly? 10 sec ? 30 sec?

What it means that the logbook and history will lag?
Is there any other way I could keep the database small? When I had the purge_keep_days: to 2 days
my database was 60-80mb whivh was fine. Now in a week is 190 mb and I would like to reduce it.

**commit_interval integer (optional, default: 1)**
How often (in seconds) the events and state changes are committed to the database. 
The default of 1 allows events to be committed almost right away without trashing 
the disk when an event storm happens. Increasing this will reduce disk I/O 
and may prolong disk (SD card) lifetime with the trade-off being 
that the logbook and history will lag. 
If this is set to 0 (zero), commit are made as soon as possible
 after an event is processed.

my recoreder configuration right now

  purge_keep_days: 30
  include:
    domains:
      - sensor
      - switch
      - automation
      - climate
#      - binary_sensor
      - person
      - device_tracker
      - media_player
    entities:
      - binary_sensor.presence_16
      - binary_sensor.conditioner_on
      - binary_sensor.openclose_18
      - binary_sensor.1_main_door
      - sensor.temperature_purifier
#      - sensor.beok_temperature
      - sensor.outdoor_temperature_2
      - sensor.living_room_temperature_2
      - sensor.bedroom_temperature_2
      - sensor.bath_temp_2
      - sensor.aircon_living_room_time_on
      - alarm_control_panel.mqtt_alarm
      - binary_sensor.14_sirina
      - sensor.outdoor_temperature
      - sensor.bath_temp
      - sensor.air_condtion_daily_energy
      - sensor.air_condtion_monthly_energy
      - sensor.daily_energy
      - sensor.monthly_energy
  exclude:
    entities:
     - sensor.date
     - binary_sensor.vibration_sensor
     - automation.message_move_sensor_living_room
     #- binary_sensor.presence_16
     - sensor.dark_sky_daily_summary
     #- sensor.wind_speed_automation
     #- sensor.dark_sky_wind_speed
     - sensor.ha_wind_speed
     - sensor.marousi
     - sensor.open_weather_rain
     - sensor.wind_speed
     - sensor.wind_speed_average_1_minute
     #- sensor.wind_speed_ss_90_m4
     - sensor.internet_time
     - climate.beok_thermostat
     - sensor.beok_temperature
     
     - sensor.open_weather_condition
     - sensor.open_weather_cloud_coverage
     - sensor.dark_sky_cloud_coverage
     - sensor.dark_sky_humidity
     - sensor.dark_sky_temperature
     - sensor.open_weather_rain
     - sensor.dark_sky_icon
     
     - sensor.cpu
     - sensor.date
     - sensor.time
     - sensor.last_boot
     - sun.sun
     - sensor.packets_in_eno1
     - sensor.packets_out_eno1
     - sensor.network_in_eno1
     - sensor.load_1m
     - sensor.load_5m
     - sensor.load_15m
     - sensor.network_out_eno1
     - sensor.memory_use_percent
     
#     - sensor.cpu_temperature
#     - sensor.last_boot
#     - sensor.memory_use_percent
     - sensor.processor_use
     - sensor.disk_use_percent_home
#     - sensor.cpu_temperature
     - sensor.cpu
     - sensor.disk_free_home
     - sensor.disk_use_home
     - sensor.network_in_eno1
     - sensor.network_out_eno1
     - sensor.load_1m
     - sensor.load_5m
     - sensor.load_15m
     - sensor.packets_in_eno1
     - sensor.packets_out_eno1
     
#     - sensor.outdoor_temperature
     - sensor.living_room_temperature
     - sensor.bedroom_temperature
#     - sensor.bath_temp
     - sensor.humidity_19
     
     - sensor.open_weather_humidity
     - sensor.open_weather_pressure
     - sensor.open_weather_weather_code
     - sensor.open_weather_wind_bearing
     - sensor.open_weather_wind_speed
     - sensor.redmi_kiriaki_battery_health
     - sensor.redmi_kiriaki_charger_type
     - sensor.redmi_kiriaki_wifi_frequency
     - sensor.redmi_margarita_battery_health
     - sensor.redmi_margarita_charger_type
     - sensor.redmi_margarita_wifi_frequency
     - sensor.redmi_margarita_wifi_link_speed
     - sensor.redmi_margarita_wifi_signal_strength
     - sensor.sm_g955f_audio_sensor
     - sensor.sm_g955f_bluetooth_connection
     - sensor.sm_g955f_charger_type
     - sensor.sm_g955f_detected_activity
     - sensor.sm_g955f_do_not_disturb_sensor
     - sensor.sm_g955f_last_reboot
     - sensor.sm_g955f_light_sensor
     - sensor.sm_g955f_next_alarm
     - sensor.sm_g955f_pressure_sensor
     - sensor.sm_g955f_public_ip_address
     - sensor.sm_g955f_steps_sensor
     - sensor.sm_g955f_storage_sensor
     - sensor.sm_g955f_wifi_frequency
     - sensor.sm_g955f_wifi_link_speed
     - binary_sensor.sm_g955f_bluetooth_state
    domains:
      #- media_player
      - event_states
2

Commit interval is not going to reduce your database size. All it does is store the state changes in memory and then write them all at once to the SD card after the interval is up. This can help with SD card longevity but will cause your logbook to lag behind (the amount of the interval).

There are only three ways to reduce the database size.

  1. Reduce the number of keep_days.
  2. Use more excludes, see How to reduce your database size and extend the life of your SD card
  3. Store long term data somewhere else. Like InfluxDB, which is better at storing time based data. See Frenk’s tutorial here: https://youtu.be/m9qIqq104as but only include the entities you want in the Influx configuration YAML (rather than everything). it’s the same include/exclude syntax as the recorder.
1 Like
3

I see. I misunderstood the commit interval option.
I think it would be nice to have more options like keep Xdays for the particular entities - and Zdays for the rest.
I imagine I cannot (somehow) record only the shelly EM for 30 days and the rest for 3. eh?
Thanks for the links!

4

See option 3 above.

I keep 7 days for most of my entities (after doing option 2) in MariaDB. This averages 350MB.

I keep 2 years of 30 or so included only entities in InfluxDB and this will eventually get to about 1.5GB.

2 Likes
5

Hey Makis,

Not sure if this is true or not but I saw comments saying the use of “include” and “exclude” in the recorder and together doesn’t work well (use one of another) - some kind of bug.
Also, try to use SQLite in the command line to:

  • check the top 20 or 30 or 40 entities that are storing more data in your DB
  • manually delete them
  • use vacuum to manually clean the DB
  • there are some new “exclude” syntax that you can use (using * in front of some things) like, for example, media* … check recorder documentation again.
2 Likes
6

Hi
I just found some time and added the influxdp addon. If I understand the consept correct I have added the following in my config,yaml

influxdb:
  host: xxx
  port: xxx
  database: xxx
  username: xxx
  password: xxx
  max_retries: 3
  default_measurement: state
  include:
    entities:
      - binary_sensor.conditioner_on
      - binary_sensor.openclose_18
      - sensor.temperature_purifier
      - sensor.outdoor_temperature_2
      - sensor.living_room_temperature_2
      - sensor.bedroom_temperature_2
      - sensor.bath_temp_2
      - sensor.aircon_living_room_time_on
      - alarm_control_panel.mqtt_alarm
      - sensor.outdoor_temperature
      - sensor.bath_temp
      - sensor.air_condtion_daily_energy
      - sensor.air_condtion_monthly_energy
      - sensor.daily_energy
      - sensor.monthly_energy

So now I can install grafana to have a visualization for the above entities which the will be kept for a year (I have to modify the database to 365d ?)

And after I can change my recorder to 7 days.
Is that right?

7

Yes.

1 Like
8

Hi tom_I
I have installed grafana with influxdb and added the sensors that I need to keep the yearly data.
I replaced the
purge_keep_days: 30
to
purge_keep_days: 7
restarted HA but my database home-assistant_v2 is still very large (now it is at 1.3GB)
Should I just delete the home-assistant_v2 and let HA make a new one or what else?

9

Yes you can delete it or run the purge service (with repack set to true) in the developer tools states menu. That’s quite a cpu intensive operation though and you still won’t see the full effect for 7 days.

1 Like
10

I deleted it. Thanks. I think is much better with this set up

1 Like