Configuring Your Greenplum System

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 4.x documentation.

Configuring Your Greenplum System

Greenplum Database can be configured using server configuration parameters.

Server configuration parameters affect the behavior of Greenplum Database. They are part of the PostgreSQL "Grand Unified Configuration" system, so they are sometimes called "GUCs." Most of the Greenplum server configuration parameters are the same as the PostgreSQL configuration parameters, but some are Greenplum-specific.

About Greenplum Master and Local Parameters

Server configuration parameters can affect the master, the segments, or both.

Server configuration files contain parameters that configure server behavior. The Greenplum Database configuration file, postgresql.conf, resides in the data directory of the database instance.

The master and each segment instance have their own postgresql.conf file. Some parameters are local: each segment instance examines its postgresql.conf file to get the value of that parameter. Set local parameters on the master and on each segment instance.

Other parameters are master parameters that you set on the master instance. The value is passed down to (or in some cases ignored by) the segment instances at query run time.

See the Greenplum Database Reference Guide for information about local and master server configuration parameters.

Setting Configuration Parameters

Server configuration parameters may be set at several different levels so that you can manage the system behavior overall and in specific situations.

Many configuration parameters limit who can change them and where or when they can be set. For example, to change certain parameters, you must be a Greenplum Database superuser. Other parameters can be set only at the system level in the postgresql.conf file or require a system restart to take effect.

Many configuration parameters are session parameters. You can set session parameters at the system level, the database level, the role level or the session level. Database users can change most session parameters within their session, but some require superuser permissions. See the Greenplum Database Reference Guide for information about setting server configuration parameters.

Setting a Local Configuration Parameter

To change a local configuration parameter across multiple segments, update the parameter in the postgresql.conf file of each targeted segment, both primary and mirror. Use the gpconfig utility to set a parameter in all Greenplum postgresql.conf files. For example:

$ gpconfig -c gp_vmem_protect_limit -v 4096MB

Restart Greenplum Database to make the configuration changes effective:

$ gpstop -r

Setting a Master Configuration Parameter

To set a master configuration parameter, set it at the Greenplum master instance. If it is also a session parameter, you can set the parameter for a particular database, role or session. If a parameter is set at multiple levels, the most granular level takes precedence. For example, session overrides role, role overrides database, and database overrides system.

Setting Parameters at the System Level

Master parameter settings in the master postgresql.conf file are the system-wide default. To set a master parameter:

  1. Edit the $MASTER_DATA_DIRECTORY/postgresql.conf file.
  2. Find the parameter to set, uncomment it (remove the preceding # character), and type the desired value.
  3. Save and close the file.
  4. For session parameters that do not require a server restart, upload the postgresql.conf changes as follows:
    $ gpstop -u
  5. For parameter changes that require a server restart, restart Greenplum Database as follows:
    $ gpstop -r

For details about the server configuration parameters, see the Greenplum Database Reference Guide.

Setting Parameters at the Database Level

Use ALTER DATABASE to set parameters at the database level. For example:

=# ALTER DATABASE mydatabase SET search_path TO myschema;

When you set a session parameter at the database level, every session that connects to that database uses that parameter setting. Settings at the database level override settings at the system level.

Setting Parameters at the Role Level

Use ALTER ROLE to set a parameter at the role level. For example:

=# ALTER ROLE bob SET search_path TO bobschema;

When you set a session parameter at the role level, every session initiated by that role uses that parameter setting. Settings at the role level override settings at the database level.

Setting Parameters in a Session

Any session parameter can be set in an active database session using the SET command. For example:

=# SET statement_mem TO '200MB';

The parameter setting is valid for the rest of that session or until you issue a RESET command. For example:

=# RESET statement_mem;

Settings at the session level override those at the role level.

Viewing Server Configuration Parameter Settings

The SQL command SHOW allows you to see the current server configuration parameter settings. For example, to see the settings for all parameters:

$ psql -c 'SHOW ALL;'

SHOW lists the settings for the master instance only. To see the value of a particular parameter across the entire system (master and all segments), use the gpconfig utility. For example:

$ gpconfig --show max_connections

Configuration Parameter Categories

Server configuration parameters that work together to configure a behavior are presented together in categories.

Configuration parameters affect categories of server behaviors, such as resource consumption, query tuning, and authentication. The following topics describe Greenplum configuration parameter categories. For details about configuration parameter categories, see the Greenplum Database Reference Guide.

Connection and Authentication Parameters

These parameters control how clients connect and authenticate to Greenplum Database. See Managing Greenplum Database Access for information about configuring client authentication.

Connection Parameters

gp_vmem_idle_resource_timeout

listen_addresses

max_connections

max_prepared_transactions

superuser_reserved_connections

tcp_keepalives_count

tcp_keepalives_idle

tcp_keepalives_interval

unix_socket_directory

unix_socket_group

unix_socket_permissions

Security and Authentication Parameters

authentication_timeout

db_user_namespace

krb_caseins_users

krb_server_keyfile

krb_srvname

password_encryption

ssl

ssl_ciphers

System Resource Consumption Parameters

Memory Consumption Parameters

These parameters control system memory usage. You can adjust gp_vmem_protect_limit to avoid running out of memory at the segment hosts during query processing.

gp_vmem_idle_resource_timeout

gp_vmem_protect_limit

gp_vmem_protect_segworker_cache_limit

gp_workfile_limit_files_per_query

gp_workfile_limit_per_query

gp_workfile_limit_per_segment

max_appendonly_tables

max_prepared_transactions

max_stack_depth

shared_buffers

temp_buffers

Free Space Map Parameters

These parameters control the sizing of the free space map, which contains expired rows. Use VACUUM to reclaim the free space map disk space. See Routine System Maintenance Tasks for information about vacuuming a database.

  • max_fsm_pages
  • max_fsm_relations

OS Resource Parameters

  • max_files_per_process
  • shared_preload_libraries

Cost-Based Vacuum Delay Parameters

Warning: Pivotal does not recommend cost-based vacuum delay because it runs asynchronously among the segment instances. The vacuum cost limit and delay is invoked at the segment level without taking into account the state of the entire Greenplum array.

You can configure the execution cost of VACUUM and ANALYZE commands to reduce the I/O impact on concurrent database activity. When the accumulated cost of I/O operations reaches the limit, the process performing the operation sleeps for a while, Then resets the counter and continues execution

vacuum_cost_delay

vacuum_cost_limit

vacuum_cost_page_dirty

vacuum_cost_page_hit

vacuum_cost_page_miss

Transaction ID Management Parameters

  • xid_stop_limit
  • xid_warn_limit

Query Tuning Parameters

Query Plan Operator Control Parameters

The following parameters control the types of plan operations the query planner can use. Enable or disable plan operations to force the planner to choose a different plan. This is useful for testing and comparing query performance using different plan types.

enable_bitmapscan

enable_groupagg

enable_hashagg

enable_hashjoin

enable_indexscan

enable_mergejoin

enable_nestloop

enable_seqscan

enable_sort

enable_tidscan

gp_enable_adaptive_nestloop

gp_enable_agg_distinct

gp_enable_agg_distinct_pruning

gp_enable_direct_dispatch

gp_enable_fallback_plan

gp_enable_fast_sri

gp_enable_groupext_distinct_ gather

gp_enable_groupext_distinct_ pruning

gp_enable_multiphase_agg

gp_enable_predicate_ propagation

gp_enable_preunique

gp_enable_sequential_window_ plans

gp_enable_sort_distinct

gp_enable_sort_limit

Query Planner Costing Parameters

Warning: Greenplum recommends that you do not adjust these query costing parameters. They are tuned to reflect Greenplum Database hardware configurations and typical workloads. All of these parameters are related. Changing one without changing the others can have adverse effects on performance.

cpu_index_tuple_cost

cpu_operator_cost

cpu_tuple_cost

cursor_tuple_fraction

effective_cache_size

gp_motion_cost_per_row

gp_segments_for_planner

random_page_cost

seq_page_cost

Database Statistics Sampling Parameters

These parameters adjust the amount of data sampled by an ANALYZE operation. Adjusting these parameters affects statistics collection system-wide. You can configure statistics collection on particular tables and columns by using the ALTER TABLESET STATISTICS clause.

  • default_statistics_target
  • gp_analyze_relative_error

Sort Operator Configuration Parameters

  • gp_enable_sort_distinct
  • gp_enable_sort_limit

Aggregate Operator Configuration Parameters

gp_enable_agg_distinct

gp_enable_agg_distinct_pruning

gp_enable_multiphase_agg

gp_enable_preunique

gp_enable_groupext_distinct_ gather

gp_enable_groupext_distinct_ pruning

gp_workfile_compress_algorithm

Join Operator Configuration Parameters

join_collapse_limit

gp_adjust_selectivity_for_outerjoins

gp_hashjoin_tuples_per_bucket

gp_statistics_use_fkeys

gp_workfile_compress_algorithm

Other Query Planner Configuration Parameters

  • from_collapse_limit
  • gp_enable_predicate_propagation
  • gp_max_plan_size
  • gp_statistics_pullup_from_child_partition

Error Reporting and Logging Parameters

Log Rotation

log_rotation_age

log_rotation_size

log_truncate_on_rotation

When to Log

client_min_messages

log_error_verbosity

log_min_duration_statement

log_min_error_statement

log_min_messages

What to Log

debug_pretty_print

debug_print_parse

debug_print_plan

debug_print_prelim_plan

debug_print_rewritten

debug_print_slice_table

log_autostats

log_connections

log_disconnections

log_dispatch_stats

log_duration

log_executor_stats

log_hostname

log_parser_stats

log_planner_stats

log_statement

log_statement_stats

log_timezone

gp_debug_linger

gp_log_format

gp_max_csv_line_length

gp_reraise_signal

System Monitoring Parameters

SNMP Alerts

The following parameters send SNMP notifications when events occur.

gp_snmp_community

gp_snmp_monitor_address

gp_snmp_use_inform_or_trap

Email Alerts

The following parameters configure the system to send email alerts for fatal error events, such as a segment going down or a server crash and reset.

gp_email_from

gp_email_smtp_password

gp_email_smtp_server

gp_email_smtp_userid

gp_email_to

Greenplum Command Center Agent

The following parameters configure the data collection agents for Greenplum Command Center.

gp_enable_gpperfmon

gp_gpperfmon_send_interval

gpperfmon_log_alert_level

gpperfmon_port

Runtime Statistics Collection Parameters

These parameters control the server statistics collection feature. When statistics collection is enabled, you can access the statistics data using the pg_stat and pg_statio family of system catalog views.

stats_queue_level

track_activities

track_counts

update_process_title

Automatic Statistics Collection Parameters

When automatic statistics collection is enabled, you can run ANALYZE automatically in the same transaction as an INSERT, UPDATE, DELETE, COPY or CREATE TABLE...AS SELECT statement when a certain threshold of rows is affected (on_change), or when a newly generated table has no statistics (on_no_stats). To enable this feature, set the following server configuration parameters in your Greenplum master postgresql.conf file and restart Greenplum Database:

  • gp_autostats_mode
  • log_autostats
Warning: Depending on the specific nature of your database operations, automatic statistics collection can have a negative performance impact. Carefully evaluate whether the default setting of on_no_stats is appropriate for your system.

Client Connection Default Parameters

Statement Behavior Parameters

check_function_bodies

default_tablespace

default_transaction_isolation

default_transaction_read_only

search_path

statement_timeout

vacuum_freeze_min_age

Locale and Formatting Parameters

client_encoding

DateStyle

extra_float_digits

IntervalStyle

lc_collate

lc_ctype

lc_messages

lc_monetary

lc_numeric

lc_time

TimeZone

Other Client Default Parameters

dynamic_library_path

explain_pretty_print

local_preload_libraries

Lock Management Parameters

  • deadlock_timeout
  • max_locks_per_transaction

Workload Management Parameters

The following configuration parameters configure the Greenplum Database workload management feature (resource queues), query prioritization, memory utilization and concurrency control.

gp_resqueue_priority

gp_resqueue_priority_cpucores_per_ segment

gp_resqueue_priority_sweeper_ interval

gp_vmem_idle_resource_timeout

gp_vmem_protect_limit

gp_vmem_protect_segworker_cache_ limit

max_resource_queues

max_resource_portals_per_ transaction

resource_cleanup_gangs_on_ wait

resource_select_only

stats_queue_level

External Table Parameters

The following parameters configure the external tables feature of Greenplum Database. See External Tables for more information about external tables.

gp_external_enable_exec

gp_external_grant_privileges

gp_external_max_segs

gp_initial_bad_row_limit

gp_reject_percent_threshold

readable_external_table_timeout

Append-Optimized Table Parameters

The following parameters configure the append-optimized tables feature of Greenplum Database. See Append-Optimized Storage for more information about append-optimized tables.

  • max_appendonly_tables
  • gp_appendonly_compaction
  • gp_appendonly_compaction_threshold

Database and Tablespace/Filespace Parameters

The following parameters configure the maximum number of databases, tablespaces, and filespaces allowed in a system.

  • gp_max_tablespaces
  • gp_max_filespaces
  • gp_max_databases

Past PostgreSQL Version Compatibility Parameters

The following parameters provide compatibility with older PostgreSQL versions. You do not need to change these parameters in Greenplum Database.

add_missing_from

array_nulls

backslash_quote

escape_string_warning

regex_flavor

standard_conforming_strings

transform_null_equals

Greenplum Array Configuration Parameters

The parameters in this topic control the configuration of the Greenplum Database array and its components: segments, master, distributed transaction manager, master mirror, and interconnect.

Interconnect Configuration Parameters

gp_interconnect_fc_method

gp_interconnect_hash_multiplier

gp_interconnect_queue_depth

gp_interconnect_snd_queue_depth

gp_interconnect_setup_ timeout

gp_interconnect_type

gp_max_packet_size

Dispatch Configuration Parameters

gp_cached_segworkers_threshold

gp_connections_per_thread

gp_enable_direct_dispatch

gp_segment_connect_timeout

gp_set_proc_affinity

Fault Operation Parameters

gp_set_read_only

gp_fts_probe_interval

gp_fts_probe_threadcount

Distributed Transaction Management Parameters

  • gp_max_local_distributed_cache

Read-Only Parameters

  • gp_command_count
  • gp_content
  • gp_dbid
  • gp_num_contents_in_cluster
  • gp_role
  • gp_session_id

Greenplum Master Mirroring Parameters

The parameters in this topic control the configuration of the replication between Greenplum Database primary master and standby master.

  • keep_wal_segments
  • repl_catchup_within_range
  • replication_timeout
  • wal_receiver_status_interval