(SOLVED) Performance of history data is extremely slow

[RobDYI]

This took me from GB to MB in size and speeded up the history.

Allow services to be excluded from Recorder: and the database

I tried to find out how to add this to the official documentation but I am unsure how. Please add to the official documentation as I think its a really important feature that will make most peoples HA run better. I would even suggest state_change only events be recorded as default.

recorder:
  db_url:   mysql+pymysql://hass:[email protected]/HOMEBASE
  exclude:
    event_types:
      - service_removed
      - service_executed
      - platform_discovered
      - homeassistant_start
      - homeassistant_stop      
      - feedreader
      - service_registered
      - call_service
      - component_loaded
      - logbook_entry
      - system_log_event
      - automation_triggered
      - script_started
      - timer_out_of_sync

[Sebex]

Hey guys, I am experiencing a similar problem. The history and logbook tabs take forever to load and show the data. The problem lies within extracting the states, see logs below (20-40s!!)

2020-02-09 20:13:18 DEBUG (SyncWorker_14) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.078752s
2020-02-09 20:13:18 DEBUG (SyncWorker_14) [homeassistant.components.history] getting 1 first datapoints took 0.174754s
2020-02-09 20:13:19 DEBUG (MainThread) [homeassistant.components.history] Extracted 5756 states in 18.286318s
2020-02-09 20:13:19 DEBUG (SyncWorker_17) [homeassistant.components.recorder.util] converting 5760 rows to native objects took 18.791389s
2020-02-09 20:13:19 DEBUG (SyncWorker_17) [homeassistant.components.history] get_significant_states took 18.923634s
2020-02-09 20:13:19 DEBUG (SyncWorker_17) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.067947s
2020-02-09 20:13:19 DEBUG (SyncWorker_17) [homeassistant.components.history] getting 1 first datapoints took 0.171320s
2020-02-09 20:13:20 DEBUG (MainThread) [homeassistant.components.history] Extracted 5761 states in 19.448704s
2020-02-09 20:13:49 DEBUG (SyncWorker_12) [homeassistant.components.recorder.util] converting 14473 rows to native objects took 20.091915s
2020-02-09 20:13:50 DEBUG (SyncWorker_12) [homeassistant.components.history] get_significant_states took 20.106214s
2020-02-09 20:13:50 DEBUG (SyncWorker_12) [homeassistant.components.recorder.util] converting 1 rows to native objects took 0.020867s
2020-02-09 20:13:50 DEBUG (SyncWorker_12) [homeassistant.components.history] getting 1 first datapoints took 0.058466s
2020-02-09 20:13:50 DEBUG (MainThread) [homeassistant.components.history] Extracted 14474 states in 20.440697s
2020-02-09 20:14:25 DEBUG (SyncWorker_15) [homeassistant.components.recorder.util] converting 25500 rows to native objects took 35.942505s
2020-02-09 20:14:25 DEBUG (SyncWorker_15) [homeassistant.components.history] get_significant_states took 36.045067s
2020-02-09 20:14:26 DEBUG (SyncWorker_15) [homeassistant.components.recorder.util] converting 7 rows to native objects took 1.125338s
2020-02-09 20:14:26 DEBUG (SyncWorker_15) [homeassistant.components.history] getting 7 first datapoints took 1.176136s
2020-02-09 20:14:27 DEBUG (MainThread) [homeassistant.components.history] Extracted 25507 states in 38.117463s

I have HA running on docker on a RPI 3+. Currently it has only 1 sensor that remotely connects and reports the temp/humidity ever minute. Not a whole lot of data and running a minimal setup that I’d greatly like to expand. However I would like to see the history and adding more sensors would only make it worse I suppose. Any suggestions of what I can improve to reduce extremely long loading times? I’d greatly appreciate it.

[BertrumUK (Neil B)]

I used to have the same slowness on my RPI 3+ running off SSD but then I had a lot more sensors than you. Moving over to a RPI 4 and running off SSD via USB3 has made a big difference.

If you are running off SD card then maybe changing to a faster card would make a difference.

[Sebex]

The SD card I’m using is a SanDisk 16gb class 10, with a reading speed of 100MB/s it’s not that slow. Fasted SD cards can currently reach 300 MB/s, extrapolating the loading times would still results in 10+ seconds of waiting for logbook/history to appear.
I’m hoping there’s another solution too strongly minimise the loading time.

[OverloadUT (Allie Laabs)]

@Sebex

converting 25500 rows to native objects took 35.942505s

This is the key to understanding where the problem is. 36 out of the 38 seconds is taken up in Python code, not database access! This means that the query is fine from a raw query performance perspective and the issue has nothing to do with read speed, SD cards, etc. This is entirely CPU-bound number-crunching work.

There’s definitely a need to rethink how history data is stored and subsequently queried as a huge number of those 25500 rows are not needed to actually render the page. But don’t go worrying about how to make your database faster, as that’s not where the problem lies.

[Sebex]

@OverloadUT There’s definitely a need to rethink how history data is stored and subsequently queried as a huge number of those 25500 rows are not needed to actually render the page. But don’t go worrying about how to make your database faster, as that’s not where the problem lies.

Thanks for this insight! As this is all quite new to me, could you point me in the right direction of rendering the page faster without loading all rows? And is there a general page that explains more about how the history data is stored and queried?