Updated on 2024-09-02 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. This type of parameters takes effect immediately after they are set. You do not need to restart the cluster. 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. This type of parameters takes effect immediately after they are set. You do not need to restart the cluster. 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. This type of parameters takes effect only after the cluster is restarted. After you modify a parameter of this type, the system displays a message indicating that the cluster is to be restarted. You are advised to manually restart the cluster during off-peak hours for the setting to take effect.
    • SIGHUP: global database parameters. This type of parameters takes effect globally and cannot take effect for single sessions.
    • BACKEND: global database parameters. This type of parameters takes effect globally and cannot take effect for single 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 Modifying Database Parameters in .
  • 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)