Load testing SQL databases

In performance testing, we often trigger load tests that simulate realistic user flows, particularly those that are most commonly seen in production. This type of acceptance testing usually interacts with various parts of our infrastructure: web servers, microservices, databases, etc.

But what if you want to test the performance or scalability of an infrastructure resource in isolation?

In many cases, internal components use custom protocols, and the testing tool needs to support those protocols to test the resource individually. Luckily, with Grafana k6, you can use or create extensions that allow you to test different protocols, such as ZMTQ, SQL, Avro, MLLP, etc.

One of the components that you might want to test separately is the database. Databases play an essential role in the performance of our applications, and they can be the bottleneck when experiencing a high volume of users.

Load testing the database directly could provide you with better insights about the database performance in advance. As a result, you could thoroughly plan out your database architecture and determine how to scale it properly.

In this load testing example, let’s explore how to load test a database using the xk6-sql extension. For simplicity, the tests will be executed using SQLite3 but the extension supports the following relational databases:

• PostgreSQL
• MySQL
• SQLite3
• Microsoft SQL Server

Build

In this section, you will install all the required components and build a k6 binary for SQL.

Install the C compiler for SQLite3

A C compiler is required if you are using SQLite3. Simply install the build-essential package if you are using Debian-based operating system. For Windows users, download the tdm-gcc compiler, extract it, and place it in any directory that you prefer. Then, add the path of the bin folder to Environment Variable as follows:

Install the Golang toolchain

Head over to Golang’s installation page and download the installer based on the operating system of your machine. Once you have installed, run the following command to verify the version.

Copy

go version

You should get information related the version number of Go as well as your system architecture:

Copy

go version go1.16.4 windows/amd64

Build the k6 binary including the SQL extension

For non-SQLite database, run the following command to build the k6 binary:

Copy

xk6 build master --with github.com/grafana/xk6-sql

You need to set CGO_ENABLED to 1 when building for SQLite3 to ensure that C compiler is used:

Copy

CGO_ENABLED=1 xk6 build master --with github.com/grafana/xk6-sql

On Windows platform, you need set it explicitly, using set first to call the build command:

Copy

set CGO_ENABLED=1
xk6 build master --with github.com/grafana/xk6-sql

You should see the following output on your console:

Copy

2021/06/17 14:29:43 [INFO] Temporary folder: C:\Users\wfng\AppData\Local\Temp\buildenv_2021-06-17-1429.359000039
2021/06/17 14:29:43 [INFO] Writing main module: C:\Users\wfng\AppData\Local\Temp\buildenv_2021-06-17-1429.359000039\main.go
2021/06/17 14:29:43 [INFO] Initializing Go module
2021/06/17 14:29:43 [INFO] exec (timeout=10s): C:\Program Files\Go\bin\go.exe mod init k6
go: creating new go.mod: module k6
go: to add module requirements and sums:
        go mod tidy
2021/06/17 14:29:44 [INFO] Pinning versions
2021/06/17 14:29:44 [INFO] exec (timeout=0s): C:\Program Files\Go\bin\go.exe get -d -v go.k6.io/k6@master
go: downloading go.k6.io/k6 v0.32.1-0.20210616133500-9f3dd60fbdc1
go get: added go.k6.io/k6 v0.32.1-0.20210616133500-9f3dd60fbdc1
2021/06/17 14:30:50 [INFO] exec (timeout=0s): C:\Program Files\Go\bin\go.exe get -d -v github.com/grafana/xk6-sql
go get: added github.com/grafana/xk6-sql v0.0.0-20210517160107-d222ad8b93eb
2021/06/17 14:30:52 [INFO] Build environment ready
2021/06/17 14:30:52 [INFO] Building k6
2021/06/17 14:30:52 [INFO] exec (timeout=0s): C:\Program Files\Go\bin\go.exe mod tidy
2021/06/17 14:30:56 [INFO] exec (timeout=0s): C:\Program Files\Go\bin\go.exe build -o C:\Users\wfng\Documents\k6_test\k6.exe -ldflags -w -s -trimpath
2021/06/17 14:31:15 [INFO] Build complete: .\k6.exe
2021/06/17 14:31:15 [INFO] Cleaning up temporary folder: C:\Users\wfng\AppData\Local\Temp\buildenv_2021-06-17-1429.359000039

After that, you should have a new k6 binary in your working directory. Since I am building on the Windows platform, I got the k6.exe executable file.

k6 script

You need to write a JavaScript file in order to perform load testing with k6. Let’s have a look at an example of a simple test script for load testing API via HTTP:

Copy

import http from 'k6/http';
import { sleep } from 'k6';

export default function () {
  http.get('https://test.k6.io');
  sleep(1);
}

Each test script requires a default function which will be executed over and over again during testing. The script above makes a GET call to our own k6 test API and sleeps for a second on each execution for a single VU.

For load testing a database, all you need to do is to import the SQL module that you have created earlier and write the corresponding code inside the default function.

Create a new JavaScript file called script.js in the same directory as your k6 binary file.

Import SQL module

You can import your newly created SQL module by adding this line to script.js:

Copy

import sql from 'k6/x/sql';

The naming is based on what has been defined in the Go file. In this case, it is defined as k6/x/sql.

Connect to database

You can easily connect to your database by calling the sql.open function:

Copy

const db = sql.open('sqlite3', './test.db');

It accepts two input parameters:

• type - the type of database (mysql, postgres, sqlite3, sqlserver)
• name - the name of the database

Setup and teardown functions

Before executing a SQL command, let’s explore a little more about the k6 test life cycle. It typically follows this structure:

Copy

// 1. init code (call once per VU)

export function setup() {
  // 2. setup code (call once at the beginning of test)
}

export default function (data) {
  // 3. VU code
}

export function teardown(data) {
  // 4. teardown code (call once at the end of test)
}

You can add in any init code right before the setup, default function, and teardown. Init code serves as the initialization and will be called once for each virtual user (VU).

Also, you can specify a setup function which is called once at the beginning of the test where VU is 0. On the other hand, teardown is called once at the end of the test.

As explained earlier, the default function serves as the VU code which will be executed continuously during testing.

Execute SQL command

After connecting to the database, you can use the db object and call the exec to run any SQL command.

For example, as part of the setup process, before the “load” runs, you can create a new table and insert a few rows of data into the table as follows:

Copy

export function setup() {
  db.exec(`CREATE TABLE IF NOT EXISTS person (
           id integer PRIMARY KEY AUTOINCREMENT,
           email varchar NOT NULL,
           first_name varchar,
           last_name varchar);`);

  db.exec(
    "INSERT INTO person (email, first_name, last_name) VALUES('johndoe@email.com', 'John', 'Doe');"
  );
  db.exec(
    "INSERT INTO person (email, first_name, last_name) VALUES('marysue@email.com', 'Mary', 'Sue');"
  );
  db.exec(
    "INSERT INTO person (email, first_name, last_name) VALUES('dorydoe@email.com', 'Dory', 'Doe');"
  );
}

And you should not forget to clean up the database at the end of the test with the teardown function. This example deletes the table and closes the database connection:

Copy

export function teardown() {
  db.exec('DELETE FROM person;');
  db.exec('DROP TABLE person;');
  db.close();
}

Query data from database

You can easily query the output with the query function. Let’s use it as part of load testing to determine how many iterations you can get when querying the database:

Copy

export default function () {
  const results = sql.query(db, 'SELECT * FROM person;');
}

As usual, you can run the check statement to determine the output. Let’s do a simple check on the total rows of data in the database:

Copy

import sql from 'k6/x/sql';
import { check } from 'k6';

// additional...

export default function () {
  const results = sql.query(db, 'SELECT * FROM person;');
  check(results, {
    'is length 3': (r) => r.length === 3,
  });
}

The complete script code is as follows:

Copy

import sql from 'k6/x/sql';
import { check } from 'k6';

const db = sql.open('sqlite3', './test.db');

export function setup() {
  db.exec(`CREATE TABLE IF NOT EXISTS person (
           id integer PRIMARY KEY AUTOINCREMENT,
           email varchar NOT NULL,
           first_name varchar,
           last_name varchar);`);

  db.exec(
    "INSERT INTO person (email, first_name, last_name) VALUES('johndoe@email.com', 'John', 'Doe');"
  );
  db.exec(
    "INSERT INTO person (email, first_name, last_name) VALUES('marysue@email.com', 'Mary', 'Sue');"
  );
  db.exec(
    "INSERT INTO person (email, first_name, last_name) VALUES('dorydoe@email.com', 'Dory', 'Doe');"
  );
}

export function teardown() {
  db.exec('DELETE FROM person;');
  db.exec('DROP TABLE person;');
  db.close();
}

export default function () {
  const results = sql.query(db, 'SELECT * FROM person;');
  check(results, {
    'is length 3': (r) => r.length === 3,
  });
}

Running the test

Once we have the completed script, you can run the test. Let’s start running the load test for 5 seconds:

Copy

k6 run script.js --duration 5s

By default, it is using just one virtual user (VU) but you can modify it via --vus flag. You should see the following output:

Copy

          /\      |‾‾| /‾‾/   /‾‾/
     /\  /  \     |  |/  /   /  /
    /  \/    \    |     (   /   ‾‾\
   /          \   |  |\  \ |  (‾)  |
  / __________ \  |__| \__\ \_____/ .io

  execution: local
     script: script.js
     output: -

  scenarios: (100.00%) 1 scenario, 1 max VUs, 35s max duration (incl. graceful stop):
           * default: 1 looping VUs for 5s (gracefulStop: 30s)


running (05.1s), 0/1 VUs, 34467 complete and 0 interrupted iterations
default ✓ [======================================] 1 VUs  5s

     ✓ is length 3

     █ setup

     █ teardown

     checks...............: 100.00% ✓ 34467       ✗ 0
     data_received........: 0 B     0 B/s
     data_sent............: 0 B     0 B/s
     iteration_duration...: avg=143.57µs min=0s med=0s max=43.24ms p(90)=519.2µs p(95)=985.47µs
     iterations...........: 34467   6812.032587/s
     vus..................: 1       min=1         max=1
     vus_max..............: 1       min=1         max=1

In this case, it shows that the database can handle about 6812 queries per second and average time of 144µs per iteration.

Scale the load

In the previous test, you have specified just a single virtual user. Let’s scale it up to 10 and see how SQLite performs. Run the following command:

Copy

k6 run script.js --duration 5s --vus 10

You should get the following result:

Copy

running (05.1s), 00/10 VUs, 43228 complete and 0 interrupted iterations
default ✓ [======================================] 10 VUs  5s

    ✓ is length 3

    █ setup

    █ teardown

    checks...............: 100.00% ✓ 43228      ✗ 0
    data_received........: 0 B  0 B/s
    data_sent............: 0 B  0 B/s
    iteration_duration...: avg=1.16ms min=0s med=0s max=136.03ms p(90)=522.5µs p(95)=570.15µs
    iterations...........: 43228   8446.461494/s
    vus..................: 10   min=10      max=10
    vus_max..............: 10   min=10      max=10

Let’s continue the test and set the VU to 100 this time.

Copy

k6 run script.js --duration 5s --vus 100

The output is as follows:

Copy

default ✓ [======================================] 100 VUs  5s

    ✓ is length 3

    █ setup

    █ teardown

    checks...............: 100.00% ✓ 97490      ✗ 0
    data_received........: 0 B  0 B/s
    data_sent............: 0 B  0 B/s
    iteration_duration...: avg=5.07ms min=0s med=506.55µs max=140.07ms p(90)=18.13ms p(95)=28.58ms
    iterations...........: 97490   19034.709634/s
    vus..................: 100  min=100     max=100
    vus_max..............: 100  min=100     max=100

It indicates that SQLite is capable of supporting 100 users with an average duration of 5.07ms per transaction.

For actual use cases, you should continue to scale it up to the point where it will congest your database and cause it to breakdown. This allows you to have a better idea of the maximum limit of your database.

If you are new to k6, check out how to configure the load options in the script or run a stress test with k6.

About k6 extensions

For your information, you can combine multiple extensions and build your own custom k6 binary. For example, you can use the following command to build a k6 binary for both sql and redis:

Copy

xk6 build v0.32.0 --with github.com/dgzlopes/xk6-redis --with github.com/grafana/xk6-sql

With this tutorial, I wanted to show you how easy it is to load test your database or other dependencies separately with k6. If you have any questions or are interested in building an extension, join the k6 community on Slack.

Grafana Cloud is the easiest way to get started with Grafana k6 and performance testing. We have a generous forever-free tier and plans for every use case. Sign up for free now!