Aleksey Merzlyakov is an experienced QA Engineer with 5+ years of experience in Web and Software QA testing.

Sergey Horban (pictured) is a QA Engineer with 3+ years of experience in automation and manual testing, bug-tracking and test management.

Learn JMeter in 5 Hours

Start Learning
Slack

Run massively scalable performance tests on web, mobile, and APIs

Nov 01 2017

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

 

API Requirements:

  • 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

 

Customer Table:

 

load testing multiple databases

 

Payment Table:

 

performance testing multiple databases

 

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:

 

select a.customer_id, a.address_id, b.payment_date, b.amount  
from customer a
join payment b
on a.customer_id = b.customer_id
where address_id is not null
and a.customer_id is not null

 

These would be the results we would have received:

 

load testing multiple servers

 

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.

 

peformance testing multiple databases

 

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.

 

jmeter multiple servers

 

jmeter multiple databases

 

Example code:

 

select customer_id, address_id  
from customer 
where address_id is not null
and customer_id is not null
limit 10

 

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

 

load testing multiple databses and servers

 

performance testing multiple databases and servers

 

Example code:

 

if(ResponseCode.equals("200")) {

	if(vars.getObject("customer").size() != 0) {	

		clients = vars.getObject("customer");
		props.put("clients",clients);
		
	} else {
	
		FailureMessage = "!!!!!!!!!!!! The response is empty !!!!!!!!!!!!!";
		Failure = true;	
		prev.setStopThread(true); 
	}		
} else {
	
	FailureMessage = "!!!!!!!!!!!! No connection to the database !!!!!!!!!!!!!";
	Failure = true;	
	prev.setStopThread(true); 
}

 

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.

 

load test many databases and servers with jmeter

 

performance testing many databases and servers with jmeter

 

Example code:

 

$ {javaScript ("$ {stop While}"! = "OK")}

 

6. Add the Counter to the While Controller. The Counter will be used to account for requests sent to the database.

 

open source testing of databases

 

Set the following parameters:

 

jmeter, load testing, databases, servers

 

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

 

databses on lots of servers, load testing

 

Add the code as shown in the image below:

 

how do i load test a lot of databses

 

Example code:

 

int i = Integer.parseInt(vars.get("counter"));

if(i <= props.get("clients").size() - 1){	
	
	customer_i = props.get("clients").get(i).get("customer_id") + "";
	vars.put("customer_i",customer_i);
}

 

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.

 

can I load test a few servers on jmeter?

 

Set the time.

 

jmeter load testing for many databses how to do it

 

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.

 

can i load test a mysql database on jmeter?

 

Example code:

 

select *
from payment 
where customer_id = "${customer_i}"

 

  • * - 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.

 

load testing multiple mysql databases

 

The following code is used to stop the While Controller when data (the payments to the customer) is received from the database:

 

stopWhile = "OK" 

 

The code below stops the test if, for all clients received in "Thread Group №1", no payment information is found on the second server:

 

if (Integer.parseInt(vars.get("counter")) == props.get("clients").size() - 1) {		
	
FailureMessage = "!!!!!!!!!!!! Data on customer's payments not found !!!!!!!!!!!!!";
Failure = true;	
prev.setStopThread(true);		 		
}

 

Example code:

 

if(ResponseCode.equals("200")) {	
	
	if(vars.getObject("customer1").size() != 0) {	

		address_id = props.get("clients").get(Integer.parseInt(vars.get("counter"))).get("address_id") + "";
		vars.put("address_id",address_id);
		
		stopWhile = "OK";
		vars.put("stopWhile",stopWhile);	
			
		log.info("address_id: " + address_id);

		if (Integer.parseInt(vars.get("counter")) == props.get("clients").size() - 1) {		
	
			FailureMessage = "!!!!!!!!!!!! Data on customer's payments not found !!!!!!!!!!!!!";
			Failure = true;	
			prev.setStopThread(true);		 		
		}		
	} 

} else {
	
	FailureMessage = "!!!!!!!!!!!! No connection to the database !!!!!!!!!!!!!";
	Failure = true;	
	prev.setStopThread(true); 
}

 

Results

 

Receiving data about clients that are located on the first database server:

 

testing multiple databases on jmeter results

 

Sending a request to receive data on customer payments on the second database server:

 

load testing many databases results

 

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:

 

easily load testing many servers

 

Data for the client on the second database server was found:

 

guide for jmeter multiple servers

 

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.

 

Click here to subscribe to our newsletter.

 

To try out BlazeMeter, out your URL or JMX file in the box below and your test will start in minutes. Or, request a demo.

Interested in writing for our Blog?Send us a pitch!

Your email is required to complete the test. If you proceed, your test will be aborted.