Hi.
good you figured it out.
question, what are you tweaking? Im interested in the what and why and how. 
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 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 
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:
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?
Thank You
man that default config is severely crippled
how ever migration still failed after giving it 50% of the available memory