History/Logbook very slow

Configuration
21

I still have that problem with an external mariadb database. It has 500MB size now and any graph in hass takes often 5-10 seconds to load!
It mostly is acceptable at the moment but getting slower each day. Soon I will have to purge the db again or only save 5 days so everything stays responsive.
My machine is an odroid HC2, HDD over SATA, 8 cores, 2GB RAM.

If there won’t be a solution to this, logging sensor values outside of hass DB might be an option as I get most of my values through mqtt but not all values…
I’d really like to be able to look back 1.5 years, not only 10 days.

22

how many days are you viewing on screen? I’m only using a Rpi3 but when set to view 1 day at a time it only takes a few seconds to show history.

23

I get that delay for the normal graphs in the gui that each sensor has, which only holds one day.
The history of all sensors for 1 day often loads within 30 seconds.

24

That hardware isn’t much better than a pi… I wouldn’t expect great performance from SQL lookups.

25

That hardware is a magnitude more powerful than a pi and achieves more than 100MB/s with easy.
Sure, it’s still a slow hardware compared to the typical x64 machine but the increase in slowliness is a bit frustrating as I can’t even keep a month in my db.

26

I’m sorry but the processor cache size on HC2 is L1 4-64kb and that is your limiting speed factor. This won’t be fast for you. Your processor has to make way more round trips to handle the data. More round trips = more time.

EDIT: Diving into this more, the raspberry pi 3B+ is faster than your processor too. So you’re going to get slower speeds than the current pi generation. Which is L2 512kb.

27

I don’t know where you get that impression from. Every benchmark I read clearly shows that the XU4 (HC2) is a lot faster than the PI 3B+ in every category: https://www.phoronix.com/scan.php?page=article&item=odroid-xu4-arm&num=2

28

That’s not an HC2… That’s an XU4.

29

It’s the same device. Same processor etc. Only that instead of USB3 connectors, the HC2 has an SATA interface

30

Virtually all of the benchmarks, in the link you provided, as for image-processing. That’s not relevant to Home Assistant’s performance.

1 Like
31

That might be true, it was just to prove that it is generally way more powerful than the PI (including homeassistant as the PIs storage interface is slow too)

32

L1, L2, and L3 is one of the most important specifications for database look ups and its not great on all these devices. That’s all I’m saying. And the PI will perform better in this category, which is not very good.

So prove away, but all you’re doing is lying to yourself. It won’t get better as long as you stay on that device. Even if you move the database to another unit, it will still be slow because your device is still managing that data. Good luck.

33

Which may mean nothing in real-world applications because they are influenced by operating-system optimizations (or lack thereof) and other architectural constraints.

Watch this video comparing the startup time of GIMP on RPi 3 (not 3+), TinkerBoard and XU4.

If you rewind the video, you’ll see a CPU benchmark test showing the XU4 is ~3 times faster than the RPi 3. Nevertheless, in real-world applications, subject to reading/writing to storage and using OS services, it was slower.

It’s a one-year old video and many things have undoubtedly changed since then but the moral of the story is to avoid relying exclusively on CPU benchmarks.

1 Like
34

If someone want to test if a remote MariaDb make the situation better. I can create a couple of databases and also give access via phpMy Admin.

35

That is true for the xu4 with sd card but not for the HC2 with OS on the HDD connected by SATA.

But whatever, everyone here can believe what he wants. I didn’t want to start a platform discussion but was trying to get constructive ideas on how to improve DB performance or workarounds, even if it means using a secondary database and keeping only a few days in the hass db.

36

I’ve gone through this process with my PI and nuc and this is what I noticed:

Database: 2gb ish. My database is 5gb now, forgot I have it holding a month.

Pi with mariaDB database on pi: ~ 5 second load for single day. 30+ for week.
Pi with mariaDB database on a synology server (with not great specs): ~2 second load for single day. 30+ for week.
I5 Nuc with mariaDB database on a synology server (with not great specs): instant for single day. 1 second for week.

37

Thanks for sharing your stats.
Maybe I should look into optimizing my mysql setup.
After running some benchmark it is really slow. There seems to be too much background load on my device.

38

I also limit my database to only sensors that I care about. For example, I don’t track the outside weather in my database. There are plenty of phone apps that do that. I only track data like CPU temperature, Network ping times, device_tracker locations, light status, etc. I don’t really track other things because I’ve noticed over the years that I’ve never looked at that data. So why clutter your db with it?

4 Likes
39

I think the history page - esp. the graphs - are mainly done in js and maybe that is very slowly?
Why should MariaDB be so much faster then mySQL?

40

Same problem here. My system:

  • Synology DS1019+ (Intel Celeron J3455 @ 1.5 GHz, 8 GB RAM)
  • HASS dockerized
  • MariaDB 10 (Synology Package)
  • purge_keep_days: 3
  • All databases have about 500.000 entries, only 800 MB altogether

But still the log and the history are both painfully slow. I’m not a database expert, but simple SQL queries are quite fast. But the HASS log and history are almost unusable…

Any help very much appreciated!