Anastasia Golovkova is a Quality Assurance specialist with variety of skills in automation testing and 2 years of experience in IT.

Learn JMeter in 5 Hours

Start Learning
Slack

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

Aug 23 2017

Reusing Open Connections When Testing Your Database

When testing how well your database queries are optimised, opening up too many connections to the database might create an overhead and cause performance degradation. To be able to isolate database query testing, Apache JMeter™ provides flexibility, allowing you to choose if you want to run many queries using one connection, or to establish many connections but to run queries less extensively.

 

In this blog post, we will show you how to run MySQL database queries with one connection, and with multiple connections. This is done through JMeter’s JDBC elements and Thread Groups. As soon as you get the idea of how it works, you will be able apply a more accurate load to your database, to simulate all possible test scenarios and to make your database application layer rock solid!

 

Opening One Connection

 

1. First of all, let's create a simple performance script that sends basic requests to a database. If you want to try all the steps yourself and you don't have an available MySQL database for testing, you can go to this link and install your own local MySQL server. After that, you can use the script to create a database with a very basic table, which we are going
to use for testing:

CREATE DATABASE jmeter_mysql_test_db;

USE jmeter_mysql_test_db;

CREATE TABLE books (
  boook_id INT NOT NULL, 
  book_title VARCHAR(30) NOT NULL,
  author_name VARCHAR(30) NOT NULL,
  PRIMARY KEY (boook_id)
);


INSERT INTO `books` (`boook_id`, `book_title`, `author_name`)
VALUES
	(1, 'Don Quixote', 'Miguel De Cervantes'),
	(2, 'Pilgrim\'s Progress', 'John Bunyan'),
	(3, 'Robinson Crusoe', 'Daniel Defoe'),
	(4, 'Anna Karenina', 'Leo Tolstoy'),
	(5, 'Daniel Deronda', 'George Eliot'),
	(6, 'War and Peace', 'Leo Tolstoy'),
	(7, 'The Portrait of a Lady', 'Henry James');

 

2. After the database is up and running, we can proceed with test script creation. First of all, we need to download the JDBC driver to our database. For example, the MySQL driver can be found here. Then, you need to add the .jar file to the test-plan by using the form below:

 

reusing open connections jmeter

 

3. We want to show JMeter database connection behavior in the case of one user. To do that, we need to run the same database query many times. Let’s use 20 as the loop count value:

 

jmeter reusing connections

 

4. After that, we need to configure the database connection by using the ‘JDBC Connection Configuration’ element:

 

Right click on Thread Group -> Add -> Config Element -> JDBC Connection Configuration

 

how do i reuse open connections

 

5. Now you have the database configuration that you can use in JDBC samplers by using the ‘JMeterTestDb’ variable name. To show database connection behavior, let’s create the simplest ‘JDBC Request’ sampler (Right click on Thread Group -> Add -> Sampler -> JDBC Request) and put a basic MySQL query to return a database value (for example, let’s select all books written by Leo Tolstoy):

 

controlling open and close connections jmeter

 

6. Our basic test script is ready and we can verify results using the ‘View Results in Table’ listener (Right click on Thread Group -> Add -> Listener -> View Results in Table):

 

mysql atabase testing jmeter

 

As you can see, when the sampler is running, the the ‘Connect Time’ (far right column) is positive (always non-zero) only for the first value. In the other cases, it is 0 for all the other samples.

 

The reason for this is simple. JMeter uses only one connection per user and does not open it during each request. Connection opening is a very expensive operation and in addition to that, there are limits on how many connections you can open a database.

 

This is why web applications usually care about the number of open connections. In the case of many different queries, a connection is established only once and all further requests just request the same connection that is already opened.

 

JMeter works in the same way. We have only one user that performs twenty JDBC requests one by one, and only the first sampler actually performs database connections that are reflected in the ‘Connection Time’ value, which is more than zero. After that the same connection is reused for all other requests.

 

There is one another way to confirm that JMeter uses only one connection per user. MySQL provides the ability to request the number of connections that are currently open. To do that you just need to run this request in your MySQL database:


SHOW status WHERE `variable_name` = 'Threads_connected';

 

jmeter reusing connections

 

If you run the mentioned query without running any test script in the background, you should see the value ‘1’ as the ‘Threads_connected’ value. We will see 1 because we already have the open connection to the database, as you are running requests there (you can do it via MySQL command line interface or maybe some other MySQL desktop client). But if you run the JMeter test script created above and run the same MySQL query one more time, you should see 2 connected threads:

 

load testing mysql database opening connections

 

That’s more proof that JMeter opens only one connection per user, independently of how many queries to a database are performed.

 

Opening Multiple Connections

 

But what if you want to simulate many connections to a database? Just use more users, and the number of database connected threads will be equal to the number of your test users. For example, if you run the same script using 20 users, you should see this:

 

jmeter multiple connections

 

jmeter opening and closing connections

 

In the case of 20 users, the database will manage 20 connected threads and in the ‘View Results in Table’ listener you will see 20 lines that should have non-zero positive value in ‘Connect Time’, which represent first database requests for each of the users.

 

That’s it! You can now apply what you learned here to your database load testing and control the number of open connections when testing your database.

 

To learn more JMeter check out our free JMeter academy.

 

Click here to subscribe to our newsletter.

 

To try out BlazeMeter, which enhances JMeter abilities, request a demo or put your URL or JMX file in the box below and your test will start in minutes.

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.