Most production ready APIs use a Database. Therefore, it's important to learn to load test APIs with a database interaction. In this blog post, we will use Gatling to load test an API endpoint that connects to a MySQL database, for 1,000 users per second. If you want to practice yourself, you can find the complete source with all the configs and codes this blog post covers, here.

When working with production data, I strongly recommend having a production database replica. Otherwise, you might affect production code performance during your tests, or break analytics statistics. Make sure the replica is similar to production.

API and Database Configuration

First, configure your API. Here, we will use the API /api/1.0/users, which uses a POST method for adding new users. Our API endpoint will receive data in JSON format. APIs can receive data in other formats, like XML. Then, the API will store the data in a database. Finally, the API will apply changes to the data and store the changes in the database. APIs can also store incoming data as is or apply transformations on it before storing. Updating database data and keeping it original is important for debugging needs.

Gatling Configuration

Let's add Gatling to our API. If you already followed the instructions in the API Load Testing with Gatling blog post, then you can skip this part.

After installing Gatling, let's add code inside our build.gradle file, as follows:

In the plugins section:

apply plugin: 'scala'

In the dependencies section:

testCompile group: 'io.gatling.highcharts', name: 'gatling-charts-highcharts', version: '2.2.5'
testCompile group: 'io.gatling', name: 'gatling-jdbc', version: '2.2.5'

Finally, create a task for test execution:

task loadTest(type: JavaExec) {
   dependsOn testClasses
   description = "Load Test With Gatling"
   group = "Load Test"
   classpath = sourceSets.test.runtimeClasspath
   jvmArgs = [
           "-Dgatling.core.directory.binaries=${sourceSets.test.output.classesDir.toString()}"
   ]
   main = "io.gatling.app.Gatling"
   args = [
           "--simulation", "BlazeMeterGatlingTest",
           "--results-folder", "${buildDir}/gatling-results",
           "--binaries-folder", sourceSets.test.output.classesDir.toString(),
           "--bodies-folder", sourceSets.test.resources.srcDirs.toList().first().toString() + "/gatling/bodies",
   ]
}

As a test file, we will use following code in src/test/scala.

This code shows the following test case:

• Connect to the DB.
• SQL selects all or part of the incoming data (for example 1000 rows).
• Execute the POST request with the gathered data.

import io.gatling.core.Predef._
import io.gatling.core.feeder.RecordSeqFeederBuilder
import io.gatling.core.structure.ScenarioBuilder
import io.gatling.http.Predef._
import io.gatling.http.protocol.HttpProtocolBuilder
import io.gatling.jdbc.Predef._

class BlazeMeterGatlingTest extends Simulation {
 private val dbConnectionString = "jdbc:mariadb://localhost:3306/BlazeDemo"
 private val sqlQuery = "SELECT data FROM original_data"
 private val sqlUserName = ${your_username}
 private val sqlPassword = ${your_password}
 private val message = "${data}"

 private val baseUrl = "http://localhost:16666"
 private val basicAuthHeader = "Basic YmxhemU6UTF3MmUzcjQ="
 private val authPass = "Q1w2e3r4"
 private val uri = "http://localhost:16666/api/1.0/users/"
 private val contentType = "application/json"
 private val endpoint = "/api/1.0/users/"
 private val authUser = "blaze"
 private val requestCount = 1000

 val sqlQueryFeeder: RecordSeqFeederBuilder[Any] = jdbcFeeder(dbConnectionString,
   sqlUserName,
   sqlPassword,
   sqlQuery
 )

 val httpProtocol: HttpProtocolBuilder = http
   .baseURL(baseUrl)
   .inferHtmlResources()
   .acceptHeader("*/*")
   .authorizationHeader(basicAuthHeader)
   .contentTypeHeader(contentType)
   .userAgentHeader("curl/7.54.0")

 val headers_0 = Map("Expect" -> "100-continue")

 val scn: ScenarioBuilder = scenario("RecordedSimulation")
   .feed(sqlQueryFeeder)
   .exec(http("request_0")
     .post(endpoint)
     .body(StringBody(message)).asJSON
     .headers(headers_0)
     .basicAuth(authUser, authPass)
     .check(status.is(200)))

 setUp(scn.inject(atOnceUsers(requestCount))).protocols(httpProtocol)
}

Now let's break it down:

• private val dbConnectionString = "jdbc:mariadb://localhost:3306/BlazeDemo" - this is the Database connection string via JDBC.
• private val sqlQuery = "SELECT data FROM original_data" - a query for selecting data
• private val sqlUserName = ${your_username} - getting a user, for the DC connection
• private val sqlPassword = ${your_password} - getting a password, for the DC connection
• private val message = "${data}" - This line specifies which SQL Query result field the Gatling feeder should take.
• All private vals that follow a message are Gatling test configs for the endpoint, like user count, request count (1,000 in our case), etc.
• sqlQueryFeeder - the feeder configuration with user/password/query.
• .feed(sqlQueryFeeder) - this feeds the data requests from the SQL.

As you can see the code here is almost the same as in the previous blog post the biggest difference is that we take the POST data directly from the database.

That's it. Let's run our test via the command line:

./gradlew loadTest

View and Analyze Test Results

We have two options to view the test results.

The first is via the command line output and the second is to open a generated HTML report in the browser.

The command line output shows the following (the result is trimmed for saving space):

./gradlew loadTest 
.
.
.
================================================================================
2017-09-09 22:39:48                                           4s elapsed
---- Requests ------------------------------------------------------------------
> Global                                                   (OK=998    KO=2     )
> request_0                                                (OK=998    KO=2     )
---- Errors --------------------------------------------------------------------
> j.i.IOException: Connection reset by peer                           2 (100.0%)

---- RecordedSimulation --------------------------------------------------------
[##########################################################################]100%
          waiting: 0      / active: 0      / done:1000  
================================================================================

22:39:48.177 [GatlingSystem-akka.actor.default-dispatcher-2] INFO io.gatling.core.controller.Controller - StatsEngineStopped
Simulation com.demo.scala.gatling.BlazeMeterGatlingTest completed in 3 seconds
22:39:48.252 [gatling-http-thread-1-16] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-16
22:39:48.252 [gatling-http-thread-1-7] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-7
22:39:48.252 [gatling-http-thread-1-14] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-14
22:39:48.252 [gatling-http-thread-1-8] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-8
22:39:48.252 [gatling-http-thread-1-13] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-13
22:39:48.252 [gatling-http-thread-1-5] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-5
22:39:48.253 [gatling-http-thread-1-2] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-2
22:39:48.253 [gatling-http-thread-1-3] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-3
22:39:48.253 [gatling-http-thread-1-15] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-15
22:39:48.254 [gatling-http-thread-1-10] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-10
22:39:48.254 [gatling-http-thread-1-1] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-1
22:39:48.255 [gatling-http-thread-1-12] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-12
22:39:48.255 [gatling-http-thread-1-9] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-9
22:39:48.256 [gatling-http-thread-1-11] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-11
22:39:48.257 [gatling-http-thread-1-6] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-6
22:39:48.258 [gatling-http-thread-1-4] DEBUG io.netty.buffer.PoolThreadCache - Freed 3 thread-local buffer(s) from thread: gatling-http-thread-1-4
Parsing log file(s)...
22:39:48.278 [main] INFO io.gatling.charts.stats.LogFileReader - Collected ArrayBuffer(/Users/avagyang/Projects/blazedemo/build/gatling-results/blazemetergatlingtest-1504989583686/simulation.log) from blazemetergatlingtest-1504989583686
22:39:48.302 [main] INFO io.gatling.charts.stats.LogFileReader - First pass
22:39:48.355 [main] INFO io.gatling.charts.stats.LogFileReader - First pass done: read 3001 lines
22:39:48.385 [main] INFO io.gatling.charts.stats.LogFileReader - Second pass
22:39:48.586 [main] INFO io.gatling.charts.stats.LogFileReader - Second pass: read 3001 lines
Parsing log file(s) done
Generating reports...

================================================================================
---- Global Information --------------------------------------------------------
> request count                                       1000 (OK=998    KO=2     )
> min response time                                      0 (OK=559    KO=0     )
> max response time                                   2658 (OK=2658   KO=0     )
> mean response time                                  1918 (OK=1922   KO=0     )
> std deviation                                        438 (OK=430    KO=0     )
> response time 50th percentile                       1987 (OK=1989   KO=0     )
> response time 75th percentile                       2257 (OK=2258   KO=0     )
> response time 95th percentile                       2462 (OK=2464   KO=0     )
> response time 99th percentile                       2618 (OK=2618   KO=0     )
> mean requests/sec                                    250 (OK=249.5  KO=0.5   )
---- Response Time Distribution ------------------------------------------------
> t < 800 ms                                             8 (  1%)
> 800 ms < t < 1200 ms                                  72 (  7%)
> t > 1200 ms                                          918 ( 92%)
> failed                                                 2 (  0%)
---- Errors --------------------------------------------------------------------
> j.i.IOException: Connection reset by peer                           2 (100.0%)
================================================================================

Reports generated in 1s.
Please open the following file: /Users/avagyang/Projects/blazedemo/build/gatling-results/blazemetergatlingtest-1504989583686/index.html

BUILD SUCCESSFUL

Total time: 10.638 secs

The HTML Report (the URL to the HTML report is shown at the end of every execution).

This report provides us with a lot of information. For example, our API survived 998 requests out of 1000 requests. These are good numbers, but our threshold was for 1,000 requests. Therefore, our API performance needs to be improved. We can also see we had 72 requests executed in less than 1200ms and more than 800ms, and 918 requests which took more than 1200ms.

That's it! You now know how to use Gatling to load test APIs with a database interaction.