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.
- 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.
- 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)
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 |
 |
| 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.
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 |
