Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MariaDB: Add option to persistently configure innodb_buffer_pool_size via UI #3754

Open
WordsOfMe opened this issue Sep 7, 2024 · 17 comments · May be fixed by #3763
Open

MariaDB: Add option to persistently configure innodb_buffer_pool_size via UI #3754

WordsOfMe opened this issue Sep 7, 2024 · 17 comments · May be fixed by #3763

Comments

@WordsOfMe
Copy link

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
  1. Restart Home Assistant via Developer Tools - YAML - Restart
  2. 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
@WordsOfMe WordsOfMe mentioned this issue Sep 7, 2024
Open
@ceskyDJ
Copy link

ceskyDJ commented Sep 7, 2024

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
Copy link

bakerkj commented Sep 7, 2024

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

@GurbEHV
Copy link

GurbEHV commented Sep 9, 2024 •
edited
Loading

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

@highlandr
Copy link

Worked here too. 30GB Database.

@samtygier
Copy link

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
Copy link

ceskyDJ commented Sep 11, 2024

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:
image

@samtygier
Copy link

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

@aaamoeder
Copy link

Would be a VERY nice option to have!!!

@danielszilagyi
Copy link

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
Copy link

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
Copy link

ceskyDJ commented Sep 15, 2024

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 danielszilagyi linked a pull request Sep 15, 2024 that will close this issue
Open
@danielszilagyi
Copy link

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
Copy link

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
Copy link

ceskyDJ commented Sep 19, 2024

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
Copy link

steel4me commented Sep 24, 2024 •
edited
Loading

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?

image

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

@janchlebek
Copy link

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
Copy link

steel4me commented Oct 5, 2024 •
edited
Loading

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Add support for mariadb server config parameter danielszilagyi/homeassistant-addons