How can I tweak HASS.io MariaDB addon settings?

I want to tweak the MariaDB addon settings.
I found the file on my HA server located in.
\addons\core\mariadb\rootfs\etc\my.cnf.d\mariadb-server.cnf
That seems to contain the DB settings I’m after. However the changes made to that file are not persistent.

How do I pass along setting to that file?

E̶D̶I̶T̶:̶
̶I̶ ̶b̶e̶l̶i̶e̶v̶e̶ ̶I̶ ̶f̶i̶g̶u̶r̶e̶d̶ ̶t̶h̶i̶s̶ ̶o̶u̶t̶ ̶m̶y̶ ̶s̶e̶l̶f̶.̶ ̶ ̶T̶h̶e̶ ̶A̶d̶d̶-̶o̶n̶ ̶C̶a̶n̶n̶o̶t̶ ̶b̶e̶ ̶r̶u̶n̶n̶i̶n̶g̶ w̶h̶e̶n̶ ̶e̶d̶i̶t̶i̶n̶g̶ ̶t̶h̶e̶ ̶f̶i̶l̶e̶̶’̶m̶a̶r̶i̶a̶d̶b̶-̶s̶e̶r̶v̶e̶r̶.̶c̶n̶f̶’̶.̶ ̶ ̶S̶o̶ ̶t̶o̶ ̶e̶d̶i̶t̶ ̶t̶h̶e̶ ̶f̶i̶l̶e̶,̶ ̶y̶o̶u̶ ̶j̶u̶s̶t̶ ̶n̶e̶e̶d̶ ̶t̶o̶ ̶s̶t̶o̶p̶ ̶t̶h̶e̶ ̶a̶d̶d̶-̶o̶n̶ ̶,̶ ̶e̶d̶i̶t̶ ̶t̶h̶e̶ ̶f̶i̶l̶e̶,̶ ̶T̶h̶e̶n̶ ̶s̶t̶a̶r̶t̶ ̶t̶h̶e̶ ̶a̶d̶d̶-̶o̶n̶ ̶a̶g̶a̶i̶n̶.̶ ̶

EDIT2: Nope still reverts back after some time

Many Thanks
Kris.

Hi.

good you figured it out.

question, what are you tweaking? Im interested in the what and why and how. :slight_smile:
regards

I’m playing around with extending the Recorder to have 90 days of data.
However with the default DB settings I started to see various errors pop up. Graphs flat line-ing, Log errors about the DB and such.

I’m not running HA on a Rpi. And the default setting are tuned to a Rpi with the default 10 (or so) days of data. My pc running Docker/HA and such is… Well just better. So I can push the limits a bit.

So Since DB stuff is my weakest point. I decided this would I good learning lesson.

I’m sadly known a the person that was most likely to kill a DB, Dead. I’ve done it once at work in my early days. And I also trashed my Mythtv and Zoneminder DB, to many times to count.

S̶o̶ ̶n̶o̶w̶ ̶t̶h̶a̶t̶ ̶I̶ ̶k̶n̶o̶w̶ ̶w̶h̶e̶r̶e̶ ̶a̶n̶d̶ ̶h̶o̶w̶ ̶t̶o̶ ̶e̶d̶i̶t̶ ̶t̶h̶e̶ ̶s̶e̶t̶t̶i̶n̶g̶ ̶f̶o̶r̶ ̶t̶h̶e̶ ̶D̶B̶.̶ I can play around with out fear of trashing a very important DB. Not that the HA DB is not important, it’s that I’m just used to losings the HA DB for various reasons. All in fun learning however. =)

Isn’t it just done in the configuration.yaml by setting

purge_keep_days: 14

Yes it is.
However there are more specific DB setting in that file.

[mysqld]
port=3306
log_error=mariadb.err

# Persistent storage location
datadir=/data/databases

# Use a proper collation set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Do not resolve DNS names
skip-name-resolve

# Tune for low-end devices (Like a Raspberry Pi)
key_buffer_size = 16M
max_connections = 64
myisam_recover_options = FORCE
myisam_sort_buffer_size = 8M
net_buffer_length = 16K
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 = 16M

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

# InnoDB Tweaks
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 64M
innodb_log_buffer_size = 8M
innodb_log_file_size = 48M
max_binlog_size = 96M

And now that I have posted the file I see now it has reverted back.
So I’m back where I started from.

How do I pass along setting to that file? \addons\core\mariadb\rootfs\etc\my.cnf.d\mariadb-server.cnf

2 Likes

Did you try stop mariaDB addon before editing?
ah, never mind. you wrote that in your opening post :slight_smile: lol
cant help you besides that. Sorry.

THank you for the explaining anyway !

I have all so tried logging in to the the docker container “addon_core_mariadb” and editing the file “etc\my.cnf.d\ mariadb-server.cnf”

However it still reverts back after a restart of the container.

And if the “addon_core_mariadb” container is stopped and/or not running. You can not log in via docker to edit the file.

Still searching for a solution.

1 Like

Problem is History state not loading. My home-assistant_v2.db database file size is 2.6GB. I installed MariaDB addon, but it seems not working .

System details:

arch x86_64
dev false
docker true
hassio true
os_name Linux
os_version 4.15.0-91-generic
python_version 3.7.7
timezone Europe
version 0.108.2
virtualenv false

Configuration file is:

homeassistant:
  customize: !include customize.yaml
  auth_providers:
    - type: homeassistant
  packages: !include_dir_named packages/

default_config:

http:
   base_url: https://xxx.duckdns.org:8123
   ssl_certificate: /ssl/fullchain.pem
   ssl_key: /ssl/privkey.pem
 
recorder:
  purge_keep_days: 5
  purge_interval: 1

Here is a LOG

[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] done.
[services.d] starting services
[services.d] done.
[08:17:55] INFO: Create a new mariadb initial system
[08:17:59] INFO: Starting MariaDB
200409 08:17:59 mysqld_safe Logging to '/data/databases/mariadb.err'.
200409 08:17:59 mysqld_safe Starting mysqld daemon with databases from /data/databases
2020-04-09  8:17:59 0 [Note] InnoDB: Using Linux native AIO
2020-04-09  8:17:59 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-04-09  8:17:59 0 [Note] InnoDB: Uses event mutexes
2020-04-09  8:17:59 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-09  8:17:59 0 [Note] InnoDB: Number of pools: 1
2020-04-09  8:17:59 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-09  8:17:59 0 [Note] mysqld: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2020-04-09  8:17:59 0 [Note] InnoDB: Initializing buffer pool, total size = 64M, instances = 1, chunk size = 64M
2020-04-09  8:17:59 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-09  8:17:59 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-04-09  8:17:59 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-09  8:17:59 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-09  8:17:59 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-04-09  8:17:59 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-04-09  8:17:59 0 [Note] InnoDB: Waiting for purge to start
2020-04-09  8:17:59 0 [Note] InnoDB: 10.4.12 started; log sequence number 60972; transaction id 21
2020-04-09  8:17:59 0 [Note] InnoDB: Loading buffer pool(s) from /data/databases/ib_buffer_pool
2020-04-09  8:17:59 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-09  8:17:59 0 [Note] InnoDB: Buffer pool(s) load completed at 200409  8:17:59
2020-04-09  8:17:59 0 [Note] Server socket created on IP: '::'.
2020-04-09  8:17:59 0 [Warning] 'proxies_priv' entry '@% root@core-mariadb' ignored in --skip-name-resolve mode.
2020-04-09  8:17:59 0 [Note] Reading of all Master_info entries succeeded
2020-04-09  8:17:59 0 [Note] Added new Master_info '' to hash table
2020-04-09  8:17:59 0 [Note] /usr/bin/mysqld: ready for connections.
Version: '10.4.12-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  MariaDB Server
[08:18:00] INFO: Check data integrity and fix corruptions
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
[08:18:00] INFO: Ensuring internal database upgrades are performed
2020-04-09  8:18:04 22 [Warning] 'proxies_priv' entry '@% root@core-mariadb' ignored in --skip-name-resolve mode.
[08:18:04] INFO: Ensure databases exists
[08:18:04] INFO: Create database homeassistant
[08:18:04] INFO: Ensure users exists and are updated
[08:18:04] INFO: Create user homeassistant
[08:18:04] INFO: Init/Update rights
[08:18:04] INFO: Alter rights for homeassistant to homeassistant

[08:18:04] INFO: Successfully send service information to Home Assistant.

Did you read the documentation for recorder ? How can you expect recorder to use mariadb if you don’t tell it to?

Assuming that’s pointed to “Smile”

But just in case not. I do:

recorder:
  db_url: mysql://hass101:password@core-mariadb/homeassistant?charset=utf8
  purge_keep_days: 10
# trying with an automation to purge for testing. so set to 0=off
  purge_interval: 0
  exclude:
    domains:
      - group
      - media_player
      - scan_store
      - zone
      - zwave  
      - automation
      - updater
    entities:
      - sensor.date
      - sensor.time
      - sensor.date_time

That reply was indeed intended for ‘smile’

Hi, did you find the solution?

No, not yet.

I’m thinking of just putting together a small pc to be a database server. Then I can combine my database needs to one server, for Hass, Mythtv and Zoneminder. As they all need/use a database.

One more project on the list of todo’s :smile:

From your log, it seems like the MariaDB was created. Have you checked the database with phpMyAdmin or something similar? I’ve also used MariaDB on my Synology NAS if that’s any help but I found the local HassOS add-on a little faster. In saying that, past 0.108.x, I seem to be having issues with the recorder not opening too. I often get “Loading state history…” and that’s it. I changed to a local Db however and things did not improve so not MariaDB related.

That’s because you can’t… all data you edit in a container is trashed everytime the container is rebuilt. The only thing that survives is stuff in a persistent volume. The addon settings are written to that file you mentioned above. If you just want to extend the recorded data, someone already showed you how to do that.
Even with my NUC, I find the DB chokes HA and I have my data set to 7 days. I don’t really need/want more than that. Too mych data and the history and states pages take forever to load. I also exclude as much as I can from recorder.

Hi together,
first of all thank you for the mariadb addon. I like to have all together in homeassistant.
i also have the requirement to tweak the default setting of the mariadb setup.
I have several small iot devices updating a second database. The issue is that despite database disconnect on the arduino devices the connections stay “asleep” under process list. The default setting for timeout is something around 28k sec.

You can modify the global settings by sql query, but you have to redo that after restart.

In order to solve my requirement i am thinking on a automation that is fires the required sql statements, but this is only a work around. Is is possible to add more options to the setting or use a persistant directory for the configuration?

Many Thanks and kind regards from Germany

Was there ever a solution to this?

I’m on an x86-64 server with many GB or RAM (4GB allocated to HassOS) fast CPU (4 threads of my Xeon 6c/12t CPU) and SSD-RAID backed storage…but its using only a tiny fractiton of the available memory (less than 1GB) and history seems annoyingly laggy.

MariaDB is light years better than SQLite but still laggy. I’m keeping 14 days history but many things just 24 hour graph is painful to load if there are a lot of data-points.

I’d like to bump up my buffers a lot in hopes of getting better performance…but seems to be no way to do this?

hi everyone,

in case you are still interested in solution i think i worked it out, basically what i did was:

  • stop the addon
  • find all mariadb-server.cnf files:
root@hass:~# find / -name mariadb-server.cnf
/var/lib/docker/overlay2/3d5339f5802d7c2efef8b40d106cfeedcab7ced036d5dfc9f656bbb651eaa352/diff/etc/my.cnf.d/mariadb-server.cnf
/var/lib/docker/overlay2/81db59c5822b9e9bea4d06cba5f0dec2ee3afaf7d915255187e568ef0b226df0/merged/etc/my.cnf.d/mariadb-server.cnf
/var/lib/docker/overlay2/1a927a3d8140e4886407e627cea6fdbe324980a7b13a2680650b0e838cd7d309/diff/etc/my.cnf.d/mariadb-server.cnf
/var/lib/docker/overlay2/a00252d8687d954bec55a4351043bb0537953a174bd40aa8795e951312dc6da6/diff/etc/my.cnf.d/mariadb-server.cnf
/usr/share/hassio/addons/core/mariadb/rootfs/etc/my.cnf.d/mariadb-server.cnf
  • edited all of the files (edited just one and replaced the others with the same copy)
  • start the addon

after the restart i see that the parameters are updated, example of 1G buffer:
mariadb

EDIT: im pretty sure this wont survive the addon patching, but i have to test it yet

2 Likes

Are you doing this on HassOS (and I assume getting on the host console?) or do you run it on your own OS/Docker?

Im running a virtual machine with debian and i installed a supervised version: link