Thread Pool

enable_thread_pool

Parameter description: Specifies whether to enable the thread pool function. This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on indicates that the thread pool function is enabled.
• off indicates that the thread pool function is disabled.

Default value: off

thread_pool_attr

Parameter description: Specifies the detailed attributes of the thread pool function. This parameter is valid only when enable_thread_pool is set to on. This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: a string, consisting of one or more characters.

This parameter consists of three parts: thread_num, group_num, and cpubind_info. The meanings of the three parts are as follows:

• thread_num indicates the total number of threads in the thread pool. The value ranges from 0 to 4096. The value 0 indicates that the database automatically configures the number of threads in the thread pool based on the number of CPU cores. If the value is greater than 0, the number of threads in the thread pool is the same as the value of thread_num.
• group_num indicates the number of thread groups in the thread pool. The value ranges from 0 to 64. The value 0 indicates that the database automatically configures the number of thread groups in the thread pool based on the number of NUMA groups. If the value is greater than 0, the number of thread groups in the thread pool is the same as the value of group_num.
• cpubind_info indicates whether the thread pool is bound to a core. The available configuration modes are as follows:
  1. '(nobind)': The thread is not bound to a core.
  2. '(allbind)': Use all CPU cores that can be queried in the current system to bind threads.
  3. '(nodebind: 1, 2)': Use the CPU cores in NUMA groups 1 and 2 to bind threads.
  4. '(cpubind: 0-30)': Use the CPU cores 0 to 30 to bind threads. This parameter is case-insensitive.
  5. '(numabind: 0-30)': Use CPU cores 0 to 30 in the NUMA group to bind threads. This parameter is case-insensitive.

Default value: '16, 2, (nobind)'

thread_pool_stream_attr

Parameter description: Specifies the detailed attributes of the stream thread pool function. This parameter is valid only when enable_thread_pool is set to on. Only the sysadmin user can access this parameter.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: String, length greater than 0

This parameter is divided into 4 parts, 'stream_thread_num, stream_proc_ratio ,group_num ,cpubind_info', the specific meanings of these 4 parts are as follows:

• stream_thread_num indicates the total number of threads in the stream thread pool. The value ranges from 0 to 4096. The value 0 indicates that the database automatically configures the number of threads in the thread pool based on the number of CPU cores. If the value is greater than 0, the number of threads in the thread pool is the same as the value of stream_thread_num. You are advised to set the thread pool size based on the hardware configuration. The formula is as follows: Value of stream_thread_num = Number of CPU cores x 3–5. The maximum value of stream_thread_num is 4096.
• stream_proc_ratio indicates the ratio of proc resources reserved for stream threads. The value is a floating point number. The default value is 0.2. The reserved proc resources are calculated as follows: stream_proc_ratio x stream_thread_num.
• group_num indicates the number of thread groups in the thread pool, the value range is 0~64, where 0 means that the database automatically configures the number of thread groups in the thread pool according to the number of NUMA groups in the system, and if the value of the parameter is greater than 0, the number of thread groups in the pool is equal to group_num. thread_pool_stream_attr's group_num should be consistent with the group_num configuration and usage of thread_pool_attr. group_num of thread_pool_attr needs to be consistent with the configuration and usage of group_num of thread_pool_attr. If it is set to a different value, the group_num of thread_pool_attr prevails.
• cpubind_info indicates the configuration parameter for whether the thread pool is core-bound or not. Optional configuration methods are: 1. '(nobind)', threads do not bind cores; 2. '(allbind)', utilize all CPU cores that can be queried by the current system to bind cores for threads; 3. '(nodebind: 1, 2)', utilize CPU cores in NUMA groups 1,2 to bind cores; 4. '(cpubind: 0-30) ', utilize CPU cores 0-30 for core binding; 5. '(numabind: 0-30)', utilize CPU cores 0-30 within NUMA group for core binding. This parameter is not case-sensitive. cpubind_info of thread_pool_stream_attr needs to be consistent with the configuration and usage of cpubind_info of thread_pool_attr. If it is set to a different value, cpubind_info of thread_pool_attr prevails.

Default value:

stream_thread_num: 16

stream_proc_ratio: 0.2

group_num, cpubind_info

resilience_threadpool_reject_cond

Parameter description: Used to control the percentage of stacked sessions for thread pool overload escapes. This parameter only takes effect when the GUC parameters use_workload_manager and enable_thread_pool are turned on.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: String, length greater than 0

This parameter is divided into recover_threadpool_percent, overload_threadpool_percent 2 parts, the specific meaning of these 2 parts is as follows:

• recover_threadpool_percent: the percentage of the number of threads initially set for the thread pool that the thread pool returns to the normal state, when the number of sessions that have been accessed is less than the percentage corresponding to the number of threads initially set for the thread pool multiplied by the value, the overload escape will be stopped and new connections will be released to access the thread pool, the value ranges from 0 to INT_MAX, the value is set to a certain number, which means how many percent.
• overload_threadpool_percent: the percentage of the accessed sessions in the overloaded thread pool, when the number of accessed sessions is greater than the initial number of thread pool multiplied by the corresponding percentage of the value, it means that the current thread pool has been overloaded, and the overload escape kill session is triggered, and the access of new connections is prohibited, the value is 0~INT_MAX, and the value is set to how much indicates the percentage of new connections. MAX, set to how much indicates how many percent.

Default value: '0,0' to disable thread pool escape.

Example:

resilience_threadpool_reject_cond = '100,200'

Indicates that the number of stacked sessions exceeds 200% of the initial number of threads set in the thread pool and then prohibits new connections and kills the stacked sessions, and stops killing sessions and allows new connections when the number of sessions returns to less than 100% of the initial number of threads set in the thread pool during the kill session.

NOTICE:

• The number of sessions that have been piled up can be obtained by querying the pg_stat_activity view for how many pieces of data are available, and a small number of background threads need to be filtered; the number of threads in the thread pool set for the initial trial thread pool can be obtained by querying the thread_pool_attr parameter.
• If the percentage of this parameter is set too small, the thread pool overload escape process will be triggered frequently, which will make the executing session be forced to exit, and the new connection fails to be accessed for a short period of time, so it needs to be set carefully according to the actual thread pool usage.

enable_ios

Parameter description: Controls whether the IOS service is enabled.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on means enabled.
• off means disabled.

Default value: off

enable_heap_async_prefetch

Parameter description: Controls whether pre-fetching is enabled for Astore Full Table Scan class scenarios.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on means enabled.
• off means disabled.

Default value: off (This parameter is invalid when enable_ios = off.)

enable_uheap_async_prefetch

Parameter description: Controls whether pre-fetching is enabled for Ustore Full Table Scan class scenarios.

This parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on means enabled.
• off means disabled.

Default value: off (This parameter is invalid when enable_ios = off.)

ios_worker_num

Parameter description: The number of ios_workers inside the IOS thread pool.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The minimum value is 1 and the maximum value is 100.

Default value: 4

parallel_scan_gap

Parameter description: The number of pages processed in a single pass per worker thread when parallel scanning is enabled (query_dop > 1).

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The minimum value is 64 and the maximum value is 4096.

Default value: 128

ios_batch_read_size

Parameter description: The number of pre-fetch pages that ios_worker sends down to disk per batch.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The minimum value is 64 and the maximum value is 1024.

Default value: 64

max_requests_per_worker

Parameter description: The maximum queue depth for each ios_worker. When exceeded, the ios_worker thread cannot accept new requests until one of the ios_workers has been processed and moved out of the task queue.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The minimum value is 1 and the maximum value is 10.

Default value: 2

min_table_block_num_enable_ios

Parameter description: The Astore table size threshold for triggering a pre-fetch. A pre-fetch may be triggered only if the total number of data pages in the table is greater than or equal to this threshold. The current data page size is 8kB.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The minimum value is 65536 (i.e. 512MB) and the maximum value is 6553600 (i.e. 512GB).

Default value: 131072（1GB）

min_uheap_table_block_num_enable_ios

Parameter description: The Ustore table size threshold for triggering a pre-fetch. Pre-fetch may be triggered only if the total number of data pages in the table is greater than or equal to this threshold. The current data page size is 8kB.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The minimum value is 65536 (i.e. 512MB) and the maximum value is 6553600 (i.e. 512GB).

Default value: 131072（1GB）

prefetch_protect_time

Parameter description: Pre-fetch buffer maximum protection time.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The unit is milliseconds. The minimum value is 100 and the maximum value is 10000.

Default value: 500

ios_status_update_gap

Parameter description: The time interval for updating the IOS performance status.

This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer. The unit is second. The minimum value is 1 and the maximum value is 100.

Default value: 1

thread_pool_committer_max_retry_count

Parameter description: Sets the maximum number of retries before the thread pool committer sleeps.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer, 1-2147483647

Default value: 10

thread_pool_committerctl_max_retry_count

Parameter description: Sets the maximum number of retries before the thread pool committerctl sleeps.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer, 1-2147483647

Default value: 10

thread_pool_worker_num_per_committer

Parameter description: Sets the ratio of the number of worker threads to the number of committer threads in the thread pool.

This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Integer, 1-2147483647

Default value: 8