With excluding a lot of entities I already had reduced the database from 1 GB to some 300 MB.
However, only three sensors showed to fill up 80% of that value. They are related to my energy management system: a P1 power sensor and two home battery power sensors. There were some 400,000 and 300,000 values stored respectively. They need to have a high frequency (multiple measurements per second) because they are used to balance the input/output to the grid through the P1-device. The data are handed over by the Homewizard integration.
If you exclude these from the database the problem is solved, but graphs cannot be displayed. A frequency of one minute is more than enough. An added bonus is that high transients of -2 to +3 kW during a second will be averaged out.
I have implemented a three-step solution:
- Introduce a statistics helper for each of the entities, calculate the mean on all data over 1 minute in this helper. If you observe this new entity, it becomes clear that this does not lower the update frequency by a significant factor. Therefore, exclude these new entities also from the database.
- Create a input_number entity for each of the helpers. These can be used to fill a graph, as shown above.
{
"id": "avg_p1_power",
"min": -4000.0,
"max": 10000.0,
"unit_of_measurement": "W",
"name": "avg P1",
"icon": "mdi:meter-electric-outline",
"step": 1.0,
"mode": "box"
}
- Create an automation that copies the helper value into the input_number each minute.
- id: '1779185397284'
alias: set power values
description: ''
triggers:
- trigger: time_pattern
minutes: /1
conditions: []
actions:
- action: input_number.set_value
metadata: {}
data:
value: '{{ states(''sensor.avg_p1_meter_power'')|int }}'
target:
entity_id: input_number.avg_p1_power
- action: input_number.set_value
metadata: {}
data:
value: '{{ states(''sensor.avg_batterkip_power'')|int }}'
target:
entity_id: input_number.avg_batterkip
- action: input_number.set_value
metadata: {}
data:
value: '{{ states(''sensor.avg_batterei_power'')|int }}'
target:
entity_id: input_number.avg_batterei
mode: single
My database was purged and is now 22 MB, I expect it to grow to 30-50 MB.
Hopefully this will also extend the life of the memory modules in my HA Green.