Updated on 2024-11-05 GMT+08:00

Configuring GUC Parameters

To ensure the optimal performance of GaussDB(DWS), you can adjust the GUC parameters in the database.

Parameter Types and Values

  • The GUC parameters of GaussDB(DWS) are classified into the following types:
    • SUSET: database administrator parameters. These parameters take effect immediately upon being set and do not require the cluster to be restarted. If a parameter of this type is set in the current session, the parameter takes effect only in the current session.
    • USERSET: common user parameters. These parameters take effect immediately upon being set and do not require the cluster to be restarted. If a parameter of this type is set in the current session, the parameter takes effect only in the current session.
    • POSTMASTER: database server parameters. Restarting the cluster is necessary to apply changes to these parameters. Once you modify a parameter of this type, a message will prompt you to restart the cluster. It is recommended to manually restart the cluster during off-peak hours for the new setting to take effect.
    • SIGHUP: global database parameters. These parameters take effect globally and cannot take effect for individual sessions.
    • BACKEND: global database parameters. These parameters take effect globally and cannot take effect for individual sessions.
  • All parameter names are case insensitive. A parameter value can be an integer, floating point number, string, Boolean value, or enumerated value.
    • The Boolean values can be on/off, true/false, yes/no, or 1/0, and are case-insensitive.
    • The enumerated value range is specified in the enumvals column of the system catalog pg_settings.
  • For parameters using units, specify their units during the setting, or default units are used.
    • The default units are specified in the unit column of pg_settings.
    • The unit of memory can be KB, MB, or GB.
    • The unit of time can be ms, s, min, h, or d.

Setting GUC Parameters

You can configure GUC parameters in the following ways:

  • Method 1: After a cluster is created, you can log in to the GaussDB(DWS) management console and modify the database parameters of the cluster.

    For details, see section Modifying Database Parameters.

  • Method 2: Connect to a cluster and run SQL commands to configure the parameters of the SUSET or USERSET type.

    Set parameters at database, user, or session levels.

    • Set a database-level parameter.
      1
      ALTER DATABASE dbname SET paraname TO value;
      

      The setting takes effect in the next session.

    • Set a user-level parameter.
      1
      ALTER USER username SET paraname TO value;
      

      The setting takes effect in the next session.

    • Set a session-level parameter.
      1
      SET paraname TO value;
      

      Parameter value in the current session is changed. After you exit the session, the setting becomes invalid.

Procedure

The following example shows how to set explain_perf_mode.

  1. View the value of explain_perf_mode.

    1
    2
    3
    4
    5
    SHOW explain_perf_mode;
     explain_perf_mode 
    -------------------
     normal
    (1 row)
    

  2. Set explain_perf_mode.

    Perform one of the following operations:

    • Set a database-level parameter.
      1
      ALTER DATABASE gaussdb SET explain_perf_mode TO pretty;
      

      If the following information is displayed, the setting has been modified.

      ALTER DATABASE

      The setting takes effect in the next session.

    • Set a user-level parameter.
      1
      ALTER USER dbadmin SET explain_perf_mode TO pretty;
      

      If the following information is displayed, the setting has been modified.

      ALTER USER

      The setting takes effect in the next session.

    • Set a session-level parameter.
      1
      SET explain_perf_mode TO pretty;
      

      If the following information is displayed, the setting has been modified.

      SET

  3. Check whether the parameter is correctly set.

    1
    2
    3
    4
    5
    SHOW explain_perf_mode;
     explain_perf_mode
    --------------
     pretty
    (1 row)