CODEWITHSUNDEEP
phpmysqlpdodatabase-replicationmysql-proxy
imperium2335
imperium2335

Reputation: 24112

MySQL Proxy and Load Balancing

I just want to understand exactly how connections and queries behave with MySQL Proxy.

I have two back-end servers both running identical MySQL installations, set up in a master-master config which seems to be functioning perfectly. I.e. I can manipulate tables and data on one, and the changes are instantly reflected on the other, and vise versa.

I have installed MySQL Proxy on a separate machine with the back-end servers specified in the command line in my batch file.

I can access my database via Workbench using the address of the proxy.

My question is how and what is delegated to each server? It states in the documentation that the proxy splits up the clients using round-robin (I assume that just splits requests equally?). Does that mean the actually PDO connections that are made get shared out or is it the queries that are being sent to it?

If it's the later, does it break up sub-queries too? If it isn't, then do I need to re-code my more complex SQL statements to take advantage of the client/connection splitting?

A simple example of how I think a query would need to re-done would be:

$SQL = "SELECT id FROM my_table LIMIT 1000";

Would be (much more involved):

$results = array();
for($1=0;$i<1000;$i++){
    $conn = new $this->dbconnect;
    $r = $conn->prepare('SELECT id FROM my_table WHERE id = ?');
    $r->execute(array($i));
    array_push($results, $r->fetch);
    $conn->close();
}

Or is that just madness? Would the extra overhead be more than negated by the fact that the requests are being split between the servers? Or would it be much slower than doing it the "conventional" way?(Apologies for any code errors as that was off the top of my head).

I know LIMIT isn't 'id', it's just a simple example.

Upvotes: 1

Views: 226

Answers (0)

Related Questions