ZHA - Huge home-assistant_v2.db file, zigbee sensor state written too often

Configuration Zigbee
1

Hello guys,
I installed 6 ZB sensors and sockets few weeks ago and my database file is now 5times bigger then before. I decrease “purge_keep_days” for lower value but main problem is that some air quality sensor write into “states” table in database every second (every change). Actually one sensor have over 200tho rows for 25days and the device have 6 of there sensors. It means that only one device created over 1.2mil rows into sensor table during 25-30days. My installation is on RPI4 and SD card (this will kill SD card very quckly) actual file size is 1.1GB, it seems to me lot!

Is any solution how to decrease writing frequency from some sensors/devices? Ideal solution would be some AVG for some time period and write ONLY average.

Thank you
Lukas, CZ

2

  1. Use Recorder’s exclude: in configuration.yaml to stop the bleeding.

  2. If you still need the data to be recorded, create a template with a time pattern trigger.

3

I using exclude, but I need collect the values. Maybe your idea would work, thank you for answer, I"ll test it.

It would be improvement for next version of ZHA, to be able set harvesting time and calculate average value.

4

I think that this should be an option on the HA recorder rather than on ZHA. You’d still want a high frequency real time value reported on your dashboard, but record the data at a lower rate. Lowering the report interval won’t give you the former. Also a lot of devices other than Zigbee are affected by this problem, especially power measuring equipment. A recorder setting would be best to handle all cases in one go.

2 Likes
5

I like @HeyImAlex’s approach. There’s already a Feature Request to allow Recorder retention periods by entity, rather than one size fits all. I think allowing users to set the update frequency for each entity would be a great feature, which could be implemented at the same time.

If you post a FR for that it has my vote! Of course, it all depends on finding a developer interested in taking on the job.

1 Like