JDBC Protocol
Learn how to configure the JDBC protocol, and how Gatling pools connections and measures response times.
Add the imports
The Gatling JDBC SDK isn’t imported by default. Add the following imports:
import static io.gatling.javaapi.jdbc.JdbcDsl.*;// Support coming soon
import io.gatling.javaapi.jdbc.JdbcDsl.*import io.gatling.jdbc.Predef._Configure the protocol
Use the jdbc object to create a JDBC protocol, then register it like any other protocol:
JdbcProtocolBuilder jdbcProtocol = jdbc
.url("jdbc:postgresql://localhost:5432/shop")
.credentials("gatling", "secret");
setUp(
scenario("JDBC").exec(jdbc("Count orders").statement("SELECT COUNT(*) FROM orders")).injectOpen(atOnceUsers(1))
).protocols(jdbcProtocol);// Support coming soon
val jdbcProtocol = jdbc
.url("jdbc:postgresql://localhost:5432/shop")
.credentials("gatling", "secret")
setUp(
scenario("JDBC").exec(jdbc("Count orders").statement("SELECT COUNT(*) FROM orders")).injectOpen(atOnceUsers(1))
).protocols(jdbcProtocol)val jdbcProtocol = jdbc
.url("jdbc:postgresql://localhost:5432/shop")
.credentials("gatling", "secret")
setUp(
scenario("JDBC").exec(jdbc("Count orders").statement("SELECT COUNT(*) FROM orders")).inject(atOnceUsers(1))
).protocols(jdbcProtocol)The protocol accepts the following options:
| Option | Default | Description |
|---|---|---|
url |
The JDBC URL of the database. Mutually exclusive with dataSource. |
|
dataSource |
A javax.sql.DataSource to get connections from. Mutually exclusive with url. |
|
credentials |
none | The username and password. |
property |
none | A property passed to the driver when opening connections. Only with url. |
maxConnections |
10 |
The size of the connection pool, which is also the maximum number of statements running concurrently. |
loginTimeout |
the driver’s | How long opening a connection may take. |
queryTimeout |
none | How long a statement may run. |
fetchSize |
the driver’s | How many rows the driver fetches per round trip. |
Pass driver properties
property passes a property to the JDBC driver when opening connections, along with the credentials, for example to enable TLS.
Refer to the documentation of your driver for the available properties.
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
.credentials("gatling", "secret")
// passed to the driver when opening connections
.property("ssl", "true")
.property("sslmode", "verify-full");// Support coming soon
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
.credentials("gatling", "secret")
// passed to the driver when opening connections
.property("ssl", "true")
.property("sslmode", "verify-full")jdbc
.url("jdbc:postgresql://localhost:5432/shop")
.credentials("gatling", "secret")
// passed to the driver when opening connections
.property("ssl", "true")
.property("sslmode", "verify-full")Get connections from a DataSource
dataSource makes Gatling get its connections from a javax.sql.DataSource instead of a URL, typically for drivers that need driver-specific settings.
Configure these settings on the DataSource itself: property is rejected with a DataSource.
When you set credentials, Gatling calls getConnection(username, password), otherwise getConnection().
// a DataSource configured with driver specific settings, not a pooling one
DataSource dataSource = someDataSource();
jdbc
.dataSource(dataSource)
// optional, passed to dataSource.getConnection(username, password)
.credentials("gatling", "secret");// Support coming soon
// a DataSource configured with driver specific settings, not a pooling one
val dataSource = someDataSource()
jdbc
.dataSource(dataSource)
// optional, passed to dataSource.getConnection(username, password)
.credentials("gatling", "secret")// a DataSource configured with driver specific settings, not a pooling one
val dataSource = someDataSource()
jdbc
.dataSource(dataSource)
// optional, passed to dataSource.getConnection(username, password)
.credentials("gatling", "secret")In JavaScript and TypeScript, dataSource takes a Java DataSource object, created with Java.type from a class provided by a JVM dependency.
DataSource, not a pooling one such as HikariCP: Gatling already pools the connections, as described below.Understand how Gatling pools connections
Each protocol has its own pool of maxConnections connections, shared by all the virtual users of all the scenarios using this protocol.
Gatling opens all the connections when the simulation starts, so the first virtual users don’t pay for it, and the simulation fails right away if the database can’t be reached.
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// the number of connections, and the maximum number of statements running concurrently (default: 10)
.maxConnections(50);// Support coming soon
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// the number of connections, and the maximum number of statements running concurrently (default: 10)
.maxConnections(50)jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// the number of connections, and the maximum number of statements running concurrently (default: 10)
.maxConnections(50)A virtual user borrows a connection for each statement, and gives it back once the statement is over.
When all the connections are borrowed, virtual users wait for one in order, without blocking any thread.
maxConnections is therefore the maximum number of statements running concurrently against the database, like the pool size of an application.
When the database or the network breaks a connection, the request using it fails, Gatling discards the connection, and the next request on this slot opens a new one.
Gatling runs JDBC calls, which are blocking, on one thread per connection, and processes the results, the checks included, on the virtual user’s own thread, so slow checks never delay other requests.
SET NOCOUNT ON on SQL Server, SET TRANSACTION ISOLATION LEVEL, or session variables, stays on the connection for the next virtual user that borrows it.Understand what Gatling measures
The response time of a JDBC request includes:
- waiting for a free connection, as an application would
- running the statement
- reading all the rows of all the results the statement produced, and every value of every row, as an application would
Checks then run on the results, outside of the response time. Gatling only keeps the values of the rows in memory when a check needs them, so large results don’t fill the memory of the load generator.
Configure timeouts
loginTimeout limits how long opening a connection may take, and queryTimeout limits how long a statement may run.
JDBC timeouts have a one second granularity, so Gatling rounds them up to the next second.
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// how long opening a connection may take, see DriverManager.setLoginTimeout
.loginTimeout(Duration.ofSeconds(5))
// how long a statement may run, see Statement.setQueryTimeout
.queryTimeout(Duration.ofSeconds(10));// Support coming soon
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// how long opening a connection may take, see DriverManager.setLoginTimeout
.loginTimeout(Duration.ofSeconds(5))
// how long a statement may run, see Statement.setQueryTimeout
.queryTimeout(Duration.ofSeconds(10))jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// how long opening a connection may take, see DriverManager.setLoginTimeout
.loginTimeout(5.seconds)
// how long a statement may run, see Statement.setQueryTimeout
.queryTimeout(10.seconds)url, loginTimeout sets DriverManager.setLoginTimeout, which applies to the whole JVM.
With dataSource, it sets the login timeout of this DataSource.
It’s up to the driver to honor it: some drivers have their own connect timeout property, that you can set with property.Configure the fetch size
fetchSize sets how many rows the driver fetches per round trip, see Statement.setFetchSize.
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// see Statement.setFetchSize
.fetchSize(1000);// Support coming soon
jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// see Statement.setFetchSize
.fetchSize(1000)jdbc
.url("jdbc:postgresql://localhost:5432/shop")
// see Statement.setFetchSize
.fetchSize(1000)Drivers handle it differently, so check the database specifics before setting it. For example, a small fetch size makes Oracle Database perform many more round trips than its default.