I have a good few sensors in my HA instance (A few dozen, not hundreds), but my history and logbook seem to run extremely slow. I moved a while back from the built in sqlite DB to a MySQL database, but this doesn’t seem to have helped much. The history screen seems to take upwards to 30-40 seconds to load, and even the data within individual sensors can take 10+ seconds to load.
My config looks like this:
recorder:
purge_keep_days: 3
db_url: mysql://
The DB size is 1.6GB, which seems high for only 3 days history. Any thoughts about why it’s performing so badly/where I can start looking? I’m thinking based on the DB size it’s logging huge amounts I can’t actually see in the UI, but really not sure. I have a few Z-Wave devices that seem to have a lot of sensors each, so maybe something there?
Even with a 1.6GB database, I still would have thought the queries needed to show history would still be pretty quick - especially for single sensors.
Did you get any resolution on this? Also running Synology NAS 8GB and history is practically not working. I have erased the whole DB and started over clean, works for 1-2 days then it is so slow i can hardly use it. (dB ~70MB)
Im not sure when this problem started, im now on 0.81.6 I know that on 0.76 it worked (dB ~800MB)
Same here Im using Python Virtual Env with HA 82.1 on RPI3. I assumed it was due to having way too much data in the database as every time I delete the database and start from scratch it works for a day or 2 instantly then slows again.
Mysql has a know problem when using innoDb. There’s a long discussion about database size and this specific mysql problem (see the comment from rpitera here Large homeassistant database files). To reclaim the space you have to export the DB, remove, import again! I’m not saying that this is impacting the performance, but doesn’t hurt to be aware of this :-).
If this is about not releasing space for deleted records, wouldn’t a possible option be to setup a partition based on day and purge by dropping partitions?
I have the same problem with a setup that has about 15 devices on a RPI3B+. Seems like that should have more than enough horsepower to retrieve this amount of info from a database and plot it without very much delay.
Same here. I’m running HA in Docker on Synology DS216+II NAS, 4 GB RAM. My HA DB file is only 300 MB big and the logbook is taking ages to load. The NAS Volume during the logbook loading process is used in CA 60%, CPU 15-20%. Usually when Synology’s DB is heavily occupied the CPU is occupied 100%. Here it’s the other way around
And I had these issues on a pi, switched to a nuc and it now loads in under a second loading an entire week which is about 2 gigs of information for me. This is purely a hardware issue with the SQL look ups being processor intensive.
And just to clarify, I sit at 1% to 5% processor use and 2gb (~25%) of ram at any given time.
Yup, if you get into really profiling what’s going on with collectd or some other high frequency system monitoring tool you can see this.
On a i5 NUC when I click on “Logbook” CPU will go to 100% for a short period; and disk IO will also show quite a bump.
On a rPI this just takes longer due to much less CPU and probably worse disk performance as well.
I even tested using the sqlite pointing to /tempfs to remove disk IO from the equation and it was better; but still slow on a rPI.
When I moved to a NUC I ran this way for awhile as lots of RAM before I setup MariaDB for the database back end. It was faster but I wanted to retain the logs through system reboots.
To all, who suffer from this. I can confirm that using an ‘external’ database for the recorder component solves the issue well. At least on a Synology NAS. I installed Maria DB package (didn’t want a dockerized version to maximize performance) in my Synology and configured HA to use it and the performance difference is just tremendous! (or huge as some used to say )
From ca 40 seconds(!) (and timeouts) to load the Logbook view I went down to 2-3 seconds!! Same for the history graph for any sensor. It’s excellent.
For some reason also the DB size itself is ca 50% smaller. Default SQLite db was ca 300 MB. Maria DB is ca 170 MB. No wonder it’s faster - also for this reason. And most important - CPU usage is a lot higher - about 60-70% now comparing to 5-10% with default SQLite db running in HA Docker container. I think my bottleneck now are disk operations, not CPU, which is ok (I wonder how would this work with SSD).
I think the most important thing is that DB queries are executed outside HA docker process - this lets them to be executed more efficiently and they can take more CPU usage which gives a lot better processing time.
I have no experience with your setup but I run my Maria DB on the same NUC as the Hass and a cctv server and a traccar server. And it is VERY fast. No delays. I have 16 Gb memory and m.2 SSD
I too have this problem, with a hexacore i7 processor @4.3GHz. I also use an SSD for all my operating systems. That includes Home Assistant.
Been reading up on this, and I want to try MariaDB. My only question is, if so many people have such a performance upgrade when switching to MariaDB, then why isn’t that the default DB in Home Assistant? Just out of curiosity.
Sounds logical and it might run better on rpi. Anyways I installed MariaDB and what a blast it is. It was pretty easy to setup (had to find dependencies first though, but those can be found on the recorder docs). It actually loads the page now :P. Most of the time the page would try to load and become stuck for minutes, I tried to exclude many entities to ease the load. But I guess this is no longer needed, I do however have it installed on another host (VM), they are on the same machine but different VM’s (as I intend to use other databases for other purposes).
Thanks for this thread everyone. It even loads on my iphone now, whereas before it would always crash out of the app (iphone X, ios 12.3). Can’t seem to get the bars to show up though might be a theme issue, I see the graphs though. And the log can finally be loaded on my iphone (and fast). Great!
Edit: tried the standard theme but the bars still don’t show anyone might have ideas? There is definitely something there as you will see in the screenshots, weird thing is, I can see graphs in the history but not the bars showing on/off states.
)
