Round robin connects to mysql

Question

Hi all,

I want to load balance queries to 2 mysql servers, in my application.

What would be the best way to do that, so that each query goes to a different db server?

I am thinking of having a global counter which is incremented each time a connection is made, mod the counter with my db servers in order to get the host I should connect. The above process however needs to be atomic, so that no 2 requests see the same counter.

How should I go about it? Use a semaphore lock, mysql's get_lock()?

pseudo code

counter = 0
hosts = array('192.168.1.1:3306', '192.168.1.2:3306')

//the below code needs to be atomic
GET A GLOBAL LOCK
counter = counter+1
RELEASE THE LOCK

host = hosts[counter % len(hosts)]

Thanks

Answer 1

The best way?

Use MySQL Proxy... It'll automatically compensate for down servers. Plus you can write rules that state where each query goes (either round robin choice, or to a specific server for better cache affinity). Plus, it can automatically direct all writes to the master server...

I wouldn't try implementing it in your application. The reason is that it's a lot harder to deal with global locks across multiple instances, and it's harder to have fault detection that doesn't result in a large performance hit...

Answer 2

why not just go with a rand approach?

$this->read_host = $this->prod_slaves_array[rand(0,(count($this->prod_slaves_array)-1))];

advantages are that it is lockless, and though it is not completely 50/50, does it really need to be?