1

I was speaking with my boss about Load-Balancing a 2 node Galera Cluster and we weren't sure if there was any reason.

For writes, his argument was, even if we balance the writes, it has to write to each server to do the replication.

For reads, we could balance the reads across the servers, but would this really save time if everything now goes thru a single VM on another server?

We have two dedicated SQL Servers which are in an Active-Active Galera setup.

The only way I can think of doing a HA-Proxy would be a 3rd VM on another server, is this really worth the performance gain to have everything go thru this one VM which will be on a server congested with other traffic?

Is it possible/Would it make sense to put HA-Proxy right on the SQL Server(s) and load balance the reads, but it would still go thru the Primary server w/ HA-Proxy to get to Server B.

Just looking for some general thoughts and advice for this simple setup.

2

There are several different topics you comment on your question, and with many "IF"s, depending on your specific workload and architecture. Let's start with the things you are right:

  • It is true that Galera by itself should not give you better write workload- it is a shared-nothing, non-sharded cluster, which means you will have to write everywhere. The only way to improve significantly your write performance is if you can share the write load between several nodes, while in your case you are duplicating them. In fact, as it is shared nothing architecture, the more servers you add, the slower the whole usually will be, as the slowest or further away server will be the bottleneck of your replication. If you need write performance, galera is not for you (it will usually have less throughput than a single server). You normally setup galera to get high availability(survivability of data and service if a node goes down), not better write performance.

There are some buts: * I had some clients that claimed better write performance, probably because horrible SQL queries + Galera requiring row-based replication, and in some special cases, you could get some gains with that (if you do 30-second writes but you only write a few records, you will get some extra scalability). That is normally very rare: you should fix first your queries, but I am just pointing to a (very) specific exception.

The meat of your question is that if the fact that you are adding a proxy in the middle will not be worth the improvement you get from load balancing queries. To answer that, you need to say 6 things:

  • What is your current average round-trip time between the client and the servers
  • What is your average round-trip time between the client and the proxy
  • What is your average round-trip time between the proxy and the servers
  • What is the overhead of the proxy processing time
  • What is the local latency of the actual mysql query time
  • How loaded is your average client?

This will tell you if you are interested on using a proxy or not for performance reasons.

You can find out the first 3 by using ping, the last 3 by profiling the actions. Normally, query time is much larger than round time within a datacenter, but that depends on which queries you are doing and how far located (physically) are those VMs. To cancel some of those times, people install the proxy on the same machine than the clients, so any overhead is mostly canceled. Also, HAproxy being mostly an IP proxy, the overhead is very low.

Now, if your servers are not very loaded, you may not get any advantage in latency- querying the server will double your throughput- if that has an impact on latency or not will depend on your current load.

There is usually a more important reason to use a proxy, which is high availability- using HAProxy will allow you to switch to a secondary galera node in case the active one goes down, automatically. It will also simplify manual switchovers. Of course, the proxy itself can be a single point of failure.

I hope that helps you decide- but the most important advice is try it yourself and measure!

Edit: BTW, with 2 nodes only, I hope you use galera as a replication solution, not as a cluster (requires special configuration) or with garbd, because if you don't, and a node goes down, the second will go down too to avoid a slit brain (no node has 50%+ quorum).

| improve this answer | |
  • You answer helped me think this through much better, for one, the performance gain is the new hardware, I don't think a VM on another server would help enough, or at all. Second, most important, is we are after high-availability more then performance. By using a VM I have a single point of failure, or need 2 VM's in HA. My plan is to use Pacemaker for a simple Floating IP but that means the SW for the floating IP resides on the actual servers vs remote location. Yes I plan to use GARBD and Pacemaker node for Voting in that quorum (The same VM) but that VM can go down without affecting cluster. – FreeSoftwareServers Dec 24 '16 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.