Tuning Sequelink Server Thread Pools
DataDirect SequeLink® Server for DB2 on z/OS
This document provides a technical overview of the architecture of the thread pools used in the SequeLink Server for DB2 on z/OS. Next, it describes the parameters used in configuring the thread pools and provides scenarios of configuring the thread pools for different needs. Finally, this document provides an example of monitoring and tuning the thread pools.
SequeLink Server Architecture on z/OS
The SequeLink Server contains thread pools that work together to execute your workload in the most optimal way:
- Worker thread pool (section 2 in Figure 1)
- DB2 thread pool (section 3 in Figure 1)
SequeLink Server provides different ways to configure these thread pools to fulfill your requirements. For example, your environment might require maximum throughput, minimal response time, or minimal resources, or a combination of these or other factors. SequeLink Server allows you to configure for these requirements, and more.
Figure 1: The worker thread pool and DB2 thread pool process incoming ODBC, JDBC, and ADO.NET connections. |
The worker thread pool (section 2 in Figure 1) can be configured with the configuration parameters described in the following section. A thread pooler task manages this thread pool.
The DB2 thread pool (section 3 in Figure 1) can also be configured using the configuration parameters explained in the following section.
The row of blocks at the bottom of Figure 1 represent other TCBs in the SequeLink Server address space that are used for various purposes, such as a logger and a command operator.
How do these thread pools work? The SequeLink Server is started with a specific number of pre-started threads. When a SequeLink client connects to the SequeLink Server, an available worker thread from the worker thread pool is assigned to this connection to execute client requests. Worker threads can be reused. When the client request connects to DB2, an available DB2 thread in the DB2 thread pool is used to establish the DB2 connection.
As additional clients connect to the SequeLink Server, they are assigned an available worker thread. If a worker thread is unavailable because the workload increases, the worker thread pool can be expanded and new threads are created. During periods of low activity, the worker thread pool can shrink to save resources.
A SequeLink client connection is an ordered set of client requests. Part of the set of client requests can be executed on a particular worker thread, and the next part can be executed on another worker thread. This means that requests for client connections can be switched over from one worker thread to another. There is no one-to-one relationship between SequeLink client connections and worker threads.
When SequeLink client connections need to access DB2, they request an available DB2 thread. A DB2 thread is reusable when it is at a point of consistency. This also means that requests for connections can be distributed over multiple DB2 threads, or in other words, the workload can be switched from one DB2 thread to another at a point of consistency. If an existing DB2 thread is unavailable, a new DB2 thread is created and the DB2 thread pool expands. When DB2 threads are no longer needed, they are released.
SequeLink client requests can be switched over from one worker thread to another in the worker thread pool, and from one DB2 thread to another in the DB2 thread pool. There is no correlation between the number of worker threads, the number of DB2 threads, and the number of incoming SequeLink client connections.
Before we show you how to configure your thread pools to execute workload as optimally as possible, let's examine the parameters SequeLink lets you configure.
SequeLink Service Parameters for Thread Pool Tuning
This section describes the parameters that can be used to tune SequeLink Server thread pools for optimal performance. The parameters are grouped according to the part of the SequeLink architecture they affect:
- Incoming connections (section 1 in Figure 1)
- Worker thread pool (section 2 in Figure 1)
- Pool of DB2 threads (section 3 in Figure 1)
For more information about these parameters, refer to the SequeLink Administration Guide.
Parameters for Tuning Incoming ConnectionsThe following parameters are used to configure how incoming connections are handled by the SequeLink service.
ServiceIdleTimeInt
Specifies the time (in seconds) a connection can be idle before the connection is ended by the SequeLink Server. Because ServiceIdleTimeInt is static, you must restart the SequeLink service to make the change effective.
Valid values are 0 and any value from 60 (seconds) to 1000000 (seconds).
- If the value of ServiceIdleTimeInt is greater than the value of ServiceDeadClntDetInt, the actual value that is used is rounded down to the nearest multiple of ServiceDeadClntDetInt.
- If the value of ServiceIdleTimeInt is less than the value of ServiceDeadClntDetInt, the value of ServiceIdleTimeInt is used.
The default is 0 (idle time checks are disabled).
Type=Static
ServiceMaxSessions
Specifies the maximum number of concurrent sessions a multithreaded SequeLink service accepts.
On z/OS, verify that this parameter does not exceed the value of MAXFILEPROC in BPXPRMxx. The MAXFILEPROC parameter can be changed dynamically.
The default value is 2000.
Type=Dynamic
Parameters for Tuning the Worker Thread Pool
The following parameters are used to configure how the worker thread pool handles SequeLink client requests.
ServiceMinThreads
Specifies the number of pre-started worker threads that will be started in the thread pool. The value should be equal to or less than the value of ServiceMaxThreads.
Valid values are from 6 to 256.
The default is 8.
Type=Static
NOTES:
- A worker thread is a dispatchable unit (a TCB in z/OS terminology).
- Two worker threads in the worker thread pool are reserved for SequeLink Server use only.
ServiceMaxThreads
Specifies the maximum number of worker threads that can be started in the thread pool. This setting is used to define the number of worker threads during peak workloads.
Valid values are from 6 to 256.
The default is 64.
Type=Static
NOTES:
- A worker thread is a dispatchable unit.
- Worker threads started in the thread pool, but not part of the pre-started worker threads, are ended after 60 seconds of inactivity. These threads are also referred to as "volatile" threads.
DataSourceThreadMaxRpc
Specifies the number of client requests (RPCs) that are accepted before the worker thread allocated to the connection is released to the thread pool. Valid values are from 0 to 1000.
If 0 is specified, the thread is released only when the connection ends.
The default is 10.
Type=Dynamic
NOTES:
- Client requests (RPCs) from the SequeLink Client include a wide variety of actions, for example, prepare requests, execute requests, and requests to fetch a block of rows.
- Releasing the worker thread means that the associated DB2 thread is put in the DB2 thread pool and set to reusable if it is at a transaction boundary. The associated connection is put in a wait state and the worker thread can now service other client connections.
DataSourceThreadRpcTimeOut
Specifies the idle time (in milliseconds) for worker threads allocated to connections. When this value is reached, the thread allocated to the connection is released to the thread pool. Valid values are from 0 to 1000000.
Specify 0 to disable the timeout mechanism.
The default is 2000.
Type=Dynamic
NOTES:
- Client requests from the SequeLink Client include a wide variety of actions, for example, prepare requests, execute requests, and requests to fetch a block of rows.
- Releasing the worker thread means that the associated DB2 thread is put in the DB2 thread pool and set to reusable if it is at a transaction boundary. The associated connection is put in a wait state and the worker thread can now service other client connections.
- We recommend that you set this parameter to a value greater than the normally expected roundtrip time on the network.
ServiceThreadLockThreshold
Specifies a percentage of the value of ServiceMaxThreads. When the number of active threads is less than this percentage, a connection that has executed more client requests (RPCs), than DataSourceThreadMaxRpc on the current thread is allowed to lock this thread for another DataSourceThreadRpcTimeOut period.
This parameter can only be set when ServiceConnectionModel=Threadpool.
Valid values are from 0 to 50.
The default is 0.
Type=Dynamic
NOTE: Use this setting to prevent switching of workload over to worker threads when this is unnecessary, for example, during periods of low activity.
Parameters for Tuning the DB2 Thread Pool
The following parameters are used to configure how the DB2 thread pool handles connection requests to DB2.
ServiceDB2MinThreads
Specifies the minimum number of reusable DB2 threads that remain in the DB2 thread pool. These DB2 threads are not released by the release timer routine or by the RRS RELEASE operator command.
Valid values are 0 and any positive integer.
A value of 0 means that all reusable DB2 threads that exceed an age of 300 seconds are released.
The default is 0.
Type=Dynamic
ServiceDB2MaxTransactions
Specifies the number of DB2 transactions a DB2 thread is allowed to service before it is closed. This ensures that when the DB2 thread terminates, the locks set on temporary tables are released.
Valid values are 0 and any positive integer.
A value of 0 means that the DB2 thread is not terminated.
The default is 0.
Type=Dynamic
NOTE: Use this setting only when required to work around the preceding issues.
How Do I Configure the SequeLink Server Thread Pools?
The first step in setting up your SequeLink Server is to determine which connection model (ServiceConnectionModel) to use. This section describes five typical scenarios that can help you decide which connection model is appropriate for your situation. Note that Threadpool and Thread/Connection are the only SequeLink Server connection models that are valid for z/OS.
For most cases, we recommend ServiceConnectionModel = Threadpool. Scenarios 1 through 4 show examples that use this model. Scenario 5 describes a specific case where ServiceConnectionModel = Thread/Connection is appropriate.
Scenario 1: Application Using Connection Pooling
Applications using connection pooling share the following characteristics:
- The number of connections is mostly fixed and stable.
- Each connection has an intensive workload.
- The set of parameters (host/port/dsn/uid/pwd) is fixed per connection.
Example: Application server accessing a database using SequeLink
If the connections of the pool are always connected and have workload available, we recommend starting the SequeLink Server with pre-started worker threads. When the server starts, a certain number of worker threads (specified by ServiceMinThreads) are started in the worker thread pool. These pre-started threads remain available for the life of the SequeLink Server. For example, if the application connection pool is started with 30 connections, we recommend setting the number of pre-started worker threads in the worker thread pool to ServiceMinThreads = 32 (30 worker threads to match the number of connections in the application connection pool + 2 threads for SequeLink use). The thread pool can expand, if necessary, to the maximum number of threads specified by ServiceMaxThreads.
Each time the SequeLink Server makes a DB2 connection as a result of a client request, a DB2 thread is created, or reused if one is available. Because a sufficient number of DB2 threads must be available for the number of connections, set ServiceDB2MinThreads to the same value of ServiceMinThreads and ServiceDB2MaxThreads to the same value of ServiceMaxThreads or higher. Note that ServiceDB2MaxThreads must be less than the IDBACK parameter from DSNZPARM.
These settings ensure that a data pipe is always available for each pooled connection, from the point when the request comes into the SequeLink Server on the worker thread to the point when it leaves the SequeLink Server on the DB2 thread. This strategy helps to keep a connection from the application pool on the same worker thread using the same DB2 thread and minimizes worker thread switching. The DataSourceMaxRpcTimeout and DataSourceThreadMaxRpc influence the switching pace; therefore, set DataSourceThreadMaxRpc = 0 and set DataSourceMaxRpcTimeout to a high value. Switching is unlikely to occur in this configuration, and in the best case, the worker thread is released only at the end of the connection.
IDBACK is the number of DB2 threads allowed for all background applications, in case you have multiple background applications, for example, multiple SequeLink Servers. Define the total of your ServiceDB2MaxThreads settings to be less than IDBACK.
In addition, set ServiceIdleTimeInt to 0 (or a high value) to avoid SequeLink Server closing idle connections.
Scenario 2: Applications Executing Short-Running Requests in DB2
Applications executing short-running requests in DB2 typically share the following characteristics:
- The number of connections is unknown, but can be high.
- The duration for a connection is short.
- Each connection executes a short request in DB2.
- The size of returned information is small.
Example: Data entry application, Web transactions, etc.
The connection overhead in this scenario is high. In these situations, it is important that worker threads and DB2 threads are available to save the expensive overhead of creating worker threads and DB2 threads for each connection. This means that a number of worker threads must be pre-started.
Set ServiceMinThreads to a number that matches the average number of concurrent users for your SequeLink Server, up to a maximum of 70 worker threads. When this number of pre-started worker threads is set to a high value, the resource consumption for scheduling (by the operating system) these worker threads is substantial. Peak load is better managed with increased worker thread switching, rather than creating additional worker threads. To achieve this, set ServiceMaxThreads to a percentage of 20 - 30% higher than ServiceMinThreads. For example, if you set ServiceMinThreads = 30, set ServiceMaxThreads = 40. As a useful rule of thumb for setting these values, set ServiceMinThreads to run your normal workload. Your peak workload is handled by switching the workload over among the worker threads.
For DB2 threads, we recommend setting ServiceDB2MaxThreads to the maximum number of concurrent connections, or higher, so that each connection can have its own DB2 thread, if necessary. Typically, some DB2 threads are in a reusable state, but in the worst-case scenario, each connection may require a DB2 thread.
To save resources in this scenario, set ServiceDB2MinThreads to 50% of the value of ServiceMinThreads. Because of the short-running requests in DB2, sufficient DB2 threads will become available (reusable) to manage the workload. Each DB2 thread that is created when no reusable DB2 threads available are ended after a 5-minute interval of inactivity.
To execute the complete workload and handle the client connections with minimal resources, it is important that the worker threads are kept busy. Switching of the workload among the worker threads in the SequeLink Server becomes very important. This means that the Threadpool connection model must be used. To influence the switching pace, use DataSourceMaxRpcTimeout and DataSourceThreadMaxRpc. An RPC, in this context, is one client request that results in a network roundtrip, such as a prepare request, an execute request, or a request to fetch a block of rows. The lower these values are set, the more switching of work among the worker threads occurs. Switching these contexts uses resources and must be optimized based on your type of workload. The workload, in this case, is characterized by a combination of the number of connections, the amount of work performed by the connection, and the type of work performed by the connection, for example, autocommit vs. manual commit or transactions vs. queries.
Some periods of low activity may occur. In this case, we want to avoid switching work among worker threads because there are enough worker threads available (more than ServiceMinThreads). In other words, the number of worker threads that are busy is a smaller percentage of ServiceMaxThreads. Therefore, use ServiceThreadLockThreshold to keep the work on the fewest worker threads that are being used.
Scenario 3: Applications Executing Long-Running Requests in DB2
Applications executing long-running requests in DB2 typically share the following characteristics:
- The number of connections is unknown, but can be random.
- The duration for a connection is long - for example, multiple hours.
- Each connection executes a long-running request in DB2.
- The size of returned information is large.
- A prolonged ”think time” occurs between requests.
Examples: OLAP tools, batch processing, etc.
In this scenario, we use a connection for a long time for a single user. This is similar to the application connection pooling scenario, except that the workload and the duration of a connection is less predictable. Also, in this scenario, the connection setup overhead is small compared to the connection duration.
Use the Threadpool connection model to save resources:
- When the load is low, worker threads and DB2 threads can be reduced to ServiceMinThreads and ServiceDB2MinThreads.
- When the load is higher, the thread pool can expand to ServiceMaxThreads and ServiceDB2MaxThreads.
As a rule of thumb, run your normal workload with ServiceMinThreads and your peak workload will be handled by ServiceMaxThreads.
In this scenario, we want to keep the worker threads busy. This means that during the ”think time,” we must switch the worker thread to a connection that provides work. To do this, set DataSourceMaxRpcTimeout to a low value, that is, the normal roundtrip time on the network + 20 %.
On the other hand, when fetching large result sets, a high number of client requests will be sent to the server to fetch the next block of rows. We want to keep all these requests on the same worker thread by setting DataSourceThreadMaxRpc to a high value.
In this scenario, you could also test the effect of using ServiceThreadLockThreshold for periods of lower activity.
Scenario 4: A Mixed Workload
A mixed workload is probably the most common scenario used by SequeLink customers. The SequeLink Server must be able to handle batch processing, fast transactions, and OLAP applications.
Make a distinction between configuration parameters with a scope of the service and configuration parameters with a scope of server data sources.
- ServiceMinThreads, ServiceMaxThreads, ServiceThreadLockThreshold, ServiceDB2MinThreads and ServiceDB2MaxThreads are service-wide configuration parameters.
- DataSourceMaxRpcTimeout and DataSourceThreadMaxRpc are server data source-wide configuration parameters.
You can specify service-wide configuration parameters only once in a service. In contrast, server data source-wide configuration parameters can be configured per server data source. This means that you need to determine one specific value for each service-wide configuration parameter. If you have multiple server data sources, you can specify multiple values for the server data source-wide configuration parameters.
How do we do this? This will be an iterative process of defining settings, monitoring your thread pool and adapting the settings. As a first step, take the number that you determined for each scenario and add the numbers.
For example, let's use a combination of the first three scenarios:
- For connection pooled applications: 20 open connections, can grow to 30 connections
- For transaction based applications: an average of 20 concurrent users, up to 30 users.
- For ad-hoc queries: an average of 10 concurrent users, up to maximum 30 concurrent users.
As we defining the service-wide settings in the following example, we know that IDBACK = 200:
ServiceMinThreads: 20 + 20 + 10 = 50
ServiceMaxThreads: 30 + 25 + 30 = 85
ServiceDB2MinThreads: 20 + 20 + 10 = 50
ServiceDB2MaxThreads: 30 + 30 + 30 = 90 < 100 < IDBACK = 200
In this case, we use 100 as a safe setting.
Figure 2: The SequeLink Manager, showing the 8 attributes defined for the Advanced node. |
For each type of application or workload we will create a server-side data source:
- Server-side data source for connection pooled applications: SDSN=POOLAPP
DataSourceMaxRpcTimeout = 1000000
DataSourceThreadMaxRpc = 0 - Server-side data source for transaction based applications: SDSN=TXNAPP
DataSourceMaxRpcTimeout = 500 (the value is greater than the network round-trip)
DataSourceThreadMaxRpc = 20 - Server-side data source for ad-hoc queries: SDSN=ADHOCAPP
DataSourceMaxRpcTimeout = 2000
DataSourceThreadMaxRpc = 500
Figure 3: The SequeLink Manager, showing the server-side data source settings.
|
Scenario 5: Dedicated SequeLink Server for Applications Using Connection Pooling
For specific purposes, you can use ServiceConnectionModel = Thread/Connection. For example, this setting is appropriate if you want to set up a dedicated SequeLink Server for your applications that use connection pooling.
In this case, the connections of the pool are always connected and also have enough workload available. The settings are configured in such a way a data pipe is available for each pooled connection from the point when the request comes into the SequeLink Server on the worker thread up to the point where it leaves the SequeLink Server using the DB2 thread. This means that we do not want any switching of work over worker threads or DB2 threads.
When the server starts up, a number of worker threads are started. These server resources will remain available for the duration of the connection. If the application thread pool is started with 50 connections, we recommend setting ServiceMinThreads = 52 as the number of pre-started worker threads. (50 worker threads to match the number of connections in the application thread pool + 2 threads for use by SequeLink Server). ServiceMaxThreads limits the number of worker threads that can be started. The number of worker threads that can be started in a SequeLink Server is 256.
Each time the SequeLink Server makes a DB2 connection, a DB2 thread is created, or reused if one is available. Because enough DB2 threads must be available for the number of connections in this scenario, set ServiceDB2MinThreads to the same value of ServiceMinThreads and ServiceDB2MaxThreads to ServiceMaxThreads or 0. Note that ServiceDB2MaxThreads should be less than IDBACK .
The thread/connection model ignores the settings that affect the switching in the SequeLink Server, being DataSourceMaxRpcTimeout and DataSourceThreadMaxRpc.
IDBACK is the number of DB2 threads allowed for all background applications. If you have multiple background applications, for example, multiple SequeLink Servers, the sum of your ServiceDB2MaxThreads settings should be smaller than the value of IDBACK.
How Can I Monitor the Thread Pools of my SequeLink Server?
Tuning your SequeLink Server for DB2 on z/OS can be an iterative process. Once the configurations are done according to your estimates, it is necessary to monitor the worker threads and the DB2 threads and eventually change these settings. For more information on these commands, see the SequeLink Administration Guide.
The THPL DISPLAY command shows a summary of the thread pool settings and the relevant server-side data source settings.
| F SLD550T7,THPL DISPLAY | ||||||||
| VAIT020I | MinThrd(050) MaxThrd(085) | ThresHold(10) DB2MinThrd(0050) DB2MaxThrd(0100) | ||||||
| VAIT019I | DataSrc Default | Timeout(02000) MaxRPC(0050) | ||||||
| VAIT019I | DataSrc POOLAPP | Timeout(00000) MaxRPC(0000) | ||||||
| VAIT019I | DataSrc TXNAPP | Timeout(00500) MaxRPC(0020) | ||||||
| VAIT019I | DataSrc ADHOCAPP | Timeout(02000) MaxRPC(0500) | ||||||
| VAII999X | END |
After making one connection to the SequeLink Server, we want to see the use of the worker thread pool.
The command THPL DISPLAY SHOW=STAT provides the necessary information.
| F SLD550T7,THPL DISPLAY SHOW=STAT | ||||||||
| VAIT021I | Thrd(001) | - Rpc(1) %Rpc(10.00) - CpuSec(0.03) %Cpu( 1.22) | ||||||
| VAIT021I | Thrd(002) | - Rpc(1) %Rpc(10.00) - CpuSec(0.03) %Cpu(1.15) | ||||||
| VAIT021I | Thrd(003) | - Rpc(8) %Rpc(80.00) - CpuSec(1.50) %Cpu(62.66) | ||||||
| VAIT022I 082 Threads without any activity CpuSec(0.84) %Cpu(34.96) | ||||||||
NOTE: The first two threads are the listener worker thread and the operator command worker thread.
For this same connection, we want to see the use of the DB2 thread pool. Each DB2 thread is associated to an RRS context, for example, using the command RRS LIST, which lists all RRS contexts associated with an RRSAF DB2 thread in use by the SequeLink Server.
| F SLD550T7,RRS LIST | |||
| VAID060I | ThreadId | RRSAF State | Age |
| VAID061I | T0000003 | INUSE | 0005 |
| VAID062I | Total 001 | DB2Thread(s), peak 001 DB2Threads used | |
Monitoring the DB2 threads can also be done using a DB2 monitor or using the following DB2 command:
/DISPLAY THREAD(*)
Example
Let's use a transaction-based application using the server-side data source TXNAPP. According to the THPL DISPLAY command executed earlier in this section, we can retrieve the thread pool settings: we have 50 pre-started threads, the thread pool can grow to 85 worker threads. We will keep 50 DB2 threads in the DB2 thread pool with a maximum of 100 DB2 threads. The switching pace is set high to keep the worker threads busy.
The following statistical report, retrieved at the end of the workload, shows the use of the worker threads.
| F SLD550T7,THPL DISPLAY SHOW=STAT | ||
| VAIT021I | Thrd(001) | -Rpc(1) %Rpc(0.04)-CpuSec(0.78) %Cpu(0.07) |
| VAIT021I | Thrd(002) | -Rpc(1) %Rpc(0.04)-CpuSec(0.10) %Cpu(0.01) |
| VAIT021I | Thrd(003) | -Rpc(163) %Rpc(6.26)-CpuSec(65.14) %Cpu(6.02) |
| VAIT021I | Thrd(004) | -Rpc(143) %Rpc(5.49)-CpuSec(58.27) %Cpu(5.38) |
| VAIT021I | Thrd(005) | -Rpc(139) %Rpc(5.34)-CpuSec(58.66) %Cpu(5.42) |
| VAIT021I | Thrd(006) | -Rpc(126) %Rpc(4.84)-CpuSec(53.23) %Cpu(4.92) |
| VAIT021I | Thrd(007) | -Rpc(127) %Rpc(4.88)-CpuSec(53.99) %Cpu(4.99) |
| VAIT021I | Thrd(008) | -Rpc(129) %Rpc(4.95)-CpuSec(54.06) %Cpu(4.99) |
| VAIT021I | Thrd(009) | -Rpc(130) %Rpc(4.99)-CpuSec(53.19) %Cpu(4.91) |
| VAIT021I | Thrd(010) | -Rpc(126) %Rpc(4.84)-CpuSec(51.98) %Cpu(4.80) |
| VAIT021I | Thrd(011) | -Rpc(123) %Rpc(4.72)-CpuSec(51.90) %Cpu(4.79) |
| VAIT021I | Thrd(012) | -Rpc(124) %Rpc(4.76)-CpuSec(51.82) %Cpu(4.79) |
| VAIT021I | Thrd(013) | -Rpc(126)%Rpc(4.84)-CpuSec(51.76)%Cpu(4.78) |
| VAIT021I | Thrd(014) | -Rpc(125) %Rpc(4.80)-CpuSec(50.92) %Cpu(4.70) |
| VAIT021I | Thrd(015) | -Rpc(121) %Rpc(4.65)-CpuSec(49.82) %Cpu(4.60) |
| VAIT021I | Thrd(016) | -Rpc(121) %Rpc(4.65)-CpuSec(50.17) %Cpu(4.63) |
| VAIT021I | Thrd(017) | -Rpc(120) %Rpc(4.61)-CpuSec(50.40) %Cpu(4.66) |
| VAIT021I | Thrd(018) | -Rpc(116) %Rpc(4.45)-CpuSec(49.75) %Cpu(4.59) |
| VAIT021I | Thrd(019) | -Rpc(111) %Rpc(4.26)-CpuSec(48.19) %Cpu(4.45) |
| VAIT021I | Thrd(020) | -Rpc(111) %Rpc(4.26)-CpuSec(45.84) %Cpu(4.23) |
| VAIT021I | Thrd(021) | -Rpc(111)%Rpc(4.26)-CpuSec(43.01)%Cpu(3.97) |
| VAIT021I | Thrd(022) | -Rpc(102) %Rpc(3.92)-CpuSec(40.83) %Cpu(3.77) |
| VAIT021I | Thrd(023) | -Rpc(78) %Rpc(3.00)-CpuSec(35.66) %Cpu(3.29) |
| VAIT021I | Thrd(024) | -Rpc(27) %Rpc(1.04)-CpuSec(11.40) %Cpu(1.05) |
| VAIT021I | Thrd(025) | -Rpc(3) %Rpc(0.12)-CpuSec(1.42) %Cpu(0.13) |
| VAIT022I 060 Threads without any activity CpuSec(0.44) %Cpu(0.04) | ||
As we can see, we have used 23 (+ 2 internal worker threads) for our workload.
In this case, we did not use the 50 worker threads. Assuming that this is our only workload, we could limit the number of worker threads to 20, as shown in Figure 4 - the last two or three worker threads don't show the same activity as the first 20 in the thread pool (Thrd 003 - Thrd 022).
Figure 4: A graph of the activity in a SequeLink Server worker thread pool. |
When we look at the DB2 thread pool, we can see that 21 DB2 threads are being used during the workload; some of these DB2 threads are set to INUSE, and others are set to REUSABLE. FLAGFORREUSE is an optimization where the SequeLink Server reuses the DB2 thread that has been used for the previous requests to save a DB2 SIGNON call. The peak load consumes 21 DB2 threads.
| F SLD550T7,RRS LIST | |||||||||||
| VAID060I | Thread Id | RRSAF State | Age | ||||||||
| VAID061I | T0000079 | FLAGFORREUSE | 0004 | ||||||||
| VAID061I | T0000078 | REUSABLE | 0000 | ||||||||
| VAID061I | T0000077 | FLAGFORREUSE | 0002 | ||||||||
| VAID061I | T0000076 | FLAGFORREUSE | 0002 | ||||||||
| VAID061I | T0000081 | INUSE | 0000 | ||||||||
| VAID061I | T0000083 | REUSABLE | 0000 | ||||||||
| VAID061I | T0000082 | FLAGFORREUSE | 0004 | ||||||||
| VAID061I | T0000080 | FLAGFORREUSE | 0005 | ||||||||
| VAID061I | T0000085 | FLAGFORREUSE | 0003 | ||||||||
| VAID061I | T0000084 | INUSE | 0000 | ||||||||
| VAID061I | T0000088 | REUSABLE | 0000 | ||||||||
| VAID061I | T0000091 | FLAGFORREUSE | 0002 | ||||||||
| VAID061I | T0000090 | FLAGFORREUSE | 0002 | ||||||||
| VAID061I | T0000089 | FLAGFORREUSE | 0002 | ||||||||
| VAID061I | T0000094 | FLAGFORREUSE | 0001 | ||||||||
| VAID061I | T0000087 | INUSE | 0000 | ||||||||
| VAID061I | T0000092 | INUSE | 0000 | ||||||||
| VAID061I | T0000095 | FLAGFORREUSE | 0001 | ||||||||
| VAID061I | T0000086 | FLAGFORREUSE | 0003 | ||||||||
| VAID061I | T0000093 | FLAGFORREUSE | 0002 | ||||||||
| VAID061I | T0000096 | FLAGFORREUSE | 0001 | ||||||||
| VAID062I Total 021 DB2Thread(s), peak 021 DB2Threads used | |||||||||||
| VAII999X | END | ||||||||||
At the end of the workload, we see that the DB2 thread pool falls back to 20 threads, as configured with ServiceDB2MinThreads. All of these DB2 threads are set to REUSABLE. If you want to limit the number of DB2 threads used during peak workloads, configure ServiceDB2MaxThreads as needed.
| F SLD550T7,RRS LIST | |||
| VAID060I | Thread Id | RRSAF State | Age |
| VAID061I | T0000217 | REUSABLE | 0045 |
| VAID061I | T0000219 | REUSABLE | 0040 |
| VAID061I | T0000218 | REUSABLE | 0044 |
| VAID061I | T0000216 | REUSABLE | 0047 |
| VAID061I | T0000222 | REUSABLE | 0028 |
| VAID061I | T0000220 | REUSABLE | 0029 |
| VAID061I | T0000221 | REUSABLE | 0029 |
| VAID061I | T0000224 | REUSABLE | 0023 |
| VAID061I | T0000227 | REUSABLE | 0019 |
| VAID061I | T0000223 | REUSABLE | 0025 |
| VAID061I | T0000232 | REUSABLE | 0018 |
| VAID061I | T0000234 | REUSABLE | 0017 |
| VAID061I | T0000226 | REUSABLE | 0018 |
| VAID061I | T0000231 | REUSABLE | 0017 |
| VAID061I | T0000229 | REUSABLE | 0018 |
| VAID061I | T0000230 | REUSABLE | 0018 |
| VAID061I | T0000225 | REUSABLE | 0019 |
| VAID061I | T0000228 | REUSABLE | 0018 |
| VAID061I | T0000235 | REUSABLE | 0017 |
| VAID061I | T0000233 | REUSABLE | 0017 |
| VAID062I Total 020 DB2Thread(s), peak 021 DB2Threads used | |||
| VAII999X | END | ||
Suppose that we had configured a smaller SequeLink Server thread pool, for example, set ServiceMinThreads = 10 and ServiceMaxThreads = 10. The workload would have been executed, but response time might decrease or CPU usage might increase because of increased switching.
Let's test this configuration with the settings shown in Figure 5:
Figure 5: The SequeLink Manager, showing the data source settings for a smaller worker thread pool. |
Running the same workload again produces the following results:
| F SLD550T7,THPL DISPLAY SHOW=STAT | ||
| VAIT021I | Thrd(001) | - Rpc(1) %Rpc(0.04) - CpuSec(0.68) %Cpu(0.07) |
| VAIT021I | Thrd(002) | - Rpc(1) %Rpc(0.04) - CpuSec(0.03) %Cpu(0.00) |
| VAIT021I | Thrd(003) | - Rpc(324) %Rpc(12.69) - CpuSec(125.97) %Cpu(12.70) |
| VAIT021I | Thrd(004) | - Rpc(314) %Rpc(12.30) - CpuSec(124.73) %Cpu(12.58) |
| VAIT021I | Thrd(005) | - Rpc(320) %Rpc(12.53) - CpuSec(125.41) %Cpu(12.64) |
| VAIT021I | Thrd(006) | - Rpc(322) %Rpc(12.61) - CpuSec(123.23) %Cpu(12.42) |
| VAIT021I | Thrd(007) | - Rpc(320) %Rpc(12.53) - CpuSec(122.75) %Cpu(12.38) |
| VAIT021I | Thrd(008) | - Rpc(323) %Rpc(12.65) - CpuSec(123.54) %Cpu(12.46) |
| VAIT021I | Thrd(009) | - Rpc(317) %Rpc(12.42) - CpuSec(121.63) %Cpu(12.26) |
| VAIT021I | Thrd(010) | - Rpc(311) %Rpc(12.18) - CpuSec(123.89) %Cpu(12.49) |
| VAII999X | END | |
Figure 6: A graph of the activity in a smaller SequeLink Server worker thread pool. |
Here we can see clearly that only 10 worker threads were pre-started. Of these, only 8 worker threads were used to answer client requests (RPCs). In contrast to the scenario shown in Figure 4, there is no decrease in number of RPCs; all worker threads have executed an equal amount of RPCs. The number of RPCs is much higher than the number of RPCs in the first run and the CPU time is higher for each worker thread (TCB). This is normal because the same workload has been handled by fewer worker threads. If response time, throughput, and resource usage are satisfactory, you can use these settings. If you want the response time or throughput to increase, increase the value of ServiceMinThreads.
Error Messages
This section describes some of the error messages that can occur when the limits of the worker thread pool or the DB2 thread pool are reached.
ServiceDB2MaxThreads Is Defined Too Small and the IDBACK Setting Is Not Reached
SequeLink Error: 3444
"[DataDirect][ODBC SequeLink driver][SequeLink Server]The maximum number of database threads is reached."
In the SequeLink Server log (VAILOGP), you'll find the following message:
VAID070W T0007078 - Maximum of allowed DB2Threads(0085) reached
ServiceDB2MaxThreads Is Set to 0 (No Limit) and the DB2 IDBACK Value Is Reached
SequeLink Error: 3411
"[DataDirect][ODBC SequeLink driver][SequeLink Server]Thread to DB2 could not be opened. Either a resource is unavailable or the requested resource (plan) is not known to DB2.
This situation can lead to problems when volatile threads need to create an extra DB2 thread and initialize this worker thread for use with DB2. Volatile threads are the worker threads started when all pre-started worker threads are already in use, that is, the worker threads between ServiceMinThreads and ServiceMaxThreads.
Therefore, we recommend always setting ServiceDB2MaxThreads to a specific value, lower than IDBACK.
ServiceMaxSession Has Been Reached
SequeLink Error: 3071
"[DataDirect][ODBC SequeLink driver][SequeLink Server]Maximum number of sessions reached."
In the SequeLink Server log (VAILOGP), you'll find the following message:
VAIL021E T0040522 - SQLLINKP,
ErrorCode=3071,
ErrorMessage=Maximum number of sessions reached.,
ClientHost=10.50.30.108,Session=40522
ServiceMinThreads and ServiceMaxThreads Are Defined Too Low
If ServiceMaxThreads (set greater than ServiceMinThreads) has a very low value, incoming requests might need to wait for an available worker thread. When this happens, a message is shown in the server log (VAILOGP):
VAIT018I ThreadPool has waited 1.10 secs for an available WorkerThread
If these messages occur frequently, either the ServiceMaxThreads parameter is set too low or the DataSourceThreadMaxRpc and/or DataSourceThreadRpcTimeOut parameters are set too high.
Conclusion
Tuning the SequeLink Server involves identifying the customer's requirements, such as maximum throughput, minimal response time, and minimal resources, and configuring the parameters to meet those requirements. This document shows that you can configure the SequeLink Server with multiple server-side data sources. When each server-side data source is configured for a specific workload, the SequeLink Server can execute your workload in the most efficient way.
We welcome your feedback! Please send any comments concerning documentation, including suggestions for other topics that you would like to see, to:
docgroup@datadirect.com
