Addon mariadb: expose more mariadb config parameters in addon config

Feature Requests
1

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:

# Tune for low-end devices (Like a Raspberry Pi)
key_buffer_size = 64M
max_connections = 64
myisam_recover_options = FORCE
myisam_sort_buffer_size = 16M
net_buffer_length = 32K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
sort_buffer_size = 512K
join_buffer_size = 128K
table_open_cache = 64
thread_cache_size = 8
thread_stack = 192K
tmp_table_size = 96M

# Disable query cache
query_cache_limit = 1M
query_cache_size = 0M
query_cache_type = 0

# InnoDB Tweaks
#innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 32M
innodb_log_file_size = 96M
max_binlog_size = 192M

(numbers are just for examples, that’s not my config)

2

In regard to your initial problem, see this post to make the migration offline:

I know, has nothing to do with your FR, but as you said you’re stuck on the old schema, this could help. :slight_smile: Sorry for disturbing your FR. :blush:

1 Like
3

@paddy0174 that’s for the update from 2024.07.x to 2024.08.0?

That’s for MariaDB or for sqlite? I’m running the MariaDB-Addon, not sqlite.

The database is on schema 43.

According to mariadb, the sql statement running too long is

MariaDB [(none)]> show processlist;
+----+---------------+--------------------+---------------+---------+------+-------------------+----------------------------------------------------------------------------------+----------+
| Id | User          | Host               | db            | Command | Time | State             | Info                                                                             | Progress |
+----+---------------+--------------------+---------------+---------+------+-------------------+----------------------------------------------------------------------------------+----------+
| 12 | homeassistant | a0d7b954-ssh:34598 | homeassistant | Query   | 3030 | copy to tmp table | ALTER TABLE state_attributes MODIFY attributes_id BIGINT NOT NULL AUTO_INCREMENT |   23.657 |

which looks nothing like the code you quoted?

I’m confused.

4

How big is your database? I have around 9GB and update went through fine…

5

@Protoncek surely more than 9 GB, probably over 50 GB (which should be no problem given the hardware it runs on)

6

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.

7

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!!!

1 Like
9

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

10

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!

11

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?