Configure via GUC

Daria Barysheva

Overview

Server configuration parameters (also known as Global User Configuration, GUC) allow ADB administrators to manage the DBMS state and configuration. Many of these parameters have the same names and behaviors as in PostgreSQL.

NOTE
To see the full list of server configuration parameters, refer to Greenplum documentation.

The main GUC features are listed below:

  • Parameter names and values are case-insensitive.

  • Every parameter takes a value of one of the following types: boolean, integer, floating point, enum, or string.

  • Boolean values may be specified as:

    • true — ON, TRUE, YES, 1;

    • false — OFF, FALSE, NO, 0.

  • Enum parameters are specified in the same way as string parameters, but are limited by the specified set of values.

  • For those parameters that specify the memory size, you can use the following size units:

    • kB — kilobytes;

    • MB — megabytes;

    • GB — gigabytes.

    Remember that the multiplier for memory units is 1024, not 1000.

  • For those parameters that specify time values, you can use the following time units:

    • ms — milliseconds;

    • s — seconds;

    • min — minutes;

    • h — hours;

    • d — days.

  • When specifying a memory or time parameter, enclose its value in single quotes ' and do not use any spaces between the value and the unit name. For example:

    SET effective_cache_size = '20GB';

Set parameters

Every server configuration parameter belongs to one of the categories (set classifications), which define limitations on who and how can change this parameter. All categories are described in detail below.

session or system

This classification defines whether the parameter can be changed "on the fly" (without the DBMS restart and the configuration file reload):

  • session — parameters of this type can be changed "on the fly" at one of the following levels:

    • session:

      SET <parameter> = <value>;

    • database and role:

      ALTER ROLE <role_name> IN DATABASE <database_name> SET <parameter> = <value>;

    • role:

      ALTER ROLE <role_name> SET <parameter> = <value>;

    • database:

      ALTER DATABASE <database_name> SET <parameter> = <value>;

    You can also define session parameters at the system level — via the postgresql.conf file. In this case, session parameters cannot be changed "on the fly" and require a system restart or a configuration file reload (like system parameters that are described below).

    If a session parameter is set at multiple levels, then the most granular setting has a higher precedence. For example, the parameter value that is defined at the session level overrides all other settings. The value specified at the database and role level overrides other level settings except the session one, and so on. The system level has the lowest priority.

  • system — parameters of this type can only be changed via the postgresql.conf file (using the gpconfig utility). You cannot alter these parameters "on the fly". To define a system parameter value, run one of the following commands and then perform a restart or reload operation (depending on the parameter classification):

    • Set the parameter value on Master only. Use this command for master parameters:

      $ gpconfig -c <parameter> -v <value> --masteronly
      CAUTION
      This command cannot be applied to the gp_default_storage_options parameter, despite this parameter belongs to the master category. If you try to set different values for this parameter on Master and Segment hosts, you get the following error: "gp_default_storage_options value cannot be different on master and segments". Use the next command instead.

    • Set the parameter value on all segments (including Master):

      $ gpconfig -c <parameter> -v <value>

    • Set different values of the specified parameter on Segment hosts and Master:

      $ gpconfig -c <parameter> -v <value_segment> -m <value_master>

    • Comment the parameter in all configuration files, thus restoring its default value:

      $ gpconfig –r <parameter>

master or local

This classification applies only to parameters which values are changed via the postgresql.conf file (system or session at the system level):

  • master — parameters of this type should be set in the configuration file of the Master instance. The values for these parameters are then passed to or ignored by segments at run time.

  • local — parameters of this type should be set in the configuration file of every segment (including Master). Each segment looks through its own configuration file to get the parameter value. Local parameters always require a DBMS restart for changes to be applied.

restart or reload

This classification applies only to parameters which values are changed via the postgresql.conf file (system or session at the system level):

  • restart — parameters of this type require a DBMS restart:

    $ gpstop -r -a

  • reload — parameters of this type do not require a DBMS restart and can be refreshed by just reloading the server configuration file:

    $ gpstop -u -a

superuser

Parameters of this type can only be set by a database superuser. Regular users cannot change values of such parameters.

read only

Parameters of this type cannot be set by any user (neither regular database user nor superuser). You can read the current values of such parameters but not alter them.

Get parameters

To show the current parameter value, use one of the following ways:

  • If you use psql or any other client program, run the SHOW <parameter>; command. For example:

    SHOW effective_cache_size;

    The result can be like:

     effective_cache_size
----------------------
 16GB
(1 row)
    IMPORTANT
    For session parameters that are defined at multiple levels, the SHOW command takes into account the priority of settings. The session level has the highest precedence, the system level — the lowest one.

  • To get the parameter value that is stored in the postgresql.conf file (at the system level), log out from psql or any other client and run the following command: gpconfig –s <parameter>. This command shows the parameter values separately for Master and Segment hosts. It also checks whether values on all segments are consistent. Example:

    $ gpconfig -s effective_cache_size

    The result is:

    Values on all segments are consistent
GUC          : effective_cache_size
Master  value: 16GB
Segment value: 16GB

Examples

Session parameter (session level)

 

     Below is an example of changing the optimizer parameter. This parameter has the master, session, reload classification. To change the parameter value at the session level, do the following:

  1. Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:

    $ sudo su - gpadmin

  2. Connect to the database via psql (or any other client program):

    $ psql adb

    The output is:

    psql (9.4.24)
Type "help" for help.

  3. Get the current parameter value:

    SHOW optimizer;

    The result is:

     optimizer
-----------
 on
(1 row)

  4. Change the parameter value to off at the session level:

    SET optimizer = off;

    The result is:

    SET

  5. Get the current parameter value again. The value has changed:

    adb=# SHOW optimizer;
 optimizer
-----------
 off
(1 row)

  6. Log out of psql (or any other client you use):

    \q

  7. Connect to the database via psql (or any other client program):

    $ psql adb

  8. Get the current parameter value again. In another session, the parameter still has a default value:

    adb=# SHOW optimizer;
 optimizer
-----------
 on
(1 row)
Session parameter (database level)

 

     Below is an example of changing the gp_external_max_segs parameter. This parameter has the master, session, reload classification. To change the parameter value at the database level, do the following:

  1. Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:

    $ sudo su - gpadmin

  2. Connect to the database via psql (or any other client program):

    $ psql adb

    The output is:

    psql (9.4.24)
Type "help" for help.

  3. Get the current parameter value:

    SHOW gp_external_max_segs;

    The result is:

     gp_external_max_segs
----------------------
 64
(1 row)

  4. Change the parameter value to 32 for the adb database:

    ALTER DATABASE adb SET gp_external_max_segs = 32;

    The result is:

    ALTER DATABASE

  5. Log out of psql (or any other client you use):

    \q

  6. Connect to the database via psql (or any other client program):

    $ psql adb

  7. Get the current parameter value again. The value has changed:

    adb=#  SHOW gp_external_max_segs;
 gp_external_max_segs
----------------------
 32
(1 row)
    TIP
    When changing the session configuration parameter at the database level, do not forget to restart the current database session. Otherwise, you cannot see the changes.

  8. Connect to any other database:

    \c books_store

    The result is:

    You are now connected to database "books_store" as user "gpadmin".

  9. Check the current parameter value again. In another database, the parameter still has a default value:

    books_store=# SHOW gp_external_max_segs;
 gp_external_max_segs
----------------------
 64
(1 row)
System parameter (master and reload)

 

     Below is an example of changing the gp_connection_send_timeout parameter. This parameter has the master, system, reload classification. To change the parameter value, do the following:

  1. Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:

    $ sudo su - gpadmin

  2. Get the current parameter values:

    $ gpconfig -s gp_connection_send_timeout

    The result is:

    Values on all segments are consistent
GUC          : gp_connection_send_timeout
Master  value: 3600
Segment value: 3600

  3. Change the parameter value to 1800 on Master:

    $ gpconfig -c gp_connection_send_timeout -v 1800 --masteronly

    The result is:

    20221214:19:32:05:010670 gpconfig:bds-mdw:gpadmin-[INFO]:-completed successfully with parameters '-c gp_connection_send_timeout -v 1800 --masteronly'

  4. Get the current parameter values again. They are still the same:

    [gpadmin@bds-mdw ~]$ gpconfig -s gp_connection_send_timeout
Values on all segments are consistent
GUC          : gp_connection_send_timeout
Master  value: 3600
Segment value: 3600

  5. Reload the configuration file:

    $ gpstop -u -a

  6. Check the current parameter values again. The value is changed on Master. As the gp_connection_send_timeout parameter belongs to the master category, it is not required to change the parameter value on other segments.

    [gpadmin@bds-mdw ~]$ gpconfig -s gp_connection_send_timeout
Values on all segments are consistent
GUC          : gp_connection_send_timeout
Master  value: 1800
Segment value: 3600
System parameter (local and restart)

 

     Below is an example of changing the max_connections parameter. This parameter has the local, system, restart classification. To change the parameter value, do the following:

  1. Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:

    $ sudo su - gpadmin

  2. Get the current parameter values:

    $ gpconfig -s max_connections

    The result is:

    Values on all segments are consistent
GUC          : max_connections
Master  value: 250
Segment value: 750

  3. Change the parameter value on Master to 300 and on Segment hosts — to 800:

    $ gpconfig -c max_connections -v 800 -m 300

    The result is:

    20221214:19:19:15:009073 gpconfig:bds-mdw:gpadmin-[INFO]:-completed successfully with parameters '-c max_connections -v 800 -m 300'

  4. Get the current parameter values again. They are still the same:

    [gpadmin@bds-mdw ~]$ gpconfig -s max_connections
Values on all segments are consistent
GUC          : max_connections
Master  value: 250
Segment value: 750

  5. Restart the system:

    $ gpstop -r -a

  6. Check the current parameter values again. The values are changed on Master and segments according to the step 3:

    [gpadmin@bds-mdw ~]$ gpconfig -s max_connections
Values on all segments are consistent
GUC          : max_connections
Master  value: 300
Segment value: 800
Found a mistake? Seleсt text and press Ctrl+Enter to report it