Sunday, March 14, 2010

Load Testing Relational Databases With JMeter

Apache JMeter is a performance testing tool which is entirely written in Java. Any application that works on request/response model can be load tested with JMeter. A relational database is not an exception: receives sql queries, executes them and returns the results of the execution.

I'am going to show you how easy it is to set up test scenarios with the graphical user interface of JMeter. But before diving into details let's give a shot to basic terms:

Test plan : describes a test scenario
Thread Group : represents users running your test scenario.
Samples : a way of sending request and waiting response. HTTP request, JDBC request, SOAP/XML-RPC request and java object request are examples of samples.
Logic Controller : used to customize the logic that JMeter uses to decide when to send requests
Listeners : receives test results and displays reports.
Timers : cause JMeter to delay a certain amount of time before each request that a thread makes.
Assertions : test that application returns expected responses

Note : This post is not meant to be an alternative documentation for JMeter. JMeter has a great documentation. You can find the details in its User's Manual (http://jakarta.apache.org/jmeter/usermanual/index.html
Suppose we have an application that logs every transaction into a relational database. We are going to create a test plan - step by step - in order to answer the questions below.
  • How many transaction records can be inserted to transaction table in a second?
  • How much time does it take to insert a single transaction record to transaction table?
  • How does number of concurrent threads (users) affects the inserts/secs and average response times?
  • How does number of records affects the insert/secs and average response times?

Step 1

Copy mysql jdbc driver into the lib folder of your JMeter installation. JMeter needs a suitable jdbc driver in the classpath to connect to the database.
Example ~/tools/jakarta-jmeter-2.3.4/lib/mysql-connector-java-5.0.5.jar
We are going to store orders of the customers and the result of the order in the transactions table.
CREATE TABLE transactions (
    id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_id INT NOT NULL,
    result INT,
    PRIMARY KEY (id)
);

Step 2

Create a test plan and name it "Test MYSQL DB". Then add the following jmeter components to the test plan.
  1. Thread group named 'Database Users'
  2. Sampler of type JDBC Request
  3. Config element of type JDBC Connection Configuration
  4. Three config elements of type Random Variable
  5. Listener of type Summary Report
After adding these components JMeter test plan looks like the following picture.

Step 3

Configure database users. The thread group component simulates the database users.
1. Number of users (threads)
2. How many times a user will send request (loop count). If you select 'Forever', threads will run in a while(true) {...} loop until you decide to stop the test.

Step 4

Configure JDBC connection pool. JDBC Connection Configuration component is used to create jdbc connection pools. Database url, jdbc driver, database user and password are configured with this component. Connection pools are identified by "Variable Name". JDBC Samplers (requests) use this variable name (connection pool name) to pop and push connections. I named the test connection pool as "my db pool"

Step 5

Define random variables that will be used in INSERT statements. In this test I am using three random variables : user id, order id and result. Following picture shows the a random number configuration for user id. Random number generator will give us a random integers between 1 and 1000000. We can refer to generated random number with the name user_id.

Step 6

JDBC Request component is the place where we tell our users (threads) what to do. The name of the pool that was configured in Step 3 "my db pool" will be used as the "variable name bound to pool". All threads will execute prepared statements. User id, order id and result will be generated by the random number configurator (described in Step 5)

Step 7

Now we have our threads configured to insert transaction records to the transactions table. In this last step we will add a Listener of type Summary Report in order to view test results.

The results tells us that 10 concurrent users (threads) working in an infinite loop can insert nearly 3300 rows in our transactions table. And the average time spent for inserting a row is 2 ms. You can also choose "Graph Results" listener to view visual representation of the results.
I created and run a simple DB test plan. I hope you'll find this post helpful. Keep this motto in mind
if you can’t measure it, you can neither manage it nor improve it
Happy testing...

21 comments:

jjjhhjhj said...

Tamil Songs Download

thanks for your nice aricle

sud said...

Thanks for this post. Didn't quite realize JMeter had this capability.

Used it for load testing web applications before. But this is an interesting use as well.

Anonymous said...

Thank you , very helpful

Ram said...

Hello, I followed same method mentioned here. But My Jmeter is not making any connections to the database. I copied jdbc into /lib directory.

My Database is on RHEL5
Version is MySQL enterprise 5.1
Jmeter is on Windows xp.

Ram said...

Hello, I followed same method mentioned here. But My Jmeter is not making any connections to the database. I copied jdbc into /lib directory.

My Database is on RHEL5
Version is MySQL enterprise 5.1
Jmeter is on Windows xp.

İlkin Ulaş BALKANAY said...

Have you checked jmeter.log?
There must be a helpful message in the log files.

Ajoy Singha said...

Well a nice blog. Here is the website about software testing and ISTQB Sample Papers www.ajoysingha.info.

Tee Chess said...

Nice article. Thanks for sharing such useful information that will help the one who will read the post. Its really very helpful. Software Testing Services

Anh Nga said...

Hi All,

I am able to run database load test with Jmeter, but I don't see the real data in table.

Please give me your idea
Thanks
Nga

İlkin Ulaş BALKANAY said...

@Anh Nga
Can you please post your jmeter test config xml.

Anh Nga said...

Thank,
I am able to observe the real data when run Database load test with Jmeter.

Thank for you nice guide.

AnhNga

rikky said...

i hav some doubt
1.what the meaning by database driver class
2.is this sample will reflect to my database also.
Plz rply ASAP

İlkin Ulaş BALKANAY said...

@rikky database (jdbc) driver is the abstraction layer between your java code and the underlying database. you must use oracle jdbc driver to access an Oracle Database.

You can use the same test case but you should use an appropriate jdbc driver

Anonymous said...

Hi,
How can i run my web load test in jmeter so that, I should be able to insert data into database for each user (Number of threads)
Please help me out on this.

Thank you,
Sunil

kunal said...

my db is hosted on another server how will i connect it i m noob plz help me anybody can

İlkin Ulaş BALKANAY said...

@kunal Step 4 explains configuring the database connection. You can tell jmeter the IP address of the remote database

Dzmitry said...

Thank you for material. Here is detailed guide, how to build database test-plan with JMeter(for MySQL, but can be addopted for any DB, I think)
- http://blazemeter.com/blog/secret-building-database-test-plan-with-jmeter

JD said...

Awesome Article!!
How can I run 2 queries per thread do I just add another JDBC request? If yes then does each thread just run through all JDBC requests in the test? Much appreciated!!!

JD said...

Awesome Article!!!
How can I run 2+ queries per thread. Say I want to create a user and insert into a variety of other tables. Do I just create more JDBC requests. If yes does each thread just run through all the JDBC requests? Much Appreciated!!!

JD said...

Awesome Article!!
How can I run 2 queries per thread do I just add another JDBC request? If yes then does each thread just run through all JDBC requests in the test? Much appreciated!!!

David said...

Thx for the post, it´s very helpfull, but I can´t see the data inserted in the DB.