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,
- ACID Compliance
- In-Memory-ish for blazing fast IO.
- Persistant (well...duh)
- Scalable as in cluster/master-slave/etc
- MySQL Drop-In replacement
- Open Source
- Should run on commodity server (IYKWIM)
So judging by my over optimistic list of requirements you'd already jump to ....ummm
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.
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!!!
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...
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!!!
- 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.