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
- System Resource Consumption Parameters
- GPORCA Parameters
- Query Tuning Parameters
- Error Reporting and Logging Parameters
- System Monitoring Parameters
- Runtime Statistics Collection Parameters
- Automatic Statistics Collection Parameters
- Client Connection Default Parameters
- Lock Management Parameters
- Resource Management Parameters (Resource Queues)
- Resource Management Parameters (Resource Groups)
- External Table Parameters
- Database Table Parameters
- Past Version Compatibility Parameters
- Greenplum Database Array Configuration Parameters
- Greenplum Mirroring Parameters for Master and Segments
- Greenplum PL/Java Parameters
Connection and Authentication Parameters
These parameters control how clients connect and authenticate to Greenplum Database.
Connection Parameters
- client_connection_check_interval
- gp_connection_send_timeout
- gp_dispatch_keepalives_count
- gp_dispatch_keepalives_idle
- gp_dispatch_keepalives_interval
- 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_directories
- unix_socket_group
- unix_socket_permissions
Security and Authentication Parameters
- authentication_timeout
- db_user_namespace
- krb_caseins_users
- krb_server_keyfile
- 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
- maintenance_work_mem
- max_stack_depth
- shared_buffers
- temp_buffers
- work_mem
OS Resource Parameters
Cost-Based Vacuum Delay Parameters
CautionDo 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
Other Parameters
GPORCA Parameters
These parameters control the usage of GPORCA by Greenplum Database. For information about GPORCA, see About GPORCA in 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_discard_redistribute_hashjoin
- optimizer_enable_associativity
- optimizer_enable_dml
- optimizer_enable_hashjoin
- optimizer_enable_fullhashjoin
- optimizer_enable_indexonlyscan
- optimizer_enable_master_only_queries
- optimizer_enable_multiple_distinct_aggs
- optimizer_enable_orderedagg
- optimizer_enable_replicated_table
- optimizer_force_agg_skew_avoidance
- optimizer_force_comprehensive_join_implementation
- 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_broadcast_threshold
- optimizer_penalize_skew
- optimizer_print_missing_stats
- optimizer_print_optimization_stats
- optimizer_skew_factor
- 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.
Postgres Planner Control Parameters
The following parameters control the types of plan operations the Postgres Planner can use. Enable 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_eager_two_phase_agg
- gp_enable_agg_distinct
- gp_enable_agg_distinct_pruning
- gp_enable_direct_dispatch
- 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_sort_distinct
- gp_enable_sort_limit
- gp_keep_partition_children_locks
Postgres Planner Costing Parameters
CautionDo 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.
Sort Operator Configuration Parameters
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_compression
Join Operator Configuration Parameters
- join_collapse_limit
- gp_adjust_selectivity_for_outerjoins
- gp_hashjoin_tuples_per_bucket
- gp_statistics_use_fkeys
- gp_workfile_compression
Other Postgres Planner 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.
Error Reporting and Logging Parameters
These configuration parameters control Greenplum Database logging.
Log Rotation
When to Log
- client_min_messages
- gp_interconnect_debug_retry_interval
- log_error_verbosity
- log_file_mode
- 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
- gp_log_format
- gp_log_interconnect
- gp_log_resqueue_priority_sleep_time
- log_autostats
- log_connections
- log_disconnections
- log_dispatch_stats
- log_duration
- log_executor_stats
- log_hostname
- gp_log_endpoints
- gp_log_interconnect
- gp_print_create_gang_time
- log_parser_stats
- log_planner_stats
- log_statement
- log_statement_stats
- log_timezone
- gp_debug_linger
- gp_reraise_signal
System Monitoring Parameters
These configuration parameters control Greenplum Database data collection and notifications related to database monitoring.
Greenplum Performance Database
The following parameters configure the data collection agents that populate the gpperfmon database.
Query Metrics Collection Parameters
These parameters enable and configure query metrics collection. When enabled, Greenplum Database saves metrics to shared memory during query execution. These metrics are used by VMware Greenplum Command Center, which is included with VMware's commercial version of Greenplum Database.
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.
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_allow_nonowner
- gp_autostats_mode
- gp_autostats_mode_in_functions
- gp_autostats_on_change_threshold
- log_autostats
CautionDepending 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_statsis 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_deferrable
- default_transaction_isolation
- default_transaction_read_only
- search_path
- statement_timeout
- temp_tablespaces
- 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
Lock Management Parameters
These configuration parameters set limits for locks and deadlocks.
- deadlock_timeout
- [gp_enable global_deadlock_detector](guc-list.html#gp_enable global_deadlock_detector)
- gp_global_deadlock_detector_period
- lock_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_log_resqueue_priority_sleep_time
- 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_count_host_segments_using_address
- gp_resgroup_memory_policy
- gp_resource_group_bypass
- gp_resource_group_bypass_catalog_query
- gp_resource_group_cpu_ceiling_enforcement
- gp_resource_group_cpu_limit
- gp_resource_group_cpu_priority
- gp_resource_group_enable_recalculate_query_mem
- gp_resource_group_memory_limit
- gp_resource_group_queuing_timeout
- 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
- gpfdist_retry_timeout
- 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
- gp_use_legacy_hashops
Append-Optimized Table Parameters
The following parameters configure the append-optimized tables feature of Greenplum Database.
- max_appendonly_tables
- gp_add_column_inherits_table_setting [gp_appendonly_compaction](guc-list.html#gp_add_column_inherits_table_setting](guc-list.html) [gp_appendonly_compaction)
- gp_appendonly_compaction_threshold
- validate_previous_free_tid
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
- array_nulls
- backslash_quote
- escape_string_warning
- quote_all_identifiers
- regex_flavor
- standard_conforming_strings
- transform_null_equals
Greenplum Database
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_address_type
- gp_interconnect_cursor_ic_table_size
- gp_interconnect_fc_method
- gp_interconnect_proxy_addresses
- gp_interconnect_queue_depth
- gp_interconnect_setup_timeout
- gp_interconnect_snd_queue_depth
- gp_interconnect_transmit_timeout
- gp_interconnect_type
- gp_max_packet_size
NoteGreenplum Database supports only the UDPIFC (default) and TCP interconnect types.
Dispatch Configuration Parameters
- gp_cached_segworkers_threshold
- 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_replication_attempt_count
- gp_log_fts
Distributed Transaction Management Parameters
Read-Only Parameters
- gp_command_count
- gp_content
- gp_dbid
- gp_retrieve_conn
- gp_session_id
- gp_session_role
- 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.
- max_slot_wal_keep_size
- repl_catchup_within_range
- replication_timeout
- wait_for_replication_threshold
- wal_keep_segments
- wal_receiver_status_interval
- wal_sender_archiving_status_interval
Greenplum PL/Java Parameters
The parameters in this topic control the configuration of the Greenplum Database PL/Java language.
- 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.
