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 for PostgreSQL(Compatible with Oracle) 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 for PostgreSQL(Compatible with Oracle) varies based on PolarDB for PostgreSQL(Compatible with Oracle) 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_worker_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 for PostgreSQL(Compatible with Oracle) parameter settings
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 for PostgreSQL(Compatible with Oracle)
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 for PostgreSQL(Compatible with Oracle) varies based on PolarDB for PostgreSQL(Compatible with Oracle) 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. |