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)

Important:
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.

10 comments:

  1. Hi, I followed steps and I receive the following 'No suitable driver found for jdbc:jtds:sqlserver://:/
    What did I wrong, could you help me?
    Thank you in advance

    ReplyDelete
  2. Dear Anonymous, This exception mainly happens when the connection string is not being written properly.
    What did you use as the Server Name/IP and Database name? Can u quote the exact line here, so that i can understand what went wrong in ur case.

    ReplyDelete
  3. First of all i would like to thank you such a important posting. I am beginner of Jmeter, i use jmeter for a website load testing but never used for database load testing....I am having fer questions on this topic....1. what is JTDS driver?..Why should i use it?...2. The result 2 indicates for what? it's not clear to me....3.if database located at local server then what should be link?...4.is this load testing is also applicable for Mysql database also....?

    ReplyDelete
    Replies
    1. Email me @ netemin at hotmail dot com

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. ERROR - jmeter.JMeter: Uncaught exception: java.lang.IllegalArgumentException: Variable Name must not be empty for element:JDBC Connection Configuration

    ReplyDelete
  7. Write statement of Delete Record in MySQL With Using Jmeter JDBC Configuration Connection and JDBC Request.

    Thank You.

    ReplyDelete
  8. Thank-you very-much for the great explaination!!

    ReplyDelete
  9. Thanks Iftekhar Alam,
    Nice Blog. Came to your link accidentally while searching something, but its a nice one I can say.

    In the end of above post you have mentioned that;

    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.

    I can't find any link for stored procedures on your blog. Actually I am having an issue with running a simple stored procedure with Jmeter. If you have any such post, can you please share the link.

    This is what I actually want to do:

    http://stackoverflow.com/questions/35066074/running-stored-procedure-from-jmeter

    ReplyDelete