The HA addon core-mariadb does not expose any config parameters from my.cnf (mariadb-server.cnf).
This killed the update from HA 2024.07.x to 2024.08.0, there is a database update involved. As my database is rather big, this update ran into “too many locks”.
This in turn is because innodb_buffer_pool_size=128M is just much too low for the size of my database. And there is no way in changing that. So I’m stuck with a non-migrated database.
The file /usr/share/hassio/addons/core/mariadb/rootfs/etc/my.cnf.d/mariadb-server.cnf is obviously created anew everytime the addon starts. It states: # Tune for low-end devices (Like a Raspberry Pi)
This is fine for most cases, I’m sure.
But my HA instance (Supervised mode on Debian) runs in a VM on a rather big server - the VM has 12 Virt-CPUs and 12 GB RAM (Hardware CPUs in the Server a Intel Xeons, 24 Threads alltogether) - it would be really nice to have theses parameters available in order to tune mariadb to a bigger server than a Raspi
I’d like these parameters to be available for configuration for the addon:
Why that big? Do you have one year(or more) history? HA’s database is not intended for such long history, for that there are other solutions, like grafana, influxdb… that’s why default is only 10 days. with so big DB you are just calling for disaster.
it’s not just if hardware that can/can’t cope, it’s software, in this case HA(OS)… i also have overkill machine: Intel NUC with i7 cpu, but i only have 15 days in HA, rest is influxdb on synology. This way you also ensure quick(er) search through HA’s history and overall HA work.
it will be very interesting to expose the config parameters in the config addon menu. not only for this problem, for future problems o future tuning of the BBDD, this will be very interesting!!!
Not sure if this is of any help but I have come across this: How can I tweak HASS.io MariaDB addon settings? - #18 by zavisko. If you install PHPMyAdmin then go to variables you can change the configuration there. Not sure if it will stay with a restart, haven’t tried it yet but could potentially be a solution to our issue
Just a quick update. I noticed the change I did stayed after a HA restart and I have just tried upgrading to 2024.9.1 with the change I did in PHPMyAdmin and the database upgrade worked!
I’m not a db expert, so i wonder… i tried to change that variable in phpmyadmin directly and it did change and retained after restart, so why not change it this way, since it’s more simple? I mean - what’s the catch of changing it via ssh?