select db by load..

joey3002

I have 2 dbs, that are identical, not replicated yet.. but identical.

here is my connect:

$servers[0] = array('host' => '10.0.0.6', 'user' => 'username', 'pass' => 'password', 'db' => 'db1');
$servers[1] = array('host' => '10.0.0.4', 'user' => 'username', 'pass' => 'password', 'db' => 'db2');

what i am asking, is can i add something to connect to the server that has the lowest load? and if so, how can i do that?

thanks

bretticus

Well, I don't have the answer you're looking for, just an alternative suggestion. If it's possible to do so, you might try making one of database servers a replication slave to the other. Of course this means transactions such as INSERT & UPDATE can only be performed on the master (makes sense since they are usually less frequent than SELECT queries.) Next, you could keep track (session perhaps??) of the last server queried and thus, do round-robin connects (...one...than the other...and so on...). That way, each server is getting (more or less--depends on your tracking technique) 50% percent of the SELECT load.

Anybody got anything else?

joey3002

Originally posted by bretticus
Well, I don't have the answer you're looking for, just an alternative suggestion. If it's possible to do so, you might try making one of database servers a replication slave to the other. Of course this means transactions such as INSERT & UPDATE can only be performed on the master (makes sense since they are usually less frequent than SELECT queries.) Next, you could keep track (session perhaps??) of the last server queried and thus, do round-robin connects (...one...than the other...and so on...). That way, each server is getting (more or less--depends on your tracking technique) 50% percent of the SELECT load.

Anybody got anything else?

thanks, any clue to the round robin approach?

sysera

You may find replication useful. (As suggested before. Here's a link.)

http://dev.mysql.com/doc/mysql/en/replication-intro.html