Memory
This section describes memory parameters.
NOTICE:
These parameters take effect only after the database restarts.
memorypool_enable
Parameter description: Specifies whether to enable a memory pool.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that the memory pool is enabled.
- off indicates that the memory pool is disabled.
Default value: off
memorypool_size
Parameter description: Specifies the memory pool size.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 128 x 1024 to INT_MAX/2. The unit is KB.
Default value: 512MB
enable_memory_limit
Parameter description: Specifies whether to enable the logical memory management module.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that the logical memory management module is enabled.
- off indicates that the logical memory management module is disabled.
Default value: on
If the result of max_process_memory - shared_buffer - cstore_buffers is less than 2 GB, openGauss forcibly sets enable_memory_limit to off.
max_process_memory
Parameter description: Specifies the maximum physical memory of a database node.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 2 x 1024 x 1024 to INT_MAX. The unit is KB.
Default value:12GB
Setting suggestions: The value on the database node needs to be determined based on the physical memory of the system and the number of primary database nodes deployed on a single node. The recommended calculation formula is as follows: (physical memory size-vm.min_free_kbytes) * 0.7 / (1 + the number of master nodes). The purpose of this coefficient is to ensure the reliability of the system as much as possible, and will not cause node OOM due to the expansion of database memory. This formula mentions vm.min_free_kbytes, which means that operating system memory is reserved for kernel use. It is usually used for communication transceiver memory allocation in operating system kernel, at least 5% of memory. That is, max_process_memory = physical memory * 0.665 / (1 + the number of master nodes).
enable_memory_context_control
Parameter description: Specifies whether to enable the function of checking whether the amount of memory contexts exceeds the specified limit. This parameter applies only to the DEBUG version.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: Boolean
- on indicates that the function of checking the amount of memory contexts is enabled.
- off indicates that the function of checking the amount of memory contexts is disabled.
Default value: off
uncontrolled_memory_context
Parameter description: Specifies which memory texts will not be checked when the enable_memory_context_control parameter is set to on. This parameter applies only to the DEBUG version.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
During the query, the title meaning string “MemoryContext white list:” is added to the beginning of the parameter value.
Value range: a string
Default value: empty
shared_buffers
Parameter description: Specifies the size of shared memory used by openGauss. Increasing the value of this parameter causes openGauss to request more System V shared memory than the default configuration allows.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 16 to 1073741823. The unit is KB.
Shared_buffers needs to be set to an integer multiple of BLCKSZ. BLCKSZ is currently set to 8KB. Shared_buffers needs to be set to an integer multiple of 8KB. The minimum value changes according to BLCKSZ.
Default value: 1 GB on a database node. If the maximum value allowed by the OS is smaller than 32 MB, this parameter will be automatically changed to the maximum value allowed by the OS during the initialization of a data storage area.
Setting suggestions:
Set shared_buffers to a value less than 40% of the memory. Set it to a large value for row-store tables and a small value for column-store tables. For column-store tables: shared_buffers = (Memory of a single server/Number of database nodes on the server) x 0.4 x 0.25
If shared_buffers is set to a larger value, increase the value of checkpoint_segments because a longer period of time is required to write a large amount of new or changed data.
bulk_write_ring_size
Parameter description: Specifies the size of a ring buffer used for parallel data import.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
Value range: an integer ranging from 16384 to 2147483647. The unit is KB.
Default value: 2GB
Setting suggestions: Increase the value of this parameter on database nodes if a huge amount of data will be imported.
standby_shared_buffers_fraction
Parameter description: Specifies the shared_buffers proportion used on the server where a standby instance is deployed.
This parameter is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Value range: a double-precision floating-point number ranging from 0.1 to 1.0
Default value: 0.3
temp_buffers
Parameter description: Specifies the maximum size of local temporary buffers used by a database session.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.
This parameter can be modified only before the first use of temporary tables within each session. Subsequent attempts to change the value of this parameter will not take effect on that session.
A session allocates temporary buffers based on the value of temp_buffers. If a large value is set in a session that does not require many temporary buffers, only the overhead of one buffer descriptor is added. If a buffer is used, additional 8192 bytes will be consumed for it.
Value range: an integer ranging from 100 to 1073741823. The unit is 8 KB.
Default value: 1MB
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 the primary server. 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 536870911
Default value: 800
NOTE:
Set this parameter to a value greater than or equal to that of max_connections to avoid failures in preparation.
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: 64MB
Setting suggestions:
If the physical memory specified by work_mem is insufficient, additional operator calculation data will be written into temporary tables based on query characteristics and the degree of parallelism. This reduces performance by five to ten times, and prolongs the query response time from seconds to minutes.
- For complex serial queries, each query requires five to ten associated operations. Set work_mem using the following formula: work_mem = 50% of the memory/10.
- For simple serial queries, each query requires two to five associated operations. Set work_mem using the following formula: work_mem = 50% of the memory/5.
- For concurrent queries, set work_mem using the following formula: work_mem = work_mem for serial queries/Number of concurrent SQL statements.
query_mem
Parameter description: Specifies the memory used by a query. If the value of query_mem is greater than 0, the optimizer adjusts the memory cost estimate to this value when generating an execution plan.
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. If the value is set to a negative value or is less than 32 MB, the default value 0 is used. In this case, the optimizer does not adjust the estimated query memory.
Default value: 0
query_max_mem
Parameter description: Specifies the maximum memory that can be used by a query. If the value of query_max_mem is greater than 0, an error is reported when the query memory usage exceeds the value.
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. If the value is set to a negative value or a positive integer less than 32 MB, the default value 0 is used. In this case, the optimizer does not limit the query memory.
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: 128MB
Setting suggestions:
- The value of this parameter must be greater than that of work_mem so that database dumps can be more quickly cleared or restored. In a database session, only one maintenance operation can be performed at a time. Maintenance is usually performed when there are not many running sessions.
- When the Automatic Vacuuming process is running, up to autovacuum_max_workers times this memory may be allocated. In this case, set maintenance_work_mem to a value greater than or equal to that of work_mem.
- If a large amount of data is to be clustered, increase the value of this parameter in the session.
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 with 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.
Several running sessions could be performing such operations concurrently. Therefore, the total memory used may be many times the value of psort_work_mem.
Value range: an integer ranging from 64 to 2147483647. The unit is KB.
Default value: 512MB
max_loaded_cudesc
Parameter description: Specifies the number of loaded CuDescs per 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.
If max_loaded_cudesc is set to a large value, memory may be insufficient.
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.
When setting this parameter, comply with the following principles:
- The database needs to reserve 640 KB stack depth. Therefore, the ideal setting for this parameter is the actual stack size limit enforced by the OS kernel (as set by ulimit -s) minus 640 KB.
- Setting this parameter to a value larger than the actual kernel limit means that a running recursive function may crash an individual backend process. In an OS, such as an SLES OS, where openGauss can determine the kernel limit, this parameter cannot be set to an unsafe value.
- Since not all OSs provide this function, you are advised to set a specific value for this parameter.
Value range: an integer ranging from 100 to INT_MAX. The unit is KB.
Default value: 2MB
2MB is a small value and will hardly cause system breakdown, but may lead to execution failures of complex functions.
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: 1GB
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 ring buffer size used for parallel data export.
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: 16MB