Distributed MySQL databases with MRSK

[nickchomey]

One of my biggest preoccupations for moving off-cloud is what to do about a HA/distributed database. The cloud providers all have managed solutions for this, but self-hosting appears to be more challenging.

There's the traditional options like Galera.

There's also things like Vitess, which is compatible with MySQL. But seems to be kubernetes-first.

Finally, you could always go the SQLite route, with primary options for replication being rqlite, dqlite, litefs and bedrockdb.

Any suggestions?

[nickchomey]

Planet Scale seems like a good option as well - it is a managed serverless mechanism on top of Vitess, which is an ultra scalable MySQL compatible database. Their free tier seems exceptionally generous, and the Scaler tier is cheap as well. Extra GBs, requests etc... are also very cheap.

Perhaps of most interest to folks here, they have docs for connecting a Rails app

[jeremy]

If you go fully self-hosted, I'd recommend keeping it as operationally simple as you can manage. For new apps and products just getting off the ground, focus on disaster recovery—incremental backup/restore with binlogs—rather than HA and replication. Solid backups cover the key risks that can otherwise entice devs into sophisticated and time/expertise/$$-costly HA setups that drain product focus. Then you're free to introduce a replica db later on when it'll be driven by lower-stakes concerns like improving app performance or implementing zero-downtime db migrations/upgrades.

Going straight to PlanetScale is certainly attractive, especially for mature apps migrating off managed cloud DBs. Worth giving self-hosted a try, though! It's less scary than it looks, the experience pays off, and the db performance you'll achieve with modern commodity hardware is utterly mind-blowing compared to cloud dbs.

[nickchomey]

Thanks very much for the insights!

When you say self-hosted, you mean running the DB on your own server - ideally the same one as the application is on, or at least in the same rack/datacenter? This is what provides the considerable DB performance improvement, by eliminating network latency?

The issue with this, though, is precisely with regards to disaster recovery - no matter how frequently you do incremental binlog backups, you will always have unrecoverable gaps. The primary point of HA is to have a continuously-backed up replica(s) at all times, no? Performance gains by having regionally-located read copies seems secondary.

The issue is with deployment complexity. Planetscale solves this, but has the network latency. But if you have a read replica of the DB on each application server - presumably easily deployed with MRSK - then you have the best of all worlds... no? And the SQLite DB cluster tools that I mentioned seem like they make setting up an HA SQLite DB quite simple, as opposed to, say, Galera. The concern with the SQLite (or any) db cluster is mostly with regards to write latency to the master node, but they have queuing mechanisms to help with that. Plus you could even have other application server or Edge caching mechanisms to improve things further.

Wouldn't this bring the best of both worlds - 1) relative simplicity and 2) ultimate disaster recovery via HA db?

Side note, i looked into the various options and here's my thoughts:

• as much as I like rqlite, it has its own API rather than accepting MySQL directly
• dqlite seems just not very user friendly
• Bedrock has poor documentation and doesn't seem particularly open/collaborative
• LiteFS is quite promising, as is Marmot - both have an MySQL API by default, but are immature. I look forward to seeing their progress. I'm most optimistic for LiteFS as it is backed by Fly.io rather than a purely open-source passion project.

I'd be very appreciative if any oversights I've made could be explained, and alternatives suggested. Thanks!

[brunoprietog]

@jeremy Would you mind giving us some more details on how you manage the databases please? I'm setting up Percona MySQL Server and the idea is to make backups with Percona XtraBackup. Do you have any script that takes care of the backup retention policy? I've not found something reliable that does it. My idea is to send the backups to Cloudflare R2 (S3 compatible). I was thinking of doing something like a daily full backup and hourly incremental backups (an incremental backup based on the previous incremental backup, not the full backup, unless it's something you don't recommend), but XtraBackup doesn't make it that easy or I'm not understanding it well. To do that you need to create a folder for each backup and base the incremental backup on the previous incremental backup, unless it is the first incremental backup, in which case it should be based on the full backup. Upload it to R2 and also do something to remove the obsolete backups.

I would love to read your experience, thank you so much!

[scart88]

For the Stateful data, we plan to use https://github.com/reclaim-the-stack/get-started, and Talos Linux for a pretty stable k8s cluster to deploy a HA PostgreSQL (Crunchy PGO, CloudNative PG or Percona operators), or if you want to use MySQL, there is the Vitess operator.

We will use Spotahome or Opstree operators for our Redis database.
With this setup, we will have backup/restore, point in time recovery, HA, sentinel, and self-healing for our DBs.

I hope MRSK will provide a complete escape from K8s so we don't have to rely on K8s for the database needs.

For SQLite route, here is a very good article that shows how to use SQLITE and Litestream, however, I'm not sure how this works if you are using multiple hosts.
https://inthepacket.com/how-to-deploy-rails-7-sqlite-and-litestream-with-mrsk

[nickchomey]

Litefs, mentioned in other comments, is the multi-server successor to litestream. It's the same dev. I detailed other comparable projects as well, but I'm most keen about litefs.