All Products
Search
Document Center

Computing layer parameters

Last Updated: Jun 01, 2022

PolarDB-X uses an architecture in which storage is decoupled from computing. The following table describes common parameters for compute node settings. In the PolarDB-X console, you can specify the parameters for your compute nodes based on your business requirements.

Parameter

Restart required

Default value

Valid values

Description

PLAN_CACHE

No

TRUE

[TUREFALSE]

Specifies whether to enable the plan cache feature.

ENABLE_RECYCLEBIN

No

FALSE

[TUREFALSE]

Specifies whether to enable the recycle bin.

SHOW_TABLES_CACHE

No

FALSE

[TUREFALSE]

Specifies whether to cache the results that are returned by SHOW TABLES statements.

MERGE_CONCURRENT

No

FALSE

[TUREFALSE]

Specifies whether to enable the massively parallel processing (MPP) mode. The value of this parameter has an impact on the degree of parallelism (DOP) of DDL operations and all simple queries that are pushed down.

MERGE_UNION

No

FALSE

[TUREFALSE]

Specifies whether to perform union operations to optimize physical SQL statements and whether to serially execute the physical SQL statements that are pushed down. If this parameter is set to TRUE, the system performs union operations to optimize physical SQL statements and serially executes physical SQL statements that are pushed down. Default value: FALSE.

MERGE_UNION_SIZE

No

-1

[0-10000]

Specifies the maximum number of physical SQL statements that the system can merge by performing a union operation. By default, the system automatically calculates the number of physical SQL statements based on the number of available connections in the connection pool.

TABLE_META_CACHE_EXPIRE_TIME

No

300

[0-180000]

Specifies the validity period of metadata caches.

COLUMN_LABEL_INSENSITIVE

No

TRUE

[FALSETRUE]

Specifies whether the names of returned columns are case-sensitive.

RECORD_SQL

No

TRUE

[FALSETRUE]

Specifies whether to enable the log audit feature.

SOCKET_TIMEOUT

No

900000

[0~3600000]

Specifies the timeout period of physical SQL statements.

TRANSACTION_POLICY

No

TSO

[XATSOTSO_READONLY]

Specifies the transaction policy.

SHARE_READ_VIEW

No

FALSE

[TRUEFALSE]

Specifies whether to enable the ReadView feature.

ENABLE_TRX_SINGLE_SHARD_OPTIMIZATION

No

TRUE

[TRUEFALSE]

Specifies whether to enable the feature to optimize a single shard for transactions.

GET_TSO_TIMEOUT

No

10

[1-1800]

Specifies the timeout period of queries for Timestamp Oracle (TSO) timestamps.

MAX_TRX_DURATION

No

28800

[1-180000]

Specifies the timeout period of transactions that are not committed.

TRANSACTION_ISOLATION

No

REPEATABLE_READ

[READ_UNCOMMITTEDREAD_COMMITTEDREPEATABLE_READSERIALIZABLE]

Specifies the transaction isolation level.

GROUP_CONCURRENT_BLOCK

No

TRUE

[TRUEFALSE]

Specifies whether to use the execution policy at the database shard level when the execution mode is not the MPP mode.

SEQUENTIAL_CONCURRENT_POLICY

No

FALSE

[TRUEFALSE]

Specifies whether to use the execution policy to process concurrent requests on a single instance when the execution mode is not the MPP mode.

DML_SKIP_DUPLICATE_CHECK_FOR_PK

No

TRUE

[TRUEFALSE]

Specifies whether to skip the check operation for primary key conflicts when DML statements are executed.

DML_SKIP_CRUCIAL_ERR_CHECK

No

FALSE

[TRUEFALSE]

Specifies whether transactions on which the system reports DML statement errors can be committed when DML statements are executed.

DML_USE_RETURNING

No

TRUE

[TRUEFALSE]

Specifies whether to use the RETURNING clause to optimize DML statements.

BROADCAST_DML

No

FALSE

[TRUEFALSE]

Specifies whether data can be written to broadcast tables on the nodes where the broadcast tables are stored.

SEQUENCE_STEP

No

10000

[1-10000000]

Specifies the step size in sequences. Default value: 100,000.

MERGE_DDL_TIMEOUT

No

0

[1-10000000]

The timeout period of physical DDL connections. Default value: 0. The default value indicates that connections do not time out.

MERGE_DDL_CONCURRENT

No

FALSE

[FALSETRUE]

Specifies whether to execute DDL statements in MPP mode. By default, DDL statements are concurrently executed at the database level.

SLOW_SQL_TIME

No

1000

[1-180000]

Specifies the threshold value for slow SQL queries.

LOAD_DATA_BATCH_INSERT_SIZE

No

1024

[1-180000]

Specifies the maximum number of records that the LOAD DATA statement can insert at a time.

LOAD_DATA_CACHE_BUFFER_SIZE

No

60

[1-180000]

Specifies the maximum size of data that can be cached by using the LOAD DATA statement. Default value: 60 MB. This parameter is used for throttling.

MAX_ALLOWED_PACKET

No

16777216

[4194304-33554432]

Specifies the maximum size of a packet.

KILL_CLOSE_STREAM

No

FALSE

[FALSETRUE]

Specifies whether to close connections between compute nodes and data nodes when the number of records that the system obtains reaches the upper limit.

ALLOW_SIMPLE_SEQUENCE

No

FALSE

[FALSETRUE]

Specifies whether to use simple sequences.

MAX_PARAMETERIZED_SQL_LOG_LENGTH

No

5000

[1-1000000]

Specifies the maximum size of a single log that the system can generate for parameterized SQL statements.

FORBID_EXECUTE_DML_ALL

No

TRUE

[TRUEFALSE]

Specifies whether to disable the full table deletion feature and the full table update feature.

GROUP_SEQ_CHECK_INTERVAL

Yes

60

[1-36000]

Specifies the interval at which the system checks inserted explicit values. Unit: seconds.

JOIN_BLOCK_SIZE

Yes

300

[1-100000]

Specifies the maximum number of values that can be included in the IN condition for a Batched Key Access (BKA) join operation in non-dynamical pruning mode.

LOOKUP_JOIN_MAX_BATCH_SIZE

Yes

6400

[1-100000]

Specifies the maximum number of values that can be included in the IN condition for a BKA join operation.

LOOKUP_JOIN_MIN_BATCH_SIZE

Yes

100

[1-100000]

Specifies the minimum number of values that can be included in the IN condition for a BKA join operation.

PURGE_TRANS_INTERVAL

Yes

300

[1-180000]

Specifies the interval at which the system deletes transaction logs.

PURGE_TRANS_BEFORE

Yes

1800

[1-180000]

Specifies the period of time for which the system stores transaction logs. The system deletes the transaction logs whose durations exceed the specified period of time.

ENABLE_BACKGROUND_STATISTIC_COLLECTION

No

TRUE

[TRUEFALSE]

Specifies whether to enable the statistics collection feature.

GENERAL_DYNAMIC_SPEED_LIMITATION

No

-1

[-1-10000000]

Specifies the limit on the speed at which the system backfills and verifies data. The default value -1 indicates that the speed limit is 10,000 rows per second.

PARALLELISM

No

-1

[1-1024]

Specifies the DOP on a node. By default, the system calculates the value based on the specification of the node.

LOGICAL_DB_TIME_ZONE

No

SYSTEM

[SYSTEM±HH:mm]

Specifies the time zone of the database.

MPP_PARALLELISM

No

-1

[1-1024]

Specifies the DOP for the MPP mode. By default, the system calculates the value based on the specifications of the nodes.

DATABASE_PARALLELISM

No

0

[0-1024]

Specifies the maximum number of SQL statements that can be executed for a single query on a data node.

POLARDBX_PARALLELISM

No

0

[0-1024]

Specifies the maximum DOP for a single query on a compute node. The default value is the number of CPU cores of the node.

MPP_METRIC_LEVEL

No

3

[0123]

Specifies the level of statistics that the system collects during the process of computing. A higher level indicates a higher granularity of statistics collection.

ENABLE_COMPLEX_DML_CROSS_DB

No

TRUE

[TRUEFALSE]

Specifies whether complex DML statements can be executed across databases.

PER_QUERY_MEMORY_LIMIT

Yes

-1

[-1-9223372036854775807]

Specifies the maximum size of the memory pool for queries. By default, the maximum size of the memory pool for queries is one third the size of the global connection pool.

ENABLE_SPILL

No

FALSE

[FALSETRUE]

Specifies whether to flush temporary tables to the disk.

CONN_POOL_MIN_POOL_SIZE

No

20

[0-10]

Specifies the minimum number of connections to physical database shards.

CONN_POOL_MAX_POOL_SIZE

No

60

[1-1600]

Specifies the maximum number of connections to physical database shards.

CONN_POOL_MAX_WAIT_THREAD_COUNT

No

0

[-1-8192]

Specifies the maximum number of pending connections from an Apache Druid database to a single database shard in PolarDB-X.

CONN_POOL_IDLE_TIMEOUT

No

30

[1-60]

Specifies the validity period of idle physical connections.

CONN_POOL_BLOCK_TIMEOUT

No

5000

[1000-60000]

Specifies the timeout period of waiting for a connection from the physical connection pool.

CONN_POOL_XPROTO_MAX_POOLED_SESSION_PER_INST

No

512

[1-8192]

Specifies the maximum number of sessions that can be cached on a single storage node. The sessions are established over proprietary protocols.

XPROTO_MAX_DN_CONCURRENT

No

500

[1-8192]

Specifies the maximum number of concurrent requests on a single storage node. The requests are sent over proprietary protocols.

XPROTO_MAX_DN_WAIT_CONNECTION

No

32

[1-8192]

Specifies the maximum number of pending requests on a single storage node. The requests are sent over proprietary protocols.

MERGE_SORT_BUFFER_SIZE

No

2048

[1024-81920]

Specifies the cache size that can be used for merge sort at the TableScan layer. Default value: 2 MB.

WORKLOAD_TYPE

No

[APTP]

Specifies the type of query workloads. By default, the system determines a type based on resource costs.

EXECUTOR_MODE

No

[MPPTP_LOCALAP_LOCAL]

Specifies the mode in which the system performs queries. By default, the system determines a mode based on the query workload type.

ENABLE_MASTER_MPP

No

FALSE

[TRUEFALSE]

Specifies whether to enable the MPP mode for the primary instance.

LOOKUP_JOIN_BLOCK_SIZE_PER_SHARD

Yes

50

[1-100000]

Specifies the maximum number of values that can be included in the IN condition for a Batched Key Access (BKA) join operation on a single shard in dynamical pruning mode.

ENABLE_RUNTIME_FILTER

No

TRUE

[TRUEFALSE]

Specifies whether to enable the runtime filter.

FEEDBACK_WORKLOAD_AP_THRESHOLD

No

FALSE

[TRUEFALSE]

Specifies whether to enable the HTAP feedback feature for analytical processing (AP) queries.

FEEDBACK_WORKLOAD_TP_THRESHOLD

No

FALSE

[TRUEFALSE]

Specifies whether to enable the HTAP feedback feature for transaction processing (TP) queries.

MASTER_READ_WEIGHT

No

-1

[0-100]

Specifies the ratio of queries that can be sent to read-only data nodes. Default value:100.

SHOW_ALL_PARAMS

No

FALSE

[TRUEFALSE]

Specifies whether to display all parameters.

FORCE_READ_OUTSIDE_TX

No

FALSE

[TRUEFALSE]

Specifies whether to forcibly establish multiple connections to a database shard to process a transaction.

ENABLE_COROUTINE

Yes

FALSE

[TRUEFALSE]

Specifies whether to enable the Wisp coroutine feature.

TRUNCATE_TABLE_WITH_GSI

No

FALSE

[TRUEFALSE]

Specifies whether TRUNCATE statements can be executed on global secondary index (GSI) tables.

DDL_ON_GSI

No

FALSE

[TRUEFALSE]

Specifies whether DDL statements can be executed on GSI tables.

DML_ON_GSI

No

FALSE

[TRUEFALSE]

Specifies whether DML statements can be executed on GSI tables.

ENABLE_HASH_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether the system can generate hash join nodes when the system optimizes execution plans.

ENABLE_BKA_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to generate BKA join nodes when the system optimizes execution plans.

ENABLE_NL_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to generate nested loop join nodes when the system optimizes execution plans.

ENABLE_SEMI_NL_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to convert semi join operations to nested loop join operations when the system optimizes execution plans.

ENABLE_SEMI_HASH_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to convert semi join operations to hash join operations when the system optimizes execution plans.

ENABLE_SEMI_BKA_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to convert semi join operations to BKA join operations when the system optimizes execution plans.

ENABLE_SEMI_SORT_MERGE_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to convert semi join operations to merge join operations when the system optimizes execution plans.

ENABLE_MATERIALIZED_SEMI_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to convert semi join operations to materialized view join operations when the system optimizes execution plans.

ENABLE_SEMI_JOIN_REORDER

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to use semi join operations to optimize the Cost-Based Optimization (CBO) recorder when the system optimizes execution plans.

ENABLE_HASH_AGG

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to use the HashAgg operator to add a hash aggregation operation when the system optimizes execution plans.

ENABLE_PARTIAL_AGG

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to split the execution of the aggregate function into two phases when the system optimizes execution plans.

ENABLE_SORT_AGG

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to use the SortAgg operator to add a sort aggregation operation when the system optimizes execution plans.

ENABLE_PUSH_PROJECT

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to push down projection nodes when the system optimizes execution plans.

ENABLE_PUSH_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to push down join operations when the system optimizes execution plans.

ENABLE_PUSH_AGG

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to push down aggregate operations when the system optimizes execution plans.

ENABLE_CBO_PUSH_AGG

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to use aggregate functions to pass through join operations when the system optimizes execution plans.

ENABLE_PUSH_SORT

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to push down sort operations when the system optimizes execution plans.

ENABLE_STATISTIC_FEEDBACK

No

TRUE

[TRUEFALSE]

Specifies whether to collect statistical information from correction feedbacks.

ENABLE_CBO_PUSH_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to pass through join operations when the system optimizes execution plans.

ENABLE_SORT_JOIN_TRANSPOSE

No

TRUE

[TRUEFALSE]

Specifies whether to allow the system to use sort functions to pass through join operations when the system optimizes execution plans.

CHUNK_SIZE

No

1024

[1-10240]

Specifies the maximum size of data that the executor can compute each time.

ENABLE_SORT_MERGE_JOIN

No

TRUE

[TRUEFALSE]

Specifies whether to prevent the system from using the MergeJoin operator to generate merge join nodes when the system optimizes execution plans.

ENABLE_BKA_PRUNING

No

TRUE

[TRUEFALSE]

Specifies whether to enable the pruning feature for BKA join operations.

ENABLE_SPM

No

TRUE

[TRUEFALSE]

Specifies whether to enable the execution plan management feature.

ENABLE_EXPRESSION_VECTORIZATION

No

TRUE

[TRUEFALSE]

Specifies whether to enable the expression vectorization feature.

FORCE_DDL_ON_LEGACY_ENGINE

No

TRUE

[TRUEFALSE]

Specifies whether to automatically update the DDL engine to the most recent version.

PURE_ASYNC_DDL_MODE

No

FALSE

[TRUEFALSE]

Specifies whether to execute DDL statements in asynchronous mode. If this parameter is set to TRUE, the client returns a response when a DDL request is received. You can execute a SHOW [FULL] DDL statement to view the status of the DDL request.

DDL_JOB_REQUEST_TIMEOUT

No

90000

[1-9223372036854775807]

Specifies the timeout period of DDL executions. The default timeout period is 25 days.

LOGICAL_DDL_PARALLELISM

No

1

[1-10240]

The DOP of logical DDL statements. If this parameter is set to 1, the system serially executes DDL statements.

ENABLE_BROADCAST_RANDOM_READ

No

TRUE

[TRUEFALSE]

Specifies whether to enable random read optimization for broadcast tables.

STORAGE_DELAY_THRESHOLD

No

16777216s

[0-16777216]

Specifies the maximum latency that is allowed when data nodes return data to compute nodes. If a compute node does not receive data from a read-only data node within the specified period of time, the system routes read queries to read-only data nodes based on the policy that is specified by the DELAY_EXECUTION_STRATEGY parameter.

DELAY_EXECUTION_STRATEGY

No

CONTINUE_TO_SLAVE

[CONTINUE_TO_SLAVE CHANGE_TO_MASTER THROW_EXCEPTION]

Specifies the policy based on which you want the system to route read queries when a compute node does not receive data from a read-only data node within the period of time that is specified by the STORAGE_DELAY_THRESHOLD parameter, Valid values:

  • CONTINUE_TO_SLAVE: The system continues to route read queries to the read-only data node that does not return data to the compute node within the specified period of time.

  • CHANGE_TO_MASTER: The system routes read queries to read-only nodes that can return data within the specified period of time. If the latency of all read-only data nodes exceeds the value of the STORAGE_DELAY_THRESHOLD parameter, the system routes read queries to the data nodes of the primary instance.

  • THROW_EXCEPTION: If the latency of all read-only data nodes exceeds the value of the STORAGE_DELAY_THRESHOLD parameter, the system returns errors when you specify the system to route read queries to the read-only data nodes.

STORAGE_BUSY_THRESHOLD

No

100

[0-16777216]

Specifies the maximum number of active threads that can be run on a single read-only data node. If an instance contains multiple read-only data nodes and the number of active threads on a read-only data node exceeds the specified threshold value, the system determines that the read-only data node is busy. In this case, the system does not route more read queries to the read-only data nodes. If all read-only data nodes are busy, the system routes read queries to a random read-only data node.

ENABLE_CONSISTENT_REPLICA_READ

No

TRUE

[TUREFALSE]

Specifies whether to enable the consistent read feature.

TRUE: The consistent read feature is enabled. The data that the system obtains from read-only data nodes is the most recent data.

FALSE: The consistent read feature is disabled. The freshness of the data that the system obtains from read-only data nodes is determined based on the latency of data synchronization between the primary instance and secondary instances.

SUPPORT_INSTANT_ADD_COLUMN

No

OFF

[ONOFF]

Specifies whether to enable the instant add column feature. You can execute a SET GLOBAL statement to enable this feature.