views:

165

answers:

2

In a MySQL master-slave replication enviroment if I have 4 slave servers how can I execute load balanced select queries?

Should I write a PHP class to dealing with the 4 slaves or it is possible to address queries to MySQL's own load balancer solution? Is there any MySQL load balancing solution?

Can I use some other tool to distribute my queries? What is the typical set up in situations like this?

Thanks for all answers!

+1  A: 

Common practice would be to set up something like ldirectord in front of your mysql cluster to provide a virtual ip as a load balanced address for the cluster, this could then distribute your queries to the various mysql servers.

This would require ipvs routing which may/may not be overkill for your specific situation, it may be that php control of the load balancing would be appropriate for what you are trying to achieve.

Mike
A: 

Yes and no.

There are really two questions to be answered:

  1. Should this query go to a slave, or the master db?
    • This logic should probably be in PHP. There are often cases where you must have the absolute freshest data, which can't be had from a slave. For example, if a user alters a page, you need to build the updated page from the master db -- the user's changes may have not made it to the slave dbs yet. This is very application-specific, so 3rd party tools may not be able to do this for you.
  2. Which slave should this query go to?
    • This, you probably don't need to write yourself -- it's not really important to your application which specific slave handles a request. Existing tools should handle this well.
Frank Farmer