0

I am looking to experiment with MariaDB 10 Galera Cluster. I am looking for highly available, scalable and reliable architecture. Basically I am curious what would be the better architecture and would this be possible and is it a good practice?

1) Galera Cluster + maxscale for spliting read write operations - here I'll logically separate both read/write operations. I want the writes to be applied on all nodes but eventually to have a specific nodes only for read operations. Similar like Master-Slave replication where writes goes to the master and slave goes to the slaves. Would this be possible here?

2) Galera Cluster with Master-Master replication and can I connect another Galera Cluster to this only for read operations?

My main goal would be to have couple of write nodes and couple of read nodes so I can split the operations and actually have a backup of each node if somethings goes wrong? What would be the best approach here?

Thank you.

user3526540
  • 1
  • 1

1 Answers1

0

I'd go with galera + maxscale + haproxy. Our setup is 3 galera nodes, 2 maxscale + 2 haproxy nodes giving an active-passive failover for maxscale - a few points to be aware of with this setup (or any galera + maxscale).

  1. Galera recommends a single node for writing - trying to write to more than one node can lead to write conflicts. This is typically implemented with a read-write splitter in maxscale, giving the single write node and multiple read nodes.
  2. Further nodes can be added for resilience and performance - most typical web apps run something like a 80:20 read:write split so this is a good model.
  3. In theory, you could run an active-active setup on haproxy for maxscale since the algorithm used by maxscale to identify the write node is supposed to guarantee that every maxscale node will choose the same galera node. When we first tested, this didn't happen but there have been several releases since then so this may work now.
  4. The big advantage with maxscale is that it means the galera setup is transparent to the end apps - as far as they're concerned they're just talking to a database server. This saves having to code to handle seperate read/write nodes.
SimonH
  • 136
  • 2
  • How exactly is the architecture with Galera + Maxscale + HaProxy? Can you give some more details on this? – user3526540 Aug 18 '15 at 19:31
  • @user3526540 Galera cluster is running on 3 nodes, then maxscale + haproxy is running on 2 nodes for a total of 5 nodes. If you need some specific details, let me know. – SimonH Aug 19 '15 at 09:38
  • Thank you for this. Can you get more details on haproxy and maxscale? Why do you use maxscale? I saw some tutorials where people use galera haproxy and keepalived. Why you injected maxscale? I would appreciate those details? Also is there a way to split the read/write ops but not to touch anything on the app side? Once again, thank you for the details! – user3526540 Aug 19 '15 at 10:14
  • @user3526540 - maxscale acts as a transparent database proxy sitting in front of galera. It typically ensures that write requests are all routed to a single node which galera then replicates. It also provides transparent access to the cluster - you don't need to build intelligence into your app about where the different nodes are; maxscale will route away from failed nodes and distribute reads across remaining active nodes. – SimonH Aug 19 '15 at 11:40
  • Thank you! What is the connection between MaxScale and HAProxy then in your setup? When I do read/write split with MaxScale I create services which contains router=readwritesplit and readconnroute and on each service I add servers=db1,db2,db3. How MaxScale choose the Master? Do I need at all to have router=readconnroute if I have router=readwritesplit? – user3526540 Aug 19 '15 at 12:05
  • I run two maxscale servers so that if one fails, the other picks up the traffic. HAProxy is used to switch between the two servers. As for the MaxScale config, I've got a readwritesplit router, but no readconnroute and all seems to work ok. – SimonH Aug 19 '15 at 14:16
  • Once again thank you very much. Can you please share you maxscale config? Of course you can hide what you think is sensitive. I just want to check it with mine because I have readwrite router and readconnroute and I am not sure what is the best case - should I have both or like you have just readwritesplit (I pressume the default behaviour is writes go to what maxscale determine master and reads go to what determine maxscale as slaves) - Is this correct? And again thank you! – user3526540 Aug 19 '15 at 21:31
  • I also forgot to ask you do you have 2 HA Proxies? What you use for the virtual ip - keepalived? – user3526540 Aug 20 '15 at 13:34