Parameter Categories

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

• Connection and Authentication Parameters
• System Resource Consumption Parameters
• GPORCA Parameters
• Query Tuning Parameters
• Error Reporting and Logging Parameters
• Runtime Statistics Collection Parameters
• Automatic Vacuum 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
• LightDB-A Database Array Configuration Parameters
• LightDB-A Mirroring Parameters for Coordinator and Segments
• LightDB-A PL/Java Parameters

Connection and Authentication Parameters

These parameters control how clients connect and authenticate to LightDB-A 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
• row_security
• ssl
• ssl_ciphers

System Resource Consumption Parameters

These parameters set the limits for system resources consumed by LightDB-A 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

OS Resource Parameters

• max_files_per_process
• shared_preload_libraries

Cost-Based Vacuum Delay Parameters

Caution 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 LightDB-A 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

Other Parameters

• gp_max_parallel_cursors

GPORCA Parameters

These parameters control the usage of GPORCA by LightDB-A Database. For information about GPORCA, see About GPORCA in the LightDB-A 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_indexonlyscan
• optimizer_enable_master_only_queries
• optimizer_enable_multiple_distinct_aggs
• 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_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_partition_pruning
• 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_limit

Postgres Planner Costing Parameters

Caution Do not adjust these query costing parameters. They are tuned to reflect LightDB-A 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

Sort Operator Configuration Parameters

• 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_compression

Join Operator Configuration Parameters

• join_collapse_limit
• gp_adjust_selectivity_for_outerjoins
• gp_hashjoin_tuples_per_bucket
• 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
• gp_statistics_use_fkeys

Query Plan Execution

Control the query plan execution.

• gp_max_slices
• plan_cache_mode

Error Reporting and Logging Parameters

These configuration parameters control LightDB-A 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_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
• 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_log_format
• gp_reraise_signal

Automatic Vacuum Parameters

These parameters pertain to auto-vacuuming databases.

• autovacuum

Query Metrics Collection Parameters

These parameters enable and configure query metrics collection. When enabled, LightDB-A Database saves metrics to shared memory during query execution. These metrics are used by VMware LightDB-A Command Center, which is included with VMware’s commercial version of LightDB-A Database.

• gp_enable_query_metrics
• gp_instrument_shmem_size

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 LightDB-A Database coordinator postgresql.conf file and restart LightDB-A Database:

• gp_autostats_allow_nonowner
• gp_autostats_mode
• gp_autostats_mode_in_functions
• gp_autostats_on_change_threshold
• log_autostats

Caution 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_deferrable
• default_transaction_isolation
• default_transaction_read_onlysearch_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

• dynamic_library_path
• explain_pretty_print
• local_preload_libraries

Lock Management Parameters

These configuration parameters set limits for locks and deadlocks.

• deadlock_timeout
• gp_enable global_deadlock_detectorgp_global_deadlock_detector_period
• lock_timeout
• max_locks_per_transaction

Resource Management Parameters (Resource Queues)

The following configuration parameters configure the LightDB-A 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 LightDB-A Database resource group workload management feature.

• gp_resgroup_memory_policy
• gp_resource_group_bypass gp_resource_group_cpu_ceiling_enforcement
• gp_resource_group_cpu_limit
• 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 LightDB-A 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 LightDB-A Database tables.

• default_table_access_method
• gp_create_table_random_default_distribution
• gp_default_storage_options
• gp_enable_segment_copy_checking
• gp_use_legacy_hashops

Append-Optimized Table Parameters

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

• gp_appendonly_compaction
• gp_appendonly_compaction_threshold
• validate_previous_free_tid

Past Version Compatibility Parameters

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

PostgreSQL

• array_nulls
• backslash_quote
• escape_string_warning
• quote_all_identifiers
• regex_flavor
• standard_conforming_strings
• transform_null_equals

LightDB-A Database

• gp_ignore_error_table

LightDB-A Database Array Configuration Parameters

The parameters in this topic control the configuration of the LightDB-A Database array and its components: segments, coordinator, distributed transaction manager, coordinator mirror, and interconnect.

Interconnect Configuration Parameters

• gp_interconnect_address_type
• 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

Note LightDB-A 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

• gp_max_local_distributed_cache

Read-Only Parameters

• gp_command_count
• gp_content
• gp_dbid
• gp_retrieve_conn
• gp_role
• gp_session_id
• gp_server_version
• gp_server_version_num

LightDB-A Mirroring Parameters for Coordinator and Segments

These parameters control the configuration of the replication between LightDB-A Database primary coordinator and standby coordinator.

• repl_catchup_within_range
• replication_timeout
• wait_for_replication_threshold
• wal_keep_segments
• wal_receiver_status_interval

LightDB-A PL/Java Parameters

The parameters in this topic control the configuration of the LightDB-A 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 LightDB-A Database XML data type.

• xmlbinary
• xmloption