Previous: Create a VPC with Public and Private subnets, Galera Configurator and deploying Galera Cluster, Setup Load Balancers and Web Servers

sysbench

sysbench is a popular system peformance benchmark tool and has a database OLTP benchmark module which is popular among MySQL users. We are going to use it to create the default OLTP schema that is used and run a light test.

The purpose is not to really bench our newly deployed Galera cluster but to get some test data that we can pull out from our web servers that we setup previously.

Prepare sysbench schema and table

Logon o to your ClusterControl instance

$ ssh -i <your aws pem> ubuntu@54.249.30.164

Install the MySQL client (if you don't have it)

$ sudo apt-get install mysql-client-core-5.5

Install sysbench

$ sudo apt-get install sysbench

Create the 'sbtest' database schema. Test that the internal MySQL load balancer user is working by going via the internal load balancer.

$ mysql -ulb -plb -h 10.0.1.17 -e "create schema sbtest"
$ mysql -ulb -plb -h 10.0.1.17 -e "show schemas'

Prepare the sysbench test table by inserting 1M records.

$ sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=lb --mysql-password=lb --mysql-host=10.0.1.17 --mysql-port=3306 prepare

Do a quick test run

$ sysbench --num-threads=2 --max-time=300 --max-requests=500000 --test=oltp --mysql-user=lb --mysql-password=lb --mysql-host=10.0.1.17 --mysql-port=3306 --oltp-test-mode=complex --oltp-table-size=1000000 run

Monitor the Galera cluster performance by going to http://<ClusterControl EIP>/cmon

Web/PHP Test

Check that the MySQL php driver is properly installed on your web instances.

$ echo "<? phpinfo(); ?> | sudo tee -a /var/www/phpinfo.php
$ wget -q0- http://10.0.0.38/phpinfo.php | grep -i mysql

or browse to the EIP of the web instance

Simple php select

On each web server instance add a sbtest.php file

$ sudo cat > "/var/www/sbtest.php" << EOF
<?
$user="lb";
$password="lb";
$database="sbtest";
$host="internal-int-lb-vpc-1396244671.ap-northeast-1.elb.amazonaws.com";

mysql_connect($host,$user,$password);

@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM sbtest limit 20";

$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();

echo "<table><th>sbtest</th><tbody>";

$i=0;
while ($i < $num) {
  $id=mysql_result($result,$i,"id");
  $k=mysql_result($result,$i,"k");
  $c=mysql_result($result,$i,"c");
  $pad=mysql_result($result,$i,"pad");

  echo "<tr>";
  echo "<td>$id</td><td>$k</td><td>$c</td><td>$pad</td>";
  echo "</tr>";
  $i++;
}
echo "</tbody></table>";
?>
EOF

$ wget -q0- http://10.0.0.28/sbtest.php
$ wget -q0- http://10.0.0.38/sbtest.php

$ wget -q0- http://54.249.29.89/sbtest.php

and/or point your web browser to http://ext-lb-153561883.ap-northeast-1.elb.amazonaws.com/sbtest.php

Now that everything is properly up and running you can try some other tools to further benchmark and tune your cluster from top to bottom, for example with jmeter, ab, siege and sysbench.