max_prepared_transactions
Parameter description: Specifies the maximum number of transactions that can stay in the prepared state simultaneously. Increasing the value of this parameter causes openGauss to request more System V shared memory than the default configuration allows.
When openGauss is deployed as an HA system, set this parameter on standby servers to a value greater than or equal to that on primary servers. Otherwise, queries will fail on the standby servers.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 0 to 262143
Default value: 10
work_mem
Parameter description: Specifies the amount of memory to be used by internal sort operations and hash tables before they write data into temporary disk files. Sort operations are required for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
In a complex query, several sort or hash operations may run in parallel; each operation will be allowed to use as much memory as this parameter specifies. If the memory is insufficient, data will be written into temporary files. In addition, several running sessions could be performing such operations concurrently. Therefore, the total memory used may be many times the value of work_mem.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 64 to 2147483647. The unit is KB.
Default value: 64 MB
query_mem
Parameter description: Specifies the memory used by a query.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: 0 or an integer greater than 32 MB. The default unit is KB.
Default value: 0
query_max_mem
Parameter description: Specifies the maximum memory that can be used by a query.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: 0 or an integer greater than 32 MB. The default unit is KB.
Default value: 0
maintenance_work_mem
Parameter description: Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. This parameter may affect the execution efficiency of VACUUM, VACUUM FULL, CLUSTER, and CREATE INDEX.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 1024 to *INT*MAX_. The unit is KB.
Default value: 16 MB
psort_work_mem
Parameter description: Specifies the memory capacity to be used for partial sorting in a column-store table before writing to temporary disk files. This parameter can be used for inserting tables having a partial cluster key or index, creating a table index, and deleting or updating a table.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 64 to 2147483647. The unit is KB.
Default value: 512 MB
max_loaded_cudesc
Parameter description: Specifies the number of cudesc cached in each column when a column-store table is scanned. Increasing the value will improve query performance and increase memory usage, particularly when there are many columns in the column-store table.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: 100 to 1073741823
Default value: 1024
max_stack_depth
Parameter description: Specifies the maximum safe depth of the openGauss execution stack. The safety margin is required because the stack depth is not checked in every routine in the server, but only in key potentially-recursive routines, such as expression evaluation.
This parameter is a SUSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 100 to *INT*MAX_. The unit is KB.
Default value:
· If the value of ulimit -s minus 640 KB is greater than or equal to 2 MB, the default value of this parameter is 2 MB.
· If the value of ulimit -s minus 640 KB is less than 2 MB, the default value of this parameter is the value of ulimit -s minus 640 KB.
cstore_buffers
Parameter description: Specifies the shared buffer size used in column-store tables.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 16384 to 1073741823. The unit is KB.
Default value: 1 GB
Setting suggestions:
Column-store tables use the shared buffer specified by cstore_buffers instead of that specified by shared_buffers. When column-store tables are mainly used, reduce the value of shared_buffers and increase that of cstore_buffers.
bulk_read_ring_size
Parameter description: Specifies the size of the ring buffer used by the operation when a large amount of data is queried (for example, during large table scanning).
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 256 to 2147483647. The unit is KB.
Default value: 16 MB
Parameter description: Specifies whether the operator memory can be released in advance.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
· on indicates that the operator memory can be released in advance.
· off indicates that the operator memory cannot be released in advance.
Default value: on
local_syscache_threshold
Parameter description: Specifies the size of system catalog cache in a session.
This parameter is a PGC_SIGHUP parameter. Set it based on instructions provided in Table 1.
If enable_global_plancache is enabled, local_syscache_threshold does not take effect when it is set to a value less than 16 MB to ensure that GPC takes effect. The minimum value is 16 MB.
If enable_global_syscache and enable_thread_pool are enabled, this parameter indicates the total cache size of the current thread and sessions bound to the current thread.
Value range: an integer ranging from 1 x 1024 to 512 x 1024. The unit is KB.
Default value: 256MB
Top comments (0)