This short tutorial shows how to run a k6 test for load testing a database.
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 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 tutorial, 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:
- Microsoft SQL Server
In this section, you will install all the required components and build a k6 binary for SQL.
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:
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.
You should get information related the version number of Go as well as your system architecture:
For non-SQLite database, run the following command to build the k6 binary:
You need to set CGO_ENABLED to 1 when building for SQLite3 to ensure that C compiler is used:
On Windows platform, you need set it explicitly, using set first to call the build command:
You should see the following output on your console:
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.
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.
You can import your newly created SQL module by adding this line to script.js:
The naming is based on what has been defined in the Go file. In this case, it is defined as k6/x/sql.
You can easily connect to your database by calling the sql.open function:
It accepts two input parameters:
- type - the type of database (mysql, postgres, sqlite3, sqlserver)
- name - the name of the database
Before executing a SQL command, let’s explore a little more about the k6 test life cycle. It typically follows this structure:
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:
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:
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:
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:
The complete script code is as follows:
Once we have the completed script, you can run the test. Let’s start running the load test for 5 seconds:
By default, it is using just one virtual user (VU) but you can modify it via --vus flag. You should see the following output:
In this case, it shows that the database can handle about 6812 queries per second and average time of 144µs per iteration.
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:
You should get the following result:
Let’s continue the test and set the VU to 100 this time.
The output is as follows:
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 on the maximum limit of your database.
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:
Simply head over to the bundle builder page to generate the corresponding command based on your own use cases.
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.