Keep in mind that all "cluster" solutions are vulnerable to a single power failure, earthquake, flood, tornado, etc.
To "protect" from such, you need a hot backup located remotely from the "live" setup. This introduces latency that will kill performance -- all cluster solutions depend on syncing, heartbeats, etc, that cannot afford long latencies.
You may choose to ignore that issue. But, before going forward you need to make that decision.
> -----Original Message-----
> From: Antonis Kopsaftis [mailto:akops@stripped]
> Sent: Wednesday, July 18, 2012 9:09 AM
> To: Carl Kabbe
> Cc: mysql@strippedom
> Subject: Re: Looking for consultant
> As far i can understand by your post, you need a high availability
> mysql cluster with large capacity.
> For having high availability you need something that can give you
> multi-master replication between two or more mysql servers.
> In my knowledge there are three solutions that can give you multi-
> 1. "Official" mysql cluster
> It's an Enterprise class solution, very complicated, but 'it fully
> multi-master. I was using one for about two year, but i dont recommend
> it because (at least in my setup) it did not have very good
> It's use it's own storage engine(NDB) which has a number of
> 2. Tungsten replicator.
> It 's relative new product. It support multi-master replication between
> different type of databases, and it seems very promising. It's java
> based. I haven't tested it but you can read a lot about on:
> 3. Percona xtraDB cluster
> It's also a relative new product. It's also support multi-master
> replication, and it seems to have very good performance. The last 3
> weeks i have installed a 3 node cluster of percona software and i'm
> testing it. It seems to works ok, and after some optimization it has
> better performance than my production mysql setup(simple primary-slave
> replication) on same hardware (virtual machines). If i dont find any
> serious problem till September i will use it for production.
> Now,for you application to communicate with the two mysql master nodes
> there several solutions:
> 1. Desing your app to use both mysql servers. With this solution you
> can ever split writes in the one server, and reads in the other. It's
> up to you to do whatever you want.
> 2. Setup a simple heartbeat solution and setup a floating virtual ip
> between you mysql servers. If one of the mysql server( i mean the whole
> OS) crash, the floating ip will be attached to the second server.
> 3. In each app server, install a tcp load balancer software like
> "haproxy" and balance the mysql tcp connections between your app
> servers and the mysql servers.
> On 18/7/2012 6:11 μμ, Carl Kabbe wrote:
> > We are actually facing both capacity and availability issues at the
> same time.
> > Our current primary server is a Dell T410 (single processor, 32 GB
> memory) with a Dell T310 (single processor, 16GB memory) as backup.
> Normally, the backup server is running as a slave to the primary server
> and we manually switch it over when the primary server fails (which it
> did last Saturday morning at 2:00AM.) The switch over process takes
> 10-15 minutes although I am reducing that to about five minutes with
> some scripting (the changeover is a little more complex than you might
> think because we have a middle piece, also MySQL, that we use to
> determine where the real data is.) Until six months ago, the time
> delay was not a problem because the customer processes could tolerate
> such a delay. However, we now have a couple of water parks using our
> system at their gate, in their gift shops and in their concessions so
> we need to now move the changeover time to a short enough period that
> they really don't notice. Hence, the need I have described as 'high
> > The T410 is normally reasonably capable of processing our
> transactions, i.e., the customers are comfortable with the latency.
> However, we have been on the T310 since last Saturday and it is awful,
> basically barely able to keep up and producing unacceptable latency.
> Further, our load will double in the next six months and double again
> the the following six months.
> > So, my thought was that since we have to deal with the issue change
> > over time which will cause us to restructure the servers, that we
> > should also deal with the capacity issue. I think a couple of Dell
> > T620's will provide the capacity we need (the servers we have spec'ed
> > should be around 8X faster than the T410) but I have no experience
> > evaluating or setting up HA systems (I have worked with MySQL for 12
> > years and am reasonably comfortable with it and I have read
> > I can find about HA options and their implementations.) Hence, my
> > post asking for help (which we are willing to pay for.)
> > The web app is primarily JSP's for the administration side and Flash
> > for the operators and other people doing transactions. The server
> > side code is about 1.25 million lines of code and there are about 750
> > JSP's. The data is 950 tables with heavy use of foreign key
> > constraints. The container is Tomcat which runs on separate servers
> > (the data servers only run MySQL.)
> > Any ideas or help in any way are always welcome.
> > Thanks,
> > Carl
> > On Jul 18, 2012, at 9:42 AM, Shawn Green wrote:
> >> On 7/17/2012 8:22 PM, Carl Kabbe wrote:
> >>> On Monday, I asked if there were consultants out there who could
> help set up an NDB high availability system. As I compared our needs
> to NDB, it became obvious that NDB was not the answer and more obvious
> that simply adding high availability processes to our existing Innodb
> system was.
> >>> So, I am back asking if there are consultants lurking on this list
> that could help with this project.
> >> As has been discussed on this list many times before, there are many
> ways to measure 'high availability'. Most of them deal with what kind
> of disaster you want to survive or return to service from. If all you
> are looking for is additional production capacity then the terms you
> may want to investigate are 'scale out', 'partitioning', and
> 'replication'. All high-availability solutions require at least some
> level of hardware redundancy. Sometimes they require multiple layers in
> multiple locations.
> >> Several of those features of MySQL also help with meeting some high-
> availability goals.
> >> Are you willing to discuss your specific desired availability
> thresholds in public?
> >> --
> >> Shawn Green
> >> MySQL Principal Technical Support Engineer Oracle USA, Inc. -
> >> Hardware and Software, Engineered to Work Together.
> >> Office: Blountville, TN
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql