History component

[Tacchi1983]

Since last update 0.81.2 the history component freezes all the time. I cant load history anymore. The page always freezes. If a new day just start i can load it. But after few hours its impossible to load the history. I cleaned and delete the file . With restart everything works but after few hours its the same. Its too slow. Before it was slow too but it always loaded eventually after waiting a bit.

[cgarwood]

What settings are you using for recorder? If you're using the default settings/SQLite DB, how big is your homeassistant_v2.db file?

[Tacchi1983]

Im using default setting sqlite DB. I deleted the homeassistant_v2.db file yesterday so the file is not big at all. It worked before when the file was much bigger. And it always loaded eventually. Now it freezes. It tries toload but after a while it shows few identities and it freezes i cant scroll or anything. I got lately lot of these errors too Log Details (ERROR)
Wed Oct 31 2018 06:45:23 GMT+0100 (CET)
Error executing query: (sqlite3.OperationalError) database is locked (Background on this error at: http://sqlalche.me/e/e3q8)

Its from the components/recorder/util.py

[Segfault198]

I'm seeing something similar on both 81.1/2. I've not seen any errors crop up but I may have to adjust my logging level. However brand new mysql database and all I see is cpu/memory spikes and history never loads. Saw the same thing before I switch from sqlite back end with a much larger database. Plays h*** on the rest of the system. Interestingly enough history graphs do seem to work. I'll adjust my logging and see if I can't get more details.

Follow up:
So I set my logging for the recorder to debug and tried to load the history page. I let it run for a good long while and never got it to load. In the meantime I watched hass eat almost all available memory and spike the cpu to the point where the system ground to a halt. Finally stopped hass. Here is the log file

https://pastebin.com/0z3tJ2qf

Let me know if I can provide more data.

[klockie86]

Same here.
Switched to mysql (MariaDB) but no improvement. After restarting home-assistant, the history is working for a few hours. But after that it's nog loading or graphs showing a horizontal line.

[adrianmihalko]

Same problem here on 0.81.5. Unusable slow History page.

My database was around 450MB (with 4-5 weeks of data). I added into config:

recorder:
  purge_keep_days: 5
  purge_interval: 1

then I manually purged database from service menu (called recorder.purge).

The size of the database was the same, but the data was available for the last 5 days. The History page still loaded very slowly, if loaded at all.

Then I executed {"keep_days":"5", "repack":"true"}, database size was shrinked to 100MB, but History page still was unusable slow.

At this moment I have an unusable History page.

[cgarwood]

Possibly related to home-assistant/frontend#2051

[riemers]

I have the same screens as @adrianmihalko. I have some entities if i click on them and i open them it should show you history. But since it some change a lot (every 10 seconds) it doesn't have enough time to load the data.

[adrianmihalko]

Yes, I had some entities which are changing a lot (movement sensors) and if I disable them, history&logbook are fast again. Some how it would be good to fix this, because disabling history&recorder on these entities is not a great solution.

[pauly7300]

I'm fairly sure this is the same issue as home-assistant/frontend#2051. One or the other should be closed as duplicate.

I have a bit more information to add however. Whenever this situation is happening, I can reproduce the same behavior across multiple browsers on different machines. Currently I can reproduce on the following browser/OS combinations.

• MacOS 10.14.2 (Mojave)
  • Safari
  • Firefox (latest)
  • Chrome (latest)
• Windows 7
  • Internet Explorer 11
  • Chrome (latest)
  • Firefox (latest)

Here are the recorder logs at the moment I click on the history link in the UI. https://hastebin.com/nedalazebe.css
It's fairly clear to see that there are continual "converting XXX rows to native objects" with very high numbers. I suspect this is due to ongoing changes in states and a continual attempt to keep the page updated in real time??

I have also deleted my DB like others and for the hours after the DB is up, the history appears to be working fine but that is before the number of states and number of items is fully populated. Very quickly for me (1-2 day) the behavior degrades to the point that it locks up the browsers and they are unusable and must be force closed. And again, that happens across all of the browsers on all platforms that i've tested.

Hope this helps and would very much like to see this fixed.

[Bubi504]

The same here.

[Petro31]

These are SQL look-ups. They always spike the best hardware to 100% CPU usage, even for a small second. I had all these issues when I was on a PI with a running a small constricted database. Since then, I've moved to a much better hardware platform, I'm logging all devices, and I have zero slow down. Have you guys tried running home assistant on something other than a PI? I've seen these complaints on the forums many times, and the root cause has always been poor hardware.

[stale]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.