- Parameter Categories
- Connection and Authentication Parameters
- System Resource Consumption Parameters
- GPORCA Parameters
- Query Tuning Parameters
- Legacy Query Optimizer Operator Control Parameters
- Legacy Query Optimizer Costing Parameters
- Database Statistics Sampling Parameters
- Sort Operator Configuration Parameters
- Aggregate Operator Configuration Parameters
- Join Operator Configuration Parameters
- Other Legacy Query Optimizer Configuration Parameters
- Query Plan Execution
- 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
- Database and Tablespace/Filespace Parameters
- Past Version Compatibility Parameters
- Greenplum Database Array Configuration Parameters
- Greenplum Mirroring Parameters for Master and Segments
- Greenplum Database Extension Parameters
- XML Data Parameters
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
- 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
- Database and Tablespace/Filespace Parameters
- Past Version Compatibility Parameters
- Greenplum Database Array Configuration Parameters
- Greenplum Mirroring Parameters for Master and Segments
- Greenplum Database Extension Parameters
Connection and Authentication Parameters
These parameters control how clients connect and authenticate to Greenplum Database.
Connection Parameters
superuser_reserved_connections
|
Security and Authentication Parameters
|
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
|
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.
|
OS Resource Parameters
|
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
|
Transaction ID Management Parameters
|
GPORCA Parameters
These parameters control the usage of GPORCA by Greenplum Database. For information about GPORCA, see About GPORCAin the Greenplum Database Administrator Guide.
optimizer_analyze_root_partition
optimizer_array_expansion_threshold
optimizer_enable_associativity
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_threshold
optimizer_print_optimization_stats
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.
|gp_enable_agg_distinct_pruning
gp_enable_groupext_distinct_gather
gp_enable_groupext_distinct_pruning
gp_enable_predicate_propagation
gp_enable_sequential_window_plans
|
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.
|
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_pruning
|gp_enable_groupext_distinct_gather
gp_enable_groupext_distinct_pruning
gp_workfile_compress_algorithm
|
Join Operator Configuration Parameters
gp_adjust_selectivity_for_outerjoins
gp_workfile_compress_algorithm
|
Other Legacy Query Optimizer Configuration Parameters
gp_enable_predicate_propagation
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
gp_interconnect_debug_retry_interval
|
What to Log
|
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.
|
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.
|
Greenplum Command Center Agent
The following parameters configure the data collection agents that populate the gpperfmon
database for Greenplum Command Center.
|
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_mode_in_functions
gp_autostats_on_change_threshold
|
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
|
Locale and Formatting Parameters
|
Other Client Default Parameters
|
Lock Management Parameters
These configuration parameters set limits for locks and deadlocks.
|
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_priority_cpucores_per_segment
gp_resqueue_priority_sweeper_interval
gp_vmem_protect_segworker_cache_limit
|max_resource_portals_per_transaction
resource_cleanup_gangs_on_wait
runaway_detector_activation_percent
|
Resource Management Parameters (Resource Groups)
The following parameters configure the Greenplum Database resource group workload management feature.
gp_resource_group_bypass gp_resource_group_cpu_limit
gp_resource_group_memory_limit
|gp_vmem_idle_resource_timeout
gp_vmem_protect_segworker_cache_limit
runaway_detector_activation_percent
|
External Table Parameters
The following parameters configure the external tables feature of Greenplum Database.
gp_external_enable_filter_pushdown
|readable_external_table_timeout
writable_external_table_bufsize
|
Database Table Parameters
The following parameter configures default option settings for Greenplum Database tables.
|gp_create_table_random_default_distribution
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.
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.
|
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
|
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_hash_multiplier
|gp_interconnect_snd_queue_depth
gp_interconnect_transmit_timeout
|
Note: Greenplum Database supports only the UDPIFC (default) and TCP interconnect types.
Dispatch Configuration Parameters
|gp_cached_segworkers_threshold
|
Fault Operation Parameters
|
Distributed Transaction Management Parameters
|gp_max_local_distributed_cache
|
Read-Only Parameters
|
Greenplum Mirroring Parameters for Master and Segments
These parameters control the configuration of the replication between Greenplum Database primary master and standby master.
|
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.
pljava_release_lingering_savepoints
|
XML Data Parameters
The parameters in this topic control the configuration of the Greenplum Database XML data type.
|