Tutorial: Stress test MySQL or any other JDBC database with Yahoo Cloud Serving Benchmark (YCSB)

Yahoo Cloud Serving Benchmark (YCSB) is an Open Source tool developed by Yahoo to stress test database systems. Both SQL and NoSQL databases are supported, which makes it a good tool to stress test databases like MySQL, Oracle, MongoDb, Cassandra, etc.

I struggled with the setup phase as the software is not actively maintained and the documentation is generic. However it is possible to benchmark a MySQL database via JDBC with the following steps:

Install Yahoo Cloud Serving Benchmark (YCSB)

Download the archive from https://github.com/downloads/brianfrankcooper/YCSB/ycsb-0.1.4.tar.gz. Once downloaded just uncompress it and check if the Java and Python environment is setup. YCSB is built in Java and requires a working installation of Java with the JAVA_HOME environment variable set. The Python interpreter is also required to run the script.

Create the MySQL database table

On a running MySQL server create a new database, for example ycsb, and the "usertable" table with the sql script found at
https://github.com/brianfrankcooper/YCSB/blob/master/jdbc/src/main/resources/sql/create_table.mysql.

Install the JDBC driver

The JDBC driver is a vendor specific driver provided by the database vendor. In case of MySQL it can be downloaded at http://dev.mysql.com/downloads/connector/j. Once downloaded the mysql-connector-java-xyz.jar file must be copied in the jdbc-binding/lib/ folder of the ycsb installation.

Load some data

Now it is play time! Move to the ycsb installation folder and run the command ./bin/ycsb jdbc from console to get a list of possible commands.
To run the data in the database use the following command:

./bin/ycsb load jdbc -P workloads/workloada -p db.driver=com.mysql.jdbc.Driver -p db.url=jdbc:mysql://localhost/DATABASENAME -p db.user=DATABASEUSER -p db.passwd=DATABASEPASSWORD -s -threads NROFTHREADS > load_results.dat

The -P parameter selects the workload strategy, for example workloads/workloada perform tests with 50% reads and 50% writes, other workloads strategy are available in the workloads/ folder.
The -threads parameter sets the number of threads, by default 1, a good idea is to set it to match the number of CPU cores
The result will be saved in load_results.dat

Run the stress test

Once loaded data can be tested, once again run this command, which is similar to the previous one:

./bin/ycsb run jdbc -P workloads/workloada -p db.driver=com.mysql.jdbc.Driver -p db.url=jdbc:mysql://localhost/DATABASENAME -p db.user=DATABASEUSER -p db.passwd=DATABASEPASSWORD -s -threads NROFTHREADS > run_results.dat

Results are saved in run_results.dat.

Use other SQL or NoSQL databases

Yahoo Cloud Serving Benchmark (YCSB) is written in Java and supports any JDBC compliant database. Several additional NoSQL databases are also provided out of the box and additional connector can be implemented.

To connect YCSB with another JDBC database:

  • Download the vendor specific driver and copy in the jdbc-binding/lib folder
  • Create the usertable table as seen before
  • Run ./bin/ycsb with the appropriate db.driver, db.url, db.user, db.passwd parameters.
tags: