History Statistics Sensor is permanently consuming high amounts of CPU load

[diplix]

Home Assistant release:
0.45.1

Python release:
Python 3.6.1 / macos el capitan

Component/platform:
History Statistics Sensor

Description of problem:
my recorder component is set to save data into mysql and i have two History Statistics Sensor defined:

recorder:
  purge_days: 14
  db_url: mysql://xxx:xxx@127.0.0.1/hass

sensor:
  - platform: history_stats
    entity_id: switch.bad_nachtlicht
    state: 'on'
    end: '{{ now().replace(hour=0).replace(minute=0).replace(second=0) }}'
    duration:
      hours: 24
    name: Bad Nachtlicht gestern
  - platform: history_stats
    entity_id: binary_sensor.licht_bad
    state: 'on'
    end: '{{ now().replace(hour=0).replace(minute=0).replace(second=0) }}'
    duration:
      hours: 24
    name: Bad Licht gestern

after startup of hass cpu load spikes to 100-150% as the two History Statistics Sensors are being calculated. this takes about 2 minutes, after which hass startup is finished. unfortunatly the History Statistics Sensor keeps on calculating the history stats, which keeps the CPU load well above 100%. this is a screenshot that shows normal load (without the two sensors mentioned above) and then a restart with the sensors activated.

Expected:

• caculate history stats data more efficient (when using mysql)
• let hass startup before initially calculating all history sensor data
• do not recalculate yesterdays data all the time

[bokub]

That's a serious problem indeed.
It would be great to run benchmarks with the default database too, just to measure the impact.

A higher scan-interval could help, at least as a temporary solution.

---

• caculate history stats data more efficient (when using mysql)

I don't know if it is possible, because the history_stats component uses the methods from the history component.
Do your home-assistant struggle to display graphes when you click on a component? (like this)

---

• do not recalculate yesterdays data all the time

That definitely deserves more thinking, I'll work on it.

[MartinHjelmare]

In the update method, check if self._period has changed after updating it. If not, return without querying the database.

[diplix]

yes, my history component displays graphs. drawing those graphs is however not producing any visible CPU load at all and querying a day worth of data is done in 1 or 2 seconds, as opposed to 1 or 3 minutes, that it takes the history_stats component to calculate a day worth of data.

a higher scan intervall help reduce the permanent followup load, but it still blocks HA-startup (after restarts) for 2-3 minutes, even if theres only one history_stats sensor defined.

[diplix]

thanks für taking care of this. tried your revision #7858 and as you said, this only partially fixes the problem. startup time is still delayed for about 2-3 minutes if i use the two history sensors mentioned above (without them startup time is around 20 seconds), but after that, everything is fine (till midnight). i can not observe less sql-load spikes for the queries, they (still) go up to 120-200%.