<?
$connection = new mysqli(<server>,<user>,<password>);
$ttl = 0.060;
/* sql related functions */
function fetchRows($query)
{
global $connection;
$rs = $connection->query($query);
$rows = array();
while ($row=$rs->fetch_assoc()) {
$rows[] = $row;
}
$rs->free();
return $rows;
}
function execSQL($query)
{
global $connection;
if ( !$connection->multi_query($query) )
throw new Exception("mysql error - " . mysqli_error($connection));
$res = array();
do
{
$res[] = array("affected_rows"=>$connection->affected_rows,"rows"=>array());
$extraResult = $connection->use_result();
if($extraResult instanceof mysqli_result){
while ($row=$extraResult->fetch_assoc()) {
$res[count($res)-1]["rows"][] = $row;
}
$extraResult->free();
}
}
while($connection->more_results() && $connection->next_result());
return $res;
}
/* galera related functions */
function getWSREPStatus()
{
$statuses = array();
$rows = fetchRows("SHOW STATUS LIKE 'wsrep_%'");
foreach ( $rows as $r )
$statuses[$r["Variable_name"]] = $r["Value"];
return $statuses;
}
/* test related functions */
// create test database and table
function setupTestEnv()
{
execSQL("CREATE DATABASE IF NOT EXISTS test_cluster");
execSQL("USE test_cluster");
execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE `test` (
`users_xsite_id` int(10) unsigned NOT NULL,
`xsite_templates_prototypes_id` int(10) unsigned NOT NULL,
`html` longtext,
`type` enum('smartphone','facebook_fangate','facebook','desktop') NOT NULL,
PRIMARY KEY (users_xsite_id,xsite_templates_prototypes_id,type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED");
}
// perform test
function performTest($size)
{
global $connection;
global $ttl;
// step 1: reset
execSQL("USE test_cluster");
execSQL("DELETE FROM test WHERE 1;");
execSQL("START TRANSACTION;");
execSQL("INSERT INTO test(users_xsite_id,xsite_templates_prototypes_id,type,html) VALUES(241383,5,'desktop','');");
execSQL("COMMIT;");
execSQL("START TRANSACTION;");
execSQL("UPDATE test SET html=REPEAT('a',$size) WHERE users_xsite_id=241383 and xsite_templates_prototypes_id=5 and type='desktop';");
execSQL("COMMIT;");
// step 2: perform N iterations of test, capture results
$times = array();
$over2TTLCount = 0;
for ( $i = 0 ; $i < 50 ; $i++ )
{
// time capture
$d = microtime(true);
// execute query
$res = execSQL("
START TRANSACTION;
UPDATE test SET html=REPEAT('" . $i%10 . "',$size) WHERE users_xsite_id=241383 and xsite_templates_prototypes_id=5 and type='desktop';
COMMIT;
");
// capture time difference
$time = ( microtime(true) - $d );
// validate query
if ( $res[1]["affected_rows"] != 1 )
throw new Exception("no rows affected!");
// populate result variables
if ( $time > 2*$ttl)
$over2TTLCount++;
$times[] = $time;
}
// validate result of queries
if ( fetchRows("SELECT COUNT(*) AS cnt FROM test")[0]["cnt"] != 1 )
throw new Exception("too many rows");
// print out result
echo $size . " .times. " . implode(" ",$times) . " .over_2ttl_count. $over2TTLCount\n";// . " .bytes_sent. " . implode(" ",$bytesSent) . " .bytes_received. " . implode(" ",$bytesReceived) . "\n";
}
// print out general information
echo "cluster size: " . getWSREPStatus()["wsrep_cluster_size"] . "\n";
// setup test environment
setupTestEnv();
// perform tests with growing data size
for ( $i = 32 ; $i < 128 ; $i++ )
performTest(1024*$i);
?>