- Feb 15, 2022
- 6 min read

Snowflake Load Testing using Apache Jmeter

Updated: Mar 16

Author: Onkar Bongirwar

Introduction:

Load Testing is a type of performance testing in which the system's reaction is tested under varied load situations. The technique of putting a software system or computing device under load and measuring its response is known as load testing. It aids in determining an application's maximum capacity, as well as any bottlenecks and which element causes degradation. Load testing can also be done in the field to get a better picture of how well a system works in real time.

There are various tools available for Load Testing like Apache JMeter, LoadView, MicroFocus, LoadRunner, and others for testing. However, this blog focuses on load testing using Apache Jmeter.

Apache Jmeter is an open-source load and performance testing tool. JMeter simulates the results of Database/browser behavior by sending requests to the application/Database.

Commonly used to measure the performance of a system.

Snowflake provides a Data Cloud that is powered by an advanced data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional database offerings.

Database Load testing involves simulating real-life user load for the target Database application.

It's a testing process in which the performance of a Database/Datawarehouse is tested under a specific expected load.
It determines how the Database/Datawarehou behaves while being accessed by multiple users simultaneously.
The goal of Load Testing is to improve performance bottlenecks and to ensure stability and smooth functioning of the Database/Data Warehouse before deployment.

Installation Process:

Step 1) Install Java

JMeter is a pure Java desktop application; it requires a fully compliant JVM 6 or higher version. Download and install the latest version of Java SE Development Kit Here.

Step 2) Download Jmeter

You can download it Here.

Choose the Binaries file (either zip or tgz) to download, as shown in the figure below.

Step 3) Installation of Jmeter

Simply unzip the zip/tar file into the directory where you want JMeter to be installed.

Step 4) Connect Snowflake with Jmeter.

In order to connect Snowflake with jmeter, we need to follow certain steps:

A) Go to the Snowflake help section-->Downloads-->JDBC Driver and click on Maven repository.

B) You will find various versions of Snowflake JDBC drivers, Apache jmeter supports 3.12.3 version and higher. Download the jar file as mentioned below

C) Paste the downloaded executable jar file in lib path of Jmeter

Step 5) Launch JMeter in GUI Mode

For Windows users, just run the file in

( C:\Program Files\Java\jdk1.8.0_271\bin) to start JMeter in GUI mode as shown below

Following all the above steps will result in the successful setup of apache Jmeter and its connection to Snowflake.

Load Testing Process:

Step 1. After successful installation, Click the jmeter.bat or ApacheJMeter.jar executables in the bin folder and launch the JMeter.

Step 2. Clicking on the batch file will pop up the main screen of Apache Jmeter.

Load Testing Process For “Select” Operation:

Step 3. On this screen, we need to add the following elements of Jmeter and start the test.

Thread group element
JDBC connection configuration
JDBC request
Duration assertion
Listeners

1. Creating a Thread Group Element

The Thread Group is a set of threads/Users that performs a test scenario. We can set the number of users and other similar settings to simulate the user requests. We right-click on the TestPlan, and then we select the Add->Threads (Users)->Thread Group.

On the Thread Group screen:

The Number of Threads (users) specifies how many threads will be used in this load test.
Ramp-Up Period (in seconds) specifies how long the total number of threads will be activated. For example, if we set the Number of Threads to 10 and Ramp-Up Period to 30. In this case, the thread number will increase to one for every 3 seconds, and at the end of the 30 seconds, ten threads will be active
Loop Count specifies the number of times the test will be executed. If we click the Infinite option, the test will run until we stop it explicitly.

Here in our case, we have used Threads= 20,Ramp-Up Period=1 sec and Loop count=1

After creating a thread group, save the load test.

2. JDBC connection configuration

JDBC request element helps to define a SQL query that will be executed by the test user(s). However, before creating a JDBC request, we need a valid database connection. For this, we right-click on the thread group element and then select the Add->Config Element -> JDBC Connection Configuration.

Provide a name for the created pool (Example: Test_Pool), and then we determine the database connection settings

Database URL: jdbc:snowflake://xxx1234.ap.aws.snowflakecomputing.com/

JDBC Driver class: net.snowflake.client.jdbc.SnowflakeDriver

UserName: Database user name

Password: User password

3. Creating JDBC Requests

After completing the JDBC Connection Configuration, we can add a JDBC request.

Right-click on the thread group and then select Add->Sampler->JDBC Request.

In the JDBC Request screen, we set the Variable Name =test_pool as declared in the JDBC Connection Configuration parameter. Write a query into the query panel and save the test.

4. Duration Assertion

The Duration Assertion tests that each server response was received within a given amount of time. Any response that takes longer than the given number of milliseconds (specified by the user) is marked as a failed response.

For example, a user request is sent to a Snowflake database by JMeter and gets a response within the expected time of 2 minutes; then the test Case gets passed, or else the test case fails.

After the successful addition of duration assertion, assign the duration value.

In our case, assign value to 120000 milliseconds(2 minutes)

5. Creating a Listener to Monitor the Test Results

Before starting our test sample, we need a Listener that helps to monitor and analyze the result of the test. For this load test, we will use two types of listeners.

1. Right-click on the JDBC Request and select Add->Listener->View Results Tree to monitor the detailed result of the test.

In our case, all the results have been passed within 2 minutes duration and no error, hence jdbc requests are with Green Mark, else if one of the cases had failed, it would be with Red Mark.

Right-click on the JDBC Request and select Add->Listener->View Results Tree to monitor the detailed result of the test.

2. Right-click on the JDBC Request and select Add->Listener->View Results in the table to get the required measures of the test.

The View Result in Table listener displays information about each sample in the form of a table. The table shows time-related data for each sample, the payload data, the thread number, and the sample execution result. Results in the table are in the order of the time samples were issued. This listener is useful for getting a quick estimation of the behavior of the system under test.

Below are the results for our test case:

Load Testing Process For “Insert” Operation:

Similar to the select operation, we can perform an Insert operation where we can determine whether a number of records can be inserted in a database object in a specific duration of time.

In our case, we have the Number of users/Thread=1, Number of records to be inserted=70

Note: Configuration for Insert operation remains similar with some minor changes to what we have done for select operation

1. Thread group element:

Number of Threads/Users=1

2. JDBC connection configuration

3. JDBC request

The major change is to change the Query Type=AutoCommit(false). It will trigger the insert operation at a Snowflake and won't commit it.

4. Duration assertion:

Set the Duration in milliseconds=60000(1 Minute)

5. Listeners:

Using the View result in table Listener, we have verified that the test case for the insert operation has been successfully completed within 1 minute duration.

Major Challenges :

1. Snowflake Jar Files Missing

If we don't have a proper setup for Apache Jmeter and Snowflake.

Will get an error message as shown below in the listeners, and test cases won’t start.

Fixing this Error:

To overcome this challenge, we need to put Snowflake JDBC JAR file in the Apache jmeter LIB folder to make a successful connection and execute the test cases.

Note: Detailed process is mentioned in Step 4 of the installation process above.

2. Test failed! java.lang.OutOfMemoryError: Java heap space

Fixing this Error:

There are several steps to overcome this issue:

1. Increase the Java Heap Size

The JMeter process runs in the Java Virtual Machine (JVM) under various settings and arguments. The Java Heap Space (as referred to in the error message) is the memory the JVM takes from the underlying operating system to allocate space for the creation of necessary objects.

JMeter’s default configuration assumes a heap space of 1024 megabytes only. This is actually pretty low.

We have increased the maximum heap size to ~80% of your total available physical RAM. To do this, find the following line in your JMeter startup script:

HEAP=-Xms1g -Xmx1g and change the -Xmx value accordingly.

In our case, we have increased the maximum limit to 12 gb (80% of the total available RAM for the system).

2. Using Listeners Wisely

Minimal Listeners should be used during load tests. Enabling all of them causes additional overheads, which consume valuable resources that are needed by more important elements of your test.

As in our case, we have used just two listeners, which is necessary, to avoid adding unwanted listeners.

3. Use Assertions Sparingly

Every test element added to the test plan will be processed - and this takes CPU time and memory. This also applies to all your Assertions; only use the assertions you need and, even then, just use the amount that is absolutely required.

As in our case, we have just used one duration Assertion, which is required.

4. Run Tests in Non-GUI Mode

Try to run a load test from a command prompt instead of a GUI. Use GUI only for test recording. This will help in saving some memory.