Memory

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: 512 MB

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

• GaussDB forcibly sets enable_memory_limit to off. Metadata is the memory used in GaussDB and is related to some concurrent parameters, such as max_connections, thread_pool_attr and max_prepared_transactions.
• If this parameter is set to off, the memory used by the database is not limited. When a large number of concurrent or complex queries are performed, too much memory is used, which may cause OS OOM problems.

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:

1400 GB (196-core CPU/1536 GB memory); 900 GB (128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory); 680 GB (96-core CPU/768 GB memory); 560 GB (80-core CPU/640 GB memory); 450 GB (64-core CPU/512 GB memory); 420 GB (60-core CPU/480 GB memory); 200 GB (32-core CPU/256 GB memory); 90 GB (16-core CPU/128 GB memory); 40 GB (8-core CPU/64 GB memory); 20 GB (4-core CPU/32 GB memory); 10 GB (4-core CPU/16 GB memory)

Setting suggestions:

The value on the database node is 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 – vm.min_free_kbytes) \ x 0.7 /(1 + Number of primary nodes) This parameter is used to prevent node OOM caused by memory usage increase, ensuring system reliability. vm.min_free_kbytes indicates the OS memory reserved for the kernel to receive and send data. Its value is at least 5% of the total memory. That is, max_process_memory = Physical memory x 0.665 / (1 + Number of primary nodes)

If this parameter is set to a value greater than the physical memory of the server, the OS OOM problem may occur.

enable_memory_context_control

Parameter description: Specifies whether to enable the function of checking whether the number of memory contexts exceeds the specified limit. This parameter applies only to the DEBUG version.

This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

• on indicates that the function of checking the number of memory contexts is enabled.
• off indicates that the function of checking the number of memory contexts is disabled.

Default value: off

uncontrolled_memory_context

Parameter description: Specifies which memory context will not be checked when the enable_memory_context_control parameter is enabled. 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 GaussDB. Increasing the value of this parameter causes GaussDB 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 8 KB.

The value of shared_buffers must be an integer multiple of BLCKSZ. Currently, BLCKSZ is set to 8 KB. That is, the value of shared_buffers must be an integer multiple of 8 KB. The minimum value changes according to BLCKSZ.

Default value:

560 GB (196-core CPU/1536 GB memory); 360 GB (128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory); 270 GB (96-core CPU/768 GB memory); 220 GB (80-core CPU/640 GB memory); 180 GB (64-core CPU/512 GB memory); 160 GB (60-core CPU/480 GB memory); 80 GB (32-core CPU/256 GB memory); 36 GB (16-core CPU/128 GB memory); 16 GB (8-core CPU/64 GB memory); 8 GB (4-core CPU/32 GB memory); 4 GB (4-core CPU/16 GB memory)

Setting suggestions:

1. Set shared_buffers to a value less than 40% of the memory.
2. 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.
3. If the process fails to be restarted after the value of shared_buffers is changed, perform either of the following operations based on the error information:
   1. Adjust the kernel.shmall, kernel.shmmax, and kernel.shmmin OS parameters. For details, see section "Configuring Other OS Parameters" in Installation Guide.
   2. Run the free -g command to check whether the available memory and swap space of the OS are sufficient. If the memory is insufficient, manually stop other user programs that occupy much memory.
   3. Do not set shared_buffers to an excessively large or small value.

segment_buffers

Parameter description: This parameter is reserved and is not supported currently.

bulk_write_ring_size

Parameter description: Specifies the size of the ring buffer used by the operation when a large amount of data is written (for example, the copy operation).

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: 2 GB

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 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: 1

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.

temp_buffers 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: 1 MB

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 GaussDB to request more System V shared memory than the default configuration allows.

When GaussDB 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:

200 (196-core CPU/1536 GB memory, 128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory, 80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory, 32-core CPU/256 GB memory, 16-core CPU/128 GB memory, 8-core CPU/64 GB memory, 4-core CPU/32 GB memory); 0 (4-core CPU/16 GB memory)

Generally, explicit PREPARE operations are not required for transactions. If explicit PREPARE operations are performed for transactions, increase the value of this parameter to be greater than the number of concurrent services that require PREPARE to prevent preparation failures.

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:

280 MB (196-core CPU/1536 GB memory); 256 MB (128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory); 128 MB (80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory, 32-core CPU/256 GB memory, 16-core CPU/128 GB memory); 64 MB (8-core CPU/64 GB memory) ; 32 MB (4-core CPU/32 GB memory); 16 MB (4-core CPU/16 GB memory)

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.
• BitmapScan hash tables are also restricted by work_mem, but will not be forcibly flushed to disks. In the case of complete lossify, every 1 MB memory occupied by the hash table corresponds to a 16 GB page of BitmapHeapScan (32 GB for Ustore). After the upper limit of work_mem is reached, the memory increases linearly with the data access traffic based on this ratio.

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

• 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.
• 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 adjust the estimated query memory.

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

• If the value of query_max_mem is greater than 0, an error is reported when the query memory usage exceeds the value.
• 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.

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:

2 GB (196-core CPU/1536 GB memory, 128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory, 80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory); 1 GB (32-core CPU/256 GB memory); 512 MB (16-core CPU/128 GB memory); 256 MB (8-core CPU/64 GB memory); 128 MB (4-core CPU/32 GB memory); 64 MB (4-core CPU/16 GB memory)

Setting suggestions:

• The value of this parameter must be greater than that of work_mem so that database dumps can be cleared or restored more quickly. 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.

max_stack_depth

Parameter description: Specifies the maximum safe depth of the GaussDB 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.

When setting this parameter, comply with the following principles:

• The database needs to reserve 640 KB stack depth. Therefore, the ideal value of this parameter is the actual stack size limit enforced by the OS kernel (as set by ulimit -s) minus 640 KB.
• If the value of this parameter is greater than the value of ulimit -s minus 640 KB before the database is started, the database fails to be started. During database running, if the value of this parameter is greater than the value of ulimit -s minus 640 KB, this parameter does not take effect.
• If the value of ulimit -s minus 640 KB is less than the minimum value of this parameter, the database fails to be started.
• Setting this parameter to a value greater than the actual kernel limit means that a running recursive function may crash an individual backend process.
• Since not all OSs provide this function, you are advised to set a specific value for this parameter.
• The default value is 2 MB, which is relatively small and does not easily cause system breakdown.

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

enable_early_free

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 is a 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:

32 MB (196-core CPU/1536 GB memory); 16 MB (128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory, 80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory, 32-core CPU/256 GB memory, 16-core CPU/128 GB memory, 8-core CPU/64 GB memory, 4-core CPU/32 GB memory, 4-core CPU/16 GB memory)

memory_trace_level

Parameter description: Specifies the control level for recording memory allocation information after the dynamic memory usage exceeds 90% of the maximum dynamic memory. This parameter takes effect only when use_workload_manager and enable_memory_limit are enabled. This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: enumerated values

• none: indicates that memory allocation information is not recorded.
• level1: After the dynamic memory usage exceeds 90% of the maximum dynamic memory, the following information is recorded and the recorded memory information is saved in the $GAUSSLOG/mem_log directory.
  • Global memory overview.
  • Memory usage of the top 20 memory contexts of the instance, session, and thread types.
  • The totalsize and freesize columns for each memory context.
• level2: After the dynamic memory usage exceeds 90% of the maximum dynamic memory, the following information is recorded and the recorded memory information is saved in the $GAUSSLOG/mem_log directory.
  • Global memory overview.
  • Memory usage of the top 20 memory contexts of the instance, session, and thread types.
  • The totalsize and freesize columns for each memory context.
  • Detailed information about all memory allocations in each memory context, including the file where the allocated memory is located, line number, and size.

Default value: level1

• If this parameter is set to level2, the memory allocation details (file, line, and size) of each memory context are recorded, which greatly affects the performance. Therefore, exercise caution when setting this parameter.
• You can use the system function gs_get_history_memory_detail(cstring) to query the recorded memory snapshot information. For details about the function, see section "SQL Reference > Functions and Operators > Statistics Functions" in Developer Guide.
• The recorded memory context is obtained after all memory contexts of the same type with the same name are summarized.

resilience_memory_reject_percent

Parameter description: Specifies the dynamic memory usage percentage for escape from memory overload. This parameter takes effect only when the GUC parameters use_workload_manager and enable_memory_limit are enabled. This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: a string, consisting of one or more characters

This parameter consists of recover_memory_percent and overload_memory_percent. The meanings of the two parts are as follows:

• recover_memory_percent: Percentage of the dynamic memory usage when the memory recovers from overload to the maximum dynamic memory. When the dynamic memory usage is less than the maximum dynamic memory multiplied by the value of this parameter, the overload escape function is disabled and new connections are allowed. The value ranges from 0 to 100. The value indicates a percentage.
• overload_memory_percent: Percentage of the dynamic memory usage to the maximum dynamic memory when the memory is overloaded. When the dynamic memory usage is greater than the maximum dynamic memory multiplied by the value of this parameter, the current memory is overloaded. In this case, the overload escape function is triggered to kill sessions and new connections are prohibited. The value ranges from 0 to 100. The value indicates a percentage.

Default value: '0,0', indicating that the escape from memory overload function is disabled.

Example:

resilience_memory_reject_percent = '70,90'

When the memory usage exceeds 90% of the upper limit, new connections are forbidden and stacked sessions are killed. When the memory usage is less than 70% of the upper limit, session killing is stopped and new connections are allowed.

• You can query the maximum dynamic memory and used dynamic memory in the gs_total_memory_detail view. max_dynamic_memory indicates the maximum dynamic memory, and dynamic_used_memory indicates the used dynamic memory.
• If this parameter is set to a small value, the escape from memory overload process is frequently triggered. As a result, ongoing sessions are forcibly logged out, and new connections fail to be connected for a short period of time. Therefore, exercise caution when setting this parameter based on the actual memory usage.
• The values of recover_memory_percent and overload_memory_percent can be 0 at the same time. In addition, the value of recover_memory_percent must be smaller than that of overload_memory_percent. Otherwise, the setting does not take effect.

resilience_escape_user_permissions

Parameter description: Specifies the escape permission of users. You can set it for multiple users and separate users by commas (,). The value sysadmin indicates that jobs of the sysadmin user can be canceled by the escape function. The value monadmin indicates that jobs of the monadmin user can be canceled by the escape function. By default, this parameter is left blank, indicating that the escape function of the sysadmin and monadmin users is disabled. The value can only be sysadmin, monadmin, or an empty string. This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Value range: a string, consisting of one or more characters

Default value: '', indicating that the escape function of the sysadmin and monadmin users is disabled.

Example:

resilience_escape_user_permissions = 'sysadmin,monadmin'

The escape function is enabled for both the sysadmin and monadmin users.

• You can set this parameter to multiple values separated by commas (,), for example, resilience_escape_user_permissions = 'sysadmin,monadmin'. You can also set this parameter to only one value, for example, resilience_escape_user_permissions = 'monadmin'.
• If this parameter is set for multiple times, the latest setting takes effect.
• If this parameter is set to any value in the value range, common users support the escape function.
• If a user has both the sysadmin and monadmin role permissions, the escape function of the user can be triggered only when resilience_escape_user_permissions is set to 'sysadmin,monadmin'.