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.
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. =)
[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.
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
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.
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?
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?
