I’m using Hassio in a docker on a Rasberry 3B+ with RaspbianOS. There is a msata ssd connected to an X850 V3.0 USB3.0 mSATA SSD Storage Expansion Board.
With the standard sqlite database everything runs fine, just the loading of graphs takes 20 seconds, so I decided to swith to MariaDB. I installed the Hassio MariaDB Add-on and this speeds things up, but after a few hours Hassio starts using a lot of CPU and memory and becomes unusable. So I switched back to sqlite and then Hassio runs fine.
I did a clean install of the OS and Hassio. I changed the recorder to just include about 50 entities. But every time I enable MariaDB there is high CPU and memory usage and Hassio becomes unusable.
The MariaDB docker is ok, but after 24 hours of running there is 15Gb of TX traffic.
The database isn’t that big.
-rw-rw---- 1 root root 61 Mar 9 11:30 db.opt
-rw-rw---- 1 root root 3510 Mar 9 11:32 events.frm
-rw-rw---- 1 root root 79691776 Mar 10 17:21 events.ibd
-rw-rw---- 1 root root 1534 Mar 9 11:32 recorder_runs.frm
-rw-rw---- 1 root root 114688 Mar 10 17:21 recorder_runs.ibd
-rw-rw---- 1 root root 995 Mar 9 11:32 schema_changes.frm
-rw-rw---- 1 root root 98304 Mar 9 11:32 schema_changes.ibd
-rw-rw---- 1 root root 6111 Mar 9 11:32 states.frm
-rw-rw---- 1 root root 41943040 Mar 10 17:21 states.ibd
Your hardware isn’t good enough. Simple as that. Move the maria db to some other hardware. Still won’t be fast though. When I had this setup, still took about 10 seconds. Only way to get < 1 second is to get a computer with some power (and even then, it spikes to 100% on lookups).
It seems to be solved by updating the MariaDB from version 0.2 to 1.2 in the Hass.io add-on page. After 24 hours with the new version the CPU stays well below 10%. Even if I open the History and Logbook there is just a small spike to ~20% and then return to normal.