#mariadb crashes frequently - always on the full hour

1 messages · Page 1 of 1 (latest)

quartz gust
#

MariaDB server which I have been using for years is behaving erratically. Frequently it crashes, always on the full hour or when instructing a purge. I suspect the root cause is in the long-term statistics part - I tried to find help on user forums (MariaDB and HA community), only suggestion I found is that it might be memory related. I increased RAM allocation to the Proxmox VM which helped for 3 days but now it's back. Anyone seen this before? Below is an excerpt, not complete.

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:202
integration: Recorder (documentation, issues)
First occurred: 4:00:01 AM (93 occurrences)
Last logged: 10:00:59 AM

Error executing query: (MySQLdb.OperationalError) (2013, 'Lost connection to server during query') [SQL: SELECT anon_1.metadata_id, anon_1.state, anon_1.last_updated_ts, anon_1.attributes FROM (SELECT anon_2.metadata_id AS metadata_id, anon_2.state AS state, anon_2.last_updated_ts AS last_updated_ts, anon_2.attributes AS attributes FROM (SELECT states.metadata_id AS metadata_id, states.state AS state, %s AS last_updated_ts, CASE WHEN (state_attributes.shared_attrs IS NULL) THEN states.attributes ELSE state_attributes.shared_attrs END AS attributes FROM states LEFT OUTER , anon_1.last_updated_ts] [parameters: (0, 1735786800.0, 4825, 1, 4825, 1735786800.0, 1735873200.0)] (Background on this error at: https://sqlalche.me/e/20/e3q8)

amber light
#

What do your recorder settings look like? What does the glances addon show during a purge?

quartz gust
#

I don't run glances, will install

#

I purge hourly through an automation every 13th minute of the hour.

amber light
#

Kinda excessive.

quartz gust
#

The recorder component is storing details in a database which

then are handled by the history component.

recorder:

Use the Core MariaDB add-on to store the recorder data in

e.g.: "mysql://homeassistant:PASSWORD@core-mariadb/homeassistant?charset=utf8"

db_url: !secret db_url_recorder

The number of days of history to keep in the recorder database

purge_keep_days: 3

Disable auto purging

auto_purge: false

exclude:
domains:
- media_player
- camera
- automation
- update
entity_globs:
- sensor._uptime_sensor
- sensor._uptime
- sensor._ip_address
- sensor._wifi_signal
- sensor.*_esphome_version
entities:
- sun.sun
- sensor.uptime
- sensor.long_power_failure_count
- sensor.power_tariff

runic relicBOT
#

To format your text as code, enter three backticks on the first line, press Enter for a new line, paste your code, press Enter again for another new line, and lastly three more backticks.
```yaml
example: here
```
Don't forget you can edit your post rather than repeatedly posting the same thing.

quartz gust
#

I now manually reduced the records to 1 day history

#

But it still choked when I applied the filter

amber light
#

3 days is not a lot. I'd check the MariaDB logs for hints as well as glances for the resource usage.
I don't know if this is the purge query at the top of my head.

quartz gust
#

I installed glances, thanks. Noticed vscode consumes 60% of my RAM so disabled it for the time being

#

Perhaps that allow MariaDB to breath

amber light
quartz gust
#

6GB ram

#

So Vs code took 4gb

amber light
#

That's quite a lot I think

quartz gust
#

Yes, 6gb should be sufficient, it was running perfectly fine for the last 3yrs on 4gb

#

But perhaps it's vscode

#

Thanks for the recommendation to install glances

#

Also see this:

'''PROCESSES DISABLED (press 'z' to display)
Warning or critical alerts (last 3 entries)
2025-01-03 09:58:59 (00:01:38) - CRITICAL on MEMSWAP (100)
2025-01-03 09:58:56 (00:01:40) - CRITICAL on MEM (96.4)
2025-01-03 09:58:56 (ongoing) - LOAD (4.4'''

#

Those don't work sorry...'''

amber light
#

Let's hope it works better now with all the memory pressure gone.

quartz gust