MariaDB: Add option to persistently configure innodb_buffer_pool_size via UI

[WordsOfMe]

Describe the issue you are experiencing

After upgrading to 2024.9 the DB migration failed. The migration completed successfully after increasing the innodb_buffer_pool_size from 128M to 4G. This change is currently not trivial, as from the CLI a shell needs to be opened inside the corresponding Docker container, the configuration file needs to be found and editied using vi and MariaDB needs to be restarted. Moreover, such a change is currently not persistent and is likely to be due also for the next migration.

What type of installation are you running?

Home Assistant OS

Which operating system are you running on?

Home Assistant Operating System

Which add-on are you reporting an issue with?

MariaDB

What is the version of the add-on?

2.7.1

Steps to reproduce the issue

1. Set up recorder with a long history, e.g.
   pure_keep_days=366
2. Upgrade to 2024.9 and experience a failed DB migration, with the following line in the logs:
   (1206, 'The total number of locks exceeds the lock table size')
3. Follow these steps in a CLI:

docker exec -it addon_core_mariadb /bin/sh
vi /etc/my.cnf.d/mariadb-server.cnf 

change innodb_buffer_pool_size=128M to innodb_buffer_pool_size=4G

killall mysqld
mysqld --datadir=/data/databases --user=root

4. Restart Home Assistant via Developer Tools - YAML - Restart
5. After some time the DB migration completes successfully

System Health information

System Information

version	core-2024.9.0
installation_type	Home Assistant OS
dev	false
hassio	true
docker	true
user	root
virtualenv	false
python_version	3.12.4
os_name	Linux
os_version	6.6.46-haos
arch	x86_64
timezone	Europe/Vienna
config_dir	/config

Home Assistant Community Store

GitHub API	ok
GitHub Content	ok
GitHub Web	ok
HACS Data	ok
GitHub API Calls Remaining	5000
Installed Version	2.0.1
Stage	running
Available Repositories	1395
Downloaded Repositories	12

Home Assistant Cloud

logged_in	false
can_reach_cert_server	ok
can_reach_cloud_auth	ok
can_reach_cloud	ok

Home Assistant Supervisor

host_os	Home Assistant OS 13.1
update_channel	stable
supervisor_version	supervisor-2024.08.0
agent_version	1.6.0
docker_version	26.1.4
disk_total	93.8 GB
disk_used	66.2 GB
healthy	true
supported	true
host_connectivity	true
supervisor_connectivity	true
ntp_synchronized	true
virtualization	kvm
board	ova
supervisor_api	ok
version_api	ok
installed_addons	Node-RED (18.0.5), File editor (5.8.0), Samba Backup (5.2.0), Studio Code Server (5.15.0), Advanced SSH & Web Terminal (18.0.0), MariaDB (2.7.1), Music Assistant Server (beta) (2.3.0b19), evcc (0.130.7), modbus-proxy (1.0.18)

Dashboards

dashboards	3
resources	0
views	2
mode	storage

Recorder

oldest_recorder_run	15. Dezember 2023 um 11:32
current_recorder_run	6. September 2024 um 20:57
estimated_db_size	33605.48 MiB
database_engine	mysql
database_version	10.11.6

Solcast PV Forecast

can_reach_server	ok
used_requests	1
rooftop_site_count	1

Anything in the Supervisor logs that might be useful for us?

No response

Anything in the add-on logs that might be useful for us?

No response

Additional information

configuration.yaml:

recorder:
  db_url: mysql://homeassistant:***@core-mariadb/homeassistant?charset=utf8mb4
  purge_keep_days: 366

[ceskyDJ]

I agree but for me increasing to just 512 MiB (as someone in Home Assistant Core mentioned in a guide how to fix the issue).

I think it should be globally increased to 512 MiB (or maybe 256 MiB could be enough, I don't know) in a default configuration. Of course a way for tunning these configuration options stored in /etc/my.cnf.d/mariadb-server.cnf would be great to have in UI of the addon.

[bakerkj]

I can confirm this allowed me to upgrade from database version 46 to 47. Thank you!

[GurbEHV]

Yup, this worked for me too, thanks!
(I increased to 512M)

[highlandr]

Worked here too. 30GB Database.

[samtygier]

Looks like it was originally set low to optimise for low RAM machines which is fair: #1436
Would be good to be configurable on larger machines.

PS: is there a trick to running docker? From the Terminal addon I get docker: command not found.
I'm running

Core 2024.9.1
Supervisor 2024.08.0
Operating System 13.1
Frontend 20240906.0

[ceskyDJ]

PS: is there a trick to running docker? From the Terminal addon I get docker: command not found.
I'm running

What terminal addon do you use? It works for me with Advanced SSH & Web Terminal. I have disabled Protection mode, which could do the trick. Protection mode provides just limited access to the system. If you use this addon, you can disable Protection mode in its Info tab:

[samtygier]

Thanks. I was using "Terminal & SSH", now switch to "Advanced SSH & Web Terminal" and disabled protection. And it works.

[aaamoeder]

Would be a VERY nice option to have!!!

[danielszilagyi]

Agreed, this is hardcoding to Pi acceptable values is a headache on bigger systems with huge DB. Had to do this workaround when upgrading to 2024.8 and then to 2024.9 as well.

[steel4me]

Ohhhhh, frustrating. Are everyone sure that settings will be kept after restarting. I mean docker containers will restart, maybe fine, but when they are updatet it will be overwritten? Maybe it is better to set global vars for the containers and not to edit config files inside the containers?

[ceskyDJ]

Ohhhhh, frustrating. Are everyone sure that settings will be kept after restarting. I mean docker containers will restart, maybe fine, but when they are updatet it will be overwritten? Maybe it is better to set global vars for the containers and not to edit config files inside the containers?

It's just a temporal solution, which helps you to upgrade Home Assistant Core to the newest version and do required database migrations. These changes "disappear" after restarting the container (global variable and config file change, too). We wait for adding some permanent option to the addon configuration. It is a reason, why this issue exists (see the first message in this thread).

[danielszilagyi]

I filed a PR, works in my dev environment, but I never contributed to HA before, so not sure when if it will go through.

[JoshuaPK]

The real question is, why is the recommendation to use MariaDB instead of Postgres? (And on HAOS, why isn't Postgres used?) On a 9GB database I've never had any performance issues, even during schema upgrades. Using a DB in Postgres 16, It took roughly 30 seconds for the schema upgrade that other folks have reported is taking hours under MySQL or MariaDB.

[ceskyDJ]

The real question is, why is the recommendation to use MariaDB instead of Postgres? (And on HAOS, why isn't Postgres used?) On a 9GB database I've never had any performance issues, even during schema upgrades. Using a DB in Postgres 16, It took roughly 30 seconds for the schema upgrade that other folks have reported is taking hours under MySQL or MariaDB.

Hmm, interesting. What HW do you running your Home Assistant instance on? It looks great according to your results. I thought PostgreSQL isn't supported, so I use MariaDB and has issues with last migrations, as you mentioned. I've dedicated Hardkernel Odroid M1 for Home Assistant Operating system with NVMe SSD as data storage. Previously I used shared (there were other services running along the supervisor version of Home Assistant) Raspberry Pi 4B 4 with GB of RAM and had pretty bad experience with performance. Now, it works well but DB migrations (and working with history in frontend) and still not as good as they could be.

[steel4me]

Just a bad nightmare, gave it one more try. hassos upgrade. changed config inside docker container mariadb. tried to restart.
So, now? Just wait till he fails. Then restart?

Nice data garbage from last migration fail.....

[janchlebek]

The question is also: why does it fail in reality.
It does not necessarily have to be related to disk space or MariaDB configuration.
In my case it was a problem with states table that had a corrupt index. I did repair it by drop and reimport of the table and migration was successful.
I suggest you also check the DB health.

[steel4me]

The question is also: why does it fail in reality. It does not necessarily have to be related to disk space or MariaDB configuration. In my case it was a problem with states table that had a corrupt index. I did repair it by drop and reimport of the table and migration was successful. I suggest you also check the DB health.

The Buffer was at 128MByte. To less for so much data (32GByte). Took over 10 hours.
With 2GByte everything was done in 30-50 Minutes...

home-assistant/core#125339 (comment)

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[ntimo]

Do you have any news on getting this merged?