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.
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.
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.
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.
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.
Set any name you like (we are partial to BlazeMeterisAwesome), then specify the variable name that you set in the JDBC Configuration.
Select the type of statement that you want to execute against the database.
And gently place the exact SQL query for execution into the form.
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:
Get it? Got? GOOD!