[Sqlrelay-discussion] Fail over for SQL relay - release 0.45
Brought to you by:
mused
|
From: findjaimin <fin...@gm...> - 2012-05-30 09:44:21
|
Hi,
I am using SQL relay between my php app and Mysql database*s*. Following
is the configuration file.
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<!-- Regular SQL Relay Instance -->
<instance id="example" port="7000" socket="/home/Jaimin/relay.sock"
dbase="mysql" connections="10" maxconnections="25" maxqueuelength="5"
growby="5" ttl="1" maxsessioncount="10000" endofsession="commit"
sessiontimeout="600" runasuser="Jaimin" runasgroup="Jaimin" cursors="5"
maxcursors="10" cursors_growby="10" authtier="listener" handoff="pass"
deniedips="" allowedips="" debug="none" maxquerysize="65536"
maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0"
reloginatstart="no" timequeriessec="-1" timequeriesusec="-1"
fakeinputbindvariables="no" translatebindvariables="no"
isolationlevel="read committed" ignoreselectdatabase="no">
<users>
<user user="jaimin" password="password"/>
</users>
<connections>
<connection connectionid="db1"
string="user=jaimin;password=password;db=schema;host=172.29.5.169"
metric="1" behindloadbalancer="no"/>
<connection connectionid="db2"
string="user=jaimin;password=password;db=schema;host=172.29.5.169;port=3308"
metric="1" behindloadbalancer="no"/>
</connections>
</instance>
</instances>
My problem is : When mysql referenced by connectionid db2 goes down, every
second request sent by php gets error "Can't connect to MySQL server on
'172.29.5.169' (111)" . However as mentioned in this (
http://sqlrelay.sourceforge.net/sqlrelay/loadbalfailover.html ) link it
should just *mark* connection(s) of db2 as *unavailable to clients*. Means,
my php app will never get error..instead it will have smaller pool of
connections so may be some performance impact. I am using release # 0.45.
Am I missing something in achieving following behaviour as mentioned in
http://sqlrelay.sourceforge.net/sqlrelay/loadbalfailover.html ? :
"Currently, if an SQL Relay connection daemon notices that the database
server it is connected to has gone down it will mark itself unavailable to
clients, log out and loop, attempting to re-connect to that database
server. If that connection daemon is configured with the *behindloadbalancer
* attribute set to "no", then it will also raise a flag and all connection
daemons connected to that database server mark themselves unavailable to
clients, close their connections and loop, attempting to re-connect to that
database server. When the database server comes back up, as each connection
daemon successfully re-connects to the database server, it marks itself
available to clients again. While one database server is down, client
sessions are still distributed over the servers that are still up, albiet
through a smaller pool of persistent database connections."
Thanks,
Jaimin
|