`history.get_last_state_changes` is poorly optimized which results in overly long queries with the filter integration

[mmiller7]

The problem

Upgrading to 2022.11 (or newer) the filter entities are extremely slow to start up (nearly 10 minutes). Reverting to 2022.10.5 startup is fast again (15 seconds). No errors appear in the regular logfile. Also sensor data does not update properly now (flat-line) after update.

Starting sensor.filter, not everything will be available until it is finished.

What version of Home Assistant Core has the issue?

core-2022.11.2 and newer

What was the last working version of Home Assistant Core?

core-2022.10.5

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Filters

Link to integration documentation on our website

https://www.home-assistant.io/integrations/filter/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

Thread on HA forums: https://community.home-assistant.io/t/after-2022-11-startup-really-slow/487368

Workaround: Downgrade to 2022.10.5

[mmiller7]

Log file with debug on

home-assistant.log_filter_startup.txt

[mmiller7]

This is still an issue in 2022.11.3, filter takes exceptionally long time to start.

[gigatexel]

I'm experiencing the exact same issue.

[mmiller7]

Still an issue in 2022.12.1.

[mmiller7]

Also if you ignore it, when the filters eventually start it looks like everything is flat-lined.

Example - going from 2022.10.5 -> 2022.12.1 for a few hours -> rollback 2022.10.5

[gigatexel]

Still an issue here. 500s suspiciously looks like some kind of wait-for-condition not ending

[gigatexel]

Still an issue in 2022.12.1.

Could you fill in all fields in the opening post (such as correct integration name and link to the docs)? Otherwise, the integration developer will not be tagged and this issue will likely not get resolved.

[mmiller7]

Still an issue in 2022.12.1.

Could you fill in all fields in the opening post (such as correct integration name and link to the docs)? Otherwise, the integration developer will not be tagged and this issue will likely not get resolved.

I don't understand?

I already had the integration name and the logs and linked thread reflect that too, I now added the link to the docs. I don't know what more I am expected to provide?

If you see where I've put a wrong thing please do help me figure out what it ought to be because I'm not understanding.

[gigatexel]

@dgomes can you help us here?

filter has also stopped working on my system too

[dgomes]

Nothing has changed in the filter integration in several releases... I would guess the issue is something related to the recorder (from which the filter ask information on load)

I see the graph but the log does not relate to the graph.... could I get a new log matching a graph ?

[gigatexel]

Nothing has changed in the filter integration in several releases... I would guess the issue is something related to the recorder (from which the filter ask information on load)

I see the graph but the log does not relate to the graph.... could I get a new log matching a graph ?

Do you mean the HA bootup log?

2022-12-13 10:47:03.751 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:48:03.899 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:49:03.992 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:50:04.096 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:51:04.186 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:52:04.275 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:53:04.359 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:54:04.440 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: sensor.filter
2022-12-13 10:54:21.025 WARNING (MainThread) [homeassistant.components.sensor] Timed out adding entities for domain sensor with platform filter after 500s

[dgomes]

I would like to gather log messages all the way from working -> not working -> working again (thanks to version changes)

[gigatexel]

HA has logs everywhere :-) Which ones do you need?

[dgomes]

home-assistant.log

[gigatexel]

2022.10.5

2022.12.4 homeassistant_20221204.log homeassistant_20221005.log

[dgomes]

please share the yaml configuration of all filters

[mmiller7]

please share the yaml configuration of all filters

Is there some easy way to get all of them? I use packages and I probably have a couple dozen package-files with filters spread between them, it's going to take some time if I have to hunt them all down by hand and copy them into a single file and sanitize any sensitive personal information from each package file.

[dgomes]

that's actually an important debugging task:

• Can you come down to a single filter and check if works or not ?

[gigatexel]

I only have one filter sensor (never had more than one)

  - platform: filter
    name: "Regenwater ADC LPF"
    entity_id: sensor.shelly_uni_regenwater_adc
    filters:
      - filter: lowpass   
        precision: 1
        time_constant: 5

[mmiller7]

So I think I've narrowed it down to the filters on all my (15-20) temperature+humidity sensors. If I cut all of them out startup is like 10-ish seconds.

It looks like for each temp+humidity sensor (which are all duplicates of this) the startup time goes up by about 30 seconds per. Just "outside_front" is like 33 seconds start, "outside_front" and "outside_rear" is like 65 seconds. I've got one of these pairs for each room.

The source sensors are MQTT sensors which update about every 15 seconds.

At the moment I don't have time to spend chasing this more tonight so I'll have to leave it at this for a bit, but maybe someone can figure out then how to reproduce or what to try next.

# Sensor Filter Package File

sensor:


  - platform: filter
    name: "Outside Front Temperature Filtered"
    entity_id: sensor.outside_front_temperature
    filters:
      - filter: outlier
        window_size: 3
        radius: 10.0
      - filter: time_simple_moving_average
        window_size: 00:01
        precision: 1
  - platform: filter
    name: "Outside Front Humidity Filtered"
    entity_id: sensor.outside_front_humidity
    filters:
      - filter: outlier
        window_size: 3
        radius: 10.0
      - filter: time_simple_moving_average
        window_size: 00:01
        precision: 1

  - platform: filter
    name: "Outside Rear Temperature Filtered"
    entity_id: sensor.outside_rear_temperature
    filters:
      - filter: outlier
        window_size: 3
        radius: 10.0
      - filter: time_simple_moving_average
        window_size: 00:01
        precision: 1
  - platform: filter
    name: "Outside Rear Humidity Filtered"
    entity_id: sensor.outside_rear_humidity
    filters:
      - filter: outlier
        window_size: 3
        radius: 10.0
      - filter: time_simple_moving_average
        window_size: 00:01
        precision: 1

[dgomes]

Another question: do you use the default database (SQLite) or something different (MySQL ?)

[gigatexel]

I'm using MySQL using the official integration.

[mmiller7]

MariaDB (official addon), 14 days history limit on recorder

[mmiller7]

I would like to gather log messages all the way from working -> not working -> working again (thanks to version changes)

Please see attached.

All good and working: working pre-upgrade 2022-10-5 log.txt

Upgraded, filters take forever to start and don't properly start: broken post-upgrade 2022-12-7 log.txt

Rolled back to old version, all working again: rollback working 2022-10-5 log.txt

[gigatexel]

Some more input. After a few days, the filter sensor kicks in.

• First arrow is a reboot
• Second arrow is when the filter sensor kicks back in for an unknown reason
• This sensor is again a reboot, filter sensor becomes "unknown" again

[dgomes]

Thanks, this last comment gives me a clue that the issue might be related to the history functionality (filter is initialised with historic information)

I will try to address this during the holiday season...

[mmiller7]

Thanks, this last comment gives me a clue that the issue might be related to the history functionality (filter is initialised with historic information)

I will try to address this during the holiday season...

Happy to run more tests or capture more logs - I've just exhausted my ideas of what else to try.

[home-assistant[bot]]

filter documentation filter source

[mmiller7]

Still an issue in 2023.1

After a bit more trial and error, it looks like maybe its related specifically to the "outlier" filter. I commented that out in all my filters and it seems now to be working better.

With "outlier" removed:

I hope this can ultimately be fixed, the reason I was using the outlier and average was to avoid the intermittent bad-data I get from some of my thermostats and flatten out some of the jitter as they report in. I also tried just "outlier" with "average" removed and it still exhibited the problems so I think it's a bug in the "outlier" code.

[gigatexel]

This config caused 500s timeout for initialisation of the filter component, so "outliers" does not seem to be the issue.

  - platform: filter
    name: "Regenwater ADC LPF"
    entity_id: sensor.shelly_uni_regenwater_adc
    filters:
      - filter: lowpass   
        precision: 1
        time_constant: 5

However, since 2023.1 it works and no longer timeouts but still takes a suspiciously long time to initialize.