Parameter Categories

Configuration parameters affect categories of server behaviors, such as resource consumption, query tuning, and authentication. The following topics describe Greenplum configuration parameter categories.

Connection and Authentication Parameters

These parameters control how clients connect and authenticate to Greenplum Database.

Connection Parameters

|gp_connection_send_timeout

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

password_hash_algorithm

ssl

ssl_ciphers

|

System Resource Consumption Parameters

These parameters set the limits for system resources consumed by Greenplum Database.

Memory Consumption Parameters

These parameters control system memory usage.

|gp_vmem_idle_resource_timeout

gp_resource_group_memory_limit (resource group-based resource management)

gp_vmem_protect_limit (resource queue-based resource management)

gp_vmem_protect_segworker_cache_limit

gp_workfile_limit_files_per_query

|gp_workfile_limit_per_query

gp_workfile_limit_per_segment

max_stack_depth

shared_buffers

temp_buffers

|

Free Space Map Parameters

These parameters control the sizing of the free space map, which containsexpired rows. Use VACUUM to reclaim the free space map disk space.

|max_fsm_pages

max_fsm_relations

|

OS Resource Parameters

|max_files_per_process

shared_preload_libraries

|

Cost-Based Vacuum Delay Parameters

Warning: Do not use 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 Database 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

|

GPORCA Parameters

These parameters control the usage of GPORCA by Greenplum Database. For information about GPORCA, see About GPORCAin the Greenplum Database Administrator Guide.

|gp_enable_relsize_collection

optimizer

optimizer_analyze_root_partition

optimizer_array_expansion_threshold

optimizer_control

optimizer_cost_model

optimizer_cte_inlining_bound

optimizer_dpe_stats

optimizer_enable_associativity

optimizer_enable_dml

optimizer_enable_master_only_queries

optimizer_force_agg_skew_avoidance

|optimizer_force_multistage_agg

optimizer_force_three_stage_scalar_dqa

optimizer_join_arity_for_associativity_commutativity

optimizer_join_order

optimizer_join_order_threshold

optimizer_mdcache_size

optimizer_metadata_caching

optimizer_parallel_union

optimizer_penalize_skew

optimizer_print_missing_stats

optimizer_print_optimization_stats

optimizer_sort_factor

optimizer_use_gpdb_allocators

optimizer_xform_bind_threshold

|

Query Tuning Parameters

These parameters control aspects of SQL query processing such as query operators and operator settings and statistics sampling.

Legacy Query Optimizer Operator Control Parameters

The following parameters control the types of plan operations the Postgres Planner can use. Activate or deactivate plan operations to force the Postgres 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_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_relsize_collection

gp_enable_sequential_window_plans

gp_enable_sort_distinct

gp_enable_sort_limit

|

Legacy Query Optimizer Costing Parameters

Warning: 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 affects 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 TABLE``SET 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 Legacy Query Optimizer Configuration Parameters

|from_collapse_limit

gp_enable_predicate_propagation

gp_max_plan_size

gp_statistics_pullup_from_child_partition

|

Query Plan Execution

Control the query plan execution.

|gp_max_slices

|

Error Reporting and Logging Parameters

These configuration parameters control Greenplum Database logging.

Log Rotation

|log_rotation_age

log_rotation_size

|log_truncate_on_rotation

|

When to Log

|client_min_messages

gp_interconnect_debug_retry_interval

log_error_verbosity

log_min_duration_statement

|log_min_error_statement

log_min_messages

optimizer_minidump

|

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

gp_log_interconnect

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

These configuration parameters control Greenplum Database data collection and notifications related to database monitoring.

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 that populate the gpperfmon database 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 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 Database master postgresql.conf file and restart Greenplum Database:

|gp_autostats_mode

gp_autostats_mode_in_functions

gp_autostats_on_change_threshold

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

These configuration parameters set defaults that are used for client connections.

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

These configuration parameters set limits for locks and deadlocks.

|deadlock_timeout

max_locks_per_transaction

|

Resource Management Parameters (Resource Queues)

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

|gp_resqueue_memory_policy

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

max_statement_mem

resource_cleanup_gangs_on_wait

resource_select_only

runaway_detector_activation_percent

statement_mem

stats_queue_level

vmem_process_interrupt

|

Resource Management Parameters (Resource Groups)

The following parameters configure the Greenplum Database resource group workload management feature.

|gp_resgroup_memory_policy

gp_resource_group_bypass gp_resource_group_cpu_limit

gp_resource_group_memory_limit

gp_resource_manager

|gp_vmem_idle_resource_timeout

gp_vmem_protect_segworker_cache_limit

max_statement_mem

memory_spill_ratio

runaway_detector_activation_percent

statement_mem

vmem_process_interrupt

|

External Table Parameters

The following parameters configure the external tables feature of Greenplum Database.

|gp_external_enable_exec

gp_external_enable_filter_pushdown

gp_external_max_segs

gp_initial_bad_row_limit

gp_reject_percent_threshold

|readable_external_table_timeout

writable_external_table_bufsize

verify_gpfdists_cert

|

Database Table Parameters

The following parameter configures default option settings for Greenplum Database tables.

|gp_create_table_random_default_distribution

gp_default_storage_options

gp_enable_exchange_default_partition

gp_enable_segment_copy_checking

|

Append-Optimized Table Parameters

The following parameters configure the append-optimized tables feature of Greenplum Database.

|max_appendonly_tables

gp_appendonly_compaction

gp_appendonly_compaction_threshold

validate_previous_free_tid

|

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 Version Compatibility Parameters

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

PostgreSQL

|add_missing_from

array_nulls

backslash_quote

escape_string_warning

|regex_flavor

standard_conforming_strings

transform_null_equals

|

Greenplum Database

|gp_ignore_error_table

|

Greenplum Database 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_setup_timeout

|gp_interconnect_snd_queue_depth

gp_interconnect_transmit_timeout

gp_interconnect_type

gp_max_packet_size

|

Note: Greenplum Database supports only the UDPIFC (default) and TCP interconnect types.

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_retries

|gp_fts_probe_timeout

gp_fts_probe_threadcount

gp_log_fts

|

Distributed Transaction Management Parameters

|gp_max_local_distributed_cache

|dtx_phase2_retry_count

|

Read-Only Parameters

|gp_command_count

gp_content

gp_dbid

gp_num_contents_in_cluster

|gp_role

gp_session_id

gp_server_version

gp_server_version_num

|

Greenplum Mirroring Parameters for Master and Segments

These parameters 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

|

This parameter controls validation between Greenplum Database primary segment and standby segment during incremental resynchronization.

|filerep_mirrorvalidation_during_resync

|

Greenplum Database Extension Parameters

The parameters in this topic control the configuration of Greenplum Database extensions.

|pgcrypto.fips

pljava_classpath

pljava_classpath_insecure

pljava_statement_cache_size

pljava_release_lingering_savepoints

pljava_vmoptions

|

XML Data Parameters

The parameters in this topic control the configuration of the Greenplum Database XML data type.

|xmlbinary

xmloption

|

check-circle-line exclamation-circle-line close-line
Scroll to top icon