Compute layer parameter reference for PolarDB-X

Parameter

Restart required

Default value

Value range

Description

PLAN_CACHE

No

FALSE

[TRUE|FALSE]

Specifies whether to enable the plan cache.

ENABLE_RECYCLEBIN

No

FALSE

[TRUE|FALSE]

Specifies whether to enable the recycle bin.

SHOW_TABLES_CACHE

No

FALSE

[TRUE|FALSE]

Specifies whether to cache the result ofSHOW TABLES.

MERGE_CONCURRENT

No

FALSE

[TRUE|FALSE]

The full parallel execution switch. This parameter mainly affects the parallelism of DDL statements and fully pushed-down simple queries.

MERGE_UNION

No

FALSE

[TRUE|FALSE]

Disabled by default. When enabled, physical SQL statements do not performUNIONoptimization, and pushed-down physical SQL statements are executed serially.

MERGE_UNION_SIZE

No

-1

[0-10000]

The number of physical SQL statements merged by UNION. Default is adaptive based on available connections in the connection pool.

TABLE_META_CACHE_EXPIRE_TIME

No

300

[0-180000]

The expiration time of the metadata cache.

COLUMN_LABEL_INSENSITIVE

No

TRUE

[FALSE|TRUE]

Specifies whether column names in query results are case-sensitive.

RECORD_SQL

No

TRUE

[FALSE|TRUE]

The audit log switch.

SOCKET_TIMEOUT

No

900000

[0~3600000]

The timeout period for physical SQL statements.

TRANSACTION_POLICY

No

TSO

[XA|TSO|TSO_READONLY]

The transaction policy.

SHARE_READ_VIEW

No

FALSE

[TRUE|FALSE]

The shared ReadView switch.

ENABLE_TRX_SINGLE_SHARD_OPTIMIZATION

No

TRUE

[TRUE|FALSE]

The single-shard transaction optimization switch.

GET_TSO_TIMEOUT

No

10

[1-1800]

The timeout period for obtaining TSO timestamps.

MAX_TRX_DURATION

No

28800

[1-180000]

The physical timeout period for transactions.

TRANSACTION_ISOLATION

No

REPEATABLE-READ

[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE]

The transaction isolation level.

GROUP_CONCURRENT_BLOCK

No

TRUE

[TRUE|FALSE]

The database-level execution policy in non-MPP mode.

SEQUENTIAL_CONCURRENT_POLICY

No

FALSE

[TRUE|FALSE]

The single-concurrency execution policy in non-MPP mode.

DML_SKIP_DUPLICATE_CHECK_FOR_PK

No

TRUE

[TRUE|FALSE]

Specifies whether to skip primary key conflict checks during DML operations.

DML_SKIP_CRUCIAL_ERR_CHECK

No

FALSE

[TRUE|FALSE]

Specifies whether to allow transactions with DML errors to continue committing.

DML_USE_RETURNING

No

TRUE

[TRUE|FALSE]

Specifies whether to use the RETURNING optimization.

BROADCAST_DML

No

FALSE

[TRUE|FALSE]

Specifies whether to allow writes to broadcast tables without distributed transactions.

SEQUENCE_STEP

No

10000

[1-10000000]

The SEQUENCE step. Default is 100000.

MERGE_DDL_TIMEOUT

No

0

[1-10000000]

The timeout period for DDL physical connections. Default is 0, which means no timeout.

MERGE_DDL_CONCURRENT

No

FALSE

[FALSE|TRUE]

Specifies whether DDL statements use full parallel mode. Default is database-level concurrency.

SLOW_SQL_TIME

No

1000

[1-180000]

The slow SQL threshold.

LOAD_DATA_BATCH_INSERT_SIZE

No

1024

[1-180000]

The number of records per batch insert in LOAD DATA.

LOAD_DATA_CACHE_BUFFER_SIZE

No

60

[1-180000]

The LOAD DATA cache size. Default is 60 MB. Mainly used for flow control.

MAX_ALLOWED_PACKET

No

16777216

[4194304-33554432]

The maximum packet size.

KILL_CLOSE_STREAM

No

FALSE

[FALSE|TRUE]

Specifies whether to enable early stream termination for physical connections.

ALLOW_SIMPLE_SEQUENCE

No

FALSE

[FALSE|TRUE]

Specifies whether to allow simple sequences.

MAX_PARAMETERIZED_SQL_LOG_LENGTH

No

5000

[1-1000000]

The maximum length of parameterized SQL for logging.

FORBID_EXECUTE_DML_ALL

No

TRUE

[TRUE|FALSE]

Specifies whether to prohibit full-table deletion or update.

GROUP_SEQ_CHECK_INTERVAL

Yes

60

[1-36000]

The period/interval for checking explicit value insertion, in seconds.

JOIN_BLOCK_SIZE

Yes

300

[1-100000]

The number of IN values during BKAJOIN execution without dynamic pruning.

LOOKUP_JOIN_MAX_BATCH_SIZE

Yes

6400

[1-100000]

The maximum number of IN values during BKAJOIN execution.

LOOKUP_JOIN_MIN_BATCH_SIZE

Yes

100

[1-100000]

The minimum number of IN values during BKAJOIN execution.

PURGE_TRANS_INTERVAL

Yes

300

[1-180000]

The transaction log cleanup interval.

PURGE_TRANS_BEFORE

Yes

1800

[1-180000]

The age of transaction logs to clean up.

ENABLE_BACKGROUND_STATISTIC_COLLECTION

No

TRUE

[TRUE|FALSE]

Specifies whether to allow statistics data collection.

GENERAL_DYNAMIC_SPEED_LIMITATION

No

-1

[-1-10000000]

The rate limit for data backfill and verification. -1 is the default limit, which means 10,000 rows per second.

PARALLELISM

No

-1

[1-1024]

The parallelism for single-node parallel execution. Default is derived from instance specifications.

LOGICAL_DB_TIME_ZONE

No

SYSTEM

[SYSTEM|±HH:mm]

The database time zone.

MPP_PARALLELISM

No

-1

[1-1024]

The concurrency for MPP execution mode. Default is derived from instance specifications.

DATABASE_PARALLELISM

No

0

[0-1024]

The number of SQL statements that can be simultaneously pushed down to a single DN per query, used to calculate scan concurrency.

POLARDBX_PARALLELISM

No

0

[0-1024]

The maximum concurrency per query on a single CN. Default is the number of CPU cores.

MPP_METRIC_LEVEL

No

3

[0|1|2|3]

The level of statistics collection during computation. A higher level indicates finer granularity.

ENABLE_COMPLEX_DML_CROSS_DB

No

TRUE

[TRUE|FALSE]

Specifies whether to support cross-database complex DML.

PER_QUERY_MEMORY_LIMIT

Yes

-1

[-1-9223372036854775807]

The query-level memory pool size limit. Default is one-third of the global connection pool.

ENABLE_SPILL

No

FALSE

[FALSE|TRUE]

The switch for persisting temporary tables to disk.

CONN_POOL_MIN_POOL_SIZE

No

20

[0-60]

The minimum number of physical database connections.

CONN_POOL_MAX_POOL_SIZE

No

60

[1-1600]

The maximum number of physical database connections.

CONN_POOL_MAX_WAIT_THREAD_COUNT

No

0

[-1-8192]

The maximum number of pending connections per database shard (Druid).

CONN_POOL_IDLE_TIMEOUT

No

30

[1-60]

The timeout period for idle physical connections.

CONN_POOL_BLOCK_TIMEOUT

No

5000

[1000-60000]

The maximum wait time for obtaining a connection from the physical connection pool.

CONN_POOL_XPROTO_MAX_POOLED_SESSION_PER_INST

No

512

[1-8192]

The maximum number of cached sessions per storage node (private protocol).

XPROTO_MAX_DN_CONCURRENT

No

500

[1-8192]

The maximum number of concurrent requests per storage node (private protocol). The default value was changed to 2000 on October 23, 2023, to meet most production and stress testing scenarios.

XPROTO_MAX_DN_WAIT_CONNECTION

No

32

[1-8192]

The maximum number of pending requests per storage node (private protocol). The default value was changed to 2000 on June 13, 2024, to meet most production and stress testing scenarios.

MERGE_SORT_BUFFER_SIZE

No

2048

[1024-81920]

The cache size used for merge sort at the TableScan layer. Default is 2 MB.

WORKLOAD_TYPE

No

[AP|TP]

Specifies whether to specify the query workload. By default, the workload is intelligently identified based on cost.

EXECUTOR_MODE

No

[MPP|TP_LOCAL|AP_LOCAL]

Specifies whether to specify the execution mode. By default, the execution mode is selected based on the workload.

ENABLE_MASTER_MPP

No

FALSE

[TRUE|FALSE]

Specifies whether to enable MPP on the primary instance.

LOOKUP_JOIN_BLOCK_SIZE_PER_SHARD

Yes

50

[1-100000]

The number of IN values per shard during BKAJOIN execution with pruning.

ENABLE_RUNTIME_FILTER

No

TRUE

[TRUE|FALSE]

The Runtime Filter switch.

FEEDBACK_WORKLOAD_AP_THRESHOLD

No

FALSE

[TRUE|FALSE]

The HTAP FEEDBACK switch for AP queries.

FEEDBACK_WORKLOAD_TP_THRESHOLD

No

FALSE

[TRUE|FALSE]

The HTAP FEEDBACK switch for TP queries.

MASTER_READ_WEIGHT

No

-1

[0-100]

Query traffic is routed to read-only DNs at a ratio of (100-MASTER_READ_WEIGHT).

SHOW_ALL_PARAMS

No

FALSE

[TRUE|FALSE]

Specifies whether to display all SHOW variables.

FORCE_READ_OUTSIDE_TX

No

FALSE

[TRUE|FALSE]

Specifies whether to force multiple connections on a single database shard within a transaction.

ENABLE_COROUTINE

Yes

FALSE

[TRUE|FALSE]

Specifies whether to enable wisp coroutines.

TRUNCATE_TABLE_WITH_GSI

No

FALSE

[TRUE|FALSE]

Specifies whether to allow TRUNCATE on tables with GSI.

DDL_ON_GSI

No

FALSE

[TRUE|FALSE]

Specifies whether to allow DDL operations directly on GSI tables.

DML_ON_GSI

No

FALSE

[TRUE|FALSE]

Specifies whether to allow DML operations directly on GSI tables.

ENABLE_HASH_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow HashJoin node generation during query optimization.

ENABLE_BKA_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow BKAJoin node generation during query optimization.

ENABLE_NL_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow NLJoin node generation during query optimization.

ENABLE_SEMI_NL_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow converting SemiJoin to NLJoin during query optimization.

ENABLE_SEMI_HASH_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow converting SemiJoin to HashJoin during query optimization.

ENABLE_SEMI_BKA_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow converting SemiJoin to BKAJoin during query optimization.

ENABLE_SEMI_SORT_MERGE_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow converting SemiJoin to MergeJoin during query optimization.

ENABLE_MATERIALIZED_SEMI_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow converting SemiJoin to MaterializedJoin during query optimization.

ENABLE_SEMI_JOIN_REORDER

No

TRUE

[TRUE|FALSE]

Specifies whether to allow SemiJoin to participate in CBO reorder optimization during query optimization.

ENABLE_HASH_AGG

No

TRUE

[TRUE|FALSE]

Specifies whether to allow HashAgg node generation during query optimization.

ENABLE_PARTIAL_AGG

No

TRUE

[TRUE|FALSE]

Specifies whether to allow splitting aggregation into two phases during query optimization.

ENABLE_SORT_AGG

No

TRUE

[TRUE|FALSE]

Specifies whether to allow SortAgg node generation during query optimization.

ENABLE_PUSH_PROJECT

No

TRUE

[TRUE|FALSE]

Specifies whether to allow projection pushdown during query optimization.

ENABLE_PUSH_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow join pushdown during query optimization.

ENABLE_PUSH_AGG

No

TRUE

[TRUE|FALSE]

Specifies whether to allow aggregation pushdown during query optimization.

ENABLE_CBO_PUSH_AGG

No

TRUE

[TRUE|FALSE]

Specifies whether to allow aggregation pass-through for joins during query optimization.

ENABLE_PUSH_SORT

No

TRUE

[TRUE|FALSE]

Specifies whether to allow sort pushdown during query optimization.

ENABLE_STATISTIC_FEEDBACK

No

TRUE

[TRUE|FALSE]

Specifies whether to support Feedback-based statistics correction.

ENABLE_CBO_PUSH_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to allow join pass-through optimization during query optimization.

ENABLE_SORT_JOIN_TRANSPOSE

No

TRUE

[TRUE|FALSE]

Specifies whether to allow sort pass-through for joins during query optimization.

CHUNK_SIZE

No

1024

[1-10240]

The batch size for each executor computation.

ENABLE_SORT_MERGE_JOIN

No

TRUE

[TRUE|FALSE]

Specifies whether to prohibit MergeJoin node generation during query optimization.

ENABLE_BKA_PRUNING

No

TRUE

[TRUE|FALSE]

Specifies whether to enable BKAJoin pruning.

ENABLE_SPM

No

TRUE

[TRUE|FALSE]

Specifies whether to enable execution plan management.

ENABLE_EXPRESSION_VECTORIZATION

No

TRUE

[TRUE|FALSE]

Specifies whether to enable vectorized expression evaluation.

FORCE_DDL_ON_LEGACY_ENGINE

No

TRUE

[TRUE|FALSE]

Specifies whether to enable the new DDL engine.

PURE_ASYNC_DDL_MODE

No

FALSE

[TRUE|FALSE]

Specifies whether to execute DDL tasks in a non-blocking manner. When enabled, the client returns immediately after executing DDL, and you can run SHOW [FULL] DDL to check the execution status.

DDL_JOB_REQUEST_TIMEOUT

No

90000

[1-9223372036854775807]

The maximum timeout period for DDL execution. Default is 25 days.

LOGICAL_DDL_PARALLELISM

No

1

[1-10240]

The concurrency for logical DDL execution. Setting to 1 means serial DDL execution.

ENABLE_BROADCAST_RANDOM_READ

No

TRUE

[TRUE|FALSE]

Specifies whether to enable random read optimization for broadcast tables.

STORAGE_DELAY_THRESHOLD

No

16777216s

[0-16777216]

The degree of CN awareness of DN latency during queries. If latency exceeds the threshold, read-only traffic routed to read-only DNs is handled based on the DELAY_EXECUTION_STRATEGY parameter.

DELAY_EXECUTION_STRATEGY

No

CONTINUE_TO_SLAVE

[CONTINUE_TO_SLAVE | CHANGE_TO_MASTER | THROW_EXCEPTION]

When read-only DN latency exceeds the threshold, the following three values can be set:

• CONTINUE_TO_SLAVE: Read-only traffic routed to read-only DNs can continue to be routed to the read-only DN whose latency exceeds the threshold.

• CHANGE_TO_MASTER: Read-only traffic is preferentially routed to read-only DNs with normal latency. If all read-only DNs exceed the latency threshold, traffic is routed to the DN of the primary instance.

• THROW_EXCEPTION: If all read-only DNs exceed the latency threshold, queries routed to read-only DNs return errors.

STORAGE_BUSY_THRESHOLD

No

100

[0-16777216]

When multiple read-only DNs exist, if the number of active threads on a read-only DN exceeds the threshold, the DN is considered busy and read-only traffic is routed away from it. If all read-only DNs are busy, read-only traffic is randomly routed to one of them.

ENABLE_CONSISTENT_REPLICA_READ

No

TRUE

[TRUE|FALSE]

The switch for consistent read requirements.

TRUE: Traffic routed to read-only DNs can meet consistent read requirements and read the freshest committed data.

FALSE: The data freshness of traffic routed to read-only DNs depends on the synchronization latency between the primary and secondary instances.

SUPPORT_INSTANT_ADD_COLUMN

No

OFF

[ON|OFF]

The switch for the Instant Add Column feature. Enable it by using the SET GLOBAL statement.

ENABLE_STATEMENTS_SUMMARY

No

true

[true|false]

The SQL aggregation switch.

STATEMENTS_SUMMARY_PERCENT

No

1

[0-100]

The SQL aggregation percentage.