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 system catalog pg_settings.
• 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 pg_settings.
  • The unit of memory can be KB, MB, or GB.
  • The unit of time can be ms, s, min, h, or d.

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.

You can set GUC parameters in GaussDB using the three methods listed in Table 2.

Table 2 Methods for setting GUC parameters

No.	Setting Method
Method 1	Modify a parameter. gs_guc set -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 database nodes at the same time. gs_guc set -Z datanode -N all -I all -c "paraname=value" Set a CM Agent parameter for database nodes. 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 database nodes. gs_guc set -Z cmserver -c "paraname=value" gs_guc set -Z cmserver -N all -I all -c "paraname=value" Restart the database to make the setting take effect. NOTE: Restarting the database 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 -D datadir -c "paraname=value" NOTE: Set a parameter for database nodes at the same time. gs_guc reload -Z datanode -N all -I all -c "paraname=value"
Method 3	Set parameters at the database, user, or session level. Set a database-level parameter. 1 gaussdb=# ALTER DATABASE dbname SET paraname TO value; The setting takes effect in the next session. Set a user-level parameter. 1 gaussdb=# ALTER USER username SET paraname TO value; The setting takes effect in the next session. 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. NOTE: Session-level parameters set by SET have the highest priority, prior to those set by ALTER. Parameter values set by ALTER DATABASE have a higher priority than those set using ALTER USER. Priorities of the three methods are all higher than using gs_guc. The parameters set by ALTER DATABASE and ALTER USER do not take effect in the current session. You need to restart the session for the parameters to take effect.

• 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 the primary node of the database using method 1.

1. Log in as the OS user omm to the primary node of the database.
2. View the value of hot_standby.

   1	cat /gaussdb/data/dbnode/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 datanode -D /gaussdb/data/dbnode -c "hot_standby=off"

   

   You can set hot_standby to off for the database nodes.

   gs_guc set -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 the primary node of the database using method 2:

1. Log in as the OS user omm to the primary node of the database.
2. View the value of authentication_timeout.

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

   authentication_timeout = 1min

3. Set authentication_timeout to 59s.

   gs_guc reload -Z datanode -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 the database nodes.

   gs_guc reload -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. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
2. View the value of explain_perf_mode.

   1 2 3 4 5

   gaussdb=# SHOW explain_perf_mode; explain_perf_mode ------------------- normal (1 row)

3. Set explain_perf_mode.

   Perform one of the following operations:

   • Set a database-level parameter.

     1	gaussdb=# ALTER DATABASE postgres SET explain_perf_mode TO pretty;

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

     ALTER DATABASE

     The setting takes effect in the next session.

   • Set a user-level parameter.

     1	gaussdb=# ALTER USER omm SET explain_perf_mode TO pretty;

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

     ALTER ROLE

     The setting takes effect in the next session.

   • Set a session-level parameter.

     1	gaussdb=# SET explain_perf_mode TO pretty;

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

     SET

4. 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: Modify the maximum number of connections for the primary database node in GaussDB using method 1.
  1. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  2. View the maximum number of connections.

     1 2 3 4 5

     gaussdb=# SHOW max_connections; max_connections ----------------- 200 (1 row)

  3. Exit the database.

     1	gaussdb=# \q

  4. Change the maximum number of connections for the primary database node in GaussDB.

     gs_guc set -Z datanode -N all -I all -c "max_connections = 800"

  5. Restart the database.

     gs_om -t stop && gs_om -t start

  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 maximum number of connections.

     1 2 3 4 5

     gaussdb=# SHOW max_connections; max_connections ----------------- 800 (1 row)

• Example 2: Set authentication_timeout (timeout period for client authentication) for the primary node using method 2.
  1. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  2. View the timeout period for client authentication.

     1 2 3 4 5

     gaussdb=# SHOW authentication_timeout; authentication_timeout ------------------------ 1min (1 row)

  3. Exit the database.

     1	gaussdb=# \q

  4. Change the timeout period for client authentication of the primary node.

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

  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. View the timeout period for client authentication.

     1 2 3 4 5

     gaussdb=# SHOW authentication_timeout; authentication_timeout ------------------------ 59s (1 row)

• Example 3: Change the maximum number of connections between GaussDB database nodes.
  1. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  2. View the maximum number of connections.

     1 2 3 4 5

     gaussdb=# SHOW max_connections; max_connections ----------------- 200 (1 row)

  3. Exit the database.

     1	gaussdb=# \q

  4. Change the maximum number of connections between GaussDB database nodes.

     gs_guc set -Z datanode -N all -I all -c "max_connections = 500"

  5. Restart the database.

     gs_om -t stop
     gs_om -t start

  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 maximum number of connections.

     1 2 3 4 5

     gaussdb=# SHOW max_connections; max_connections ----------------- 500 (1 row)

• Example 4: Set authentication_timeout (timeout period for client authentication) for database nodes.
  1. Connect to the database. For details, see "Database Quick Start > Connecting to a Database > Using gsql to Connect to a Database" in Developer Guide.
  2. View the timeout period for client authentication.

     1 2 3 4 5

     gaussdb=# SHOW authentication_timeout; authentication_timeout ------------------------ 1min (1 row)

  3. Exit the database.

     1	gaussdb=# \q

  4. Change the timeout period for client authentication of GaussDB database nodes.

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

  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. View the timeout period for client authentication.

     1 2 3 4 5

     gaussdb=# SHOW authentication_timeout; authentication_timeout ------------------------ 30s (1 row)