mysql load balancing

Hi,

I am searching fot a Mysql Load Balacing tool. I read about mysql
proxy, sqlrelay, haproxy...

What do you prefer?

What are the benefits and bugs?

:)

Thank you.

--=20
Lo que har=EDa ser=EDa hacerme pasar por sordomudo y as=ED no tendr=EDa que
hablar. Si quer=EDan decirme algo, tendr=EDan que escribirlo en un
papelito y enseñármelo. Al final se hartar=EDan y ya no tendr=EDa que
hablar el resto de mi vida.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql load balancing

2009/12/21 Miguel Angel Nieto :
> Hi,
>
> I am searching fot a Mysql Load Balacing tool. I read about mysql
> proxy, sqlrelay, haproxy...
>
> What do you prefer?

Hi,

The solutions I have heard most from our customers (in production) are
not mysql-specific:

1) Simple, not load-aware *DNS balancing* for simple applications
without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS
2) More complex and customizable *Linux Virtual Server*, with
integrated heartbeat and "session aware":
http://www.linuxvirtualserver.org/

It depends a lot on how you plan to coordinate the db servers
(sharding, replication, ndb), the kind of applications you are going
to deploy and how much scability you need.

I hope that helps. Regards,
--
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: mysql load balancing

Hi,

El d=EDa 22 de diciembre de 2009 10:14, Jaime Crespo Rinc=F3n
escribi=F3:
> 2009/12/21 Miguel Angel Nieto :
>> Hi,
>>
>> I am searching fot a Mysql Load Balacing tool. I read about mysql
>> proxy, sqlrelay, haproxy...
>>
>> What do you prefer?
>
> Hi,
>
> The solutions I have heard most from our customers (in production) are
> not mysql-specific:
>
> 1) Simple, not load-aware *DNS balancing* for simple applications
> without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS
> 2) More complex and customizable *Linux Virtual Server*, with
> integrated heartbeat and "session aware":
> http://www.linuxvirtualserver.org/
>
> It depends a lot on how you plan to coordinate the db servers
> (sharding, replication, ndb), the kind of applications you are going
> to deploy and how much scability you need.

Thank you. I have read about LVS and keepalived but I can't see the
difference between them. Are they the same thing? I want the load
balancing for my replicated servers. I suppose that LVS can't
distinguish between inserts and selects (to send queries to the master
o slave server).

See you.

--=20
Lo que har=EDa ser=EDa hacerme pasar por sordomudo y as=ED no tendr=EDa que
hablar. Si quer=EDan decirme algo, tendr=EDan que escribirlo en un
papelito y enseñármelo. Al final se hartar=EDan y ya no tendr=EDa que
hablar el resto de mi vida.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql load balancing

El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto
escribió:
>> It depends a lot on how you plan to coordinate the db servers
>> (sharding, replication, ndb), the kind of applications you are going
>> to deploy and how much scability you need.
>
> Thank you. I have read about LVS and keepalived but I can't see the
> difference between them. Are they the same thing? I want the load
> balancing for my replicated servers. I suppose that LVS can't
> distinguish between inserts and selects (to send queries to the master
> o slave server).

I do not know about keepalived, but it seems to provide the same
service that heartbeat does: user-level awareness of the failure of a
machine or a service. LVM, however, does kernel module, ip-level, load
balancing and automatic failure clustering. The big confusion with
those apps is that most of them are extensible and combinable to
achieve the same goal.

About the read/write balancing (which is a good question, and that is
why I asked what you were intending to do on the other end), I have
seen it done **only at application level**, not transparently, because
of the problems derived from asynchronous replication (lag between
master writes and slaves see the data). I am sorry I cannot help you,
but please, share here if you found something useful AND with good
performance.

Merry Xmas!
--=20
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql load balancing

Hi,

I've had quite a bit of success deploying mysql-proxy in my clients
infrastructure.

The standard read/write splitting is quite easy to achieve - but I
also add some custom code to match with specific case (connection
pooling, x second to the same master after a write/update, specific
command goes to this specific 'async' server...).

Since the 'rewrite' language is LUA its is quite easy to extend the feature=
..

I did complete some benchmarks of those systems for a client (overhead
cost) but I don't know if they are ready to share them (and I don't
have access to them since I'm mobile right now). Ill inquire.

P.

On 12/24/09, Jaime Crespo Rincón wrote:
> El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto
> escribió:
>>> It depends a lot on how you plan to coordinate the db servers
>>> (sharding, replication, ndb), the kind of applications you are going
>>> to deploy and how much scability you need.
>>
>> Thank you. I have read about LVS and keepalived but I can't see the
>> difference between them. Are they the same thing? I want the load
>> balancing for my replicated servers. I suppose that LVS can't
>> distinguish between inserts and selects (to send queries to the master
>> o slave server).
>
> I do not know about keepalived, but it seems to provide the same
> service that heartbeat does: user-level awareness of the failure of a
> machine or a service. LVM, however, does kernel module, ip-level, load
> balancing and automatic failure clustering. The big confusion with
> those apps is that most of them are extensible and combinable to
> achieve the same goal.
>
> About the read/write balancing (which is a good question, and that is
> why I asked what you were intending to do on the other end), I have
> seen it done **only at application level**, not transparently, because
> of the problems derived from asynchronous replication (lag between
> master writes and slaves see the data). I am sorry I cannot help you,
> but please, share here if you found something useful AND with good
> performance.
>
> Merry Xmas!
> --
> Jaime Crespo
> MySQL & Java Instructor
> Warp Networks
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dpascal.charest@gmail.co m
>
>

--=20
Sent from my mobile device

--
Pascal Charest, skype: pascal.charest
Free software consultant @ Laboratoire Phoenix
http://www.labsphoenix.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: mysql load balancing

Miguel,

On Sun, Dec 20, 2009 at 6:21 PM, Miguel Angel Nieto
wrote:
> Hi,
>
> I am searching fot a Mysql Load Balacing tool. I read about mysql
> proxy, sqlrelay, haproxy...

Load balancing, or high availability?

I do not think there is anything good and simple AND generic out of
the box. As previous posters have noted, you generally have to build
something on top of other tools.

- Baron

--
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: mysql load balancing

> Load balancing, or high availability?
>
> I do not think there is anything good and simple AND generic out of
> the box. =A0As previous posters have noted, you generally have to build
> something on top of other tools.

Hi,

I have the HA solved with MMM. Now, I want load balacing, sending read
queries to slaves and write queries to masters. I read about mysql
proxy, sqlrelay... but I didn't know the difference betwen them, and
the possible problems of each tool (latency for example). If anyone,
like Pascal, have experience with those kind of tools, please, share
them :P


--=20
Lo que har=EDa ser=EDa hacerme pasar por sordomudo y as=ED no tendr=EDa que
hablar. Si quer=EDan decirme algo, tendr=EDan que escribirlo en un
papelito y enseñármelo. Al final se hartar=EDan y ya no tendr=EDa que
hablar el resto de mi vida.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: mysql load balancing

Baron:

> Load balancing, or high availability?
> I do not think there is anything good and simple

We use MySQL master-master replication to keep
geographically separated databases in sync.
It works very well.

We built a management layer on top of it to allow
the endpoints (Web servers) to talk to either database
in case one fails. That is not necessary but it adds
a extra layer of proctection.

Neil

--
Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
Host your MySQL database on a CentOS virtual server for $25/mo
Unmetered bandwidth = no overage charges, 7 day free trial


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: mysql load balancing

Miguel,

On Fri, Dec 25, 2009 at 4:56 PM, Miguel Angel Nieto
wrote:
>> Load balancing, or high availability?
>>
>> I do not think there is anything good and simple AND generic out of
>> the box. =A0As previous posters have noted, you generally have to build
>> something on top of other tools.
>
> Hi,
>
> I have the HA solved with MMM. Now, I want load balacing, sending read
> queries to slaves and write queries to masters. I read about mysql
> proxy, sqlrelay... =A0but I didn't know the difference betwen them, and

I think the best way to do this is to split in your application. The
"magical" read/write split, done in a way that's invisible to the
application, is almost invariably a source of problems when there is
replication lag (which there always is). The application needs to be
aware of replication lag and must know how to handle it or when it's
OK to ignore it. Most applications cannot simply let a dumb
intermediate layer handle it for them, because there are always cases
when lag is not permissible at all, mixed with cases where lag is OK,
and the application needs to make the decision.

--=20
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg