Wednesday, January 4, 2012

Load Testing of Database Query with Jmeter

Most of people who are involved in the testing activities have some ideas about Jmeter. JMeter is a popular open source tool for load testing, with many useful modeling features such as thread group, timer, and HTTP sampler elements. Interesting thing is that along with the usual load testing of a web application or a web server, Jmeter has the feature to test the load of the database. In this post i have tried to give a step-by-step procedure to test a simple database queries with Jmeter. I have a plan to make several blog posts to cover the important load testing scenarios of database. Here, i have done the load test for Microsoft SQL Server 2008. I have used the jTDS Driver (from net.sourceforge.jtds.jdbc.Driver class) to integrate the Jmeter with SQL server. You can test for the other databases (Oracle, MsSql, PGSQL ) also.

Required Software:

Steps to follow:

Step 1: Adding Thread Group Control
  • Thread Group allows to run script with nth no. of users with defined ramp-up period and also the scheduling of execution.
  •  Right click on the Test Plan and add the Thread Group control.
                                                   Figure 1: Adding Thread Group Control
                                              Figure 2: Adding Thread Group Control

Step 2: Adding JDBC Connection Configuration control
  •   Used to configure the database i.e. Oracle, MS SQL Server etc.
  • Add JDBC Connection Configuration control to the Test Plan.
Database Connection Configuration
         Database URL:  jdbc:jtds:sqlserver:// [server name or ip] / [db name]
         JDBC Driver Class: net.sourceforge.jtds.jdbc.Driver
         Username: Username of the database (e.g. Username: scott)
         Password: Password of the user (e.g. Password: tiger)

Add the following Jars in JMeter Installation directory (i.e. D:\jakarta-jmeter-2.3.1\lib)
·         jtds-1.2.5.jar

                                         Figure 3: Adding JDBC Connection Configuration control

Step 3:  Adding JDBC Request Control
  •   JDBC Request control allows to run a SQL query, procedure etc.
  • Add the JDBC Request control below the Thread Group.
                                    Figure 4: Adding JDBC Request Control

In my db, there is a single table ("Student") which contains only two columns(id, name) and this table has only 2 rows. You can test for the 'Insert', 'Update' and 'Delete' commands also. For that, change the 'Query type' to 'Update Statement' from the drop-down box.

Step 4:  Adding Listeners: View Result Tree control
  • View Result Tree control is used to view the result of the query executed.
  •  Add View Result Tree control to the Test Plan.
                                  Figure 5: Adding Listeners: View Result Tree control

Step 5: Verify Result from the Listeners 

 Check the load testing results from the Listeners. 

                                        Figure 6: Adding Listeners: View Result Tree control

                                    Figure 7: Verify Result: View Result Tree control

In the next few blog posts, i will try to extend this load test with the stored procedures and reading data from configuration files etc.