After you know the current status and problems of your database based on information such as database statistics and slow SQL queries, you can modify the parameter settings of your database to solve these problems. The default parameter template of PolarDB O Edition is applicable to most common database scenarios but cannot meet the requirements of some special database scenarios. You can modify the database parameters to improve cluster performance.

Resource consumption parameters

Parameter Description
shared_buffers The size of memory used by database servers for shared memory buffers. A larger buffer can cache more data. The value of this parameter in PolarDB-O varies based on PolarDB-O cluster specifications.
work_mem The amount of memory to be used by internal sort operations and hash tables before data is written to temporary disk files. For a complex query, several sort or hash operations may run in parallel. Each operation is allowed to use the amount of memory that is specified by this parameter before the operation starts to write data into temporary files. Several running sessions may also perform such operations in parallel. Therefore, the total used memory may be several times the value of the work_mem parameter. Take this into consideration when you specify this parameter. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
maintenance_work_mem

The maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. In a database session, only one of these operations can be executed at a time point. In most cases, a few such operations run in parallel when you use the database. Therefore, to prevent memory exhaustion, set this parameter to a value that is much greater than the value of the work_mem parameter. A large value improves the performance of vacuuming and restoring database dumps.

When the autovacuum worker process runs, the system may allocate memory up to autovacuum_max_workers times as much as the amount of memory that is specified by the maintenance_work_mem parameter. Therefore, do not set the maintenance_work_mem parameter to a large value. Independent setting of the autovacuum_work_mem parameter is helpful.

autovacuum_work_mem The maximum amount of memory that each autovacuum worker process can use. The default value is -1. This indicates that the value of the maintenance_work_mem parameter is used.
temp_file_limit The maximum disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction that attempts to exceed this limit will be canceled. The unit is kilobyte. The value -1 indicates no limit.
max_worker_processes The maximum number of background processes that the system can support. You can specify this parameter only before a server start.
max_parallel_workers The maximum number of workers that the system can support for parallel queries. If the value of this parameter is greater than the value of the max_worder_processes parameter, the value of this parameter does not take effect. Parallel queries may consume more resources than non-parallel queries, because each worker process is an independent process. A worker process roughly has the same impact on the system as an additional user session.
max_parallel_workers_per_gather The maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are obtained from a process pool. The max_worker_processes parameter specifies the number of processes in the pool. The maximum number of parallel workers to be obtained cannot exceed the value of the max_parallel_workers parameter.
max_parallel_maintenance_workers The maximum number of parallel workers that can be started by a single CREATE INDEX operation.

Parameters related to autovacuum

Parameter Description
autovacuum_max_workers The maximum number of autovacuum processes that can run at the same time, except the autovacuum launcher. Default value: 3. You can specify this parameter only before a server start.
autovacuum_naptime The minimum delay for autovacuum to run on a database. In each round, the background process checks the database, and issues the VACUUM and ANALYZE commands for tables in the database as needed. Unit: seconds. Default value: 1min.
autovacuum_vacuum_threshold The minimum number of inserted, updated, or deleted tuples that are needed to trigger a VACUUM operation in a table. Default value: 50. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_analyze_threshold The minimum number of inserted, updated, or deleted tuples that are needed to trigger an ANALYZE operation in a table. Default value: 50. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_vacuum_scale_factor The fraction of the table size to be added to autovacuum_vacuum_threshold when a decision is made on whether to trigger a VACUUM operation. Default value: 0.05. This indicates 5% of the table size. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_analyze_scale_factor The fraction of the table size to be added to autovacuum_analyze_threshold when a decision is made on whether to trigger an ANALYZE operation. Default value: 0.1. This indicates 10% of the table size. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_vacuum_cost_delay The cost delay value to be used in automatic VACUUM operations. Default value: -1. If this parameter is set to -1, the value of the vacuum_code_delay parameter is used. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_vacuum_cost_limit The cost limit value to be used in automatic VACUUM operations. Default value: -1. If this parameter is set to -1, the value of the vacuum_cost_limit parameter is used. If multiple autovacuum workers exist, this value is distributed in proportion among the running autovacuum workers. Therefore, the sum of the limit values for each worker does not exceed the value of the autovacuum_vacuum_cost_limit parameter. You can overwrite this parameter setting by modifying table storage parameters.

Query planning parameters

We recommend that you do not modify global settings of the following parameters. When you test a specific query, you can modify parameters in the current session.

Parameter Description
enable_bitmapscan Specifies whether to enable the query planner to use bitmap-scan plan types.
enable_hashagg Specifies whether to enable the query planner to use hashed aggregation plan types.
enable_hashjoin Specifies whether to enable the query planner to use hash-join plan types.
enable_indexscan Specifies whether to enable the query planner to use index-scan plan types.
enable_indexonlyscan Specifies whether to enable the query planner to use index-only-scan plan types.
enable_seqscan Specifies whether to enable the query planner to use sequential scan plan types. Sequential scans cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods.
enable_sort Specifies whether to enable the query planner to use explicit sort steps. Explicit sorts cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods.
enable_mergejoin Specifies whether to enable the query planner to use merge-join plan types.
enable_nestloop Specifies whether to enable the query planner to use nested-loop join plans. Nested-loop joins cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods.
enable_parallel_append Specifies whether to enable the query planner to use parallel-aware append plan types.
enable_parallel_hash Specifies whether to enable the query planner to use hash-join plan types for parallel hashes. If hash-join plans are not enabled, this parameter does not take effect.

Planner cost parameters

Parameter Description
seq_page_cost The planner-estimated cost of a sequential disk page fetch. Default value: 1.0.
random_page_cost The planner-estimated cost of a non-sequential disk page fetch. Default value: 4.0.
cpu_tuple_cost The planner-estimated cost of processing each row during a query. Default value: 0.01.
cpu_index_tuple_cost The planner-estimated cost of processing each index entry during an index scan. Default value: 0.005.
cpu_operator_cost The planner-estimated cost of processing each operator or function during a query. Default value: 0.0025.
parallel_setup_cost The planner-estimated cost of starting parallel worker processes. Default value: 1000.
parallel_tuple_cost The planner-estimated cost of transferring one tuple from a parallel worker process to another process. Default value: 0.1.
min_parallel_table_scan_size The minimum amount of table data that must be scanned. If the amount of scanned table data exceeds this value, the use of parallel scans is considered. For a parallel sequential scan, the amount of scanned table data is always equal to the size of the table. However, if indexes are used, the amount of scanned table data is less than the size of the table in most cases. Default value: 8 MB.
min_parallel_index_scan_size The minimum amount of index data that must be scanned. If the amount of scanned index data exceeds this value, the use of parallel scans is considered. In most cases, a parallel index scan does not involve the entire index, but involves the number of relevant pages that the planner believes to be scanned. Default value: 512 KB.

PolarDB-O parameter settings

Table 1. Details of pg_setting
Column Value Description
name vacuum_cost_delay The name of the parameter.
setting 10 The current value of the parameter.
unit ms The unit of the parameter.
category Resource Usage / Cost-Based Vacuum Delay The category of the parameter.
short_desc Vacuum cost delay in milliseconds. The description of the parameter.
extra_desc None The additional description of the parameter.
context user The context that is required to specify the parameter value.
vartype integer The type of the parameter. Valid values: bool, enum, integer, real, and string.
source database The source of the parameter value.
min_val 0 The minimum allowed value of the parameter.
max_val 100 The maximum allowed value of the parameter.
enumvals None The allowed values of an enum parameter.
boot_val 0 The parameter value to be used during server startup if the parameter is left empty.
reset_val 10 The value to which the parameter is reset in the current session.
sourcefile None The profile in which the current value is specified.
sourceline None The line number of the profile in which the current value is specified.
pending_restart f If the parameter is modified and a restart is required, use the value true. Otherwise, use the value false.

Resource consumption parameters of PolarDB-O

Parameter Description
shared_buffers The size of memory used by database servers for shared memory buffers. A larger buffer can cache more data. The value of this parameter in PolarDB-O varies based on PolarDB-O cluster specifications.
work_mem The amount of memory to be used by internal sort operations and hash tables before data is written to temporary disk files. For a complex query, several sort or hash operations may run in parallel. Each operation is allowed to use the amount of memory that is specified by this parameter before the operation starts to write data into temporary files. Several running sessions may also perform such operations in parallel. Therefore, the total used memory may be several times the value of the work_mem parameter. Take this into consideration when you specify this parameter. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
maintenance_work_mem

The maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. In a database session, only one of these operations can be executed at a time point. In most cases, a few such operations run in parallel when you use the database. Therefore, to prevent memory exhaustion, set this parameter to a value that is much greater than the value of the work_mem parameter. A large value improves the performance of vacuuming and restoring database dumps.

When the autovacuum worker process runs, the system may allocate memory up to autovacuum_max_workers times as much as the amount of memory that is specified by the maintenance_work_mem parameter. Therefore, do not set the maintenance_work_mem parameter to a large value. Independent setting of the autovacuum_work_mem parameter is helpful.

autovacuum_work_mem The maximum amount of memory that each autovacuum worker process can use. The default value is -1. This indicates that the value of the maintenance_work_mem parameter is used.
temp_file_limit The maximum disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction that attempts to exceed this limit will be canceled. The unit is kilobyte. The value -1 indicates no limit.