PHP Check slave status without mysql_connect timeout issues
- by Jonathon
I have a web-app that has a master mysql db and four slave dbs. I want to handle all (or almost all) read-only (SELECT) queries from the slaves. Our load-balancer sends the user to one of the slave machines automatically, since they are also running Apache/PHP and serving webpages. I am using an include file to setup the connection to the databases, such as:
//for master server (i.e. - UPDATE/INSERT/DELETE statements)
$Host = "10.0.0.x";
$User = "xx";
$Password = "xx";
$Link = mysql_connect( $Host, $User, $Password );
if( !$Link ) )
{
die( "Master database is currently unavailable. Please try again later." );
}
//this connection can be used for READ-ONLY (i.e. - SELECT statements) on the localhost
$Host_Local = "localhost";
$User_Local = "xx";
$Password_Local = "xx";
$Link_Local = mysql_connect( $Host_Local, $User_Local, $Password_Local );
//fail back to master if slave db is down
if( !$Link_Local ) )
{
$Link_Local = mysql_connect( $Host, $User, $Password );
}
I then use $Link for all update queries and $Link_Local as the connection for SELECT statements. Everything works fine until the slave server database goes down. If the local db is down, the $Link_Local = mysql_connect() call takes at least 30 seconds before it gives up on trying to connect to the localhost and returns back to the script. This causes a huge backlog of page serves and basically shuts down the system (due to the extremely slow response time).
Does anyone know of a better way to handle connections to slave servers via PHP? Or, is there some kind of timeout function that could be used to stop the mysql_connect call after 2-3 seconds?
Thanks for the help. I searched the other mysql_connect threads, but didn't see any that addressed this issue.
