MariaDB with high load

Seems like MariaDB keeps running at high load - almost 1 core.
The query is always lists as running is the one below. Trying to find state_max. I have like 20 lines of entity IDs, so probably around 80.

I am not sure how often recorder writes data, some of the sensors updates quite often. I have tried to set the commit to 2 to slow down writing, but it is the constant query that is the problem. Must be system call, since it is max on more or less all sensors. I think it is a bug.

SELECT states.entity_id, states.state, states.last_changed, states.last_updated, states.attributes, state_attributes.shared_attrs
FROM (SELECT max(states.state_id) AS max_state_id
FROM states
WHERE states.last_updated >= '2022-12-06 18:16:40' AND states.last_updated < '2022-12-11 05:44:59.999999' AND states.entity_id IN ('sensor.processor_tem...
...) AS anon_1, states LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id
1 Like

I’ running MariaDB on Synology NAS and have ~370 sensors configured to store hostory in recorder. What I found is that I have constant write of 2~8MB/s of data to volume on which DB is kept. As other apps using this volume generate marginal load it all belongs to HA. Side effect, as I was not using NAS grade SSDs for this volume, due to lots of writes SSD reached 92% of wear level in less than one year. I’ll need to replace it in few weeks :frowning:
So, I’d say HA writes a lot of data and high CPU consumption by MariaDB is result of this, not DB fault as such.

Not saying it is a mariadb bug.
But spending 2 minutes to catchup 5 minutes is bad design in HA. They keep finding max of all sensors from a fixed start time to a new time that is increased by 5 minutes at a time. Since start is fixed they could remember old max and search 5 minutes for max.

Recorder can be set to write less often. I did that.

Has this been reported as a bug? It killed my latest SSD :frowning:

@povlhp what did you use to reduce the write frequency of recorder? I’m using commit_interval: 120 but it doesn’t seem to improve the situation.

I just moved from Synology package to docker based MariaDB 10.9.5 and now I can observe that write rate to SSD dropped significantly (from 2~8mbps+ down to 200~400kbps)! So together with replacement of consumer grade SSD to NAS grade one (so increase in TBW from 300TB to 1300TB) I feel safe now for years to come. Old SSD was fully worn after less than 1 year :frowning: