Logbook and History panels almost completely useless. Will MariaDB help?

Installation Home Assistant OS
1

My logbook and History panels take at least one minute to load and sometimes don’t load at all. I’m running on a RPi3 with 32Gb Samsung Evo Select SD card. I don’t remember having this problem with the AIO install. Other areas of the GUI are also slow but not unusably so. System Monitor component shows CPU using around 5% normally and spiking up to 30% when taxed. There is a healthy 500+MiB of RAM free and I’m using just 10% of the disk space with a 7 day recorder limit.

I’m curious if moving to the MariaDB add-on with the same hardware will result any real-world performance increase? It seems like the best results are found in moving the DB off the Pi, but at that point I may as well just move the entire install, which I’m not in the mood to do for a while after just rebuilt my system. Anyone done it?

2

Your chances are 50/50. While my speed issue wasn’t as severe, I went to a mssql database and saw no change.

I have been looking at the code and I have some suspicions. I am thinking of putting in some auditing data to try and see where all the time goes.

I know that for the logbook, for one day it pulls 30,000 records from the DB and then works them over in the code to aggregate that to 300 records. It seems like some of that aggregation should to left to the DB since that is what a DB does.

3

I was wondering about this as well and instead of splitting off the database am going to start excluding certain domains and entities from logging.

4

I moved over to Maria-DB for the very same reason. It helped here and my interface is more responsive now. Also History loads faster.

5

In my case, i noticed that the db file grew to 500Mb. Now I just set a cron job to delete this file bi-weekly

7

Funny. I find it very helpful, easy to configure, and relatively fast given the amount of data it’s trying to parse. my recorder, history, and logbook are all configured with specific includes and excludes. set to keep 28 days worth of data and purge anything older. it’s storing to a MariaDB database in a separate docker. Loads within 5-10 seconds of clicking on history or logbook. and my database is 3.2Gb. Can’t give a specific count of devices as there’s so many.

I would say for the standard RPi install, yes, offloading the database to a specific MariaDB or MySQL database will vastly help in performance of both HA and the DB.

9

In my case the problem is rendering it all in browser. Maybe pagination would help in this case.

10

Ahh Yes. I need a $2000 CPU to render the history panel or a browser that renders using the GPU. That’s a no and a no.

11

I’ve just moved my sqlite db to an existing mariadb on my aging 6 year old budget NAS which has been serving as a kodi db for a while. Huge gui performance gain from doing this!