Trying to isolate slow history

Configuration
1

I’m trying to isolate the slow loading of my history and logbook, I am not sure I understand the log information I am reading.

Host system is on a server running Linux KVM/Qemu - 6c/12t CPU Xeon E-2176G @ 3.70GHz; 64GB RAM
HassOS VM has 4 CPU cores, 2GB RAM, 100GB disk (backed by RAID1 7200RPM WD-Red drives)

I’m already using MariaDB (addon) and that helped a lot vs SQLite but when the SQLite DB got big I enlarged my disk and now it’s too big for going back to a SSD so I may be getting a performance hit on that but I don’t think it should be THAT bad.

My configuration:

recorder:
  db_url: mysql://user:password@core-mariadb/homeassistant?charset=utf8
  purge_keep_days: 21
#  purge_interval: 1
  exclude:
    domains:
#      - automation
      - device_tracker
    entities:
      - sensor.time
      - sensor.date
      - sensor.date_time

history:
  exclude:
    domains:
      - automation

MariaDB looks like it’s not using much memory

image
image1234×639 48 KB

My logfile showing load times:

The logfile while pulling up a view with a couple dozen 24-hour graphs (temp and humidity of all rooms and inside/outside)

2021-02-09 22:28:41 DEBUG (SyncWorker_45) [homeassistant.components.recorder.util] querying 4011 rows took 3.120492s
2021-02-09 22:28:41 DEBUG (SyncWorker_45) [homeassistant.components.history] get_significant_states took 3.120681s
2021-02-09 22:28:42 DEBUG (SyncWorker_10) [homeassistant.components.recorder.util] querying 7200 rows took 3.784467s
2021-02-09 22:28:42 DEBUG (SyncWorker_10) [homeassistant.components.history] get_significant_states took 3.784633s
2021-02-09 22:28:42 DEBUG (SyncWorker_17) [homeassistant.components.recorder.util] querying 5991 rows took 3.857610s
2021-02-09 22:28:42 DEBUG (SyncWorker_17) [homeassistant.components.history] get_significant_states took 3.857728s
2021-02-09 22:28:44 DEBUG (SyncWorker_36) [homeassistant.components.recorder.util] querying 8816 rows took 5.305845s
2021-02-09 22:28:44 DEBUG (SyncWorker_36) [homeassistant.components.history] get_significant_states took 5.305992s
2021-02-09 22:28:50 DEBUG (SyncWorker_45) [homeassistant.components.recorder.util] querying 13 rows took 8.763604s
2021-02-09 22:28:50 DEBUG (SyncWorker_45) [homeassistant.components.history] getting 13 first datapoints took 8.767441s
2021-02-09 22:28:50 DEBUG (SyncWorker_45) [homeassistant.components.history] Extracted 4017 states in 11.901393s
2021-02-09 22:28:51 DEBUG (Recorder) [homeassistant.components.recorder] Sending keepalive
2021-02-09 22:28:52 DEBUG (SyncWorker_10) [homeassistant.components.recorder.util] querying 16 rows took 10.193875s
2021-02-09 22:28:52 DEBUG (SyncWorker_10) [homeassistant.components.history] getting 16 first datapoints took 10.197701s
2021-02-09 22:28:52 DEBUG (SyncWorker_10) [homeassistant.components.history] Extracted 7206 states in 14.002799s
2021-02-09 22:28:53 DEBUG (SyncWorker_37) [homeassistant.components.recorder.util] querying 7199 rows took 0.120140s
2021-02-09 22:28:53 DEBUG (SyncWorker_37) [homeassistant.components.history] get_significant_states took 0.120322s
2021-02-09 22:28:53 DEBUG (SyncWorker_37) [homeassistant.components.recorder.util] querying 16 rows took 0.032555s
2021-02-09 22:28:53 DEBUG (SyncWorker_37) [homeassistant.components.history] getting 16 first datapoints took 0.036247s
2021-02-09 22:28:53 DEBUG (SyncWorker_37) [homeassistant.components.history] Extracted 7205 states in 0.175908s
2021-02-09 22:28:54 DEBUG (SyncWorker_17) [homeassistant.components.recorder.util] querying 15 rows took 11.298667s
2021-02-09 22:28:54 DEBUG (SyncWorker_17) [homeassistant.components.history] getting 15 first datapoints took 11.301585s
2021-02-09 22:28:54 DEBUG (SyncWorker_17) [homeassistant.components.history] Extracted 5975 states in 15.177097s
2021-02-09 22:28:56 DEBUG (SyncWorker_36) [homeassistant.components.recorder.util] querying 18 rows took 12.423784s
2021-02-09 22:28:56 DEBUG (SyncWorker_36) [homeassistant.components.history] getting 18 first datapoints took 12.426979s
2021-02-09 22:28:56 DEBUG (SyncWorker_36) [homeassistant.components.history] Extracted 8807 states in 17.760508s

image
image513×901 83.2 KB

This is one example, but some other things are worse…I’m hoping there is a way to improve this.

2

You have 64Gb ram. Did you try increasing the ram for your VM to 4Gb ?

3

I’ve not because it looked like it was only using about 50% of the 2GB allocated so far for the entire system and much less for the DB.

I’ll bump it up now and give it a try thru the day today but I’m not expecting much difference.

4

That did help on some pages, but others are still very slow. I don’t suppose there is a guideline on figuring out what reasonable system requirements are, or how I would have identified RAM as an issue (and what else might be)?

5

Update: Seems slow as ever again with the 4GB RAM…now suspect it was only “faster” for a bit due to the reboot for increasing. Resources suggest it is using ~7% of the available RAM for MariaDB and the HassOS system reports that ~1GB is used (2.8GB free). Seems increasing RAM did not accomplish anything.

image
image803×734 26 KB

Any other ideas?