Galera Cluster for MySQL with Amazon Virtual Private Cloud [4]

Previous: Create a VPC with Public and Private subnetsGalera Configurator and deploying Galera ClusterSetup 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);

($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
Verify that the page loads by
$ wget -q0- http://10.0.0.28/sbtest.php
$ wget -q0- http://10.0.0.38/sbtest.php

or browse to the EIP of the web instances.

and finally test load the page through the external load balancer.

$ 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.

Tags: 

Add new comment

Plain text

  • No HTML tags allowed.
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.