We have a client-side load balancer, as well as a central load balancer
for mySQL traffic. I've done many different models at a variety of
companies, all which still use the solutions. Building on top of the NDB
protocol would take a very long time to produce-a HA solution native to
mySQL, when other technologies already exist-free to do this for you.
For example use heartbeat from linux-ha.org for failover.
Now for client-side load balancing, memcache from danga.com has some
interesting ideas. DBI::Multiplex also has some interesting ideas.
One of the easiest solutions is to load a database definitions file on a
shared drive. Have an external program add or remove hosts from each
cluster of the shared definitions file. Then use a random algorithm to
pick a host from a list that is defined apart a cluster to connect to.
array ('mycluster' => array ( array ('host' => myhost1,
'db'=>'mydb',...), array ('host' => myhostN,....));
If your algorithm is random enough and spread across enough servers, the
results coalesce into a even distribution among all the servers in a
list. Very basic, very easy to implement and works.
The monitor program, run out of cron-or something, can have logic of
what to keep in a pool or what to take out of a pool.
DVP
----
Dathan Vance Pattishall http://www.friendster.com
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 01, 2005 1:21 PM
> To: Kevin Burton
> Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
> Subject: Re: Design of a Client-side MySQL Java oad Balancer
>
> Kevin Burton <[EMAIL PROTECTED]> wrote on 06/01/2005 02:31:54 PM:
>
> > I'd love to get some feedback here:
> >
> > > MySQL currently falls down by not providing a solution to
> > > transparent MySQL load balancing. There are some hardware
> solutions
> > > but these are expensive
> and
> > > difficult to configure. Also none of them provide any information
> > > about the current state of your MySQL configuration. For example
> > > they can't
> handle
> > > transparent query failover if a MySQL box fails. They also can't
> > > disconnect and reconnect to another host if the load
> grows too high.
> > >
> > > To that end I think it makes a lot of sense to have a MySQL
> > > client-side load balancer.
> > >
> > > This area is difficult to implement. There are a log of
> design issues.
>
> > > Also the
> > > issues WRT distributed connection management start to make the
> > > problem difficult.
> > >
> > > The other day I had a bit of an epiphany on this topic.
> > >
> > ....
> >
> > http://peerfear.typepad.com/blog/2005/06/design_of_a_cli.html
> >
> > --
> >
> >
> > Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
> > See irc.freenode.net #rojo if you want to chat.
> >
> > Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
> >
> > Kevin A. Burton, Location - San Francisco, CA
> > AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG
> > fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
> >
> >
>
> Wouldn't it make better sense to build on the NDB protocol
> and keep the native messaging infrastructure than it would be
> to build a similar wrapper from scratch? I mean to use the
> NDB communications on top of regular MySQL servers to provide
> for failover, hotswaps, and client registration. Haven't they
> already solved some or most of your client registration and
> reconnection issues during the development of that tool?
> There is also the "Federated" database engine that allows for
> queries to be run across more than one server at a time (not
> only can a query include a table from another database on the
> same server but also from a table in a database hosted on a
> different server).
>
> I think that between the two of those projects you have
> 80-90% of the wrapper you will need to run a MySQL cluster in
> a HA environment. The biggest problem I see is to prevent
> data collisions between servers (users connected to different
> servers trying to update the same record at the same time on
> different servers). Part of the NDB code has to contain the
> necessary cross-server locking messaging. And Federated
> servers will also do the same (at least to some degree).
>
> If you can get a JDBC version working, that would be great
> but I think the best dev path would be to re-use what is
> already there as much as possible. I am not discouraging the
> effort as much as I am suggesting another body of work to
> consider (or incorporate) in your design.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
