I see! I use the HA docker image but the sqlite database file is outside docker, its an mounted volume. I havent seen any instability, it is about 100MB. But I wanted to increase recording time from 10 days to like 1 or 2 years. Not sure if I will start running into issues now.
I to run on a decent powerful NUC with 128GB NVME SSD.
If I wanted to run another db for the recorder, I guess it would make sense to run the db in a separate docker image and connect it to the HA image. Seems complicated.
Just wondering: if you have intel NUC: why you don’t use HAOS? If. If your nuc supports uefi bios boot then it shouldn’t be a problem: just flash HAOS image on your drive - i used m2 external case and etcher - put it back into nuc and you’re done. All other addons are a fun to add then…
Regarding 1-2 years : it will be a huuuuge file… for example: i’ve had 10 days set on my previous db and db file was just over 2GB. Personally, i wouldn’t reccomend it, but if you DO go with it it’s another reason to switch to MariaDB.
Note that in any case you already have long term statistics for all major entities anyway. Check history of your, say, temoerature for a few months or 1 year back. It should appear.
I found out that many of my custom sensors caused an extreme amount of db entries. The reason is that any entity value change triggers the calculation of a custom sensor.
And as I was using three different entities to calculate a sum of them, it caused every 5 to 20 seconds a new db entry. Finally I solved this issue by adding a time_pattern trigger. By that the calculation isn’t triggered by the involved entities, instead its triggered by a fixed time interval. In my example every 5 minutes:
- trigger:
- platform: time_pattern
minutes: "/5"
sensor:
- name: WP Energy Sum
unique_id: shelly3wp_energy
unit_of_measurement: 'kWh'
state: >-
{#- on reboot some sensors do not return a number (instead they return "unknown" or "unavailable") -#}
{%- if is_number(states("sensor.shelly3wp_channel_a_energy")) and is_number(states("sensor.shelly3wp_channel_b_energy")) and is_number(states("sensor.shelly3wp_channel_c_energy")) -%}
{{ (states("sensor.shelly3wp_channel_a_energy") | float + states("sensor.shelly3wp_channel_b_energy") | float + states("sensor.shelly3wp_channel_c_energy") | float) | round(2) }}
{%- else -%}
unavailable
{%- endif -%}
device_class: energy
state_class: total_increasing
The graph for example confirms the much rarer calculation:
Good point. My approach has been to exclude any new entities I create, unless it’s something I really need to keep. Thus any interim entities used just for a template, calculation or whatever, would be put on the “exclude” list immediately.
I like the analogy of a self-inflicted DOS attack. I think that that way Recorder is configured by default (keep everything) is setting new users up for exactly this kind of failure.
Great work. Did you manipulated the backup file name or format in any way cause it looks like the script expects the naming to be different than standard backup naming, eg: Full Backup 2023-12-05 09:00:20.tar
recorder:
#How many seconds to wait before writing data to db, default is 1 second.
commit_interval: 32
#How many days to keep the data before purging it, default 10 days.
purge_keep_days: 720
auto_purge: true
auto_repack: true
And my DB grew to about 770MB.
Now I wanted to reduce it to 60 days:
recorder:
#How many seconds to wait before writing data to db, default is 1 second.
commit_interval: 32
#How many days to keep the data before purging it, default 10 days.
purge_keep_days: 60
auto_purge: true
auto_repack: true
However, after running this a couple of days, my db size is not decreasing. Why is it not purged?
The purge is not run daily. I read somewhere (probably this thread) that it’s only once a month, or is it once a week?
Anyway, you can do the purge from within HA. Developer Tools / Services / Recorder: Purge.
NOTE: Be sure to select the check box for Repackand turn on the slider switch that’s hidden way over on the right. I have no idea why you have to select this option twice, but that’s how it is.
Give it a few minutes to work and you should see your db size diminish. BTW, 770M is low, given your 720 days purge setting. With the default of 10 days mine would jump up to 3G in no time. I had to ruthlessly exclude everything I could and shorten the purge setting to make it manageable. Of course there have been some improvements since then, so maybe what you’re seeing is more typical now.
Purge is ran every night. Purge does not reduce the size, it removes the data. The allocated memory stays the same.
Repack (which is what reduces your database size) runs every second sunday of the month. Also, repack is not instant, it takes hours or days in some cases.
