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 configure common database parameters. For details, see Modifying Parameters. You can also view the parameter modification history. For details, see Viewing Parameter Change History. You can run SQL commands to view or set other database parameters. For details, see Setting Configuration Parameters in the Data Warehouse Service Database Developer Guide.

Prerequisites

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

Modifying Parameters

  1. Log in to the GaussDB(DWS) management console.

  2. In the navigation tree on the left, click Clusters.

  3. In the cluster list, find the target cluster and click the cluster name. The Cluster Information page is displayed.

  4. Click the Parameters tab and modify the parameter values. Then click Save.

    image1

  5. In the Modification Preview dialog box, confirm the modifications and click Save.

  6. You can determine whether you need to restart the cluster after parameter modification based on the Restart Cluster column.

    image2

    Note

    • If cluster restart is not required for a parameter, the parameter modification takes effect immediately.

    • If cluster restart is required for parameter modifications to take effect, the new parameter values will be displayed on the page after the modification, but will not take effect until the cluster is restarted.Before a restart, the cluster status is To be restarted, and some O&M operations are disabled.

Viewing Parameter Change History

Perform the following steps to view the parameter modification history and check whether the modifications have taken effect:

Procedure

  1. Log in to the GaussDB(DWS) management console.

  2. In the navigation tree on the left, click Clusters.

  3. In the cluster list, find the target cluster and click the cluster name. The Cluster Information page is displayed.

  4. Click the Modify Records tab.

    image3

    Note

    • If a parameter can take effect immediately after modification, its status will change to Synchronized after you modify it.

    • If a parameter can take effect only after a cluster restart, its status will change to To be restarted after you modify it. You can click the expansion icon on the left to view the parameters that have not taken effect. After the cluster is restarted, the status of the record will change to Synchronized.

  5. By default, only the change history within a specified period is displayed. To check the entire change history of a parameter, search for it in the search box in the upper right corner.

    image4

Parameter Description

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

Note

The default values of the following parameters are for reference only. For more information, see "Setting GUC Parameters".

Table 1 Parameter Description

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.

By default, a password must meet the following requirements:

  • Contain 8 to 32 characters.

  • Contain at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.

  • Cannot be the same as the user name or the user name in reverse order, case insensitive.

  • Cannot be the current password or the current password in reverse order.

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 cryptographic 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.

512MB

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

last updated: 2024-04-28 00:29