How to Test Multiple Databases and Servers on JMeter
Often, large software products work with several databases that are located on different servers. This leads to an increase in the time required for preparing test data. By using Apache JMeter™, this time can be reduced.
Suppose that a sample API works in conjunction with several MySQL databases located on different servers. In this blog post, we will use the same API example we used in the previous blog posts about testing MySQL databases with JMeter.
This API uses input parameters (for example, the "address" parameter). It then takes the unique value (the client's address) and returns data (about the payments made by it). The API takes the information from the database (the Payment Table).
- The verification of the existence of a unique client address value is checked in the Customer Table, where address = address_id
- The unique value of the customer address, in the Customer Table, defines a unique customer number (customer_id)
- The value of the customer_id in the Payment Table shows the payments made by the customer
- If the payment exists, the API displays all the data from the Payment Table
- If there are no payments, the API returns an empty response
In the "Payment" table, payments exist for customers starting with customer_id = 9.
But suppose the Customer table is on one server, and the Payment table is on another. Also, suppose there are customers in the Customer table that may not have payment data in the Payment table, and that there are payments that might not have a customer in the Customer table.
We will perform a test case for obtaining payment data for a customer who has payments in the Payment table. The test case will do the following:
- Obtain the client ID and the unique customer address (customer_id, address_id) in the Customer table from the first server.
- For each customer ID = customer_id, look for payment information in the Payment table on the second server.
- If the customer finds payment information, then take his address_id from the first server from the Customer table and pass it as an incoming value for the API request.
If the Customer and Payment table belonged to the same database and were on the same server, then the execution of clauses 1 and 2 could be done by using the following SQL statement:
These would be the results we would have received:
But since our tables belong to several databases that are on different servers, it takes a lot of time to perform point 1 and 2 manually. By using JMeter, the time will be significantly reduced.
In order to perform the test case above in JMeter, you need to do the following:
1. Add two Thread Group elements. Each thread group will be assigned to one of the two databases on a specific server.
2. Configure the connections to the databases as indicated in the previous article "MySQL Database and JMeter - How to Test Your Connection".
3. To Thread Group # 1 add JDBC Request # 1 with an SQL query, as shown in the image below. This request will receive data from the database about the clients that are stored on the first server.
- select customer_id, address_id - select the values "customer_id", "address_id " from the table "customer "
- where address_id is not null and customer_id is not null - choose "address_id " and "customer_id " that have filled values
4. Into JDBC Request # 1, add the BeanShell Assertion element with the code, as shown in the images below.
In this code, we save the entire received response from the "customer" table (that is on the first server) to the variable "clients". This variable will store 10 lines, with the values "customer_id" and "address_id".
5. Into Thread Group №2 add a While Controller. The While Controller is used to perform a sequence of actions more than one time. In our example, it is designed to send repeated requests to the database that is located on the second server.
6. Add the Counter to the While Controller. The Counter will be used to account for requests sent to the database.
Set the following parameters:
- Start = “0” is the initial value that is assigned to the variable "counter" before sending the first request to the database
- Increment = “1” is the value that is added to the value of the variable "counter" before sending the second request to the database, etc.
- Reference Name = "counter" is the name of the variable
7. Add the BeanShell PreProcessor to the While Controller. The BeanShell PreProcessor will be used to get the customer_id values from the created variables and send them to query the database on the second server.
Add the code as shown in the image below:
8. Add the Test Action to the While Controller. The Test Action is used to set the time through which the next request will be sent to the database, i.e the time between requests.
Set the time.
9. Add the JDBC Request # 2 with an SQL query to the While Controller. This request will receive data on customer payments from the database that is on the second server.
- * - means that we get all the columns from the table "payment"
10. Add the BeanShell Assertion element with the code to JDBC Request # 2, as shown in the image below.
The following code is used to stop the While Controller when data (the payments to the customer) is received from the database:
The code below stops the test if, for all clients received in "Thread Group №1", no payment information is found on the second server:
Receiving data about clients that are located on the first database server:
Sending a request to receive data on customer payments on the second database server:
Receiving payment data for clients that are located on the second database server. Since the data is not received, the next request for the another client is executed. These requests are sent until finding a client that has payments, or the client list ends:
Data for the client on the second database server was found:
That’s it! You now know how to use JMeter to test multiple databases and servers. Learn more about what you can do with JMeter from our free JMeter academy.