Greenplum DBA - Useful configuration parameters

Configuration Parameter Categories
  • There are many configuration parameters that affect the behavior of a Greenplum Database system. 
  • Connection and Authentication Parameters
  • System Resource Consumption 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
  • Workload Management Parameters
  • External Table Parameters
  • Past PostgreSQL Version Compatibility Parameters
  • Greenplum Array Configuration Parameters
Connection and Authentication Parameters
These parameters control how clients connect and authenticate to Greenplum Database.
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
  • 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 where expired rows are held. Disk space in the free space map is reclaimed by a VACUUM command.
  • max_fsm_pages
  • max_fsm_relations
OS Resource Parameters
  • max_files_per_process
  • shared_preload_libraries
Cost-Based Vacuum Delay Parameters
Standard PostgreSQL has a number of parameters for configuring the execution cost of VACUUM and ANALYZE commands. The intent of this feature is to allow administrators to reduce the I/O impact of these commands on concurrent database activity. When the accumulated cost of the various I/O operations reaches the limit, the process performing the operation will sleep for a while. Then it will reset the counter and continue execution.

Warning: Cost-based vacuum delay is not recommended in Greenplum Database because it runs asynchronously between the segment instances. The vacuum cost limit and delay is invoked locally at the segment level without taking into account the state of the entire Greenplum array.
  • vacuum_cost_delay
  • vacuum_cost_limit
  • vacuum_cost_page_dirty
  • vacuum_cost_page_hit
  • vacuum_cost_page_miss
Query Tuning Parameters
Query Plan Operator Control Parameters
The following parameters control the types of plan operations the query planner has to choose from. Enabling or disabling certain plan operations is a way to force the planner to choose a different plan. These can be useful in testing a query using different plan types to see which plan offers the best performance.
  • 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 have already been 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 will affect statistics collection system-wide. You can also 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 Query Planner Configuration Parameters
  • from_collapse_limit
  • gp_enable_predicate_propagation
  • gp_statistics_pullup_from_child_partition
Error Reporting and Logging Parameters

  • 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 are used to send SNMP notifications when events occur on a Greenplum Database system.
  • gp_snmp_community
  • gp_snmp_monitor_address
  • gp_snmp_use_inform_or_trap
Email Alerts
The following parameters are used to 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 are used to configure the data collection agents for Greenplum Command Center.
  • gp_enable_gpperfmon
  • gp_gpperfmon_send_interval
  • gpperfmon_port
Runtime Statistics Collection Parameters
These parameters control the PostgreSQL server statistics collection feature. When statistics collection is enabled, the data that is produced can be accessed via 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, ANALYZE can be run 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 may 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 are used to 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 are used to configure the external tables feature of Greenplum Database. See “About External Tables” on page 149.
  • gp_external_enable_exec
  • gp_external_grant_privileges
  • gp_external_max_segs
  • gp_reject_percent_threshold
Append-Only Table Parameters
The following parameters are used to configure the append-only tables feature of Greenplum Database. See “Choosing the Table Storage Model” on page 81 for more information about append-only tables.
  • max_appendonly_tables
Database and Tablespace/Filespace Parameters
The following parameters are used to 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 are for compatibility with older PostgreSQL versions. Most likely, you do not need to change these 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 section control the configuration of the Greenplum Database array and its various components (segments, master, distributed transaction manager, and interconnect).

Interconnect Configuration Parameters
  • gp_interconnect_hash_multiplier
  • gp_interconnect_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