Re: mysql load balancing
Miguel, On Fri, Dec 25, 2009 at 4:56 PM, Miguel Angel Nieto cor...@miguelangelnieto.net wrote: 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. 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 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. -- 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=arch...@jab.org
Re: mysql load balancing
Miguel, On Sun, Dec 20, 2009 at 6:21 PM, Miguel Angel Nieto cor...@miguelangelnieto.net 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=arch...@jab.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. As 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 -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría 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=arch...@jab.org
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=arch...@jab.org
Re: mysql load balancing
El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto cor...@miguelangelnieto.net 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 http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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 jcre...@warp.es wrote: El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto cor...@miguelangelnieto.net 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 http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=pascal.char...@gmail.com -- 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=arch...@jab.org
Re: mysql load balancing
2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: 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 http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql load balancing
Hi, El día 22 de diciembre de 2009 10:14, Jaime Crespo Rincón jcre...@warp.es escribió: 2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: 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. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría 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=arch...@jab.org
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. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría 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=arch...@jab.org
Re: MySQL Load Balancing
Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. Yeah, known problem... We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. Why do you need a backup site to write things to your master ? Or did I got wrong ? I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. Let me see if I got it right: you have two sites, one master on each, one slave on each., and you want both master to replicate to the other and both slaves to receive data from them as well. Right ? If so, MySQL does not support multi-master setup. As far as I've heard there is no direct move into that direction from MySQL developers, although a voting system for a new master among one of the slaves can happen in the near future. What may work is to have only one master on one of your sites and both sites update the same master (reducing speed for the slave site) and in the case of failure you switch them (manually or with some tool). hope that helps, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Renato Golin wrote: Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. Yeah, known problem... We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. Why do you need a backup site to write things to your master ? Or did I got wrong ? This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. Let me see if I got it right: you have two sites, one master on each, one slave on each., and you want both master to replicate to the other and both slaves to receive data from them as well. Right ? If so, MySQL does not support multi-master setup. It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. As far as I've heard there is no direct move into that direction from MySQL developers, although a voting system for a new master among one of the slaves can happen in the near future. What may work is to have only one master on one of your sites and both sites update the same master (reducing speed for the slave site) and in the case of failure you switch them (manually or with some tool). hope that helps, --renato -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. Ok, If that's your problem, they might have something to you in the near future with the new master election. It'll probably work on a 2-way replication when the old master is restored. While this is not out yet, what you might do is what I did back with 3.23 which is: bring old restored master up as slave, wait for all changes replicate, shutdown temp master, set it as slave, bring old master as master and bring temp master as slave, restoring production scheme. It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. Load balance them using the local master or using both slaves as a load balance ? I'd rather go for the first one because the second one will raise more problems than solve, specially related to network delays. If, in the first case, one master fails you can issue a change master to the other master and it'll keep running. As you'll only have the slave there will be no load balancing on that site. --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. I don't think you're describing it properly. Do you mean that both masters are not master for the same database? In that case you could make them slaves of each other, but not for the same db. At least, not as I understand it. (And I have set up Mysql as active-active at 2 geographically diverse colos.) It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. We use a Netscaler 9000 in front of our slaves but you can use any load balancing appliance really. We used to use an Alteon. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Peter Zaitsev wrote: On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. As some people mentioned there is Continuent solution, this is what was Emic networks previously. If you're building solution on your own such as master and number of slaves there are plenty of things to think, regarding load balancing, for example if replication breaks for any reason of falls behind on one of the slaves you might want to kick it up. For very basic setup you even can use DNS for load balancing, which does not solve many of the problems describe. The same kind of simple load balancing is build in MySQL JDBC Driver. In general everyone seems to implement something on their own, working well for their application. Continuent's m/cluster will not work for me as it does not allow replication across a WAN. We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. I have not been able to find an appliance that will balance the reads for me. I have looked into possible solutions such as Sequoia, which I know would require a different setup. Is anyone actually using Sequoia? Does anyone use an appliance for load balancing MySQL? LVS seems like a good possibility but I would rather have an out-of-box solution since I will be repeating this setup at a remote location. -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. I have not been able to find an appliance that will balance the reads for me. I have looked into possible solutions such as Sequoia, which I know would require a different setup. Is anyone actually using Sequoia? Does anyone use an appliance for load balancing MySQL? LVS seems like a good possibility but I would rather have an out-of-box solution since I will be repeating this setup at a remote location. Sorry, did not try Sequoia so can't tell how well it works -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load Balancing
I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
You can have a simple LVS setup running with a plugin from Nagios, check_mysql, which will connect to the mysql daemon and run a status query. If you want anything more than that you most likely will have to write a custom check plugin (shouldn't be that hard). LVS works nicely as a mysql loadbalancer in my experience. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 4 Aug 2006, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed, in Jeremy Zawodny's (excellent) book High Performance MySQL, there is a chapter on load balancing - though it's a bit more of a theoretical discussion than a how-to. There are a couple of commercial products mentioned briefly - Veritas and EMIC Networks. One idea he presents might work for you, since you're already doing HTTP load balancing: have each HTTP server talk to a mysql instance on localhost, with the mysql instance configured as a replication slave. Depends on your situation but that could be elegantly simple - assuming you have a farm of servers, you just add mysql to your standard build image, rebuild them and start replicating. Roll out your app talking to localhost and you've got it. Of course rarely are things read-only; for something that writes as well as reads, you'll likely have to do some re-coding so that db writes go to the master and reads go to localhost. Then you have possible problems if you need reads to show the results of writes right away. It gets fun! Sound like this might work for you? Dan On 8/4/06, Ed Pauley II [EMAIL PROTECTED] wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
I should mention that the below concerns read-only daemons, Dan's post reminded me of that. Having multiple masters in a load balanced environment is extremely difficult to do right. I would wager that for most applications, at least internet related, you'll have a much higher read-to-write ratio where you can get by having only one master while using multiple replicated slaves (even for really high traffic sites). Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 4 Aug 2006, Atle Veka wrote: You can have a simple LVS setup running with a plugin from Nagios, check_mysql, which will connect to the mysql daemon and run a status query. If you want anything more than that you most likely will have to write a custom check plugin (shouldn't be that hard). LVS works nicely as a mysql loadbalancer in my experience. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 4 Aug 2006, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. As some people mentioned there is Continuent solution, this is what was Emic networks previously. If you're building solution on your own such as master and number of slaves there are plenty of things to think, regarding load balancing, for example if replication breaks for any reason of falls behind on one of the slaves you might want to kick it up. For very basic setup you even can use DNS for load balancing, which does not solve many of the problems describe. The same kind of simple load balancing is build in MySQL JDBC Driver. In general everyone seems to implement something on their own, working well for their application. -- Peter Zaitsev, MySQL Performance Expert MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Kevin A. Burton wrote: Jason J. W. Williams wrote: Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Define DoS? - Denial of Service... - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Ian Sales (DBA) wrote: Kevin A. Burton wrote: Define DoS? - Denial of Service... ug... Thats not what I meant... I mean what type of behavior were you noticing? Just all connections being occupied on the server? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load Balancing w/ Alteons...Half Open Connections
Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Best Regards, Jason Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Jason J. W. Williams wrote: Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Define DoS? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
We have fail-over using Linux Virtual Server, now upgrading to Red Hat Cluster Suite. We do not implement load-balancing. Here is why. In order to have full true load balancing, you need to have two or more MySQL database server replicating data to each other in real time. Currently your only choice is master-master replication. master-master replication is not fast enough for real-time updates for a load-balanced environment. It is good enough for fail-over which is what we currently implement. You can still have load-balancing in a distributed fashion. If you have two MySQL database servers for your cluster, and you designate half your databases for one server and half for the other, technically you are implementing load balancing as your distributing your data among two servers. Although this is not really the best practice. However in this setup, each server can be a fail-over for the other. So if one server fails the other takes over as master for it's databases. fail-over recovery. This is one issue worth considering. If your MySQL database server comes back online, LVS (Linux Virtual Server) wants to immediately throw it back in as master/primary. However, once the server is up, it needs time to get back up to date from the other master. So for recovery, you will need to perform this manually and not let LVS do this automatically. Bring the server back online, allow time for the replication to complete, then go to the LVS and set the server as active primary. -RG Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
We also use Linux Virtual Server for load balancing, but only on our read-only cluster. Our current levels of RW traffic do not demand more than one machine. Russell E Glaue wrote: We have fail-over using Linux Virtual Server, now upgrading to Red Hat Cluster Suite. We do not implement load-balancing. Here is why. In order to have full true load balancing, you need to have two or more MySQL database server replicating data to each other in real time. Currently your only choice is master-master replication. master-master replication is not fast enough for real-time updates for a load-balanced environment. It is good enough for fail-over which is what we currently implement. You can still have load-balancing in a distributed fashion. If you have two MySQL database servers for your cluster, and you designate half your databases for one server and half for the other, technically you are implementing load balancing as your distributing your data among two servers. Although this is not really the best practice. However in this setup, each server can be a fail-over for the other. So if one server fails the other takes over as master for it's databases. fail-over recovery. This is one issue worth considering. If your MySQL database server comes back online, LVS (Linux Virtual Server) wants to immediately throw it back in as master/primary. However, once the server is up, it needs time to get back up to date from the other master. So for recovery, you will need to perform this manually and not let LVS do this automatically. Bring the server back online, allow time for the replication to complete, then go to the LVS and set the server as active primary. -RG Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing we're using an Alteon 2424 load balancer. we have 5 mysql slave machines behind it. it works very well, except for the normal problems with mysql replication. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing...
Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing
Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. Good idea. You may also want to check how query caching helps your application (look at the query cache stats in SHOW STATUS with different cache sizes), see if some dynamic pages could be converted to periodically re-generated static, and check if you can pool the writes (eg. if you are logging page hits, append to a file instead of writing to db, and run load data infile once a minute) -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing
Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Any advice on this would be appreciated. Alex Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing
At 02:06 PM 2/25/2004, you wrote: Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Here are a couple of suggestions. You can have auto-inc fields unique between databases by creating a primary index from the AutoInc, and Db_Id columns. Each database would have its own Db_Id so you know which database created the row once it is merged back into the main database. This also works with InnoDb tables as long as the auto-inc field appears first in the key. So the key would be '1,A' for rcd_id=1 for database A, and '1,B' for rcc_id=1 on database B etc. Another alternative would be to split the application into several databases, one that is replicated for reading (DBR1, DBR2, DBR3 etc), a single database for updates (DBU1) and a master database (DBM). The webservers access the replicated databases (DBR1...DBR3), and updates are made to DBU1. Your web application just uses a different connection to access DBU1 instead of DBRn. The updated rows of DBU1 are then fetched and imported into DBM, the master database. This can be a manual process that is done every 5 minutes or so. When the rows are imported into DBM, the rows are removed from DBU1 so DBU1 has only new rows that haven't been imported yet. The changes made to DBM are then replicated back to DBR1 through DBR3. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
