Automatic failover and Load balancing on Galera cluster

[Sai Ram K]

Hi all,

I have few queries on Load balancing on Galera cluster.

1. How to distribute the load (writes) to one node and Reads to other nodes.

How to make one node as Master and remaining all are secondary nodes.

2. How automatic failover can be performed on the Galera cluster.

As i have 3 nodes - node1 ,node2, node3.

If node1 is the bootstrapped node than if it goes down than how to switches to another node automatically.

do we need to config any parameters in the cluster .cnf file.

Please explain me the process.

Thanks everybody for your great full ideas in advance.

Regards

Sairamk

[hunter86bg]

I'm using HA Proxy for load balancer and it does the job for me.

frontend DBWRITE
            bind ip:port
            default_backend GALERA_WRITE

    backend GALERA_WRITE
            mode tcp
            option tcpka
            option httpchk
            default-server port 9200 inter 2s downinter 5s rise 3 fall 3 slowstart 2m 
            server galera1 galera1:3306 check
            server galera2 galera2:3306 check backup
           server galera3 galera3:3306 check backup
           server galera4 galera4:3306 check backup

frontend DBREAD
            bind ip:port+1
            default_backend GALERA_READ

    backend GALERA_READ
            mode tcp
            balance leastconn
            option tcpka
            default-server port 9200 inter 2s downinter 5s rise 3 fall 3 slowstart 2m 
            server galera2 galera2:3306 check 
            server galera3 galera3:3306 check
            server galera4 galera4:3306 check

Ofcourse , you will need some other options. Check in google for a sample haproxy config. Use high client and server timeouts ("1h" for example).

[Sairam Nuthalapati]

I do have same question about the draining connections? ( few writes on Node1, few writes on Node2, reads on Node3).. Can we implement in Galera Cluster without HA Proxy?

--
You received this message because you are subscribed to the Google Groups "codership" group.
To unsubscribe from this group and stop receiving emails from it, send an email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[hunter86bg]

You can access the Galera cluster without load balancer , but this way you risk to overload certain servers , while others will be less loaded.
In my case, the application knows that it's going to write or read.

Please bear in mind that there are several load balancing solutions for the galera cluster.One is the HA Proxy, another one is codership's (creators of galera) solution. For details of the second one you can check : http://www.fromdual.com/galera-load-balancer-documentation .

[Sairam Nuthalapati]

Does this mean that GA cluster has no load balancing. We need to take care of that by using other solutions like HA Proxy..etc.

[claudi...@gmail.com]

Hi,

On Thursday, February 25, 2016 at 7:00:19 AM UTC+1, Sai Ram K wrote:

Hi all,

I have few queries on Load balancing on Galera cluster.

1. How to distribute the load (writes) to one node and Reads to other nodes.

How to make one node as Master and remaining all are secondary nodes.

2. How automatic failover can be performed on the Galera cluster.

As i have 3 nodes - node1 ,node2, node3.

If node1 is the bootstrapped node than if it goes down than how to switches to another node automatically.

do we need to config any parameters in the cluster .cnf file.

Please explain me the process.

You can consider using MaxScale, it has various modules including R/W Split for Galera automatic failover, etc and it is open source.

MaxScale 'talks' MySQL protocol so it's much more powerful than a plain TCP/IP load balancer.

https://github.com/mariadb-corporation/MaxScale

Some reference to start:

https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-readwrite-splitting-with-galera-cluster/

https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-galera-monitor/

https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-configuration-usage-scenarios/

Regards,

--

Claudio

[hunter86bg]

Dear Sai,

Please check the following video about how Galera Cluster works : Galera Replication Demistified

You will notice that there is no load balancing as every node could be an entry point for clients. It's the application's responsibility to decide where to write and when. If you have writing in a lot of tables , you can use writing to multiple nodes at the same time. But, if you need to write all the time in certain tables - then only 1 master (writing node) is recommended.
Thus, Codership has developed their load balancer. You can also use other solutions.

In conclusion, you can't send all requests to Galera Node 1 and to expect this node to offload itself by sending part of the job to the rest - as it's impossible. Imagine this case : Galera 1 is for writing, Galera 2,3,4 replicate and respond to selects (reading). If you have no load balancer - you can send all reads to Galera Node 2 while the other  will be less loaded. Once Galera Node 2 get's overloaded , it will  replicate  slower and Flow Control will kick-in (send pauses to the rest of the cluster) and the whole cluster will stop serving clients for a short amount of time (some miliseconds). At the end you will have 1 overloaded Node and 3 not so loaded. It's your job to decide how to offload that Galera2 node (using load balancer of any type- tcp/mysql) , so the load is shared equally.

Best Regards,
Strahil Nikolov

[James Wang]

Hi Claudio,

Have you ever used MaxScale for production please?

People asked about MaxScale HA and I have not heard of satisfactory answers yet.

Thanks

[Claudio Nanni]

James,

2016-02-29 15:02 GMT+01:00 James Wang <jwang...@gmail.com>:

Hi Claudio,

Have you ever used MaxScale for production please?

People asked about MaxScale HA and I have not heard of satisfactory answers yet.

 

 

MaxScale is used in production, but different production environments can have different requirements, it's getting quite mature in my opinion, but again testing is the best thing you can do to have an idea of the impact and stability on your systems.

Performance wise, it's still a intermediator, I didn't run tests recently, in my past testing there was a noticeable overhead which was improving greatly release by release.

As for stability, latest 1.3.0 has a long list of bug fixes, but again, testing is the best way to go, imo.

If you just need plain R/W split you may seek a simpler solution, in the driver maybe as suggested, if you want a much more powerful solution MaxScale is currently the only software I know that can parse and interpret MySQL protocol and take action consequently, it has a modular design so that it can be expanded.

Regards

--

Claudio