The Real Secret to Building a Database Test Plan With JMeter

Posted by Dzmitry Kashlach


Sometimes testing an entire website is not enough. Sometimes, you also need to test the website or web-app database itself.
 

More work you say? Why do we have to you say?
 
First of all-don't despair (and don't whine either 'cause load testing is fun!). The more thoroughly you run your load testing checks now, the easier it will be for your to maintain later. Your composure and your site or app. Little to no crashing, avoiding bottlenecks or recurring bottlenecks, avoiding the wrath of management when their site crashes midway through a huge sale (yeah, we all know about that). It's win win.
 
Imagine the scenario where you have bad performance results and you want to investigate whether it is the fault of a database with poor architecture or if there is another reason for a response time of...for example... 30 seconds.  We definitely need to delve deeper.
 
Test Plan- HTTP Test Plan- BlazeMeter
 
And of course, you SHOULD test the database separately if you are developing a large application, like Internet-banking or something similar. That's a given.
The first step is standard procedure, create a new test plan and add a Thread Group. 
Then switch to test plan. At the bottom of the element you will see the form.
 
JMeter Test Plan Using BlazeMeter
 
You should download the JDBC driver for your database (refer to the database vendor), and place the *.jar file in JMETER_HOME/lib. Then add  the *.jar file to the test-plan using the form. Switch to the Thread Group and add the JDBC Connection Configuration element.
 
JDBC Driver JMeter test plan using BlazeMeter
 
AND
 
JDBC Connection Configuration
 
After assigning the element a name, pay extra attention to the “variable name” field. If you have more than one configuration(for different databases), this parameter should be unique because it will be used as a reference for configuration in the JDBC Request. 
 

Let's talk max number of connections.

Do you have an estimation of expected virtual users who may connect to the database? If yes, specify this value. If not.... well then that makes it near impossible to begin testing a database. Do NOT pass GO. Do NOT collect $200.
 
This field is absolutely required.
 
Pool timeout. The time interval for establishing the connection to the database. If the connection is not established, the error will be thrown.
 
Idle Cleanup Interval. This particular parameter can be frustrating. Why? Because the meaning is unclear even for advanced JMeter developers.
 
Idle Cleanup Interval
 
So, let's keep it at default value.
 
Auto Commit. If  “true”, the transaction will be committed automatically.
 
Transaction isolation. In this case we are using the MySQL database and “Default” means “Repeatable read”. That means that if you read the same strings repeatedly inside a single transaction, you will have the same data.
 
Validation Query. Leave the default value.
 

VII (Very Important Info)- “Database connection configuration”

  • Database URL: Place a link to the database that you plan to test
  • JDBC Driver class: Enter the full qualified name of the driver class
  • Username & Password: Enter the database credentials
Now that the database connection is configured, it's time to create the JDBC request itself.
 
Creating the JDBC Request
 
Set any name you like (we are partial to BlazeMeterisAwesome), then specify the variable name that you set in the JDBC Configuration.
 
JDBC Request Set Variable Name
 
Select the type of statement that you want to execute against the database.
 
JDBC Request- SQL Query
 
 
And gently place the exact SQL query for execution into the form.
 
JDBC Request SQL Query
 
And the last element to add – is View Results Tree. But be careful with it as it  takes up a lot of memory because it saves all results of ALL requests. So, use it only for debugging purposes and don't use it while running tests with a huge load. After debugging is complete, simply disable the View Results Tree and add the Simple Data Writer. It's more accurate with memory.
 
Now-Let's test our test! 
Execute this statement against the database:
Select  username from mantisbt.mantis_user_table WHERE id=1 
 
And here is what we get in the View Results Tree:
 
JDBC Request- View Results Tree
 
Get it? Got? GOOD!

Have you tried BlazeMeter's plugins for JMeter or Jenkins CI yet?

If not, take a gander at the phenomenal Compare Test Reports Feature.

Download the JMeter plugin. Or, download the Jenkins CI plugin.

Visit BlazeMeter's Knowledge Hub for more resources and tutorials on JMeter and load testing.