Migrated to mariaDB long ago but default database keeps getting more efficient. Is it worth unwinding MariaDB? If yes are there any guides to doing so?
I personally rolled back, because I felt an external DB wasn’t worth the hassle anymore. See this thread for pointers
1 Like
I moved back too. The only downside I have is when analysing the db (e.g. statistics values), as for that you need to stop HA first and make a copy to use with dbbrowser. If you donot have this need then … no issues I guess. Even with above, still happy to have moved back as running a separate dbserver just-for-ha made little sense and required other things too wrt to backup etc.
1 Like
I keep it because when I do backups I really don’t care about the db.
And when I do a backup it takes way less time to do the backup without the fairly large db included.
Also I save a years worth of backups at a time and it saves a lot on disk space in my storage location.
if I ever need to recover my instance as long the current mariadb is still intact I haven’t lost anything. if it’s not then like I said I really don’t care about the lost data and I’ll just create a new blank one.
I don’t bother stopping HA. I just copy off the “live” database file and do my analysis on the copy.
Obviously this doesn’t work if you plan to re-save the database back to HA after making changes to your copy. In that case I would stop HA before copying it off, and not start it again until my changes (which I’d already developed and tested on a throw-away copy of the db) are done and the updated db is copied back to HA.
I’m currently running the default SQLite. But I want longer term reporting. Would it be reasonable to keep SQLite and add InfluxDB (as HA add-on), or should I also be thinking about swapping out SQLite for MariaDB in this configg?
MariaDB doesn’t bring anything more in term of functionality
1 Like
What Chris said, and also consider whether the long term statistics provide good enough long term data.
If your sensor is set up properly, state class measurement will generate hourly snapshots of min, max, average. State class total and total_increasing will generate hourly snapshots of state and sum. This info is retained indefinitely.
Do you really need to know the humidity of your bathroom from two years ago in 30 second intervals, or is hourly good enough?
2 Likes
agree. i think long term statistics are not fleshed out enough to be useful for me. I tried migrating db back but ran into errors when I tried entering the password for mariadb. I gave up and just commented out my configuration.yaml entry for recorder: altogether and rebooted the system.
No improvement in size of back-up. No change in responsiveness that I notice. But I was able to remove a few more add-ons and simplify the installation which I prefer.
1 Like
Actually it does. MariaDB provides full ACID. Thus it’s impossible to make data within it inconsistent (considering that all related data changes are made within single transaction). It also allows consistent backups in runtime. It’s durable.
This all cannot be said about sqlite.
For me the decision was easy.
I run HA on a virtual machine.
Since the host already ran MySQL (for something else), why run 2 DB engines instead of re-using it for HA too 
Keeps my HA backup smaller (as well as my VM Snapshot/backups), and saves me some cpu cycles 
The main reason I switched back to sqlite (beside performance) is specifically because the 2 backups were desynchronized (I also run mysql for other stuff).
If you are not proficient with sysadmin, there is just no questioning: stick to sqlite.
2 Likes