Help Center> GaussDB> Distributed_8.x> Configuring Running Parameters> Setting Parameters
Updated on 2024-06-03 GMT+08:00
View PDF

Setting Parameters

Context

GaussDB provides multiple methods to set GUC parameters for databases, users, or sessions.

  • Parameter names are case-insensitive.
  • The parameter values can be integers, floating points, strings, Boolean values, or enumerated values.
    • 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 pg_settings system catalog.
  • For parameters using units, specify their units during the setting. Otherwise, default units are used.
    • The default units are specified in the unit column of the pg_settings system catalog.
    • The unit of memory can be KB, MB, or GB.
    • The unit of time can be ms, s, min, h, or d.
  • You can set parameters related to CNs and DNs at a time, but cannot do the same to other parameters.

For details about parameters, see GUC Parameters.

Setting GUC Parameters

GaussDB provides six types of GUC parameters. For details about parameter types and their setting methods, see Table 1.

Table 1 GUC parameter types

Parameter Type

Description

Setting Method

INTERNAL

Fixed parameters. They are set during database creation and cannot be modified. Users can only view the parameters by running the SHOW command or in the pg_settings view.

None

POSTMASTER

Database server parameters. They can be set when the database is started or in the configuration file.

Method 1 in Table 2.

SIGHUP

Global database parameters. They can be set when the database is started or be modified later.

Method 1 or 2 in Table 2.

BACKEND

Session connection parameters. They are specified during session connection creation and cannot be modified after that. The parameter setting becomes invalid when the session is disconnected. The parameters of this type are internal parameters and not recommended for users to set it.

Method 1 or 2 in Table 2.

NOTE:

The parameter setting takes effect when the next session is created.

SUSET

Database administrator parameters. They can be set by common users during database startup or after the database is started. They can also be set by database administrators using SQL statements.

Method 1 or 2 by a common user, or method 3 by a database administrator in Table 2.

USERSET

Common user parameters. They can be set by any user at any time.

Method 1, 2, or 3 in Table 2.

NOTE:

When you set parameters of the USERSET type, the parameter value set using ALTER DATABASE takes precedence over that set using gs_guc. To make the parameter settings of gs_guc take effect, run the alter database xxx reset xxx command to reset the parameters.
You can set GUC parameters in GaussDB using the methods listed in Table 2.
Table 2 Methods for setting GUC parameters

No.

Setting Method

Method 1
  1. Modify a parameter.
    gs_guc set -Z nodetype -D datadir -c "paraname=value"
    NOTE:
    • If any parameter is a string variable, use -c parameter="'value'" or -c "parameter = 'value'".
    • If any parameter is a string variable, gs_guc does not check the validity of the parameter. If the database runs abnormally due to invalid parameter settings, view gs_log to locate the fault.
    • Set a parameter for CNs and DNs at the same time.
      gs_guc set -Z coordinator -Z datanode -N all -I all -c "paraname=value"
    • Set a CM Agent parameter for CNs and DNs.
      gs_guc set -Z cmagent -c "paraname=value"
gs_guc set -Z cmagent -N all -I all -c "paraname=value"
    • Set a CM Server parameter for CNs and DNs.
      gs_guc set -Z cmserver -c "paraname=value"
gs_guc set -Z cmserver -N all -I all -c "paraname=value"
  2. Restart the database to make the setting take effect.
    NOTE:

    Restarting the cluster results in operation interruption. Properly plan the restart to avoid affecting users.

    gs_om -t stop && gs_om -t start

Method 2

 
gs_guc reload -Z nodetype -D datadir -c "paraname=value"
NOTE:

Set a parameter for CNs and DNs at the same time.

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "paraname=value"

Set a CM Agent parameter for CNs and DNs.

gs_guc reload -Z cmagent -N all -I all -c "paraname=value"
gs_guc reload -Z cmagent -c "paraname=value"

Set a CM Server parameter for CNs and DNs.

gs_guc reload -Z cmserver -N all -I all -c "paraname=value"
gs_guc reload -Z cmserver -c "paraname=value"

Method 3

Modify a session-level parameter.

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

    The parameter value in the current session is changed. After you exit the session, the setting becomes invalid.
  • If you use method 1 or 2 to set a parameter that does not belong to the current node, the database displays a message indicating that the parameter is not supported.
  • When you use method 3 to set a parameter, if the parameter value is an integer, leading zeros will be filtered out. For example, SET paraname TO 008192 and SET paraname TO 8192 have the same effect.

Procedure

The following example shows how to set hot_standby on a CN using method 1:

  1. Log in to the host where CN is located as the cluster installation user.
  2. View the value of hot_standby.

    1
    		 
    cat /gaussdb/data/coordinator/gaussdb.conf | grep "hot_standby ="

    hot_standby = on

    The value on indicates that the query operation in the restoration phase is allowed.

  3. Set hot_standby to off to disable query operations in the restoration phase.

    gs_guc set -Z coordinator -D /gaussdb/data/coordinator -c "hot_standby=off"

    You can set hot_standby to off for all CNs and DNs.

    gs_guc set -Z coordinator -Z datanode -N all -I all -c "hot_standby=off"

  4. Restart the database to make the setting take effect.

    gs_om -t stop && gs_om -t start

  5. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  6. Check whether the parameter is correctly set.

    1
2
3
4
5
    		 
    gaussdb=# SHOW hot_standby;
 hot_standby
-------------
 off
(1 row)

The following example shows how to set authentication_timeout on a CN using method 2:

  1. Log in to the host where CN is located as the cluster installation user.
  2. View the value of authentication_timeout.

    1
    		 
    cat /gaussdb/data/coordinator/gaussdb.conf | grep authentication_timeout

    authentication_timeout = 1min

  3. Set authentication_timeout to 59s.

    gs_guc reload -Z coordinator -N all -I all -c "authentication_timeout = 59s"

Total instances: 2. Failed instances: 0.
Success to perform gs_guc!

    You can set authentication_timeout to 59s for all CNs and DNs.

    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "authentication_timeout = 59s"

  4. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  5. Check whether the parameter is correctly set.

    1
2
3
4
5
    		 
    gaussdb=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 59s
(1 row)

The following example shows how to set explain_perf_mode using method 3:

  1. Log in to the host where CN is located as the cluster installation user.
  2. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  3. View the value of explain_perf_mode.

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

  4. Set explain_perf_mode.

    Perform the following operations:

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

      If the following information is displayed, the setting is successful:

      SET

  5. Check whether the parameter is correctly set.

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

Examples

  • Example 1: modifying the allowed maximum number of connections for all CNs in the cluster using method 1
    1. Log in to the host where CN is located as the cluster installation user.
    2. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    3. View the maximum number of connections. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)
    4. Exit the database. 
      1
      		 
      gaussdb=# \q
    5. Modify the maximum number of connections for all CNs in the cluster.
      gs_guc set -Z coordinator -N all -I all -c "max_connections = 800"
    6. Restart the cluster.
      gs_om -t stop && gs_om -t start
    7. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    8. View the maximum number of connections. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW max_connections;
 max_connections 
-----------------
 800
(1 row)
  • Example 2: setting authentication_timeout (timeout period for client authentication) for all CNs using method 2
    1. Log in to the host where CN is located as the cluster installation user.
    2. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    3. View the timeout period for client authentication. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)
    4. Exit the database. 
      1
      		 
      gaussdb=# \q
    5. Modify the timeout period for client authentication for all CNs in the cluster.
      gs_guc reload -Z coordinator -N all -I all -c "authentication_timeout = 59s"
    6. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    7. View the timeout period for client authentication. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 59s
(1 row)
  • Example 3: modifying the maximum number of connections for all CNs and DNs in the cluster
    1. Log in to the host where CN is located as the cluster installation user.
    2. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    3. View the maximum number of connections. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW max_connections;
 max_connections 
-----------------
 200
(1 row)
    4. Exit the database. 
      1
      		 
      gaussdb=# \q
    5. Modify the allowed maximum number of connections for all CNs and DNs in the cluster.
      gs_guc set -Z coordinator -Z datanode -N all -I all -c "max_connections = 500"
    6. Restart the cluster.
      gs_om -t stop
gs_om -t start
    7. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    8. View the maximum number of connections. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW max_connections;
 max_connections 
-----------------
 500
(1 row)
  • Example 4: setting authentication_timeout (timeout period for client authentication) for all CNs and DNs
    1. Log in to the host where CN is located as the cluster installation user.
    2. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    3. View the timeout period for client authentication. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 1min
(1 row)
    4. Exit the database. 
      1
      		 
      gaussdb=# \q
    5. Modify the timeout period for client authentication for all CNs and DNs in the cluster.
      gs_guc reload -Z coordinator -Z datanode -N all -I all -c "authentication_timeout = 30s"
    6. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
    7. View the timeout period for client authentication. 
      1
2
3
4
5
      		 
      gaussdb=# SHOW authentication_timeout;
 authentication_timeout 
------------------------
 30s
(1 row)