MySQL ODBC Connector - load balance for mysql servers

Author

load balance for mysql servers

lee_mezimedia

2005-12-28, 8:24 pm

------ =_NextPart_000_005D_
01C60C61.93C4B120
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello,

We have four Mysql database servers: S1, S2, S3, S4 and we use Mysql
replications,

S1 is the master server

S2, S3, S4 are the slave servers.

Our website use S2, S3, S4 as production databases;

But we have no good idea to load balance between three severs.

We use php scripts to chose a server by random, but sometimes we found

S2 was very very busy but S3 was a little vacancy.

Is there any good idea to load balance for our mysql servers?

Thanks a lot!

Lee

------ =_NextPart_000_005D_
01C60C61.93C4B120--

Atle Veka

2005-12-29, 1:23 pm

Less optimal solution:
Create a DNS round-robin alias, say
mydatabase.myinternaldomain.com, that point to S2-4.

"Optimal" solution:
Set up a real load balancer, hardware based or software based
(http://www.linuxvirtualserver.org/), and have it configured to distribute
your connections. I have quoted optimal as your success depends on how you
configure your distribution, but it should still work better than the
previous solution.

Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Thu, 29 Dec 2005, lee_mezimedia wrote:

> Hello,
>
> We have four Mysql database servers: S1, S2, S3, S4 and we use Mysql
> replications,
>
> S1 is the master server
>
> S2, S3, S4 are the slave servers.
>
> Our website use S2, S3, S4 as production databases;
>
> But we have no good idea to load balance between three severs.
>
> We use php scripts to chose a server by random, but sometimes we found
>
> S2 was very very busy but S3 was a little vacancy.
>
> Is there any good idea to load balance for our mysql servers?
>
>
>
> Thanks a lot!
>
>
>
> Lee
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2005-12-29, 8:23 pm

Hello.

>Is there any good idea to load balance for our mysql servers?

I'm not sure, but have a look at "High Performance MySQL" by
Jeremy D. Zawodny. This is a snip from it's publically available
chapter.

####################
####################
####################
###

Real-time data transmission
MySQL's replication isn't the ideal vehicle for transmitting real-time
or nearly real-
time data such as a stock quote feed or an online auction site. In those
applications,
it's important that the user sees up-to-date data no matter which
database server
they use.
The only way to combat MySQL's lack of any latency guarantee is to
implement
your own monitoring system. It needs to use some sort of heartbeat to
verify that
each server has a reasonably up-to-date copy of the data. In the event
that a server
falls too far behind, the monitoring system needs to proactively remove
it from the
list of active servers until it can catch up.
Of course, you can also build your application in such a way that it
updates all the
slaves with the newest data. However, that can add a lot of complexity
and may not
be worth the effort. You'd end up writing a lot of code to handle the
exceptional
conditions, such as when a single server falls behind or is
intermittently inaccessible.
Testing and debugging all those situations can be very time-consuming
and difficult.
As Derek went over this, he thought, "Wouldn't it be cool if MySQL could
provide a
query response that signified, `Go ask another server, I'm really busy
right now'?"
This would allow clients to automatically find willing servers in a
multihost DNS
rotation.
For example, the client wants to connect to db.example.com (which is
db1, db2, and
db3). It connects (randomly) to db2, and the server answers the query
with "I'm
busy; go ask someone else," whereupon the client knows enough to try db1
or db3.
Because the client library would be connecting to the same virtual
server, it could
transparently disconnect from the busy server and connect to some other
(hopefully
less busy) server.
As a result, all you would need is some automated way for a slave server
to know
how far behind they are and to shut themselves off from queries when
they get too
far behind, and you'd have some protection. Of course, this could also
be subject to
a cascading failure. If all the slaves are very busy, the last thing
you'd want is for
them to start removing themselves from the pool of available servers.
Continue on to
Chapter 8 for a deeper discussion of these issues.

"lee_mezimedia" <lee@mezimedia.com> wrote:
>Hello,
>
>We have four Mysql database servers: S1, S2, S3, S4 and we use Mysql
>replications,
>
>S1 is the master server
>
>S2, S3, S4 are the slave servers.
>
>Our website use S2, S3, S4 as production databases;
>
>But we have no good idea to load balance between three severs.
>
>We use php scripts to chose a server by random, but sometimes we found
>
>S2 was very very busy but S3 was a little vacancy.
>
>Is there any good idea to load balance for our mysql servers?

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links