Tag Archives: mysqlslap

Benchmarking MySQL Cluster with mysqlslap

18 Dec

Differences with pgbench

  • There is no option in mysqlslap to specify the time in seconds to run the test for
  • There is no built-in TPC-B test
  • Does not show Transactions per second automatically, but can be calculated manually.
  • No custom variables or function support (setrandom etc).

Installation

  • mysqlslap is part o the MySQL Cluster distribution. See the post on installing MySQL Cluster.

Test Connectivity to MySQL Cluster

  • mysqlslap -h ec2-xx-xx-xx-x.compute-1.amazonaws.com -u testuser –password=testpwd –auto-generate-sql

Add verbose option to see behind-the-scenes-action

  • mysqlslap -vvv -h ec2-xx-xx-xx-x.compute-1.amazonaws.com -u testuser –password=testpwd –auto-generate-sql
    • Note that a schema called “mysqlslap” is created at the beginning of the test and deleted at the end of the test

Concurrent User Test

  • 10 concurrent users, repeat the test 5 times (iterations)
    • mysqlslap -h ec2-xx-xx-xx-x.compute-1.amazonaws.com -u testuser –password=testpwd –auto-generate-sql –concurrency=10 –iterations=5

Custom Queries with Schema Generate on the fly

Create Schema with Tables/Data and specify SELECT statement – Single User

mysqlslap -vvv -h ec2-xx-xx-xx-x.compute-1.amazonaws.com -u testuser --password=testpwd --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a"

Create Schema with Tables/Data and specify SELECT statement – 10 concurrent users, repeated 5 times

 mysqlslap -vvv -h ec2-xx-xx-xx-x.compute-1.amazonaws.com -u testuser --password=testpwd --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=10 --iterations=5

Reading SQL from files

  • create.sql file
    CREATE TABLE a (b int);INSERT INTO a VALUES (21)

     

  • query.sql file
    SELECT * FROM a

     

  • mysqlslap command
     mysqlslap -vvv -h ec2-xx-xx-xx-x.compute-1.amazonaws.com -u testuser --password=testpwd --delimiter=";" --create=create.sql --query=query.sql --concurrency=10 --iterations=5

     

Simulating the TPC-B Test

Initialize TPC-B Test Database