Parameters for compute node settings
PolarDB-Xuses an architecture in which storage is decoupled from computing. The following table describes common parameters for compute node settings. You can configure specific parameters for the compute nodes of your PolarDB-X instance based on your business requirements.
| Parameter | Restart required | Default value | Valid values | Description | |||
|---|---|---|---|---|---|---|---|
| PLAN_CACHE | No | FALSE | [TRUE\ | FALSE] | Specifies whether to enable the plan cache feature. | ||
| 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 results that are returned by SHOW TABLES statements. |
||
| MERGE_CONCURRENT | No | FALSE | [TRUE\ | FALSE] | Specifies whether to enable the massively parallel processing (MPP) mode. The value of this parameter determines the degree of parallelism (DOP) of DDL operations and simple queries that are pushed down to the data node layer for execution. | ||
| MERGE_UNION | No | FALSE | [TRUE\ | FALSE] | Specifies whether to perform union operations to optimize physical SQL statements and whether to serially execute the physical SQL statements that are pushed down to the data node layer. 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 to the data node layer. 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 | [FALSE\ | TRUE] | Specifies whether the names of returned columns are case-sensitive. | ||
| RECORD_SQL | No | TRUE | [FALSE\ | TRUE] | 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 | [XA\ | TSO\ | TSO_READONLY] | Specifies the transaction policy. | |
| SHARE_READ_VIEW | No | FALSE | [TRUE\ | FALSE] | Specifies whether to enable the ReadView feature. | ||
| ENABLE_TRX_SINGLE_SHARD_OPTIMIZATION | No | TRUE | [TRUE\ | FALSE] | Specifies whether to enable the optimization 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_UNCOMMITTED\ | READ_COMMITTED\ | REPEATABLE_READ\ | SERIALIZABLE] | Specifies the transaction isolation level. |
| GROUP_CONCURRENT_BLOCK | No | TRUE | [TRUE\ | FALSE] | Specifies whether to use the execution policy on database shards when the execution mode is not the MPP mode. | ||
| SEQUENTIAL_CONCURRENT_POLICY | No | FALSE | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | Specifies whether to skip the check operation for primary key conflicts when DML statements are executed. | ||
| DML_SKIP_CRUCIAL_ERR_CHECK | No | FALSE | [TRUE\ | FALSE] | Specifies whether transactions on which the system reports DML errors can be committed when DML statements are executed. | ||
| DML_USE_RETURNING | No | TRUE | [TRUE\ | FALSE] | Specifies whether to use the RETURNING clause to optimize DML statements. | ||
| BROADCAST_DML | No | FALSE | [TRUE\ | FALSE] | Specifies whether data can be written to broadcast tables without executing distributed transactions. | ||
| SEQUENCE_STEP | No | 10000 | [1-10000000] | Specifies the step size in sequences. Default value: 100,000. | |||
| MERGE_DDL_TIMEOUT | No | 0 | [1-10000000] | Specifies 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 | [FALSE\ | TRUE] | 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 | [FALSE\ | TRUE] | 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 | [FALSE\ | TRUE] | Specifies whether simple sequences are allowed. | ||
| 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 | [TRUE\ | FALSE] | 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 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 | [TRUE\ | FALSE] | 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. The DOP of scanning operations is calculated based on the value of this parameter. | |||
| POLARDBX_PARALLELISM | No | 0 | [0-1024] | Specifies the maximum DOP for a single query on a compute node. The default value is equal to the number of CPU cores of the node. | |||
| MPP_METRIC_LEVEL | No | 3 | [0\ | 1\ | 2\ | 3] | 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 | [TRUE\ | FALSE] | 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 | [FALSE\ | TRUE] | Specifies whether to flush temporary tables to the disk. | ||
| CONN_POOL_MIN_POOL_SIZE | No | 20 | [0-60] | 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 longest period of time that the system can wait 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 data node. The sessions are established over the proprietary protocol provided by PolarDB-X. | |||
| XPROTO_MAX_DN_CONCURRENT | No | 500 | [1-8192] | Specifies the maximum number of concurrent requests on a single data node. The requests are sent over the proprietary protocol provided by PolarDB-X. | |||
| XPROTO_MAX_DN_WAIT_CONNECTION | No | 32 | [1-8192] | Specifies the maximum number of pending requests on a single data node. The requests are sent over the proprietary protocol provided by PolarDB-X. | |||
| MERGE_SORT_BUFFER_SIZE | No | 2048 | [1024-81920] | Specifies the size of cache that can be used for merge sort at the TableScan layer. Default value: 2 MB. | |||
| WORKLOAD_TYPE | No | [AP\ | TP] | Specifies the type of query workloads. By default, the system automatically determines a type based on resource costs. | |||
| EXECUTOR_MODE | No | [MPP\ | TP_LOCAL\ | AP_LOCAL] | Specifies the mode in which the system performs queries. By default, the system determines a mode based on the type of the query workload. | ||
| ENABLE_MASTER_MPP | No | FALSE | [TRUE\ | FALSE] | 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 BKA join operation on a single shard in dynamical pruning mode. | |||
| ENABLE_RUNTIME_FILTER | No | TRUE | [TRUE\ | FALSE] | Specifies whether to enable the runtime filter. | ||
| FEEDBACK_WORKLOAD_AP_THRESHOLD | No | FALSE | [TRUE\ | FALSE] | Specifies whether to enable the HTAP feedback feature for analytical processing (AP) queries. | ||
| FEEDBACK_WORKLOAD_TP_THRESHOLD | No | FALSE | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | Specifies whether to display all parameters. | ||
| FORCE_READ_OUTSIDE_TX | No | FALSE | [TRUE\ | FALSE] | Specifies whether to forcibly establish multiple connections to a database shard to process a transaction. | ||
| ENABLE_COROUTINE | Yes | FALSE | [TRUE\ | FALSE] | Specifies whether to enable the Wisp coroutine feature. | ||
| TRUNCATE_TABLE_WITH_GSI | No | FALSE | [TRUE\ | FALSE] | Specifies whether the TRUNCATE statement can be executed on global secondary index (GSI) tables. | ||
| DDL_ON_GSI | No | FALSE | [TRUE\ | FALSE] | Specifies whether DDL statements can be executed on GSI tables. | ||
| DML_ON_GSI | No | FALSE | [TRUE\ | FALSE] | Specifies whether DML statements can be executed on GSI tables. | ||
| ENABLE_HASH_JOIN | No | TRUE | [TRUE\ | FALSE] | Specifies whether to allow the system to add hash join operations when the system optimizes execution plans. | ||
| ENABLE_BKA_JOIN | No | TRUE | [TRUE\ | FALSE] | Specifies whether to allow the system to add BKA join operations when the system optimizes execution plans. | ||
| ENABLE_NL_JOIN | No | TRUE | [TRUE\ | FALSE] | Specifies whether to allow the system to add nested loop join operations when the system optimizes execution plans. | ||
| ENABLE_SEMI_NL_JOIN | No | TRUE | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | Specifies whether to allow the system to split the execution of an aggregate function into two phases when the system optimizes execution plans. | ||
| ENABLE_SORT_AGG | No | TRUE | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | Specifies whether to allow the system to push down projection operations when the system optimizes execution plans. | ||
| ENABLE_PUSH_JOIN | No | TRUE | [TRUE\ | FALSE] | Specifies whether to allow the system to push down join operations when the system optimizes execution plans. | ||
| ENABLE_PUSH_AGG | No | TRUE | [TRUE\ | FALSE] | Specifies whether to allow the system to push down aggregate operations when the system optimizes execution plans. | ||
| ENABLE_CBO_PUSH_AGG | No | TRUE | [TRUE\ | FALSE] | 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 | [TRUE\ | FALSE] | Specifies whether to allow the system to push down sort operations when the system optimizes execution plans. | ||
| ENABLE_STATISTIC_FEEDBACK | No | TRUE | [TRUE\ | FALSE] | Specifies whether to collect statistical information from correction feedbacks. | ||
| ENABLE_CBO_PUSH_JOIN | No | TRUE | [TRUE\ | FALSE] | Specifies whether to allow the system to pass through join operations when the system optimizes execution plans. | ||
| ENABLE_SORT_JOIN_TRANSPOSE | No | TRUE | [TRUE\ | FALSE] | 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 at a time. | |||
| ENABLE_SORT_MERGE_JOIN | No | TRUE | [TRUE\ | FALSE] | Specifies whether to prevent the system from using the MergeJoin operator to add merge join operations when the system optimizes execution plans. | ||
| ENABLE_BKA_PRUNING | No | TRUE | [TRUE\ | FALSE] | Specifies whether to enable the pruning feature for BKA join operations. | ||
| ENABLE_SPM | No | TRUE | [TRUE\ | FALSE] | Specifies whether to enable the execution plan management feature. | ||
| ENABLE_EXPRESSION_VECTORIZATION | No | TRUE | [TRUE\ | FALSE] | Specifies whether to enable the expression vectorization feature. | ||
| FORCE_DDL_ON_LEGACY_ENGINE | No | TRUE | [TRUE\ | FALSE] | Specifies whether to automatically update the DDL engine to the most recent version. | ||
| PURE_ASYNC_DDL_MODE | No | FALSE | [TRUE\ | FALSE] | Specifies whether to execute DDL statements in asynchronous mode. If this parameter is set to TRUE, the client returns a response when the client receives a DDL request. 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] | Specifies 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 | [TRUE\ | FALSE] | 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 node. 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 | [TRUE\ | FALSE] | 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 | [ON\ | OFF] | Specifies whether to enable the instant add column feature. You can execute a SET GLOBAL statement to enable this feature. |
||
| ENABLE_STATEMENTS_SUMMARY | No | true | [true\ | false] | Specifies whether to enable the summary feature for SQL statements. | ||
| STATEMENTS_SUMMARY_PERCENT | No | 1 | [0-100] | Specifies the percentage of SQL statements that are included in a summary. |