Using JMeter for DB (Apache Derby) benchmarking

Recently I had to do check or I was for support in a case where the Apache Derby was used in embedded mode. The questions were related to performance, demiensioning and also some non-functional aspects like backup and restore. Going through documentation is one thing but being able to verify some performance figures is another. This article will describe how I used the JMeter to setup environment measuring reads, writes and doing the backup in the background (to check the impact on processed transactions) for the Apache Derby working in the embedded mode (the external one is out of scope of this article).

Obtaining JMeter

Although the JMeter is available in the repos for most popular distribtions (e.g. Ubuntu), I recommend to download it from the webpage (newer version, more features etc.): http://jmeter.apache.org/ - the installation is about unzipping an archive (you can use e.g. unzip).

Instaling JMeter

After unpacking you will need to add the jdbc driver to the classpath - in my case it basically meant that I needed to copy the derby.jar into the lib subdirectory of jmeter. If you already have a derby database on which you want to do benchmark (my case) it would be better to find the Derby version matching the one which was used to create the database. As I mentioned before in this article the embedded mode for derby will be used.

Install additional chart/monitoring JMeter plugins

There is a set of nice, additional plugins available on google code: http://code.google.com/p/jmeter-plugins/ that I would recommend to install. Not only they provide nicer charting listeners but also monitoring capabilities (each system to be monitor will need to have a special agent started). The installation procedure is described in the README and on the webpage.
What I did is after downloading I unpacked the archive to a subdirectory under JMETER_HOME. Next I copied the JMeterPlugins.jar to the lib/ext subdirectory. That's it, the new plugins should be available now.

Building the testcase

Ok, now you are ready to prepare the testcase.

1. Start the JMeter:
# $JMETER_HOME/bin/jmeter.sh

2. Add the database (ApacheDerby) connection definition (JDBC Connection Definition) as follows:

The database URL in case of embedded derby shall contain the path to the database location on the filesystem.
The JDBC Driver class is the driver class - here a specific one for Derby.
In addition one needs to provide username and password for connecting to the database.

3. Add thread pools and samplers

I added three different thread pools: one for testing the reads, one for testing the writes and one (single threaded) for backup. As you see all are using the same JDBC connection definition - defined on the global level

4. Add JDBC request samplers

Inside each thread pool you should define the JDBC request sampler, responsible for generating the appropriate traffic type. Below you can find example used by me for read, write and starting the backup:

The JDBC request sampler for read access

The JDBC request sampler for write access

The JDBC request sampler for initiating the backup

5. Add listeners for summary, CPU, memory, IO statistics.

For getting the summary you should add to each thread group the so called 'Aggregate report' listener. It will provide statistics like: number of samples, average response time, q90, median, min, max, error rate and throughput.

Example aggregate report listener for read access

In addition on the top level you should add the collectors for CPU, memory and disk, network - these are available after installing the additional google code plugin in the Listener section. The important thing is you need to start the agent on the monitored node as follows:



$ $JMETER_PLUGIN/serverAgent/startAgent.sh 
JMeterPlugins Agent version 1.4
No port specified, the default value is used: 4444
------ File Systems init: ------
File System detected: /dev/mapper/krystianek-root
File System detected: /dev/sda1
--------------------------------
--- Network Interfaces init: ---
Network interface detected: lo
Network interface detected: wlan0
Network interface detected: virbr1
Network interface detected: virbr2
Network interface detected: virbr0
Network interface detected: tun0
Network interface detected: eth0
--------------------------------
Waiting for incoming connections...
Client id=0 connected!
Client id=1 connected!
Client id=2 connected!
Client id=3 connected!
Client id=3 disconnected!
Client id=0 disconnected!
Client id=1 disconnected!
Client id=2 disconnected!

And the statistics collector shall be configured to connect to that agent:

Example listener for collecting disk, network statistics

Example listener for collecting CPU statistics

I also recommend to put the Transaction per second listener from google code to each thread pool section, which will provide nice charts for per second statistics.

Example lstener providing graphical represenation of throughput per second

Now you just have to start the execution of the test and do the measurements;)