Performance issue

[chemelli74]

HA core version

2024.10.4

Current integration version

0.6.5

Last known working integration version

none

Device type and model

Working mobile app

The detail description of bug/problem

1. Install sqllite3 with apk add --no-cache sqlite-dev
2. Save the following code as test_query.sql:

 SELECT
    states_meta.entity_id,
        states.state
FROM states
LEFT join states_meta ON states_meta.metadata_id=states.metadata_id
WHERE states_meta.entity_id like 'climate.%'

3. Run sqlite3 home-assistant_v2.db < test_query.sql from /config folder of HA
4. Analyse the result

You'll notice that there is a status continuously new state written even if the climate doesn't change state at all.

The logs

1. DEBUG log MUST be enabled (Please refer to README document)
2. Execute the action to trigger error log（you should description the error action）
3. Upload the debug log file as attachment。
4. Please don't paste the log content! upload as a attachment file.
5. Please don't paste the log content! upload as a attachment file.
6. Please don't paste the log content! upload as a attachment file.
   ...

[wuwentao]

I don't thinks it's a bug or error.
from any integration it only provide the origin data to HA, and don't process any db related action.
so it should be saved by HA api.

after some check, it should be below feature:
https://www.home-assistant.io/integrations/recorder#commit_interval

the default value is 5 seconds, and the detail answer for this args is:

How often (in seconds) the events and state changes are committed to the database. 
The default of 5 allows events to be committed almost right away without trashing the disk when an event storm happens.
 Increasing this will reduce disk I/O and may prolong disk (SD card) lifetime with the trade-off being that the database will lag (the logbook and history will not lag, 
because the changes are streamed to them immediatelly). If this is set to 0 (zero), 
commit are made as soon as possible after an event is processed.

[chemelli74]

If the state doesn't change, then there should not be any write on the DB. We need to understand why is seen as a change.

[wuwentao]

it's a sensor data, for example, a temperature value,HA will always write the result to db, if there is no data, the history chart will be none....
the official HA document should be very clear about this action and result, also include how to change it.
please check all the detail in the URL, integration not write db data, HA core will do it.

[chemelli74]

@wuwentao yoi didn't got the point.
Climate entity wrote a off to the db every few seconds. Witch is not normal at all.
We need to understand why if it doesn't change state is still written. Only for this integration.

I have a couple of climates based on Shelly and they don't write on the db ever until they really change state.