MySQL XtraDB/Galera Cluster – Achieving High Availability with RamDisk for SuperFast Performance

galerain-memory-databaseMySQLperconaxtradb-cluster

Ok, So this is going to be a speculative question, mostly design oriented and a rather long one. I'd grab a cuppa' covfefe, if I were you.

Preface: So I have been researching into databases and wanted a really fast (Like really-really) database(engine) with the following must haves,

  1. ACID Compliance
  2. In-Memory-ish for blazing fast IO.
  3. Persistant (well…duh)
  4. Scalable as in cluster/master-slave/etc
  5. High-Availability(HA)
  6. MySQL Drop-In replacement
  7. Open Source
  8. Should run on commodity server (IYKWIM)

So judging by my over optimistic list of requirements you'd already jump to ….ummm

How to speed up mysql, slow queries

Alright, alright jokes aside, I know if innodb_buffer_pool_size is tweaked properly it'll run off of the memory most of the time, but I say

It ain't in-memory yo!

But you'd say Hey, its 2k18 people might have already created some 100% in memory DBs, Right? Umm… actually they have but each have their own tradeoffs.

  1. VoltDB Community Edition
    Everything seems fine until you realise it isn't a drop in replacement. It needs some stored procedures-ish commands in java which requires you rewriting whole of your application or atleast the db layer/driver/etc of your php app. So? DEALBREAKER!!!

  2. MemSQL, Well this seems a pretty strong contender for our "Best OpenSource In-Mem Scalable SQL Acid DB ever" contest. Only for, the memSQL Boss be like…

MemSQL Server Requirements

Needless to say, memSQL needs atleast 4 cores and 8Gigs of RAM at minimum, and recommeded is pretty insane at 4 cores and 32 gigs per core!!!!
Also the community version of memSQL(which btw, isn't fully OpenSource!, its just free) doesn't support high availability, as its a paid feature. Also its NoSQL. So? DEALBREAKER!!!

  1. All other NoSQLish dbs like membase, Redis, Memcached, etc are prelly much ruled out.

So now for my genius idea!!!

I was wondering if we could run an XtraDB/Galera Cluster with all the instances running off of a RAMDisc with regular snapshots?

It gets all the checkboxes ticked.

Just hear me out, First addressing the elephant in the room, We know that running full mysql Dbs off of RAMDiscs is pretty umm…bold, put in the most polite of ways. So what happens if the server crashes/shuts off/etc we loose a node. While all our DB Cluster as a whole is still alive and kicking a**. All we have to implement is booting the db up from last snapshot and syncing back with the cluster which btw the clusters are pretty good at, inherently!

OK, peeps, Don't go all salty on me, if you see a flaw in my implementation, guide me.

Best Answer

OK, 3 Galera nodes, with:

  • Each having all disk stuff sitting in RAM disks.
  • Enough RAM to make that possible.
  • The nodes separated geographically so that a volcano, flood, etc, cannot take out all of them at once.
  • High speed network.

Notes:

  • That will allow for full and automatic recovery from one server loss.
  • The only delay during a failure is switching clients to one of the other two nodes.
  • But, you may find that the network is the slow point. (Entanglement has not been perfected yet.)
  • No snapshots needed. (You always have 2 other nodes with full copies of the data.)
Related Question