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
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.
Add new comment