Lots of states changes logged for devices where state doesn't change

Configuration
1

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?

2

Could it be the battery and/or LQI attributes updating?

3

There are a few attributes that are probably changing, such as power, energy and LQI and I would expect these to be logged, but not the switch state.

If attribute is updated is entity state also logged as “new” state?

4

I don’t know the schema well enough to say, but it certainly looks that way.

5

The “state” table hold both “last_changed” and “last_updated”. The former records when the state was last changed and the latter when the state or any attribute changed (or it’s the other way around; I don’t have a mnemonic to remember which is which).

So I guess any attribute change also create a new state.

6

I guess that is the reason for such a big database. Interesting as I don’t have much devices, wonder how big DBs are for users with 100+ devices.

7

I have thousands. I keep 7 days and it is about 1.5GB. I use excludes extensively.