-----Original Message-----
From: Antonis Kopsaftis [mailto:ak...@edu.teiath.gr]
Sent: Wednesday, July 18, 2012 9:09 AM
To: Carl Kabbe
Cc: mysql@lists.mysql.com
Subject: Re: Looking for consultant
Hello,
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-
master
replication:
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
performance.
It's use it's own storage engine(NDB) which has a number of
limitations.
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:
http://datacharmer.blogspot.com
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.
Regards,
akops
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
availability'.
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
everything
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
