Is MariaDB worth keeping or is it now inferior?

[nima]

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?

[koying (Chris B)]

I personally rolled back, because I felt an external DB wasn’t worth the hassle anymore. See this thread for pointers

Migrating from MySQL (mariaDB) back to sqlite

So about one year ago, my HA install slowed to a crawl and it was related to the sqlite database, everything making queries took forever. At the time, I moved my database to an external MySQL (well MariaDB) hosted on an external ubuntu machine. So far so good, but upgrading from 2023.2.x to 2023.3.x was a nightmare, upgrade got stuck with both HA and mariadb using over 100% CPU time forever. Turned out to be related to invalid timestamps. @bdraco was amazing and helped troubleshoot the issue…

[vingerha]

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.

[finity]

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.

[CaptTom (Tom)]

@vingerha

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.

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.

[tim.plas]

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?

[koying (Chris B)]

MariaDB doesn’t bring anything more in term of functionality

[mekaneck (Rick Auch)]

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?