Performance Testing SQL database is important these days because the amount of data stored and retrieved per second is increased to nearing to more then 10,000 requests per second on average. Due to this we need to make sure our databases are able to handle such loads and working as expected.
In this tutorial I’ll be providing a quick and easy tutorial for setup of JMeter and remote database for performance tests. Happy Learning!
Step — 1
Install JMeter and JAVA in your machine if not already
JMeter download Link: https://jmeter.apache.org/download_jmeter.cgi
JAVA download Link: https://www.oracle.com/in/java/technologies/downloads/
Why JAVA? because JMeter is based on JAVA and needs it environment to setup and run.
Step — 2
Download JAR for your SQL database such as SQL Server, MY SQL, Oracle etc.
Download Link: https://dev.mysql.com/downloads/connector/j/
Why do we need JAR’s? because to connect our remote or local database JMeter needs to setup the credentials and to connect and execute our test plan or queries for performance tests.
Step — 3
Once basic installation and setup is done, then check in your CLI the following commands to validate whether installation is completed successfully or not?
- java — — version (It should provide the java version installed in your machine)
- jmeter — — version (It should provide the jmeter version installed in your system)
Step — 4
Update the Jar file of your SQL connector in JMeter like shown below in image.
Step — 5
Add JDBC Connection Configuration in your test plan using following path:
- Right Click on your Test Plan element and Add Thread Group
- Right Click on your Thread Group -> Click on ADD -> Click on Config Element -> Click on JDBC Connection Configuration
- Right Click on Thread Group -> Click on Add -> Click on Sampler -> Click on JDBC Request
Setup your elements as shown in picture
JDBC Connection Configuration: is used to configure JMeter connections to the database.
Fill in the Variable Name field. The value of this field is used to associate a specific connection configuration (JDBC Connection Configuration) to the database and a specific request (JDBC Request) sent by JMeter.
If the field isn’t filled, the request won’t be sent and the JMeter Console would show the following message: java.lang.IllegalArgumentException: Variable Name must not be empty for element: JDBC Connection Configuration.
Configure the JDBC Connection Configuration. Anyone can use the following configuration by changing the value of localhost and the database schema:
- Database URL — used for the database address. Fill in:
- jdbc:mysql:// — the unchanged sequence used in JMeter when specifying the MySQL database address
- localhost:3306/ — the IP address and port that makes the database available. Since in the given example we use a database that is installed on a local PC, we use the IP address and port, configured while installing MySQL. In most cases, it would be necessary to install different connections for databases, which are on the remote server and have, for instance, an IP address and the port 10.56.56.2:410
- Note — I have setup a remote db connection so depending upon your usecase you can update the URL.
- sakila — is a scheme of database (or in a nutshell the name of a definite quantity of tables). Without specifying the scheme, JMeter returns the error: “Response message: java.sql.SQLException: No database selected”
- JDBC driver class — the class in which the code of work with a particular database is implemented (This class was created by the developers of JDBC driver):
- com.mysql.jdbc.Driver — a static series while connecting to MySQL
- Username — the name of a user who has access to the DB
- Password — the password for accessing the DB
JDBC Request: Add a JDBC Request as a child element to the Thread Group. The request will allow sending requests to the DB
Configure the JDBC Request
- Fill in the Variable Name field. I filled in “JDBC Request №1” because it is a meaningful name to the request installed by the JDBC Connection Configuration.
- Set the Query Type field to Select Statement. The value of Select Statement indicates that a request will be made to retrieve data from the database
- Set the variable name in the Result variable name field. This variable will store all fields and field values received from the database. The name of this variable must be unique within the Thread Group. If the name of this variable is used in several requests to the database (JDBC Request), then it will store the fields and field values received from the last query.
You can have multiple JDBC requests under a single thread group if you want to club them and use with a single thread. Now what this will do is, your 1st database request is completed by Thread, then it moves to 2nd databased request and so on..
- Create an SQL query against the database in the Query field. In this example: select * from persons limit 10
- The remaining parameters can be left out since further processing of the data will be performed by using Java. (For example, instead of the Response Assertion element, a BeanShell Assertion with the code written will be used, as shown in image)
Add a View Results Tree Listener and run the test. The item View Results Tree Listener as shown in screenshot.
Asserting the DB Response
In most of the cases when working with a DB, data receiving is complete with respect to the given conditions. If the data corresponding to the request exists in the database, we will get the results.
But what happens when the DB doesn’t have the data we were testing? In such a case, we will get an empty response, but JMeter won’t report it as an error.
I hope this tutorial is useful for your learning and implementation, do follow for more!
For regular updates use my LinkedIn: https://www.linkedin.com/in/japneet-sachdeva/
If you need complete QA Automation Interview Prep and Questions with Answers then use: https://lnkd.in/gcWuCJmr
Do follow for more such posts — Japneet Sachdeva
New Venture: https://lnkd.in/gj9Djd37
1) QA-UI-API Testing & Automation with real-time interview prep — https://lnkd.in/d35-Cbsd
4) AI & Prompt Engineering guide: https://lnkd.in/gV3NqvN7
Other Important Links:
If you need 1on1 sessions with me then you can use — https://lnkd.in/dcDtHp2r
My YouTube Videos — https://lnkd.in/guBKkQVv
My Medium Articles — https://lnkd.in/dCDi84Q9
My GitHub — https://lnkd.in/dy7AQD8f