core: High CPU load in MariaDB

The problem

Yesterday after daily recorder database purge CPU load of MariaDB go high and stay so more than a day.

MariaDB installed as Supervisor add-on. I connected to database and call “show processlist;”. Every time one of process execute SQL command begins with:

SELECT DISTINCT states.attributes_id 
FROM states 
WHERE states.attributes_id IN (563, 564, 565, 566

where numbers in the parentheses every time are different. mysqld process uses about 50% of CPU most of time.

I upgrade HA to 2022.4.х more than a week ago, so I think it’s not database conversion after migration from 2022.3.x.

What version of Home Assistant Core has the issue?

core-2022.4.5

What was the last working version of Home Assistant Core?

core-2022.4.4

What type of installation are you running?

Home Assistant OS

Integration causing the issue

recorder

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

# Use MariaDB as internal database
# Limit storage age and num of entities
recorder:
  purge_keep_days: 7
  db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8

Anything in the logs that might be useful for us?

No response

Additional information

CPU usage graph.

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 36 (20 by maintainers)

Most upvoted comments

Thanks for the update. I did some work for 2022.6 already that should reduce the size of the data stored by roughly 30% and make it a bit faster to purge.

I’ve started planning additional improvements for 2022.7. Once you have been running 2022.6 for a few days it would be great to another database dump to see if there is anything I can do to further improve the situation.

bdraco on May 6, 2022

sensor.map_sine_pro sensor.map_dominator_ups

- platform: command_line Handmade script to get info from UPS. Mostly for influxdb storage. I can create template sensors for current states and exclude these two entities from history.

👍

air_quality.sensor_vozdukha_1 air_quality.sensor_vozdukha_2

Xiaomi miio Air quality monitor - old style multi attributes entities. Also needed for influxdb, can be excluded from history database.

That one is a bit legacy. We don’t even have a recorder platform for reducing what is stored in the db for the base entity. I’ll see if we can clean that up a bit.

binary_sensor.54ef442d537c_gateway

Custom integration Xiaomi Gateway 3. It can be excluded from history at all, binary sensor state not interesting, attributes are some technical info.

Might be nice to open an issue for this one in the chance they can be optimized at some point

sensor.sredniaia_temperatura_abk_tsokol sensor.sredniaia_temperatura_zh_d_vagon_pom_g

Min/max integration. Attributes are side effect. 😃

The min/max attributes will be solved in 2022.5 via #70142

climate.obogrev_tualet_levyi

Custom integration Smart Thermostat (PID). Attributes are also mostly technical.

https://github.com/ScratMan/HASmartThermostat/blob/master/custom_components/smart_thermostat/climate.py#L638

Wow, that one is going to fill up anyone’s database quick! Most of these should probably be sensor entities instead

See https://developers.home-assistant.io/docs/core/entity/?_highlight=extra_state_attributes

WARNING Entities that generate a significant amount of state changes can quickly increase the size of the database when the extra_state_attributes also change frequently. Minimize the number of extra_state_attributes for these entities by removing non-critical attributes or creating additional sensor entities.

The following generate a lot of state changes since they don’t round values sensor.energomera_modul_podval_moshchnost_faza_b sensor.energomera_modul_podval_tok_faza_b sensor.energomera_abk_energiia_reaktivnaia_sgenerirovannaia … more of the same … These are values from energy meters got via MQTT.

Not sure if it is possible to get the source to round the values before injecting them as I don’t think MQTT will do that.

bdraco on Apr 27, 2022

Also I forgot to mention, the purging is dramatically faster now

bdraco on Apr 26, 2022

@bdraco I’m compressing (xz) mysql dump (10GB), after ~2h I’ll upload it to cloud and send you a mail with a link.

igogold on Apr 26, 2022

Never-mind you posted the explain. I just didn’t see it on my phone

bdraco on Apr 25, 2022