Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Reusing Open Connections When Testing Your Database

DZone's Guide to

Reusing Open Connections When Testing Your Database

Learn how to improve the way you load test your database and how to effectively control the number of open connections when testing your database.

Free Resource

Read why times series is the fastest growing database category.

When testing how well your database queries are optimized, opening up too many connections to the database might create 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 both 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 to apply a more accurate load to your database, simulate all possible test scenarios, and make your database application layer rock solid!

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');

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 using the form below:

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:

After that, we need to configure the database connection using the JDBC Connection Configuration element:

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

Now you have the database configuration that you can use in JDBC samplers 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):

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

As you can see, when the sampler is running, 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';

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. This is 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 two connected threads:

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

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:

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

Learn how to get 20x more performance than Elastic by moving to a Time Series database.

Topics:
jmeter ,mysql ,database ,tutorial ,database testing ,open connections ,load testing

Published at DZone with permission of Anastasia Golovkova, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}