Configuring Thread Pools

SAP ASE records individual thread pool information in the configuration file under the Thead Pool heading.

By default, SAP ASE includes a set of system thread pools that are required for it to function. These include the syb_default_pool engine pool and multiple run to completion (RTC) thread pools, which do not contain engines. Users can create their own thread pools in addition to the system pools. User created thread pools are always engine pools.

At start-up, SAP ASE lists information for syb_default_pool and syb_blocking_pool, with their parameters set to the default values; the information for syb_system_pool is not included because Adpative Server calculates its number of threads at runtime, based on other configuration demands.

The configuration file lists these parameters for all thread pools:

number of threads – configured number of threads in the pool.
description – brief description of pool.

When SAP ASE starts, this is the default thread pool configuration:

[Thread Pool:syb_blocking_pool]
        number of threads = 4

[Thread Pool:syb_default_pool]
        number of threads = 1

As you add or remove thread pools, SAP ASE updates the configuration file, but does not require a restart for the changes to take effect. User-created thread pools (that you create with create thread pool) must be engine pools.

This example includes the two SAP-provided thread pools and a user-created thread pool, sales_pool:

[Thread Pool:sales_pool]
        description = pool for the sales force
        number of threads = 14

        idle timeout = 75

[Thread Pool:syb_blocking_pool]
         number of threads = 20

[Thread Pool:syb_default_pool]
        number of threads = 1

Use a file editor to edit the thread pool information in the configuration file, or use the create thread pool, alter thread pool, and drop thread pool commands to administer thread pools. See the Reference Manual: Commands.

If you edit the configuration file, SAP ASE starts using the new thread pool configuration, printing any change information to the log file (if you add thread pools with create thread pool, you need not restart SAP ASE). This output is from the log file after adding the smaller_pool thread pool to SAP ASE:

00:0000:00000:00000:2010/06/03 16:09:56.22 kernel  Create Thread Pool 4, "smaller_pool", type="Engine (Multiplexed)", with 10 threads

In isql, use create thread pool to add thread pools. This example adds the sales_pool thread pool with 5 threads:

create thread pool sales_pool with thread count = 1

Use sp_helpthread to determine the runtime values for thread pools, including syb_system_pool. This is the sp_helpthread output for the thread pools above:

sp_helpthread

Name                Type                  Size   IdleTimeout
          Description
-----------------   --------------------  ----   -----------
          --------------------------------------------
sales_pool          Engine (Multiplexed)     1           100
          NULL
syb_blocking_pool   Run To Completion        4             0
          A pool dedicated to executing blocking calls
syb_default_pool    Engine (Multiplexed)     1           100
          The default pool to run query sessions
syb_system_pool     Run To Completion        4             0
          The I/O and system task pool

To drop the sales_pool thread pool, use:

drop thread pool sales_pool

See the Reference Manual: Commands.

You may see a message similar to this when you create a thread pool with an insufficient amount of memory (determined with kernel resource memory) to create the threads and there are an insufficient number of engines available:

00:0001:00000:00011:2010/06/11 14:46:38.32 kernel  Setting console to nonblocking mode.
00:0001:00000:00011:2010/06/11 14:46:38.32 kernel  Create thread pool pubs_pool
00:0001:00000:00011:2010/06/11 14:46:38.32 kernel  Create Thread Pool 4, "pubs_pool", type="THREADPOOL_MULTIPLEXED", with 2 threads
00:0001:00000:00011:2010/06/11 14:46:38.32 kernel  could not allocate memory for dynamic engine
00:0001:00000:00011:2010/06/11 14:46:38.32 kernel  Thread creation failed to allocate an engine.
00:0001:00000:00011:2010/06/11 14:46:38.32 server  Configuration file '/new_user/siena.cfg' has been written and the previous version has been renamed to '/new_user/siena.009'.
1> 00:0025:00000:00000:2010/06/11 14:46:38.33 kernel  Network and device connection limit is 1009.
00:0025:00000:00000:2010/06/11 14:46:38.33 kernel  ASE - Dynamic Pluggable Component Interface is disabled
00:0025:00000:00000:2010/06/11 14:46:38.33 kernel  Encryption provider initialization succeeded on engine 1.
00:0025:00000:00000:2010/06/11 14:46:38.33 kernel  Thread 25 (LWP 15726) of Threadpool pubs_pool online as engine 1

To create the thread pool, increase the value of max online engines, and restart SAP ASE.