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