Guest Post: Philip Stoev. If you love it break it. Getting started with the RQG

I am glad to host in these pages a post by Philip Stoev, a remarkable QA engineer, creative, resourceful, and a notorious troublemaker. I met Philip by email in 2007, when I was exploring his Perl modules, which I used for one of my most rewarding articles. A few months later, when we met in person during the MySQL Developers Meeting, Philip was hired as a QA engineers, with my warmest recommendations. This post is about the Random Query Generator, which is also the topic of a MySQL University session. Thanks, Philip!

Want to test and break MySQL without having to cook up your own test framework?

Sun Microsystems have open-sourced the Random Query Generator, a new tool for generating tables with random data and then executing a random query workload against them. Both the data and the queries are fully configurable.

For example, assume that you want to evaluate how well partitioning will work for your workload:

Step1. Describe your tables.

Create a configuration file named partitioning.zz, instructing the tool to create a partitioned table containing a few varchar() fields. The format of the file is described in the Forge wiki. The configuration file will contain the following.

$tables = {
     rows => [65535],
  pk => [ 'integer not null auto_increment' ],
     partitions => [ 'PARTITION BY KEY (pk) PARTITIONS 2' ],
};

$fields = {
     types => [ 'varchar', 'char' ],
     indexes => [undef ]
};

$data = {
  strings => [ 'english' ]
}

This basically says "create a table with 64K rows, two char fields, 2 partitions, auto_increment primary key and fill it with English words."

Step2. Describe your workload.

The configuration file describing the workload takes the form of an SQL grammar. Its format is described in the Forge wiki. For example, let's check how updates on a primary key are doing. Let's name the configuration partitioning.yy and put the following in:

query:
  UPDATE _table SET _field_no_pk = _english WHERE `pk` = _smallint_unsigned;

This says "UPDATE a random non-PK field from the table to a random english word for a random value of the PK".

This is just the very tip of the iceberg. Arbitrarily complex SQL queries can be expressed with the appropriate SQL grammar.

Step3. Run the test!

Fetch the Random Query Generator from Launchpad:

bzr branch lp:~randgen/randgen/main

and then run the test:

perl runall.pl \
--basedir=/path/to/mysql-distribution \
--gendata=partitioning.zz \
--grammar=partitioning.yy \
--queries=100000 \
--threads=10 \
--duraton=360

This will start a server by using the specified MySQL location and will then run random queries in 10 threads, executing up to 100000 queries per thread for no more than 360 seconds.

While the test is running, monitor the performance of your server (which will run on port 19306). Check the CPU usage and use SHOW STATUS and mysqladmin status to see how things are going. To see performance without partitioning, edit the partitioning.zz file and remove the "partitions" line and run the test again.

You are also most welcome to join the MySQL University session on the RQG, to be held tomorrow, December 11 at 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 BST / 15:00 CET / 17:00 MDT (Moscow).

More information is available here:

• http://forge.mysql.com/wiki/Random_Query_Generator
• http://forge.mysql.com/wiki/MySQL_University