Modifying Database Parameters

After a cluster is created, you can modify the cluster's database parameters as required. On the GaussDB(DWS) management console, you can view or set common database parameters. For details, see Managing Parameter Templates. You can run SQL commands to view or set other database parameters. For details, see Setting Configuration Parameters in the Data Warehouse Service Database Development Guide.

Prerequisites

You can modify parameters only when no task is running in the cluster.

Procedure

Log in to the GaussDB(DWS) management console.
In the navigation pane on the left, choose Clusters.
In the cluster list, find the target cluster and click the cluster name. The Basic Information page is displayed.
Click the Parameters tab and modify the parameter values. Then click Save.
In the Modification Preview dialog box, confirm the modifications and click Save.

Parameter Description

The following table describes part of the database parameters. You can search for and check more parameters by following the instructions in Procedure.

The default values of the following parameters are for reference only. For more information, see "Setting GUC Parameters".
After you modify parameters, restart the cluster to make the new settings take effect. Before the restart, the old parameter settings are still displayed.

**Table 1** Parameters
Parameter	Description	Default Value
session_timeout	Specifies the timeout interval of an idle session, in seconds. The value 0 indicates that the timeout limit is disabled. The value ranges from 0 to 86400.	600
datestyle	Sets the display format for date and time.	ISO,MDY
failed_login_attempts	Sets the number of consecutive incorrect password attempts after which the account is locked. The value 0 indicates that the number of incorrect password attempts is not limited. The value ranges from 0 to 1000.	10
timezone	Sets the time zone displayed in the time stamps.	UTC
log_timezone	Sets the time zone for timestamps in the server log.	UTC
enable_resource_record	Specifies whether to enable resource recording. If the actual execution time of an SQL statement is greater than the value of resource_track_duration (the default value is 60s; customizable), the monitoring information will be archived. This function will cause storage space expansion and slightly affect system performance. Disable it when it is not required. NOTE: Archiving: The monitoring information is stored in the history view and archived in the info table. The archiving time is 3 minutes. After the archiving, records in the history view are cleared. History view GS_WLM_SESSION_HISTORY, which corresponds to info table GS_WLM_SESSION_INFO History view GS_WLM_OPERATOR_HISTORY, which corresponds to info table GS_WLM_OPERATOR_INFO	off
query_dop	Sets the Symmetric Multi-Processing (SMP) degree. Value 0 indicates that the SMP is adaptive. Value 1 indicates that the SMP is disabled. Value 2 indicates that the SMP degree is 2.	0
resource_track_cost	Sets the minimum execution cost for resource monitoring on statements. The value -1 indicates that resource monitoring is disabled (execution cost less than 10). If the value is greater than or equal to 0, and the cost of executing statements exceeds the value and is greater than or equal to 10, resource monitoring is performed. You can run the SQL command Explain to query the estimated execution cost of an SQL statement.	100000
resource_track_duration	Sets the minimum time for archiving executed statements recorded during real-time monitoring, in seconds. The value 0 indicates that all the statements are archived. If the value is greater than 0, historical statements are archived when the execution time of the statements exceeds this value.	60
password_effect_time	Sets the validity period of the account password. When the password is about to expire or has expired, the system prompts the user to change the password. The value ranges from 0 to 999, in days. If this parameter is set to 0, the function is disabled.	90
update_lockwait_timeout	Sets the maximum duration that a lock waits for concurrent updates on a row to complete. If the lock wait time exceeds the value, the system will report an error. If this parameter is set to 0, an error is reported immediately when a lock appears. The unit is milliseconds.	120000
enable_resource_track	Specifies whether to enable resource monitoring. After you enable this function, SQL statements can be monitored.	on
password_policy	Specifies whether to check the password complexity when you create a GaussDB(DWS) account using CREATE ROLE/CREATE USER, or modify the account using ALTER ROLE/ALTER USER. 0 indicates that no password complexity policy is used. 1 indicates that the default password complexity policy is used.	1
password_reuse_time	Specifies whether to check the password reuse interval when you modify the account password using ALTER USER/ALTER ROLE. The value ranges from 0 to 3650, in days. 0 indicates that the password reuse interval is not restricted. A positive number indicates that the new password cannot be chosen from passwords in history that are newer than the specified number of days. NOTE: The password_reuse_time and password_reuse_max parameters are checked during password change. If the value of either password_reuse_time or password_reuse_max is positive, the password can be reused. If the value of password_reuse_time is 0, password reuse is restricted based on the number of password changes, but not on the time elapsed. If the values of both parameters are 0, password reuse is not restricted.	60
password_reuse_max	Specifies whether to check the number of password changes when you modify the account password using ALTER USER/ALTER ROLE. 0 indicates that the password is not restricted by the number of password changes. A positive number indicates that the new password cannot be chosen from the specified number of the most recent passwords. NOTE: The password_reuse_time and password_reuse_max parameters are checked during password change. If the value of either password_reuse_time or password_reuse_max is positive, the password can be reused. If the value of password_reuse_max is 0, the password is not restricted based on the time elapsed, but not on the number of password changes. If the values of both parameters are 0, password reuse is not restricted.	0
password_lock_time	Specifies the duration before a locked account is automatically unlocked. 0 indicates that the account is not automatically locked if the password verification fails. A positive number indicates the duration after which a locked account is automatically unlocked.	1
password_encryption_type	Specifies the encryption type of user passwords. 0 indicates that passwords are encrypted with MD5. 1 indicates that passwords are encrypted with SHA-256, which is compatible with the MD5 user authentication method of the PostgreSQL client. 2 indicates that passwords are encrypted with SHA-256. MD5 is not recommended because it is not a secure encryption algorithm.	2
password_notify_time	Specifies how many days in advance a user is notified before a password expires. 0 indicates that the notification is disabled. A value ranging from 1 to 999 indicates the number of days prior to password expiration that a user will receive a notification.	7
enable_stateless_pooler_reuse	Specifies whether to enable the pooler reuse mode. The setting takes effect after the cluster is restarted. on indicates that the pooler reuse mode is enabled. off indicates that the pooler reuse mode is disabled. NOTE: Set this parameter to the same value for CNs and DNs. If this parameter is set to off for CNs and on for DNs, the cluster communication fails. Restart the cluster for the setting to take effect.	off
work_mem	Specifies the amount of memory to be used by internal sort operations and hash tables before they write data into temporary disk files, in KB. 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.	64MB
maintenance_work_mem	Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY, in KB. NOTE: This parameter may affect the execution efficiency of VACUUM, VACUUM FULL, CLUSTER, and CREATE INDEX.	128MB
enable_orc_cache	Specifies whether to reserve 1/4 of cstore_buffers for storing ORC metadata when cstore_buffers is initialized. on indicates that the ORC metadata is cached, which improves the query performance of HDFS tables but occupies column-store caches. As a result, the column-store performance is compromised. off indicates that the ORC metadata is not cached.	on
sql_use_spacelimit	Specifies the space size for files to be flushed to disks when a single SQL statement is executed on a single DN, in KB. The managed space includes the space occupied by ordinary tables, temporary tables, and intermediate result sets to be flushed to disks. -1 indicates no limit.	-1
enable_bitmapscan	Specifies whether to enable the optimizer's use of bitmap-scan plan types. on off	on
enable_hashagg	Specifies whether to enable the optimizer's use of hash aggregation plan types. on off	on
enable_hashjoin	Specifies whether enable the optimizer's use of hash join plan types. on off	on
enable_indexscan	Specifies whether to enable the optimizer's use of index-scan plan types. on off	on
enable_indexonlyscan	Specifies whether to enable the optimizer's use of index-only-scan plan types. on off	on
enable_mergejoin	Specifies whether the optimizer's use of merge-join plan types. on off	off
enable_nestloop	Specifies whether the optimizer's use of nested-loop-join plan types. It is impossible to suppress nested-loop joins entirely, but disabling this parameter encourages the optimizer to choose other methods if available. on off	off
enable_seqscan	Specifies whether enable the optimizer's use of sequential-scan plan types. It is impossible to suppress sequential scans entirely, but disabling this parameter encourages the optimizer to choose other methods if available. on off	on
enable_tidscan	Specifies whether enable the optimizer's use of TID scan plan types. on off	on
enable_kill_query	In CASCADE mode, when a user is deleted, all the objects belonging to the user are deleted. This parameter specifies whether the queries of the objects belonging to the user can be unlocked when the user is deleted. on indicates that the unlocking is allowed. off indicates that the unlocking is not allowed.	off
enable_vector_engine	Specifies whether to enable the optimizer's use of vectorized execution engines. on off	on
enable_broadcast	Specifies whether to enable the optimizer's use of broadcast distribution when it evaluates the cost of stream. on off	on
skew_option	Specifies whether to enable an optimization policy. off indicates that the policy is disabled. normal indicates that a radical policy is used. All possible skews are optimized. lazy indicates that a conservative policy is used. Uncertain skews are ignored.	normal
default_statistics_target	Specifies the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. If this parameter is set to a positive number, it indicates the number of samples of statistics information. If this parameter is set to a negative number, percentage is used to set the statistic target. The negative number converts to its corresponding percentage, for example, -5 means 5%.	100
enable_codegen	Specifies whether to enable code optimization. Currently, LLVM optimization is used. on off	on
autoanalyze	Specifies whether to automatically collect statistics on tables that have no statistics when a plan is generated. on indicates that the table statistics are automatically collected. off indicates that the table statistics are not automatically collected. NOTE: This parameter is now not available to foreign tables. If you need the statistics, manually perform the analyze operation. This parameter is not available to temporary tables with the ON COMMIT [DELETE ROWS\|DROP] option. If you need the statistics, manually perform the analyze operation. If an exception occurs in the database during the execution of autoanalyze on a table, after the database is recovered, the system may still prompt you to collect the statistics of the table when you run the statement again. In this case, manually perform ANALYZE on the table to synchronize statistics.	off
enable_sonic_hashagg	Specifies whether to enable the hash aggregation operator designed for column-oriented hash tables when certain constraints are met. on off	on
log_hostname	By default, connection log messages only show the IP address of the connecting host. The host name can be recorded when this parameter is set to on. It may take some time to parse the host name. Therefore, the database performance may be affected. on off	off
max_active_statements	Specifies the maximum number of concurrent jobs. This parameter applies to all the jobs on one CN. The values -1 and 0 indicate that the number of concurrent jobs is not limited.	60
enable_resource_track	Specifies whether to enable resource monitoring.	on
resource_track_level	Sets the resource monitoring level of the current session. This parameter is valid only when enable_resource_track is set to on. none indicates that resources are not monitored. query enables the query-level resource monitoring. If this function is enabled, the plan information (similar to the output information of explain) of SQL statements will be recorded in top SQL statements. perf enables the perf-level resource monitoring. If this function is enabled, the plan information (similar to the output information of EXPLAIN ANALYZE) that contains the actual execution time and the number of execution rows will be recorded in top SQL statements. operator enables the operator-level resource monitoring. If this function is enabled, not only the information including the actual execution time and number of execution rows is recorded in the top SQL statements, but also the operator-level execution information is updated to the top SQL statements.	query
enable_dynamic_workload	Specifies whether to enable dynamic load management. on off	on
topsql_retention_time	Specifies the data storage retention period of the gs_wlm_session_info and gs_wlm_operator_info catalogs in historical top SQL statements. The unit is day. If it is set to 0, the data is stored permanently. If the value is greater than 0, the data is stored for the specified number of days.	0
track_counts	Specifies whether to enable collection of statistics on database activities. on off	off
autovacuum	Specifies whether to enable the autovacuum process. track_counts must be set to on for autovacuum to work. on off	off
autovacuum_mode	Specifies the autovacuum mode. autovacuum must be set to on. analyze indicates that only autoanalyze is performed. vacuum indicates that only autovacuum is performed. mix indicates that both are performed. none indicates that neither is performed.	mix
autoanalyze_timeout	Specifies the autoanalyze timeout period, in seconds. If the duration of autoanalyze on a table exceeds the value of autoanalyze_timeout, the autoanalyze operation is automatically canceled.	5min
autovacuum_io_limits	Specifies the maximum number of I/Os triggered by the autovacuum process per second. –1 indicates that the default cgroup is used.	-1
autovacuum_max_workers	Specifies the maximum number of concurrent autovacuum threads. 0 indicates that autovacuum is disabled.	3
autovacuum_naptime	Specifies the interval between two autovacuum operations, in seconds.	10min
autovacuum_vacuum_threshold	Specifies the threshold for triggering VACUUM. When the number of deleted or updated records in a table exceeds the specified threshold, the VACUUM operation is executed on this table.	50
autovacuum_analyze_threshold	Specifies the threshold for triggering ANALYZE. When the number of deleted, inserted, or updated records in a table exceeds the specified threshold, the ANALYZE operation is executed on this table.	50
autovacuum_analyze_scale_factor	Specifies a fraction of the table size added to the autovacuum_analyze_threshold parameter when deciding whether to analyze a table.	0.1
statement_timeout	Specifies the statement timeout interval, in milliseconds. When the execution time of a statement exceeds the value (starting from the time when the server receives the command), the statement reports an error and exits.	0
deadlock_timeout	Specifies the deadlock timeout interval, in milliseconds. When the applied lock exceeds the value, the system will check whether a deadlock occurs.	1s
lockwait_timeout	Specifies the maximum wait time for a single lock, in milliseconds. If the lock wait time exceeds the value, the system will report an error.	20min
max_query_retry_times	Specifies the maximum number of automatic retry times when an SQL statement error occurs. Currently, a statement can start retrying if the following errors occur: Connection reset by peer, Lock wait timeout, and Connection timed out. If this parameter is set to 0, the retry function is disabled.	6
max_pool_size	Specifies the maximum number of connections between the connection pool of a CN and another CN or DN.	800
enable_gtm_free	Specifies whether the GTM-FREE mode is enabled. In large concurrency scenarios, the snapshots delivered by the GTM increase in number and size. The network between the GTM and the CN becomes the performance bottleneck. The GTM-FREE mode is used to eliminate the bottleneck. In this mode, the CN communicates with DNs instead of the GTM. The CN sends queries to each DN, which locally generates snapshots and XIDs, ensuring external write consistency but not external read consistency.	off
enable_fast_query_shipping	Specifies whether to enable the optimizer's use of a distributed framework.	on
enable_crc_check	Specifies whether to enable data checks. Check information is generated when table data is written and is checked when the data is read. You are not advised to modify the settings.	on
explain_perf_mode	Specifies the display format of explain. normal indicates that the default printing format is used. pretty indicates that the optimized display format of GaussDB(DWS) is used. The new format contains a plan node ID, directly and effectively analyzing performance. summary indicates that analysis of the pretty printed information is added. run indicates that the system exports the printed information specified by summary as a CSV file for further analysis.	pretty
udf_memory_limit	Specifies the maximum physical memory that can be used when UDFs are executed on each CN and DN, in KB.	200MB
default_transaction_read_only	Specifies whether each newly created transaction is read only. on indicates the transaction is read only. off indicates the transaction is not read only.	off