MariaDB issue high CPU

Installation Home Assistant OS
1

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

portainer_mariadb
portainer_mariadb1754×818 112 KB

The Hassio docker uses 100% CPU.

portainer_ha
portainer_ha1754×884 102 KB

ha_cpu ha_memory

I would like to keep the DB on the Raspberry and not move it externally. How can I troubleshoot this further?

2 
MariaDB [homeassistant]> mysql homeassistant

SELECT table_schema "homeassistant", Round(Sum(data_lh + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHEable_schema="homeassistant" GROUP BY table_schema;
+---------------+-------+
| homeassistant | value |
+---------------+-------+
| homeassistant |  94.3 |
+---------------+-------+
1 row in set (0.00 sec)

SELECT table_name "Table Name", table_rows "Rows Counround(((data_length + index_length)/1024/1024),2) "Table Size (MB)" FROM inforon_schema.TABLES WHERE table_schema = "homeassistant";
+----------------+------------+-----------------+
| Table Name     | Rows Count | Table Size (MB) |
+----------------+------------+-----------------+
| events         |      40836 |           65.63 |
| recorder_runs  |          1 |            0.03 |
| schema_changes |          0 |            0.02 |
| states         |      41565 |           28.58 |
+----------------+------------+-----------------+
4 rows in set (0.00 sec)
3

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).

4

I’m not sure that would be it, I run Mariadb on a 3B with no issue, it runs 3 different databases including HA. Total databases sizes are about 1gb.

5

https://github.com/home-assistant/hassio-addons/commit/61480978d24c4b9c388d2e8b6ac09c41dc11e53b#diff-ee3f806d1e230871b10dd7bdf40da54b

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.

@pvizeli Thank you!

image

1 Like