Testing and timing RDS/MySQL scheduled downtime with PHP

Something I was asked at work recently was “if we do this to the server, how long will it be unavailable for?” and usually the answer would be easy… however in certain circumstances you may not know. One of these is Amazon RDS, whereby you don’t really know what happens when you reboot or change the configuration of an instance, it just sort-of happens and eventually becomes available again.

For Amazon RDS an example would be when you modify the DB Instance Class of your main instance, which will cause the server to be “modified” which will cause some downtime; of course a reboot will cause downtime also.

Therefore I needed to time how long the website would go down for, at what point connections to the database would fail, or running a query would fail, etc.

I wrote a PHP CLI script to record how long it will take (in seconds) for the database to recover for any given operation that will cause downtime. Start the script, and it will keep outputting ‘+’ to the command line. Start the operation, and eventually the ‘+’ will turn into ‘-‘, and once they turn into ‘+’ again it means the downtime is over and the database(s) will be available again, and it will tell you how long it took.

The output will look something like this…

user@server:~$ php test_upgrade.php
Starting...
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + - - - - - - - - - - - - - - - -
- +

Database down for 262 seconds

The script is as follows…

<?php

error_reporting(0);

$host = 'myhost.us-east-1.rds.amazonaws.com';
$port = 3306;
$database = 'mydb';
$username = 'user';
$password = 'supersecretpassword';

$sql = "SELECT count(*) FROM users";

echo "Starting...\n";

$disconnectTime = 0;
$reconnectTime = 0;
$reconnected = null;

while ($reconnected !== true) {

	$link = mysql_connect($host.':'.$port, $username, $password);
	if (! $link) {
		flagConnection(false);
		$reconnected = false;
		continue;
	}

	$db_selected = mysql_select_db($database, $link);
	if (! $db_selected) {
    	flagConnection(false);
		$reconnected = false;
		continue;
	}

	$result = mysql_query($sql);
	if (! $result) {
		flagConnection(false);
		$reconnected = false;
		continue;
	}
	$row = mysql_fetch_row($result);
	if (! $row) {
		flagConnection(false);
		$reconnected = false;
		continue;
	}

	// If everything works, flag as connected
	flagConnection(true);

	// If was previously disconnected, exit the loop
	if ($reconnected === false) {
		$reconnected = true;
	}

	mysql_close($link);

}

$outageTime = $reconnectedTime - $disconnectedTime;

echo "\n\nDatabase down for $outageTime seconds\n";

function flagConnection($flag)
{
	global $disconnectedTime;
	global $reconnectedTime;
	global $reconnected;

	echo ($flag ? '+' : '-').' ';
	$log[] = $flag;

	if ($flag == false && is_null($reconnected)) {
		$disconnectedTime = time();
	}

	if ($flag == true && $reconnected === false) {
		$reconnectedTime = time();
	}

	sleep(1);

}

?>