Thank you for this guide!
I run HA in a VM on my Synology NAS, so brokken SD cardes are no issue.
Will moving the DB to MariaDB still boost performance or will performance come more from excluding/including?
And if moving to MariaDB still helps, what would be the best option: MariaDB addon in HA Supervised or the MariaDB package of Synology?
@bungo63 Really good idea to keep the database off the RPI. Will give it a go as I already use MariaDB on Synology NAS for data logging. Just never considered plonking my HA database there too 
btw new to HA !!
I would like to create nice reports too. Does anyone have any comparative experience with Grafana and KoolReport as it will be take some time to do stuff so always good to have peoples feedback to find the right package ?
Regarding SD Card failure (not really the main topic here) I have been using RPI for over 10 years and had corrupt cards every now and then. Since I moved to Sandisk Class 10 never had a problem
Regards, Kevin
@edozat1 didnāt realise you could run HA in a VM on Synology NAS. I have however used MariaDB on Synology for quite a few years now and it has never let me down. I canāt help thinking that
MariaDB addon in HA Supervised would be easier if you donāt have experience with phpMyAdmin on Synology
MariaDB package of Synology would be much more powerful however more learning curve and planning maintenance etc.
Regards , Kevin
I installed MariaDB to DS, created tables and etc. Connected my HASSIO to it, no problems.
I have huge disk load, 100% of the time on my DS. What could be wrong?
I tested this for 1-2 weeks. Always the same problem.
Once i change HASSIO to internal MariaDB, there is no load to DS at all.
Great guide, works fine for me! Very much appreciated 
I am planing on doing this, does the very first post of this thread still work? or should I look around something else??
Any issues apart from powering on order (not an issue for me, both devices are VMs inside proxmox with virtual power on order and delays, first Synology, 180 seconds later HASOS)
Iāve been using this solution for about a year. While troubleshooting something else, I noticed the following warning in my log:
Version 5.5.68 of MariaDB is not supported; minimum supported version is 10.3.0.
Starting with Home Assistant 2022.2 this will prevent the recorder from starting.
Please upgrade your database software before then.
I donāt remember how it looked a year ago, but now my QNAP app store offers me MariaDB 5 ver 1.0.1.235 and MariaDB 10 ver 1.0.1.235. Is it as simple as installing version 10 and copying the configuration from version 5? Has anyone else been through this on a NAS that can offer some advice? Iām thinking I will have to re-learn what I did a year ago and re-create it with the new MariaDB version.
I have now also switched to a MariaDB hosted on my Synology NAS. Logically, the hard disk access on the NAS now increases significantly. Actually, a NAS is designed for this, but do you think it has a strong impact on the life of the hard drives when constantly written to?
I have installed MariaDB on synology and connect everything like here is descriptedā¦ worksā¦
but after couple minutes got error:
ERROR (MainThread) [homeassistant.components.recorder] The recorder queue reached the maximum size of 30000; Events are no longer being recorded
there is no problem with dates/ times etcā¦ for checking I switch back to MariaDB on HA and was no problemā¦
any sugestion how to make bigger size of record queue ?
thanks !!
Thank you for this, got it working just today!
this is a great post and it got me up and running a few months ago. thank you so much. one question - is mariadb (specifically, phpmyadmin) supposed to show GMT times no matter what time zone you have set? i started playing with sql queries to pull data from sensors from 24 hours ago and realized i had to set the interval to -16 hours instead of -24 hours to account for my time zone. But itās weird because if i ssh into both my NAS and by Rpi ā and type the command ādateā ā both give me the correct -08:00 time zone. and i spent like all day yesterday figuring out time zone tables in mariadb. but if i have the time_zone variable set to system and the system_time_zone variable set to PDT ā i still get the weird issue where my sql queries have to be for -16 hours instead of 24. The ONLY way i can get them to sync up is if i set my time_zone variable in mariadb to GMT. which is fine, because home assistant still translate the recorder logs to the right time when i view through hass, but now itās irking me :).
i noticed yesterday when sorting throuhg all of this that no matter what i have my time zone variables set to in phpmyadmin/mariadb, the date fields in the database still show up as GMT. Is there no way to change the time actually displayed?
completely separately but not entirely off point: does anyone have any thoughts on performance of mariadb on a SEPARATE (from my home assistant) RPI vs on NAS? my snyology nas is pretty low end and it definitely feels it now that i have my mariadb hosted on there. wondering if iād be better off buying a second rpi and putting my recorder db there instead.
Is it GMT or UTC? I think it should default or alway be at UTC time, but may be wrong.
No not really. Youāll calendar life expire the drives long before the relatively small number of I/O transactions cause any significant excessive wear. As you say, NASās (And proper NAS discs) are built for one main task, disc I/O.
What sort of speed increase āshouldā I see from this?
I have a VERY large system so Im running a dell optiplex 7050 i7 @3.6ghz running a M.2 NVMe (speed is the key not memory) and am getting reboot times going UPWARDS from 40s to now upwards of 50+ seconds with the Core at 43s and the recorder service at 15s
Is it possible that using a NVMe is faster than using the Network to the NAS considering I have BOAT LOADS of horsepower?
Iād think your system would offer a faster DB performance than going the round trip to the NAS via the network. But depending on the the DB implementation with HA you may not notice much of a difference. The recent HA updates have made great strides in DB management, mine is much snappier now than it was without any changes my end other the HA updates.
Great guide! But I have some questions regarding setting up the DB like described here.
How fast would the db grow if I would disable auto_purge? I know it depends on how many entities and so on but a reasonable ball park figure? And what would it do to the life span of the HDD in my NAS?
Just to check if I understand correctly, if I see that the db is growing what I think is too much, I would set auto_purge: true and add purge_keep_days: 365 to save data for the last year, so every night at 4:12 the recorder would purge the 366th day?
Is there some way to truncate data that is older than, say, a year? (Like the ABB 450 controller does, if anyone is familiar with that)
I switched from a RPi3 to a NUC with SSD this weekend. I reloaded a backup off my Pi to keep my config intact, but that of course also means my db/log is still located at the MariaDB/Synology.
Honestly I didnāt really know what I was doing when I followed the guide in the first place 
So is there any easy way to move my DB/logs back from the Synology to my NUC?
Struggled to figure this out for days - found this, switched over in 20 minutes. Thank you.
Hi.
Iām new to HA and wanted to move my db to my NAS. So great tutorial that I might try. I have a question first. Now if I make a backup (automatically every night on my NAS) and do a restore the db is also restored. How will this work when the db is external? Will this be included in the backup as well?
Ok I managed to get the external database working on my NAS. I have a few questions about this. I have a yearly min temp sensor and also data from my utilities like gas consumption and kwh usage. where are these stored in the data base because I cannot find them. The reason why I ask is because I want to control the size of the database to a couple of weeks. If I purge the database will the yearly min temp value and the utilities value also be purged, or are these stored somewhere else? I want to keep those.