[MXS-2679] Galera router with write load balancing Created: 2019-09-16  Updated: 2020-08-26  Resolved: 2020-08-26

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: None
Fix Version/s: N/A

Type: New Feature Priority: Major
Reporter: Assen Totin (Inactive) Assignee: Todd Stoffel (Inactive)
Resolution: Won't Do Votes: 0
Labels: Galera

Epic Link: Galera Compatibility

 Description   

Currently, on Galera clusters the most commonly used router is read-write split, which only sends the writes to one of the nodes. One of the advantages of Galera is the full write support on all nodes, so it would be very useful to have a router which could route the write requests to all nodes.

One way to do this would be to open a connection to only one of the Galera nodes for each incoming session; then all traffic from this session goes to the chosen node. The choice of the node could be a simple round-robin, or, optionally, a some more advanced algorithm based on the number of open sessions to each node.

 Comments   
Comment by markus makela [ 2019-09-26 ]

This has proven to be a misconception among users: our research suggests that writing to multiple Galera nodes does not increase the scalability of writes and only leads to deadlock errors on commit.

This configuration is possible right now with the readconnroute router with the router_options parameter set to synced but we don't recommend it due to the changes that are required to the application logic.

Comment by Assen Totin (Inactive) [ 2019-09-27 ]

Thanks for the hint, Markus, very helpful.

As of the misconception, I now feel a happily misconcepted Galera user since I've been dong for years exactly this in a multi-app, multi-db environment - using HAProxy It is good to know that MaxScale can do it too.

In fact, it could be made even better as in the above scenario it is best when each client is routed to only one of the nodes, so clients are distributed among the nodes based on which DB they what to access. This helps avoid deadlocks and nicely scales the write capability of the cluster while providing transparent failover in case a node goes down. What I describe is also doable with HAProxy and works nicely - so would be cool to have this in MaxScale too some day.

You may close the ticket at your discretion.

Comment by markus makela [ 2019-09-27 ]

Splitting the load according to the database does prevent deadlocks but I don't think it improves write scalability as the same amount of IO is done on all nodes. I defer to the Galera experts on this but to my recollection, there's no throughput benefit to writing to multiple Galera nodes at the same time.

Comment by Anthony [ 2020-05-28 ]

Hello,

@markus, it doesn't works on my side. All requests are always forwarded to slaves instead to both.

I have a listener for RW and a service related:
[server-Service]
type=service
router=readconnroute
router_options=synced
servers=server-01,server-02,server-03

After restart -> All requests are redirected only to the slaves (read/write).

I also tried with router_options=master,slave and it's the same.

Also for router_options=running.

server protocol: MySQLBackend
listener protocol: MySQLClient
With 3 backends: Percona XtraDB Cluster 5.7

I want to use MaxScale only like LB in a first time (read/write) but it seems that we can only split read/write between master & slave :o

Best regards,

Comment by markus makela [ 2020-05-28 ]

anthosz this seems to be a separate issue, please file a new bug report for it.

Generated at Thu Feb 08 04:15:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.