Benchmark tests for concurrent reloads using Qlik Data Gateway - Direct Access

[Charlton_Book]

Introduction

The goal of these tests was to assess the performance and behavior of Qlik Data Gateway - Direct Access (hereafter Direct Access gateway) when installed on machines with limited resources.

Direct Access gateway 1.7.2 was used for these tests since it was the latest version available at the time. Later versions might provide improved performance.

Test environment

We conducted these tests on two resource-constrained machines with configurations detailed in the table below. To minimize the effects of network latency, these machines, the source database, and the Qlik Cloud Services staging tenant were all located in the US East region.

Name	Windows Server	No. of CPUs	Memory (GB)	Storage (GB)
Minimal	Windows 2022	2	8	32
Small	Windows 2022	4	8	32

Database description

We used a SQL Server database called benchmark containing four tables, each with a varying number of entries. Detailed descriptions of these tables are provided in the table below.

Table name	Number of entries
sample_table_1m	1,000,000
sample_table_5m	5,000,000
sample_table_10m	10,000,000
Sample_table_48m	48,000,000

Each table contained ten columns and was created using the following SQL Query:

The data in each table was generated randomly using the following SQL Query:

Test scenarios

The following tests were executed using the Qlik SQL Server Connector.

Multiple parallel reloads

In this scenario, we ran multiple reloads in parallel to determine the maximum number of concurrent reloads each machine was able to handle.

Minimal machine results

The results for the minimal machine are presented below.

Table name	3 reloads	5 reloads	10 reloads	15 reloads
sample_table_10m	2m21s	3m11s	6m13s	2m13s
			6m21s	8m1s
	2m54s	3m19s	6m29s	8m26s
			7m2s	8m26s
		3m44s	7m3s	8m26s
			7m11s	9m7s
	2m54s	7m52s	7m19s	9m15s
			7m44s	9m16s
		8m8s	7m44s	9m24s
				9m49s
				14m30s
			8m17s	15m11s
				16m1s
				16m17s
				16m51s
sample_table_48m	14m38s	21m56s	26m	> 1h
				> 1h
				> 1h
			26m	> 1h
		22m21s	26m	> 1h
		22m21s	27m	> 1h
	19m28s	23m2s	27m	> 1h
	19m28s	24m	DirectAccess-4025	> 1h
			31m	> 1h
			33m	> 1h
			34m	> 1h
			34m	DirectAccess-4025
				DirectAccess-4025
				DirectAccess-2009
				Closing data chunk stream from client

Findings

It seems then, that the safe number of concurrent reloads for the minimal machine is 13. In some cases, a few reloads failed with the DirectAccess-4025 error. However, when reloads were performed with chunk recovery (RELOAD_CACHE_MEMORY_MB=100) enabled, they were always successful.

Small machine results

The results for the small machine are presented below.

Table name	3 reloads	5 reloads	10 reloads	15 reloads
sample_table_10m	1m48s	1m56s	4m58s	5m57s
			5m23s	5m57s
	2m13s	2m54s	5m32s	7m19s
			5m40s	7m19s
		3m27s	5m48s	7m19s
			7m36s	8m1s
	4m50s	3m43s	7m52s	8m17s
			8m50s	8m26s
		3m43s	9m6s	8m34s
				8m34s
				8m42s
			9m6s	8m50s
				9m7s
				9m15s
				9m15s
sample_table_48m				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h
				> 1h

Findings

During the testing, CPU utilization was nearly 100%. However, memory usage did not differ significantly between tests. At its highest, memory usage reached only half of the available memory.

Large reloads

In this scenario, we performed reloads with large amounts of data (1 million-10 million rows).

Minimal machine results

The results for the minimal machine are presented below.

Table name	Average fetch time
sample_table_1m	11
sample_table_5m	62
sample_table_10m	80

Small machine results

The results for the small machine are presented below.

Table name	Average fetch time
sample_table_1m	3
sample_table_5m	14
sample_table_10m	30

Conclusion

Based on the benchmark tests, Direct Access gateway can safely run a maximum of 13 concurrent reloads on a minimal Windows machine, although it will take less time to run them on a small machine.