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" <[EMAIL PROTECTED]> 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
