|
Actually, I ran into the same problem (ie, load balancing and scaling mysql)
recently. The way that I solved this is I wrote a sql dispatch daemon, which
can execute a sql statement concurrently on several backend systems. Then, I
rewrote my app so that it seperated reads from writes. Basically, what my
program now does is track a set of open connections to mysql servers, and
how many requests are pending on each, and when a new request comes in, it
choses the least weighted connection, and processes all the reads along
that. Any writes which are required (I have a high read/write ratio) are
written to a file, which is then picked up by the dispatch daemon, and
executed in parallel across the server. The dispatch daemon also has the
advantages that because it records a global log of changes, I can bring a
new server into sync my snapshotting another server's DB files, and then
replaying the log from the time of the snapshot. If that kind of arrangement
is of any help to you, check out lsdproject.sourceforge.net where I've stuck
up the source for the dispatch deamon. I'll probably have the read only
connection balancer up there too in a few days, depending on interest. The
problem I found with using LVS for load balancing was the need to create a
new connection for each request (a slow operation) to get the new connection
load balanced. Its faster to have a pool of open connections and balance
between them in user land.
-Sean Ward
----- Original Message -----
From: Joseph Mack <mack.joseph@xxxxxxx>
To: Jean-Christophe Boggio <cat@xxxxxxxxxx>
Cc: <mack@xxxxxxxxxxx>; Jason Saunders <jsaunders@xxxxxxxxx>; Stephen Zander
<gibreel@xxxxxxxxx>; jake buchholz <jake@xxxxxxxxxx>;
<lvs-users@xxxxxxxxxxxxxxxxxxxxxx>
Sent: Tuesday, April 04, 2000 9:48 AM
Subject: Re: mysql through lvs?
> Jean-Christophe BOGGIO wrote:
>
> > For now, my idea is to separate read-only queries from write queries :
>
> how about sending all the writes to one realserver, the (rw) realserver
and
> have the other realservers (ro)? The mysql client will have to be
rewritten.
>
>
> >
> > * read queries could be dispatched by the Director to one of the mySQL
> > servers (I'm looking for the info for Oracle at this time but the
problem is
> > the same and I'll have a mySQL project of this kind in a week or two).
> > * update/insert/delete queries could be done as many times as there are
SQL
> > servers. This is at the application level. Don't know if stored
procedures
> > can access other SQL servers. The application will be heavy to maintain,
> > will forbid some mySQL functionnalities but it has a slight chance to
work.
> > Maybe the system will be slower than having a single mySQL server. I
guess
> > it depends on the ratio #reads / #writes : the highest the ratio, the
best
> > speed improvement.
>
> this will only work for read mostly.
>
> > Anyone tested this ?
>
> Jake is working on it.
>
> Joe
>
> --
> Joseph Mack PhD, Senior Systems Engineer, Lockheed Martin
> contractor to the National Environmental Supercomputer Center,
> mailto:mack.joseph@xxxxxxx ph# 919-541-0007, RTP, NC, USA
>
>
>
|
