Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking to create a load-balanced MySQL cluster, but without the actual load-balancer, in order not to add another point of failure or complexity.

What I was thinking was to have the following:

  1. Have a master-master setup for MySQL

  2. On every client, place a simple round-robin proxy which would rotate the requests between servers.

Is this possible? Or there are better ways to achieve this?

share|improve this question

migrated from stackoverflow.com Jan 27 '12 at 16:12

This question came from our site for professional and enthusiast programmers.

    
I am curious, what are You going to use it for? –  Reef May 22 '09 at 17:13
    
I trying to add HA to our solution, without involving load-balancers and similar stuff. –  SyRenity May 23 '09 at 16:55

8 Answers 8

Master-master replication is not as good as You might think, same goes to the round-robin proxy and similar 'easy' solutions. If You commit colliding data to separate servers fast enough (faster than the delay between the servers, which on production servers might be up to a full second*), both will accept the data. If You have an auction server, You just sold the same car twice. Who bought it? It depends on which DB will You ask!

The application must be aware that there are actually 2 databases out there and it has to know both of their ip addresses. If You want to "sell", You should f.e.

DB_number = `auction_number` % `number_of_databases`

( % is for modulo )

... and commit it to DB_number database. If You get a connection error, then perhaps do it with the other one (but in case of an auction server, I'd just display an error).

Also, the IP addresses should be wackamole-d between both servers. On a disaster scenario, where one database server goes down for a couple of hours in peak usage time, You will find that the application will try to connect to the absent server and hang until TIMEOUT, say, 3s. Suddenly half of Your queries run 3s longer (and they all go to the same database eventually - which doesn't make it run faster than before the disaster). This doesn't make Your httpd happy, as it probably has a limited connection pool of concurrent request handler threads...

* replication delay on production servers might be up to a full second - I have tested this in a remote colocation and in our datacenter and for like 99% of the time it's 0, but sometimes mysql shows 1s. On massive traffic I had many collisions due to client application making two requests resulting in two queries, insert and select. For some cases, the row just wasn't there yet, so We used hash of the userID and it fixed the problem

I hope You will learn from my mistakes ;-)

share|improve this answer
    
Hi. Thanks for sharing. I thought about Wackamole, which is actually good for HA. My issue with it that all the load would be on one of the master servers, when the second would be idle, basically creating active/pasive, while I'm looking for active/active. Perhaps it's better to place some light LB solution on each client, in order to allow it to switch requests between the servers? Any idea if such tool exists? –  SyRenity May 21 '09 at 20:32
    
If You need redundancy, then "one working, one idle" is good. Let's say one of the 2 servers dies (I remind You, that You bought the other one so if the first one breaks You can still function). If the second server can't handle all the traffic, then it's for scale, not for HA! Also: relying only on Wackamole is a bad solution (ping ok != mysqld ok). –  Reef May 21 '09 at 22:09
up vote 3 down vote accepted

Please read my other answer to this question before actually using a MySQL proxy of any kind. If You have 2 master-master servers that a CMS is writing to, and 10 httpd that only read from it, You'll be fine, but (as pointed out in the other answer) that's not always the case. You've been warned.

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.

.

HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications

If You would run it in TCP mode, it could be even better than Wackamole. If I had to choose between them, I would use HAProxy. Also HAProxy can have a lot of backends, Waclamole can have only 2. Note that HAProxy is "dumb", it connects sockets without any looking on what's inside the stream - dedicated MySQL Proxy might f.e. have an option to point various requests to specified servers.

share|improve this answer
    
Just to verify: 1) HAProxy would require additional machine / 2 machines for HA 2) Wackamole can only support 2 servers per setup? Regards. –  SyRenity May 22 '09 at 6:40
    
Wackamole's standard usage pattern (in fact the only one that I know) is to have serverA and serverB watch each other and take the other one's IP if it dies. Wackamole website says that it can be used to guard a pool of IPs... But I must say that Wackamole does not give stability as one would like to, so I don't recomment that. About HAProxy, You would put 2 of them on 2 dedicated machines for redundancy, or You could even put one on each node, as You said in the question. If Your queries are mostly reading, then I think it'll work pretty well. –  Reef May 22 '09 at 17:06
    
Hi Reef. Just a last bit about Wackamole - from your experience, it's not stable enough on two machines? –  SyRenity May 23 '09 at 9:05
    
2 machines ping eachother ok, but one of them has load 200, all cpu's on 100% usage, all ram used. MySQL has crashed. <-- wackamole will NOT work there. HAProxy can check if the remote APPLICATION is up, Wackamole only if the server is up and application_uptime < server_uptime. We had many cases where We relied on wackamole and it let us down. –  Reef May 23 '09 at 9:26

A load-balanced MySQL (or some other) database cluster is pretty futile. If you're writing to more than one server, then you will run into trouble, or you use synchronous replication (which MySQL doesn't support anyway), and that hurts performance badly as it needs to synchronise locks.

I recommend you split read/write loads, and load balance the reads amongst mysql slaves, and have either a single master for writes, or use an active/passive failover pair for your master.

Essentially, you cannot scale writes by putting more servers in a database as slaves, as each still has to write the entire write-load of your application.

To scale writes you need to split your data logically across multiple servers, by partitioning or "sharding" etc. This typically requires nontrivial (think very difficult to test) changes to your application, so you don't want to do this unless you REALLY need it.


You can of course use MySQL cluster if you really want, but it's a completely different engine with its own features and drawbacks - it's a bit complicated to set up but does really provide a HA load-balanced database on commodity hardware. It still suffers from write performance penalties from using synchronous replication, but does allow you to scale writes as it has built in partitioning across servers.

share|improve this answer

Another great guide on this subject I've found...

http://www.dancryer.com/2010/01/mysql-circular-replication

This is part 1 of a three posts series:

  • MySQL Load-Balanced Cluster Guide – Part 1 - setting up the servers themselves and configuring MySQL replication.

  • MySQL Load-Balanced Cluster Guide – Part 2 - set up a script to monitor the status of your MySQL cluster nodes, which we’ll use in the next guide to set up our proxy.

  • MySQL Load-Balanced Cluster Guide – Part 3 - setting up the load balancer with HAProxy, using the monitoring scripts

share|improve this answer

Probably worth mentioning, Galera Replication for MySQL for a true multi-master MySQL setup. Galera is a synchronous replication protocol, so applications can read from and write to any of the MySQL Servers. Here is a quick tutorial: http://www.severalnines.com/clustercontrol-mysql-galera-tutorial

As for load balancers in front of the MySQL Servers, either use a MySQL connector that supports this functionality (e.g. Connector/J for Java, or Mysqlnd for php)

If you do not have a connector that can do this, then use something like an HA Proxy. This script automatically sets up HA Proxy and maintains the list of good MySQL Servers: https://github.com/severalnines/haproxy

Best regards,

Vinay

www.severalnines.com

share|improve this answer
    
It's important for you to disclose your association with the product you are recommending very clearly. Also, this site is not for self-promotion. If you have a product that would solve a problem posted, great! If all your answers revolve around your products, then you may want to talk to someone about getting ad space instead of posting answers. Please see our faq. –  JNK Jun 5 '12 at 15:59

Personally, the better way would be to use a load balancer!

Yes, it does add another point of failure, but any routine that you put in place, or install on EVERY client, adds a lot more complexity than a standard load balancer....

share|improve this answer
    
It makes sense, but the problem is the single point of failure - even with 2 LB's... In case one of clients goes down, only it impacted and no one else. –  SyRenity May 21 '09 at 20:34
    
It's hard to maintain LB on every node. If You install a LB on 12 servers and then You will like to change something (address of one of the DBs or add a DB or something) - You will notice the trouble. I did. –  Reef May 21 '09 at 22:21

Splitting writes won't take the load off of the servers because the writes still must be replicated.

If your using only 2 servers use heartbeat with drbd and let drbd handle the replication. If the first server fails the second server will take over. If you would like to put the second server to use, you can use gfs over drbd and then run the second server as read only and use it as a read server. When failover occurs then change the server to read / write.

re: wackamole - wackamole isn't limited to 2 servers

I'm working on a tutorial series covering this, but it's really simple to setup.

share|improve this answer
    
Yes, in theory, wackamole can support more than 2 servers, but have You ever tried this on production? We did. We now regret. –  Reef Jul 8 '09 at 10:28
    
So far I've had no issues, other than the fact I can't get it to compile under centos 5 64 bit –  Sid Jul 10 '09 at 15:18

Connector/J has the ability to loadbalance queries across several servers. This is primarily intended for MySQL NDB Cluster where all SQL nodes will have a consistent view of the data but if you can ensure that the two masters database will be reasonably consistent between these two masters it might be safe for your application.

The connect string would look something like:

jdbc:mysql:loadbalance://host-1,host-2,... host-n/dbname?loadBalanceStrategy="random"&loadBalanceBlacklistTimeout=5000

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.