How to test database using Jmeter

JMeter is open source software for performance testing. It is written in Java and works as a desktop application. We can also perform database performance testing with this useful tool.

Precondition: - Java & Jmeter must be properly installed and configured with environment variables.

Objective: - I need to know how efficiently my SQL queries can perform under some given load.

Most important part to test database with Jmeter is JDBC driver.

JDBC configuration: - I will show how to configure JDBC with SQL Express 2008.

1. To configure JDBC with Jmeter, first download the JDBC driver from http://www.microsoft.com/download/en/details.aspx?id=11774 and then paste the driver JAR file under Jmeter/lib folder.
2. Add "Thread Group" element under the test plan by right clicking on test plan->Add->Threads (Users) ->Thread Group. Name it like "Insert" or something meaningful. In this element I will give no. of users to be simulated at database server. Now I'll give inputs according to my test plan under section "Thread Properties":

Number of Threads (Users) : 25
Ramp-up period (seconds) : 4 (how much to delay starting each user)
Loop Count : 5 (number of iterations)

3. Now, open Jmeter and create a test plan. Add JDBC connection configuration under thread group as a child. right click on thread group->Add->Config Element->JDBC Connection Configuration.

Test Plan Creation

4. Provide the requested values in connection configuration.

         Variable Name: (User defined)

         Maximum Number of Connections: 0 (if you want connection to be shared then give value more than zero)

         Pool Timeout: 10000

         Idle cleanup interval: 60000

         Auto Commit: True

         Maximum Connection Age: 5000

         Validation query: Select 1

         DatabaseURL: jdbc:sqlserver://localhost:port;DatabaseName=mydb;

         JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

         User Name: (SQL server access user name)

         Password: (user's password)

JDBC connection Configuration

You will get the port number from your from your SQL server configuration Manager.

Port

5. Now to perform a task with database you have to add a sampler-> JDBC request under thread group.

•          Name: user defined
•          Variable Name: Same variable name given at JDBC connection configuration.
•          Query Type: Select your query type from the "Query Type:" drop down list.

JDBC Request

6. Add listeners by which we will view the performance test results. For basic level of report add "Summary Report" & "View Results Tree". To be noted that listeners will use a lot of memory.

Summary Report

Result Tree

Parameterization in JDBC request

Now, I am going to show you how easy it is to set up test scenarios by using parameterization in JDBC.

Suppose we have an application that logs every transaction into a database which consists of many tables such as users, logs, categories etc.

(I will use parameterization in the same script which has been mentioned above)

There are many ways of using parameterization in JDBC, and two of them are most preferably used.

1. By calling CSV file

•          Create a CSV file with desired name suppose I gave a name “data.csv” which consist large number of input data and then save the file under Jmeter/bin directory. CSV file can have more than 1 column and/or rows depending upon your requirement.
•          Generate the function with “function helper dialog” by which you will call the data saved in CSV file.

Function Helper

CSV file to get values from| *alias: - Provide the name of your CSV file.

Column number of CSV file | next| *alias: - give 0 for first row in CSV excel file and 1 for next row and so on…

Hit Generate button and you will get a function string, copy this function string for first row values and paste it on JDBC request parameter values.

Like you have provided email addresses in the first row of CSV file then the generated function string will call all data of first row.

Parameter Values

Use ‘?’ symbol in place of values where you want to use parameterization.

Do this for other rows also by changing the function string value “${__CSVRead(name.csv,0)}” to “${__CSVRead(name.csv,1)}” and so on…

2. By using Config element - > Random Variable

Add a config element - > Random Variable.
Name: - Meaningful name (user-defined)
Variable Name: - used to call in Parameter Values under JDBC Request. Like if you have given a name “pin” then it will be called in parameter values as “${pin}”.
Minimum Value: - As per your requirement.
Maximum Value: - As per your requirement.

Random Variable