My setup has grown to a fair size with several hundreds of sensors and with quite a few different integrations. The event flow is quite intense often with many events per second. This is really no problem for Home Assistant, and my RPi 4 usually had CPU around 10%. However, with the default recorder (MySQL I believe), the system was in general sluggish, often freezing for a few seconds, both in the admin screens and over Samba.
Yesterday I changed to MariaDB using the default docker setup that is available in the supervisor, yes I run a plain “Home Assistant”.
The change has been remarkable. Changing to MariaDB has the CPU also around 10% (perhaps a nudge higher), but the sluggishness is gone. Everything is now as zippy as when I only had a hundred sensors.
How can that be?
I am quite certain (but guessing of cause) that the sluggishness was caused by the saving and not by querying. I don’t buy that MariaDB should be so much more efficient than MySQL. At least not unless the default MySQL has a really strange setup.
To me it seems like writing to the default recorder happens in a way that pretty much freezes HA for the duration of saving an event; it seems to block not only a single thread, but all threads; or at least it blocks a core. None of that blocking seems to be happening when going over tcp/ip to MariaDB. My guess is, that that is what makes the great difference of going from the default recorder to an “external” one.
The default database is SQLite not MySQL. SQLite is not recommended for working with lots of data and high write volumes.
Check “When Not TO Use SQLite” here.
What I would recommend is, to start excluding entities, for which you don’t need any history like sun.sun or sensor.time.
Check this thread here.
I don’t think my reasoning depends on it being SQLite and not MySQL. With proper internal queuing it don’t see why the limitations of SQLite should matter much for writing (but might for querying). It is the asynchronous queuing of writes I think we are missing.
I do already exclude all I don’t want in my history
Anyway, I am now happy with MariaDB.
High write volumes : SQLite allows only one write operation to take place at any given time, which significantly limits its throughput. If your application requires lots of write operations or multiple concurrent writers, SQLite may not be adequate for your needs.
I don’t think that HA should handle qeueing of writes to the DB, that’s the job of the DB.
Great do you also use influxDB or similar for long-term data?
Well, could be. Personally I think it is a shame that the default recorder becomes sluggish so fast. And a bit of queueing might not be hard to do.
Yep, I have 4 days in HA and full history in Influx. In my tablet Lovelace layout I use quite a few short-history graphs and then also some long-history Grafana graphs. Actually with the default recorder the drawing of the short-history graphs was quite fast, that was no problem - the problem was the sluggishness caused by the writes.
do you also use influxDB or similar for long-term data?