Performance Tuning Guide

10.5.8
- 10.5.7
- 10.5.6
- 10.5

Back Next

SQL Data Service Properties for Memory and Concurrent Requests

To optimize SQL data service performance, configure concurrency and memory properties for the Data Integration Service in the Administrator tool.

The following table describes the maximum memory per request property for the SQL Service Module:

Property	Description
Maximum Memory Per Request	The behavior of Maximum Memory Per Request depends on the following Data Integration Service configurations: The service runs jobs in separate local or remote processes, or the service property Maximum Memory Size is 0 (default). In this case, Maximum Memory Per Request is the maximum amount of memory, in bytes, that the Data Integration Service can allocate to all transformations that use auto cache mode in a single request. The service allocates memory separately to transformations that have a specific cache size. The total memory used by the request can exceed the value of Maximum Memory Per Request. The service runs jobs in the Data Integration Service process, and the service property Maximum Memory Size is greater than 0. In this case, Maximum Memory Per Request is the maximum amount of memory, in bytes, that the Data Integration Service can allocate to a single request. The total memory used by the request cannot exceed the value of Maximum Memory Per Request. Default is 50,000,000.

Property

Description

Maximum Memory Per Request

The behavior of Maximum Memory Per Request depends on the following Data Integration Service configurations:

The service runs jobs in separate local or remote processes, or the service property Maximum Memory Size is 0 (default).

In this case, Maximum Memory Per Request is the maximum amount of memory, in bytes, that the Data Integration Service can allocate to all transformations that use auto cache mode in a single request. The service allocates memory separately to transformations that have a specific cache size. The total memory used by the request can exceed the value of Maximum Memory Per Request.

The service runs jobs in the Data Integration Service process, and the service property Maximum Memory Size is greater than 0.

In this case, Maximum Memory Per Request is the maximum amount of memory, in bytes, that the Data Integration Service can allocate to a single request. The total memory used by the request cannot exceed the value of Maximum Memory Per Request.

Default is 50,000,000.

The following table describes the maximum heap size property for the Data Integration Service process:

Property	Description
Maximum Heap Size	Amount of RAM allocated to the Java Virtual Machine (JVM) that runs the Data Integration Service. Use this property to increase the performance. Append one of the following letters to the value to specify the units: b for bytes. k for kilobytes. m for megabytes. g for gigabytes. Default is 1024 megabytes. Consider increasing the heap size when the Data Integration Service needs to process large amounts of data.

Property

Description

Maximum Heap Size

Amount of RAM allocated to the Java Virtual Machine (JVM) that runs the Data Integration Service. Use this property to increase the performance. Append one of the following letters to the value to specify the units:

b for bytes.

k for kilobytes.

m for megabytes.

g for gigabytes.

Default is 1024 megabytes.

Consider increasing the heap size when the Data Integration Service needs to process large amounts of data.

The following table describes the SQL properties for the Data Integration Service process:

Property	Description
Maximum # of Concurrent Connections	Limits the number of database connections that the Data Integration Service can make for SQL data services. Default is 100.

The following table describes the execution options for the Data Integration Service:

Property	Description
Maximum On-Demand Execution Pool Size	Maximum number of on-demand jobs that can run concurrently. Jobs include data previews, profiling jobs, REST and SQL queries, web service requests, and mappings run from the Developer tool. All jobs that the Data Integration Service receives contribute to the on-demand pool size. The Data Integration Service immediately runs on-demand jobs if enough resources are available. Otherwise, the Data Integration Service rejects the job. Default is 10.
Maximum Native Batch Execution Pool Size	Maximum number of deployed jobs that can run concurrently in the native environment. The Data Integration Service moves native mapping jobs from the queue to the native job pool when enough resources are available. Default is 10.
Maximum Hadoop Batch Execution Pool Size	Maximum number of deployed jobs that can run concurrently in the Hadoop environment. The Data Integration Service moves Hadoop jobs from the queue to the Hadoop job pool when enough resources are available. Default is 100.
Maximum Memory Size	Maximum amount of memory, in bytes, that the Data Integration Service can allocate for running all requests concurrently when the service runs jobs in the Data Integration Service process. When the Data Integration Service runs jobs in separate local or remote processes, the service ignores this value. If you do not want to limit the amount of memory the Data Integration Service can allocate, set this property to 0. If the value is greater than 0, the Data Integration Service uses the property to calculate the maximum total memory allowed for running all requests concurrently. The Data Integration Service calculates the maximum total memory as follows: Maximum Memory Size + Maximum Heap Size + memory required for loading program components Default is 0. If you run profiles or data quality mappings, set this property to 0.