Hey,
I was looking at my DB size and noticed there is around 4mio state changes logged in the last 10 days which seems a lot to me.
I ran some SQL queries on the mariaDB instance and found some high numbers:
SELECT
states.metadata_id,
states_meta.entity_id,
COUNT(*)
FROM states, states_meta
WHERE states.metadata_id = states_meta.metadata_id
GROUP BY
states_meta.entity_id
ORDER BY
COUNT(*) DESC;
+-------------+----------------------------------------------------------------+----------+
| metadata_id | entity_id | COUNT(*) |
+-------------+----------------------------------------------------------------+----------+
| 478 | switch.toilet_towel_radiator_led_disabled_night | 106021 |
| 412 | sensor.toilet_towel_radiator_update_state | 106021 |
| 465 | binary_sensor.toilet_towel_radiator_update_available | 106021 |
| 473 | switch.stairway_1m_extender_button_lock | 98117 |
| 287 | sensor.stairway_1m_extender_update_state | 98117 |
| 481 | switch.bedroom_extender | 94818 |
| 499 | update.bedroom_extender | 94818 |
...
These sensors have mostly static values. Bedroom extender for example is always on and state doesn’t change.
Looking at the states of switch.bedroom_extender:
SELECT
states.state,
COUNT(*)
FROM states, states_meta
WHERE states.metadata_id = states_meta.metadata_id
AND states_meta.entity_id = "switch.bedroom_extender"
GROUP BY
states.state
ORDER BY
COUNT(*) DESC;
+-------------+----------+
| state | COUNT(*) |
+-------------+----------+
| on | 94940 |
| unavailable | 5 |
| unknown | 3 |
+-------------+----------+
Why does it log same state so many times. Seems like a useless thing to do and is only filling up space. If it’s because of “last update” field, one would expect it to only update the timestamp field, not add a new entry.
Some entities have normal number of states logged. Looking at the entities, it does seem like most of the problematic sensors are Zigbee2mqtt devices and some shellies, but with shellys, the most chaged entities are temperatures, current, voltage which can change more often than the “on/off” switch.
Anyone has any idea where to look. Is this a HA or Z2M issue?