Performance Tuning-Analyzing Hardware Bottlenecks-Memory（2）
Tags: opengauss
Published: 2023-04-24 09:24:54

**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**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/47f05nxnl8gjwh9m4dj0.png)

**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**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kiyqepn0rylxdlfacyo4.png)

**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**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mr69h7c137hixnljbrbb.png)

**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**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/50g3mw1rzseo0k79t9br.png)

**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**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1bjajtri5hbl71q9wknv.png)

**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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ho5ax72r3q518ocgefyx.png)

**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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8wdw3ow3nsb4x5nqhngl.png)

**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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h6l4mwicsv51mu30wuih.png)

**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**