Server Configuration Parameters

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 4.x documentation.

Server Configuration Parameters

There are many Greenplum server configuration parameters that affect the behavior of the Greenplum Database system. Many of these configuration parameters have the same names, settings, and behaviors as in a regular PostgreSQL database system.

Parameter Types and Values

All parameter names are case-insensitive. Every parameter takes a value of one of four types: Boolean, integer, floating point, or string. Boolean values may be written as ON, OFF, TRUE, FALSE, YES, NO, 1, 0 (all case-insensitive).

Some settings specify a memory size or time value. Each of these has an implicit unit, which is either kilobytes, blocks (typically eight kilobytes), milliseconds, seconds, or minutes. Valid memory size units are kB (kilobytes), MB (megabytes), and GB (gigabytes). Valid time units are ms (milliseconds), s (seconds), min (minutes), h (hours), and d (days). Note that the multiplier for memory units is 1024, not 1000. A valid time expression contains a number and a unit. When specifying a memory or time unit using the SET command, enclose the value in quotes. For example:

SET statement_mem TO '200MB';
Note: There is no space between the value and the unit names.

Setting Parameters

Many of the configuration parameters have limitations on who can change them and where or when they can be set. For example, to change certain parameters, you must be a Greenplum Database superuser. Other parameters require a restart of the system for the changes to take effect. A parameter that is classified as session can be set at the system level (in the postgresql.conf file), at the database-level (using ALTER DATABASE), at the role-level (using ALTER ROLE), or at the session-level (using SET). System parameters can only be set in the postgresql.conf file.

In Greenplum Database, the master and each segment instance has its own postgresql.conf file (located in their respective data directories). Some parameters are considered local parameters, meaning that each segment instance looks to its own postgresql.conf file to get the value of that parameter. You must set local parameters on every instance in the system (master and segments). Others parameters are considered master parameters. Master parameters need only be set at the master instance.

This table describes the values in the Settable Classifications column of the table in the description of a server configuration parameter.

Table 1. Settable Classifications
Set Classification Description
master or local A master parameter only needs to be set in the postgresql.conf file of the Greenplum master instance. The value for this parameter is then either passed to (or ignored by) the segments at run time.

A local parameter must be set in the postgresql.conf file of the master AND each segment instance. Each segment instance looks to its own configuration to get the value for the parameter. Local parameters always requires a system restart for changes to take effect.

session or system Session parameters can be changed on the fly within a database session, and can have a hierarchy of settings: at the system level (postgresql.conf), at the database level (ALTER DATABASE...SET), at the role level (ALTER ROLE...SET), or at the session level (SET). If the parameter is set at multiple levels, then the most granular setting takes precedence (for example, session overrides role, role overrides database, and database overrides system).

A system parameter can only be changed via the postgresql.conf file(s).

restart or reload When changing parameter values in the postgrsql.conf file(s), some require a restart of Greenplum Database for the change to take effect. Other parameter values can be refreshed by just reloading the server configuration file (using gpstop -u), and do not require stopping the system.
superuser These session parameters can only be set by a database superuser. Regular database users cannot set this parameter.
read only These parameters are not settable by database users or superusers. The current value of the parameter can be shown but not altered.

Parameter Categories

System Resource Consumption Parameters

Memory Consumption Parameters

Free Space Map Parameters

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

max_fsm_pages

max_fsm_relations

 

Cost-Based Vacuum Delay Parameters

Warning: Pivotal does not recommend cost-based vacuum delay because it runs asynchronously anong 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 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

Query Tuning Parameters

Query Planner Costing Parameters

Warning: Greenplum recommends that you 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 TABLESET STATISTICS clause.

default_statistics_target

gp_analyze_relative_error

 

Sort Operator Configuration Parameters

System Monitoring Parameters

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 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 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, 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 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 can have a negative performance impact. Carefully evaluate whether the default setting of on_no_stats is appropriate for your system.

Lock Management Parameters

Workload Management Parameters

External Table Parameters

Append-Optimized Table Parameters

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

max_appendonly_tables

gp_appendonly_compaction

 

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

The following parameters provide compatibility with older PostgreSQL versions. You do not need to change these parameters 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 topic control the configuration of the Greenplum Database array and its components: segments, master, distributed transaction manager, master mirror, and interconnect.

Distributed Transaction Management Parameters

Greenplum Master Mirroring Parameters

The parameters in this topic 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

 

Configuration Parameters

add_missing_from

Automatically adds missing table references to FROM clauses. Present for compatibility with releases of PostgreSQL prior to 8.1, where this behavior was allowed by default.

Value Range Default Set Classifications
Boolean off master

session

reload

application_name

Sets the application name for a client session. For example, if connecting via psql, this will be set to psql. Setting an application name allows it to be reported in log messages and statistics views.

Value Range Default Set Classifications
string   master

session

reload

array_nulls

This controls whether the array input parser recognizes unquoted NULL as specifying a null array element. By default, this is on, allowing array values containing null values to be entered. Greenplum Database versions before 3.0 did not support null values in arrays, and therefore would treat NULL as specifying a normal array element with the string value 'NULL'.

Value Range Default Set Classifications
Boolean on master

session

reload

authentication_timeout

Maximum time to complete client authentication. This prevents hung clients from occupying a connection indefinitely.

Value Range Default Set Classifications
Any valid time expression (number and unit) 1min local

system

restart

backslash_quote

This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks because in some client character set encodings, there are multibyte characters in which the last byte is numerically equivalent to ASCII \.

Value Range Default Set Classifications
on (allow \' always)

off (reject always)

safe_encoding (allow only if client encoding does not allow ASCII \ within a multibyte character)

safe_encoding master

session

reload

block_size

Reports the size of a disk block.

Value Range Default Set Classifications
number of bytes 32768 read only

bonjour_name

Specifies the Bonjour broadcast name. By default, the computer name is used, specified as an empty string. This option is ignored if the server was not compiled with Bonjour support.

Value Range Default Set Classifications
string unset master

system

restart

check_function_bodies

When set to off, disables validation of the function body string during CREATE FUNCTION. Disabling validation is occasionally useful to avoid problems such as forward references when restoring function definitions from a dump.

Value Range Default Set Classifications
Boolean on master

session

reload

client_encoding

Sets the client-side encoding (character set). The default is to use the same as the database encoding. See Supported Character Sets in the PostgreSQL documentation.

Value Range Default Set Classifications
character set UTF8 master

session

reload

client_min_messages

Controls which message levels are sent to the client. Each level includes all the levels that follow it. The later the level, the fewer messages are sent.

Value Range Default Set Classifications
DEBUG5

DEBUG4

DEBUG3

DEBUG2

DEBUG1

LOG NOTICE

WARNING

ERROR

FATAL

PANIC

NOTICE master

session

reload

cpu_index_tuple_cost

Sets the planner's estimate of the cost of processing each index row during an index scan. This is measured as a fraction of the cost of a sequential page fetch.

Value Range Default Set Classifications
floating point 0.005 master

session

reload

cpu_operator_cost

Sets the planner's estimate of the cost of processing each operator in a WHERE clause. This is measured as a fraction of the cost of a sequential page fetch.

Value Range Default Set Classifications
floating point 0.0025 master

session

reload

cpu_tuple_cost

Sets the planner's estimate of the cost of processing each row during a query. This is measured as a fraction of the cost of a sequential page fetch.

Value Range Default Set Classifications
floating point 0.01 master

session

reload

cursor_tuple_fraction

Tells the query planner how many rows are expected to be fetched in a cursor query, thereby allowing the planner to use this information to optimize the query plan. The default of 1 means all rows will be fetched.

Value Range Default Set Classifications
integer 1 master

session

reload

custom_variable_classes

Specifies one or several class names to be used for custom variables. A custom variable is a variable not normally known to the server but used by some add-on module. Such variables must have names consisting of a class name, a dot, and a variable name.

Value Range Default Set Classifications
comma-separated list of class names unset local

system

restart

DateStyle

Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. This variable contains two independent components: the output format specification and the input/output specification for year/month/day ordering.

Value Range Default Set Classifications
<format>, <date style>

where:

<format> is ISO, Postgres, SQL, or German

<date style> is DMY, MDY, or YMD

ISO, MDY master

session

reload

db_user_namespace

This enables per-database user names. If on, you should create users as username@dbname. To create ordinary global users, simply append @ when specifying the user name in the client.

Value Range Default Set Classifications
Boolean off local

system

restart

deadlock_timeout

The time to wait on a lock before checking to see if there is a deadlock condition. On a heavily loaded server you might want to raise this value. Ideally the setting should exceed your typical transaction time, so as to improve the odds that a lock will be released before the waiter decides to check for deadlock.

Value Range Default Set Classifications
Any valid time expression (number and unit) 1s local

system

restart

debug_assertions

Turns on various assertion checks.

Value Range Default Set Classifications
Boolean off local

system

restart

debug_pretty_print

Indents debug output to produce a more readable but much longer output format. client_min_messages or log_min_messages must be DEBUG1 or lower.

Value Range Default Set Classifications
Boolean off master

session

reload

debug_print_parse

For each executed query, prints the resulting parse tree. client_min_messages or log_min_messages must be DEBUG1 or lower.

Value Range Default Set Classifications
Boolean off master

session

reload

debug_print_plan

For each executed query, prints the Greenplum parallel query execution plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

Value Range Default Set Classifications
Boolean off master

session

reload

debug_print_prelim_plan

For each executed query, prints the preliminary query plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

Value Range Default Set Classifications
Boolean off master

session

reload

debug_print_rewritten

For each executed query, prints the query rewriter output. client_min_messages or log_min_messages must be DEBUG1 or lower.

Value Range Default Set Classifications
Boolean off master

session

reload

debug_print_slice_table

For each executed query, prints the Greenplum query slice plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

Value Range Default Set Classifications
Boolean off master

session

reload

default_statistics_target

Sets the default statistics target for table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time needed to do ANALYZE, but may improve the quality of the planner's estimates.

Value Range Default Set Classifications
integer > 0 25 master

session

reload

default_tablespace

The default tablespace in which to create objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace.

Value Range Default Set Classifications
name of a tablespace unset master

session

reload

default_transaction_isolation

Controls the default isolation level of each new transaction.

Value Range Default Set Classifications
read committed

read uncommitted

repeatable read

serializable

read committed master

session

reload

default_transaction_read_only

Controls the default read-only status of each new transaction. A read-only SQL transaction cannot alter non-temporary tables.

Value Range Default Set Classifications
Boolean off master

session

reload

dynamic_library_path

If a dynamically loadable module needs to be opened and the file name specified in the CREATE FUNCTION or LOAD command does not have a directory component (i.e. the name does not contain a slash), the system will search this path for the required file. The compiled-in PostgreSQL package library directory is substituted for $libdir. This is where the modules provided by the standard PostgreSQL distribution are installed.

Value Range Default Set Classifications
a list of absolute directory paths separated by colons $libdir local

system

restart

effective_cache_size

Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. This parameter has no effect on the size of shared memory allocated by a Greenplum server instance, nor does it reserve kernel disk cache; it is used only for estimation purposes.

Value Range Default Set Classifications
floating point 512MB master

session

reload

enable_bitmapscan

Enables or disables the query planner's use of bitmap-scan plan types. Note that this is different than a Bitmap Index Scan. A Bitmap Scan means that indexes will be dynamically converted to bitmaps in memory when appropriate, giving faster index performance on complex queries against very large tables. It is used when there are multiple predicates on different indexed columns. Each bitmap per column can be compared to create a final list of selected tuples.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_groupagg

Enables or disables the query planner's use of group aggregation plan types.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_hashagg

Enables or disables the query planner's use of hash aggregation plan types.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_hashjoin

Enables or disables the query planner's use of hash-join plan types.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_indexscan

Enables or disables the query planner's use of index-scan plan types.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_mergejoin

Enables or disables the query planner's use of merge-join plan types. Merge join is based on the idea of sorting the left- and right-hand tables into order and then scanning them in parallel. So, both data types must be capable of being fully ordered, and the join operator must be one that can only succeed for pairs of values that fall at the 'same place' in the sort order. In practice this means that the join operator must behave like equality.

Value Range Default Set Classifications
Boolean off master

session

reload

enable_nestloop

Enables or disables the query planner's use of nested-loop join plans. It's not possible to suppress nested-loop joins entirely, but turning this variable off discourages the planner from using one if there are other methods available.

Value Range Default Set Classifications
Boolean off master

session

reload

enable_seqscan

Enables or disables the query planner's use of sequential scan plan types. It's not possible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_sort

Enables or disables the query planner's use of explicit sort steps. It's not possible to suppress explicit sorts entirely, but turning this variable off discourages the planner from using one if there are other methods available.

Value Range Default Set Classifications
Boolean on master

session

reload

enable_tidscan

Enables or disables the query planner's use of tuple identifier (TID) scan plan types.

Value Range Default Set Classifications
Boolean on master

session

reload

escape_string_warning

When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('...' syntax). Escape string syntax (E'...') should be used for escapes, because in future versions, ordinary strings will have the SQL standard-conforming behavior of treating backslashes literally.

Value Range Default Set Classifications
Boolean on master

session

reload

explain_pretty_print

Determines whether EXPLAIN VERBOSE uses the indented or non-indented format for displaying detailed query-tree dumps.

Value Range Default Set Classifications
Boolean on master

session

reload

extra_float_digits

Adjusts the number of digits displayed for floating-point values, including float4, float8, and geometric data types. The parameter value is added to the standard number of digits. The value can be set as high as 2, to include partially-significant digits; this is especially useful for dumping float data that needs to be restored exactly. Or it can be set negative to suppress unwanted digits.

Value Range Default Set Classifications
integer 0 master

session

reload

from_collapse_limit

The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but may yield inferior query plans.

Value Range Default Set Classifications
1-n 20 master

session

reload

gp_adjust_selectivity_for_outerjoins

Enables the selectivity of NULL tests over outer joins.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_analyze_relative_error

Sets the estimated acceptable error in the cardinality of the table " a value of 0.5 is supposed to be equivalent to an acceptable error of 50% (this is the default value used in PostgreSQL). If the statistics collected during ANALYZE are not producing good estimates of cardinality for a particular table attribute, decreasing the relative error fraction (accepting less error) tells the system to sample more rows.

Value Range Default Set Classifications
floating point < 1.0 0.25 master

session

reload

gp_appendonly_compaction

Enables compacting segment files during VACUUM commands. When disabled, VACUUM only truncates the segment files to the EOF value, as is the current behavior. The administrator may want to disable compaction in high I/O load situations or low space situations.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_appendonly_compaction_threshhold

Sets the threshold ratio (as a percentage) of hidden tuples to allow compaction of the segment file. If the ratio of hidden tuples in a segment file on a segment is less than this threshold, the segment file is not compacted on a full VACUUM call, and a log message is issued.

Value Range Default Set Classifications
integer(%) 10 master

session

reload

gp_autostats_mode

Specifies the mode for triggering automatic statistics collection with ANALYZE. The on_no_stats option triggers statistics collection for CREATE TABLE AS SELECT, INSERT, or COPY operations on any table that has no existing statistics.

The on_change option triggers statistics collection only when the number of rows affected exceeds the threshold defined by gp_autostats_on_change_threshold. Operations that can trigger automatic statistics collection with on_change are:

CREATE TABLE AS SELECT

UPDATE

DELETE

INSERT

COPY

Default is on_no_stats.

Note: For partitioned tables, automatic statistics collection is not triggered if data is inserted from the top-level parent table of a partitioned table.

Automatic statistics collection is triggered if data is inserted directly in a leaf table (where the data is stored) of the partitioned table. Statistics are collected only on the leaf table.

Value Range Default Set Classifications
none

on_change

on_no_stats

on_no_ stats master

session

reload

gp_autostats_on_change_threshold

Specifies the threshold for automatic statistics collection when gp_autostats_mode is set to on_change. When a triggering table operation affects a number of rows exceeding this threshold, ANALYZE is added and statistics are collected for the table.

Value Range Default Set Classifications
integer 2147483647 master

session

reload

gp_backup_directIO

Direct I/O allows Greenplum Database to bypass the buffering of memory within the file system cache for backup. When Direct I/O is used for a file, data is transferred directly from the disk to the application buffer, without the use of the file buffer cache.

Direct I/O is supported only on Red Hat Enterprise Linux, CentOS, and SUSE.

Value Range Default Set Classifications
on, off off local

session

reload

gp_backup_directIO_read_chunk_mb

Sets the chunk size in MB when Direct I/O is enabled with gp_backup_directIO. The default chunk size is 20MB.

The default value is the optimal setting. Decreasing it will increase the backup time and increasing it will result in little change to backup time.

Value Range Default Set Classifications
1-200 20 MB local

session

reload

gp_cached_segworkers_threshold

When a user starts a session with Greenplum Database and issues a query, the system creates groups or 'gangs' of worker processes on each segment to do the work. After the work is done, the segment worker processes are destroyed except for a cached number which is set by this parameter. A lower setting conserves system resources on the segment hosts, but a higher setting may improve performance for power-users that want to issue many complex queries in a row.

Value Range Default Set Classifications
integer > 0 5 master

session

reload

gp_command_count

Shows how many commands the master has received from the client. Note that a single SQLcommand might actually involve more than one command internally, so the counter may increment by more than one for a single query. This counter also is shared by all of the segment processes working on the command.

Value Range Default Set Classifications
integer > 0 1 read only

gp_connectemc_mode

Controls the ConnectEMC event logging and dial-home capabilities of Greenplum Command Center on the EMC Greenplum Data Computing Appliance (DCA). ConnectEMC must be installed in order to generate events. Allowed values are:

on (the default) - log events to the gpperfmon database and send dial-home notifications to EMC Support

off - turns off ConnectEMC event logging and dial-home capabilities

local - log events to the gpperfmon database only

remote - sends dial-home notifications to EMC Support (does not log events to the gpperfmon database)

Value Range Default Set Classifications
on, off, local, remote on master

system

restart

superuser

gp_connections_per_thread

A value larger than or equal to the number of primary segments means that each slice in a query plan will get its own thread when dispatching to the segments. A value of 0 indicates that the dispatcher should use a single thread when dispatching all query plan slices to a segment. Lower values will use more threads, which utilizes more resources on the master. Typically, the default does not need to be changed unless there is a known throughput performance problem.

Value Range Default Set Classifications
integer 64 master

session

reload

gp_content

The local content id if a segment.

Value Range Default Set Classifications
integer   read only

gp_dbid

The local content dbid if a segment.

Value Range Default Set Classifications
integer   read only

gp_debug_linger

Number of seconds for a Greenplum process to linger after a fatal internal error.

Value Range Default Set Classifications
Any valid time expression (number and unit) 0 master

session

reload

gp_dynamic_partition_pruning

Enables plans that can dynamically eliminate the scanning of partitions.

Value Range Default Set Classifications
on/off on master

session

reload

gp_email_from

The email address used to send email alerts, in the format of:

'username@example.com'

or

'Name <username@example.com>'

Value Range Default Set Classifications
string   master

system

restart

gp_email_smtp_password

The password/passphrase used to authenticate with the SMTP server.

Value Range Default Set Classifications
string   master

system

restart

gp_email_smtp_server

The fully qualified domain name or IP address and port of the SMTP server to use to send the email alerts. Must be in the format of:

smtp_servername.domain.com:port

Value Range Default Set Classifications
string   master

system

restart

gp_email_smtp_userid

The user id used to authenticate with the SMTP server.

Value Range Default Set Classifications
string   master

system

restart

gp_email_to

A semi-colon (;) separated list of email addresses to receive email alert messages to in the format of: 'username@example.com'

or

'Name <username@example.com>'

If this parameter is not set, then email alerts are disabled.

Value Range Default Set Classifications
string   master

system

restart

gp_enable_adaptive_nestloop

Enables the query planner to use a new type of join node called "Adaptive Nestloop" at query execution time. This causes the planner to favor a hash-join over a nested-loop join if the number of rows on the outer side of the join exceeds a precalculated threshold. This parameter improves performance of index operations, which previously favored slower nested-loop joins.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_agg_distinct

Enables or disables two-phase aggregation to compute a single distinct-qualified aggregate. This applies only to subqueries that include a single distinct-qualified aggregate function.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_agg_distinct_pruning

Enables or disables three-phase aggregation and join to compute distinct-qualified aggregates. This applies only to subqueries that include one or more distinct-qualified aggregate functions.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_direct_dispatch

Enables or disables the dispatching of targeted query plans for queries that access data on a single segment. When on, queries that target rows on a single segment will only have their query plan dispatched to that segment (rather than to all segments). This significantly reduces the response time of qualifying queries as there is no interconnect setup involved. Direct dispatch does require more CPU utilization on the master.

Value Range Default Set Classifications
Boolean on master

system

restart

gp_enable_fallback_plan

Allows use of disabled plan types when a query would not be feasible without them.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_fast_sri

When set to on, the query planner plans single row inserts so that they are sent directly to the correct segment instance (no motion operation required). This significantly improves performance of single-row-insert statements.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_gpperfmon

Enables or disables the data collection agents of Greenplum Command Center.

Value Range Default Set Classifications
Boolean off local

system

restart

gp_enable_groupext_distinct_gather

Enables or disables gathering data to a single node to compute distinct-qualified aggregates on grouping extension queries. When this parameter and gp_enable_groupext_distinct_pruning are both enabled, the planner uses the cheaper plan.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_groupext_distinct_pruning

Enables or disables three-phase aggregation and join to compute distinct-qualified aggregates on grouping extension queries. Usually, enabling this parameter generates a cheaper query plan that the planner will use in preference to existing plan.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_multiphase_agg

Enables or disables the query planner's use of two or three-stage parallel aggregation plans. This approach applies to any subquery with aggregation. If gp_enable_multiphase_agg is off, then gp_enable_agg_distinct and gp_enable_agg_distinct_pruning are disabled.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_predicate_propagation

When enabled, the query planner applies query predicates to both table expressions in cases where the tables are joined on their distribution key column(s). Filtering both tables prior to doing the join (when possible) is more efficient.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_preunique

Enables two-phase duplicate removal for SELECT DISTINCT queries (not SELECT COUNT(DISTINCT)). When enabled, it adds an extra SORT DISTINCT set of plan nodes before motioning. In cases where the distinct operation greatly reduces the number of rows, this extra SORT DISTINCT is much cheaper than the cost of sending the rows across the Interconnect.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_sequential_window_plans

If on, enables non-parallel (sequential) query plans for queries containing window function calls. If off, evaluates compatible window functions in parallel and rejoins the results. This is an experimental parameter.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_sort_distinct

Enable duplicates to be removed while sorting.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_enable_sort_limit

Enable LIMIT operation to be performed while sorting. Sorts more efficiently when the plan requires the first limit_number of rows at most.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_external_enable_exec

Enables or disables the use of external tables that execute OS commands or scripts on the segment hosts (CREATE EXTERNAL TABLE EXECUTE syntax). Must be enabled if using the Command Center or MapReduce features.

Value Range Default Set Classifications
Boolean on master

system

restart

gp_external_grant_privileges

In releases prior to 4.0, enables or disables non-superusers to issue a CREATE EXTERNAL [WEB] TABLE command in cases where the LOCATION clause specifies http or gpfdist. In releases after 4.0, the ability to create an external table can be granted to a role using CREATE ROLE or ALTER ROLE.

Value Range Default Set Classifications
Boolean off master

system

restart

gp_external_max_segs

Sets the number of segments that will scan external table data during an external table operation, the purpose being not to overload the system with scanning data and take away resources from other concurrent operations. This only applies to external tables that use the gpfdist:// protocol to access external table data.

Value Range Default Set Classifications
integer 64 master

session

reload

gp_filerep_tcp_keepalives_count

How many keepalives may be lost before the connection is considered dead. A value of 0 uses the system default. If TCP_KEEPCNT is not supported, this parameter must be 0.

Use this parameter for all connections that are between a primary and mirror segment. Use tcp_keepalives_count for settings that are not between a primary and mirror segment.

Value Range Default Set Classifications
number of lost keepalives 2 local

system

restart

gp_filerep_tcp_keepalives_idle

Number of seconds between sending keepalives on an otherwise idle connection. A value of 0 uses the system default. If TCP_KEEPIDLE is not supported, this parameter must be 0.

Use this parameter for all connections that are between a primary and mirror segment. Use tcp_keepalives_idle for settings that are not between a primary and mirror segment.

Value Range Default Set Classifications
number of seconds 1 min local

system

restart

gp_filerep_tcp_keepalives_interval

How many seconds to wait for a response to a keepalive before retransmitting. A value of 0 uses the system default. If TCP_KEEPINTVL is not supported, this parameter must be 0.

Use this parameter for all connections that are between a primary and mirror segment. Use tcp_keepalives_interval for settings that are not between a primary and mirror segment.

Value Range Default Set Classifications
number of seconds 30 sec local

system

restart

gp_fts_probe_interval

Specifies the polling interval for the fault detection process (ftsprobe). The ftsprobe process will take approximately this amount of time to detect a segment failure.

Value Range Default Set Classifications
10 seconds or greater 1min master

system

restart

gp_fts_probe_threadcount

Specifies the number of ftsprobe threads to create. This parameter should be set to a value equal to or greater than the number of segments per host.

Value Range Default Set Classifications
1 - 128 16 master

system

restart

gp_fts_probe_timeout

Specifies the allowed timeout for the fault detection process (ftsprobe) to establish a connection to a segment before declaring it down.

Value Range Default Set Classifications
10 seconds or greater 20 secs master

system

restart

gp_gpperfmon_send_interval

Sets the frequency that the Greenplum Database server processes send query execution updates to the data collection agent processes used by Command Center. Query operations (iterators) executed during this interval are sent through UDP to the segment monitor agents. If you find that an excessive number of UDP packets are dropped during long-running, complex queries, you may consider increasing this value.

Value Range Default Set Classifications
Any valid time expression (number and unit) 1sec master

system

restart

gpperfmon_log_alert_level

Controls which message levels are written to the gpperfmon log. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log.

Note: If the Greenplum Database Command Center is installed and is monitoring the database, the default value is warning.
Value Range Default Set Classifications
none

warning

error

fatal

panic

none local

system

restart

gp_hadoop_home

When using Pivotal HD, specify the installation directory for Hadoop. For example, the default installation directory is /usr/lib/gphd.

When using Greenplum HD 1.2 or earlier, specify the same value as the HADOOP_HOME environment variable.

Value Range Default Set Classifications
Valid directory name Value of HADOOP_HOME local

session

reload

gp_hadoop_target_version

The installed version of Greenplum Hadoop target.

Value Range Default Set Classifications
gphd-1.0

gphd-1.1

gphd-1.2

gphd-2.0

gpmr-1.0

gpmr-1.2

hdp2

cdh3u2

cdh4.1

gphd-1.1 local

session

reload

gp_hashjoin_tuples_per_bucket

Sets the target density of the hash table used by HashJoin operations. A smaller value will tend to produce larger hash tables, which can increase join performance.

Value Range Default Set Classifications
integer 5 master

session

reload

gp_idf_deduplicate

Changes the strategy to compute and process MEDIAN, and PERCENTILE_DISC.

Value Range Default Set Classifications
auto

none

force

auto master

session

reload

gp_initial_bad_row_limit

For the parameter value n, Greenplum Database stops processing input rows when you import data with the COPY command or from an external table if the first n rows processed contain formatting errors. If a valid row is processed within the first n rows, Greenplum Database continues processing input rows.

Setting the value to 0 disables this limit.

The SEGMENT REJECT LIMIT clause can also be specified for the COPY command or the external table definition to limit the number of rejected rows.

INT_MAX is the largest value that can be stored as an integer on your system.

Value Range Default Set Classifications
integer 0 - INT_MAX 1000 master

session

reload

gp_interconnect_fc_method

Specifies the flow control method used for UDP interconnect when the value of gp_interconnect_type is UDPIFC.

For capacity based flow control, senders do not send packets when receivers do not have the capacity.

Loss based flow control is based on capacity based flow control, and also tunes the sending speed according to packet losses.

Value Range Default Set Classifications
CAPACITY

LOSS

LOSS master

session

reload

gp_interconnect_hash_multiplier

Sets the size of the hash table used by the UDP interconnect to track connections. This number is multiplied by the number of segments to determine the number of buckets in the hash table. Increasing the value may increase interconnect performance for complex multi-slice queries (while consuming slightly more memory on the segment hosts).

Value Range Default Set Classifications
2-25 2 master

session

reload

gp_interconnect_queue_depth

Sets the amount of data per-peer to be queued by the UDP interconnect on receivers (when data is received but no space is available to receive it the data will be dropped, and the transmitter will need to resend it). Increasing the depth from its default value will cause the system to use more memory; but may increase performance. It is reasonable for this to be set between 1 and 10. Queries with data skew potentially perform better when this is increased. Increasing this may radically increase the amount of memory used by the system.

Value Range Default Set Classifications
1-2048 4 master

session

reload

gp_interconnect_setup_timeout

Time to wait for the Interconnect to complete setup before it times out.

This parameter is used only when gp_interconnect_type is set to UDP.

Value Range Default Set Classifications
Any valid time expression (number and unit) 2 hours master

session

reload

gp_interconnect_snd_queue_depth

Sets the amount of data per-peer to be queued by the UDP interconnect on senders. Increasing the depth from its default value will cause the system to use more memory; but may increase performance. Reasonable values for this parameter are between 1 and 4. Increasing the value might radically increase the amount of memory used by the system.

This parameter is used only when gp_interconnect_type is set to UDPIFC.

Value Range Default Set Classifications
1 - 4096 2 master

session

reload

gp_interconnect_type

Sets the networking protocol used for Interconnect traffic. With the TCP protocol, Greenplum Database has an upper limit of 1000 segment instances - less than that if the query workload involves complex, multi-slice queries.

UDP allows for greater interconnect scalability. Note that the Greenplum software does the additional packet verification and checking not performed by UDP, so reliability and performance is equivalent to TCP.

UDPIFC specifies using UDP with flow control for interconnect traffic. Specify the interconnect flow control method with gp_interconnect_fc_method.

Value Range Default Set Classifications
TCP

UDP

UDPIFC

UDPIFC local

system

restart

gp_log_format

Specifies the format of the server log files. If using gp_toolkit administrative schema, the log files must be in CSV format.

Value Range Default Set Classifications
csv

text

csv local

system

restart

gp_max_csv_line_length

The maximum length of a line in a CSV formatted file that will be imported into the system. The default is 1MB (1048576 bytes). Maximum allowed is 4MB (4194184 bytes). The default may need to be increased if using the gp_toolkit administrative schema to read Greenplum Database log files.

Value Range Default Set Classifications
number of bytes 1048576 local

system

restart

gp_max_databases

The maximum number of databases allowed in a Greenplum Database system.

Value Range Default Set Classifications
integer 16 master

system

restart

gp_max_filespaces

The maximum number of filespaces allowed in a Greenplum Database system.

Value Range Default Set Classifications
integer 8 master

system

restart

gp_max_local_distributed_cache

Sets the number of local to distributed transactions to cache. Higher settings may improve performance.

Value Range Default Set Classifications
integer 1024 local

system

restart

gp_max_packet_size

Sets the size (in bytes) of messages sent by the UDP interconnect, and sets the tuple-serialization chunk size for both the UDP and TCP interconnect.

Value Range Default Set Classifications
512-65536 8192 master

system

restart

gp_max_plan_size

Specifies the total maximum uncompressed size of a query execution plan multiplied by the number of Motion operators (slices) in the plan. If the size of the query plan exceeds the value, the query is cancelled and an error is returned. A value of 0 means that the size of the plan is not monitored.

You can specify a value in KB,MB, or GB. The default unit is KB. For example, a value of 200 is 200KB. A value of 1GB is the same as 1024MB or 1048576KB.

Value Range Default Set Classifications
integer 0 master

superuser

session

gp_max_tablespaces

The maximum number of tablespaces allowed in a Greenplum Database system.

Value Range Default Set Classifications
integer 16 master

system

restart

gp_motion_cost_per_row

Sets the query planner cost estimate for a Motion operator to transfer a row from one segment to another, measured as a fraction of the cost of a sequential page fetch. If 0, then the value used is two times the value of cpu_tuple_cost.

Value Range Default Set Classifications
floating point 0 master

session

reload

gp_num_contents_in_cluster

The number of primary segments in the Greenplum Database system.

Value Range Default Set Classifications
- - read only

gp_reject_percent_threshold

For single row error handling on COPY and external table SELECTs, sets the number of rows processed before SEGMENT REJECT LIMIT n PERCENT starts calculating.

Value Range Default Set Classifications
1-n 300 master

session

reload

gp_reraise_signal

If enabled, will attempt to dump core if a fatal server error occurs.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_resqueue_memory_policy

Enables Greenplum memory management features. In Greenplum Database 4.2 and later, the distribution algorithm eager_free, takes advantage of the fact that not all operators execute at the same time. The query plan is divided into stages and Greenplum Database eagerly frees memory allocated to a previous stage at the end of that stage's execution, then allocates the eagerly freed memory to the new stage.

When set to none, memory management is the same as in Greenplum Database releases prior to 4.1. When set to auto, query memory usage is controlled by statement_mem and resource queue memory limits.

Value Range Default Set Classifications
none, auto, eager_free eager_free local

system

restart/reload

gp_resqueue_priority

Enables or disables query prioritization. When this parameter is disabled, existing priority settings are not evaluated at query run time.

Value Range Default Set Classifications
Boolean on local

system

restart

gp_resqueue_priority_cpucores_per_segment

Specifies the number of CPU units allocated per segment instance. For example, if a Greenplum Database cluster has 10-core segment hosts that are configured with four segments, set the value for the segment instances to 2.5. For the master instance, the value would be 10. A master host typically has only the master instance running on it, so the value for the master should reflect the usage of all available CPU cores.

Incorrect settings can result in CPU under-utilization or query prioritization not working as designed.

The default values for the Greenplum Data Computing Appliance V2 are 4 for segments and 25 for the master.

Value Range Default Set Classifications
0.1 - 512.0 4 local

system

restart

gp_resqueue_priority_sweeper_interval

Specifies the interval at which the sweeper process evaluates current CPU usage. When a new statement becomes active, its priority is evaluated and its CPU share determined when the next interval is reached.

Value Range Default Set Classifications
500 - 15000 ms 1000 local

system

restart

gp_role

The role of this server process " set to dispatch for the master and execute for a segment.

Value Range Default Set Classifications
dispatch

execute

utility

  read only

gp_safefswritesize

Specifies a minimum size for safe write operations to append-optimized tables in a non-mature file system. When a number of bytes greater than zero is specified, the append-optimized writer adds padding data up to that number in order to prevent data corruption due to file system errors. Each non-mature file system has a known safe write size that must be specified here when using Greenplum Database with that type of file system. This is commonly set to a multiple of the extent size of the file system; for example, Linux ext3 is 4096 bytes, so a value of 32768 is commonly used.

Value Range Default Set Classifications
integer 0 local

system

restart

gp_segment_connect_timeout

Time that the Greenplum interconnect will try to connect to a segment instance over the network before timing out. Controls the network connection timeout between master and primary segments, and primary to mirror segment replication processes.

Value Range Default Set Classifications
Any valid time expression (number and unit) 10min local

system

reload

gp_segments_for_planner

Sets the number of primary segment instances for the planner to assume in its cost and size estimates. If 0, then the value used is the actual number of primary segments. This variable affects the planner's estimates of the number of rows handled by each sending and receiving process in Motion operators.

Value Range Default Set Classifications
0-n 0 master

session

reload

gp_session_id

A system assigned ID number for a client session. Starts counting from 1 when the master instance is first started.

Value Range Default Set Classifications
1-n 14 read only

gp_set_proc_affinity

If enabled, when a Greenplum server process (postmaster) is started it will bind to a CPU.

Value Range Default Set Classifications
Boolean off master

system

restart

gp_set_read_only

Set to on to disable writes to the database. Any in progress transactions must finish before read-only mode takes affect.

Value Range Default Set Classifications
Boolean off master

session

reload

gp_snmp_community

Set to the community name you specified for your environment.

Value Range Default Set Classifications
SNMP community name public master

system

reload

gp_snmp_monitor_address

The hostname:port of your network monitor application. Typically, the port number is 162. If there are multiple monitor addresses, separate them with a comma.

Value Range Default Set Classifications
hostname:port   master

system

reload

gp_snmp_use_inform_or_trap

Trap notifications are SNMP messages sent from one application to another (for example, between Greenplum Database and a network monitoring application). These messages are unacknowledged by the monitoring application, but generate less network overhead.

Inform notifications are the same as trap messages, except that the application sends an acknowledgement to the application that generated the alert.

Value Range Default Set Classifications
inform

trap

trap master

system

reload

gp_statistics_pullup_from_child_partition

Enables the query planner to utilize statistics from child tables when planning queries on the parent table.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_statistics_use_fkeys

When enabled, allows the optimizer to use foreign key information stored in the system catalog to optimize joins between foreign keys and primary keys.

Value Range Default Set Classifications
Boolean off master

session

reload

gp_vmem_idle_resource_timeout

If a database session is idle for longer than the time specified, the session will free system resources (such as shared memory), but remain connected to the database. This allows more concurrent connections to the database at one time.

Value Range Default Set Classifications
Any valid time expression (number and unit) 18s master

system

restart

gp_vmem_protect_limit

Sets the amount of memory (in number of MBs) that all postgres processes of an active segment instance can consume. To prevent over allocation of memory, set to:

( X * physical_memory ) / primary_segments

Where X is a value between 1.0 and 1.5. X=1 offers the best system performance. X=1.5 may cause more swapping on the system, but less queries will be cancelled.

For example, on a segment host with 16GB physical memory and 4 primary segment instances the calculation would be:

(1 * 16) / 4 = 4GB

4 * 1024 = 4096MB

If a query causes this limit to be exceeded, memory will not be allocated and the query will fail. Note that this is a local parameter and must be set for every segment in the system (primary and mirrors).

Value Range Default Set Classifications
integer 8192 local

system

restart

gp_vmem_protect_segworker_cache_limit

If a query executor process consumes more than this configured amount, then the process will not be cached for use in subsequent queries after the process completes. Systems with lots of connections or idle processes may want to reduce this number to free more memory on the segments. Note that this is a local parameter and must be set for every segment.

Value Range Default Set Classifications
number of megabytes 500 local

system

restart

gp_workfile_checksumming

Adds a checksum value to each block of a work file (or spill file) used by HashAgg and HashJoin query operators. This adds an additional safeguard from faulty OS disk drivers writing corrupted blocks to disk. When a checksum operation fails, the query will cancel and rollback rather than potentially writing bad data to disk.

Value Range Default Set Classifications
Boolean on master

session

reload

gp_workfile_compress_algorithm

When a hash aggregation or hash join operation spills to disk during query processing, specifies the compression algorithm to use on the spill files. If using zlib, it must be in your $PATH on all segments.

If your Greenplum database installation uses serial ATA (SATA) disk drives, setting the value of this parameter to zlib might help to avoid overloading the disk subsystem with IO operations.

Value Range Default Set Classifications
none

zlib

none master

session

reload

gp_workfile_limit_files_per_query

Sets the maximum number of temporary spill files (also known as workfiles) allowed per query per segment. Spill files are created when executing a query that requires more memory than it is allocated. The current query is terminated when the limit is exceeded.

Set the value to 0 (zero) to allow an unlimited number of spill files. master session reload

Value Range Default Set Classifications
integer 100000 master

session

reload

gp_workfile_limit_per_query

Sets the maximum disk size an individual query is allowed to use for creating temporary spill files at each segment. The default value is 0, which means a limit is not enforced.

Value Range Default Set Classifications
kilobytes 0 master

session

reload

gp_workfile_limit_per_segment

Sets the maximum total disk size that all running queries are allowed to use for creating temporary spill files at each segment. The default value is 0, which means a limit is not enforced.

Value Range Default Set Classifications
kilobytes 0 local

system

restart

gpperfmon_port

Sets the port on which all data collection agents (for Command Center) communicate with the master.

Value Range Default Set Classifications
integer 8888 master

system

restart

integer_datetimes

Reports whether PostgreSQL was built with support for 64-bit-integer dates and times.

Value Range Default Set Classifications
Boolean on read only

IntervalStyle

Sets the display format for interval values. The value sql_standard produces output matching SQL standard interval literals. The value postgres produces output matching PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to ISO. The value postgres_verbose produces output matching Greenplum releases prior to 3.3 when the DateStyle parameter was set to non-ISO output. The value iso_8601 will produce output matching the time interval format with designators defined in section 4.4.3.2 of ISO 8601. See the PostgreSQL 8.4 documentation for more information.

Value Range Default Set Classifications
postgres

postgres_verbose

sql_standard

iso_8601

postgres master

session

reload

join_collapse_limit

The planner will rewrite explicit inner JOIN constructs into lists of FROM items whenever a list of no more than this many items in total would result. By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of inner JOINs. Setting this variable to a value between 1 and from_collapse_limit might be useful to trade off planning time against the quality of the chosen plan (higher values produce better plans).

Value Range Default Set Classifications
1-n 20 master

session

reload

keep_wal_segments

For Greenplum Database master mirroring, sets the maximum number of processed WAL segment files that are saved by the by the active Greenplum Database master if a checkpoint operation occurs.

The segment files are used to sycnronize the active master on the standby master.

Value Range Default Set Classifications
integer 5 master

system

reload

superuser

krb_caseins_users

Sets whether Kerberos user names should be treated case-insensitively. The default is case sensitive (off).

Value Range Default Set Classifications
Boolean off master

system

restart

krb_server_keyfile

Sets the location of the Kerberos server key file.

Value Range Default Set Classifications
path and file name unset master

system

restart

krb_srvname

Sets the Kerberos service name.

Value Range Default Set Classifications
service name postgres master

system

restart

lc_collate

Reports the locale in which sorting of textual data is done. The value is determined when the Greenplum Database array is initialized.

Value Range Default Set Classifications
<system dependent>   read only

lc_ctype

Reports the locale that determines character classifications. The value is determined when the Greenplum Database array is initialized.

Value Range Default Set Classifications
<system dependent>   read only

lc_messages

Sets the language in which messages are displayed. The locales available depends on what was installed with your operating system - use locale -a to list available locales. The default value is inherited from the execution environment of the server. On some systems, this locale category does not exist. Setting this variable will still work, but there will be no effect. Also, there is a chance that no translated messages for the desired language exist. In that case you will continue to see the English messages.

Value Range Default Set Classifications
<system dependent>   local

system

restart

lc_monetary

Sets the locale to use for formatting monetary amounts, for example with the to_char family of functions. The locales available depends on what was installed with your operating system - use locale -a to list available locales. The default value is inherited from the execution environment of the server.

Value Range Default Set Classifications
<system dependent>   local

system

restart

lc_numeric

Sets the locale to use for formatting numbers, for example with the to_char family of functions. The locales available depends on what was installed with your operating system - use locale -a to list available locales. The default value is inherited from the execution environment of the server.

Value Range Default Set Classifications
<system dependent>   local

system

restart

lc_time

This parameter currently does nothing, but may in the future.

Value Range Default Set Classifications
<system dependent>   local

system

restart

listen_addresses

Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications - a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. If the list is empty, only UNIX-domain sockets can connect.

Value Range Default Set Classifications
localhost,

host names,

IP addresses,

* (all available IP interfaces)

* master

system

restart

local_preload_libraries

Comma separated list of shared library files to preload at the start of a client session.

Value Range Default Set Classifications
    local

system

restart

log_autostats

Logs information about automatic ANALYZE operations related to gp_autostats_mode and gp_autostats_on_change_threshold.

Value Range Default Set Classifications
Boolean off master

session

reload

superuser

log_connections

This outputs a line to the server log detailing each successful connection. Some client programs, like psql, attempt to connect twice while determining if a password is required, so duplicate "connection received" messages do not always indicate a problem.

Value Range Default Set Classifications
Boolean off local

system

restart

log_disconnections

This outputs a line in the server log at termination of a client session, and includes the duration of the session.

Value Range Default Set Classifications
Boolean off local

system

restart

log_dispatch_stats

When set to "on," this parameter adds a log message with verbose information about the dispatch of the statement.

Value Range Default Set Classifications
Boolean off local

system

restart

log_duration

Causes the duration of every completed statement which satisfies log_statement to be logged.

Value Range Default Set Classifications
Boolean off master

session

reload

superuser

log_error_verbosity

Controls the amount of detail written in the server log for each message that is logged.

Value Range Default Set Classifications
TERSE

DEFAULT

VERBOSE

DEFAULT master

session

reload

superuser

log_executor_stats

For each query, write performance statistics of the query executor to the server log. This is a crude profiling instrument. Cannot be enabled together with log_statement_stats.

Value Range Default Set Classifications
Boolean off local

system

restart

log_hostname

By default, connection log messages only show the IP address of the connecting host. Turning on this option causes logging of the host name as well. Note that depending on your host name resolution setup this might impose a non-negligible performance penalty.

Value Range Default Set Classifications
Boolean off local

system

restart

log_min_duration_statement

Logs the statement and its duration on a single log line if its duration is greater than or equal to the specified number of milliseconds. Setting this to 0 will print all statements and their durations. -1 disables the feature. For example, if you set it to 250 then all SQL statements that run 250ms or longer will be logged. Enabling this option can be useful in tracking down unoptimized queries in your applications.

Value Range Default Set Classifications
number of milliseconds, 0, -1 -1 master

session

reload

superuser

log_min_error_statement

Controls whether or not the SQL statement that causes an error condition will also be recorded in the server log. All SQL statements that cause an error of the specified level or higher are logged. The default is PANIC (effectively turning this feature off for normal use). Enabling this option can be helpful in tracking down the source of any errors that appear in the server log.

Value Range Default Set Classifications
DEBUG5 DEBUG4 DEBUG3 DEBUG2, DEBUG1 INFO NOTICE WARNING ERROR FATAL PANIC ERROR master

session

reload

superuser

log_min_messages

Controls which message levels are written to the server log. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log.

Value Range Default Set Classifications
DEBUG5 DEBUG4 DEBUG3 DEBUG2 DEBUG1 INFO NOTICE WARNING ERROR LOG FATAL PANIC WARNING master

session

reload

superuser

log_parser_stats

For each query, write performance statistics of the query parser to the server log. This is a crude profiling instrument. Cannot be enabled together with log_statement_stats.

Value Range Default Set Classifications
Boolean off master

session

reload

superuser

log_planner_stats

For each query, write performance statistics of the query planner to the server log. This is a crude profiling instrument. Cannot be enabled together with log_statement_stats.

Value Range Default Set Classifications
Boolean off master

session

reload

superuser

log_rotation_age

Determines the maximum lifetime of an individual log file. After this time has elapsed, a new log file will be created. Set to zero to disable time-based creation of new log files.

Value Range Default Set Classifications
Any valid time expression (number and unit) 1d local

system

restart

log_rotation_size

Determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files.

The maximum value is INT_MAX/1024. If an invalid value is specified, the default value is used. INT_MAX is the largest value that can be stored as an integer on your system.

Value Range Default Set Classifications
number of kilobytes 0 local

system

restart

log_statement

Controls which SQL statements are logged. DDL logs all data definition commands like CREATE, ALTER, and DROP commands. MOD logs all DDL statements, plus INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.

Value Range Default Set Classifications
NONE

DDL

MOD

ALL

ALL master

session

reload

superuser

log_statement_stats

For each query, write total performance statistics of the query parser, planner, and executor to the server log. This is a crude profiling instrument.

Value Range Default Set Classifications
Boolean off master

session

reload

superuser

log_timezone

Sets the time zone used for timestamps written in the log. Unlike TimeZone, this value is system-wide, so that all sessions will report timestamps consistently. The default is unknown, which means to use whatever the system environment specifies as the time zone.

Value Range Default Set Classifications
string unknown local

system

restart

log_truncate_on_rotation

Truncates (overwrites), rather than appends to, any existing log file of the same name. Truncation will occur only when a new file is being opened due to time-based rotation. For example, using this setting in combination with a log_filename such as gpseg#-%H.log would result in generating twenty-four hourly log files and then cyclically overwriting them. When off, pre-existing files will be appended to in all cases.

Value Range Default Set Classifications
Boolean off local

system

restart

max_appendonly_tables

Sets the maximum number of append-optimized relations that can be written to or loaded concurrently. Append-optimized table partitions and subpartitions are considered as unique tables against this limit. Increasing the limit will allocate more shared memory at server start.

Value Range Default Set Classifications
2048 10000 master

system

restart

max_connections

The maximum number of concurrent connections to the database server. In a Greenplum Database system, user client connections go through the Greenplum master instance only. Segment instances should allow 5-10 times the amount as the master. When you increase this parameter, max_prepared_transactions must be increased as well. For more information about limiting concurrent connections, see "Configuring Client Authentication" in the Greenplum Database Administrator Guide.

Increasing this parameter may cause Greenplum Database to request more shared memory.Increasing this parameter might cause Greenplum Database to request more shared memory. See shared_buffers for information about Greenplum server instance shared memory buffers.

Value Range Default Set Classifications
10-n 250 on master

750 on segments

local

system

restart

max_files_per_process

Sets the maximum number of simultaneously open files allowed to each server subprocess. If the kernel is enforcing a safe per-process limit, you don't need to worry about this setting. Some platforms such as BSD, the kernel will allow individual processes to open many more files than the system can really support.

Value Range Default Set Classifications
integer 1000 local

system

restart

max_fsm_pages

Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page slot.

Value Range Default Set Classifications
integer > 16 * max_fsm_relations 200000 local

system

restart

max_fsm_relations

Sets the maximum number of relations for which free space will be tracked in the shared memory free-space map. Should be set to a value larger than the total number of:

tables + indexes + system tables.

It costs about 60 bytes of memory for each relation per segment instance. It is better to allow some room for overhead and set too high rather than too low.

Value Range Default Set Classifications
integer 1000 local

system

restart

max_function_args

Reports the maximum number of function arguments.

Value Range Default Set Classifications
integer 100 read only

max_identifier_length

Reports the maximum identifier length.

Value Range Default Set Classifications
integer 63 read only

max_index_keys

Reports the maximum number of index keys.

Value Range Default Set Classifications
integer 32 read only

max_locks_per_transaction

The shared lock table is created with room to describe locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects, so no more than this many distinct objects can be locked at any one time. This is not a hard limit on the number of locks taken by any one transaction, but rather a maximum average value. You might need to raise this value if you have clients that touch many different tables in a single transaction.

Value Range Default Set Classifications
integer 128 local

system

restart

max_prepared_transactions

Sets the maximum number of transactions that can be in the prepared state simultaneously. Greenplum uses prepared transactions internally to ensure data integrity across the segments. This value must be at least as large as the value of max_connections on the master. Segment instances should be set to the same value as the master.

Value Range Default Set Classifications
integer 250 on master

250 on segments

local

system

restart

max_resource_portals_per_transaction

Sets the maximum number of simultaneously open user-declared cursors allowed per transaction. Note that an open cursor will hold an active query slot in a resource queue. Used for workload management.

Value Range Default Set Classifications
integer 64 master

system

restart

max_resource_queues

Sets the maximum number of resource queues that can be created in a Greenplum Database system. Note that resource queues are system-wide (as are roles) so they apply to all databases in the system.

Value Range Default Set Classifications
integer 9 master

system

restart

max_stack_depth

Specifies the maximum safe depth of the server's execution stack. The ideal setting for this parameter is the actual stack size limit enforced by the kernel (as set by ulimit -s or local equivalent), less a safety margin of a megabyte or so. Setting the parameter higher than the actual kernel limit will mean that a runaway recursive function can crash an individual backend process.

Value Range Default Set Classifications
number of kilobytes 2MB local

system

restart

max_statement_mem

Sets the maximum memory limit for a query. Helps avoid out-of-memory errors on a segment host during query processing as a result of setting max_statement_mem too high. When gp_resqueue_memory_policy=auto, statement_mem and resource queue memory limits control query memory usage. Taking into account the configuration of a single segment host, calculate this setting as follows:

(seghost_physical_memory) / (average_number_concurrent_queries)

Value Range Default Set Classifications
number of kilobytes 2000MB master

session

reload

superuser

password_encryption

When a password is specified in CREATE USER or ALTER USER without writing either ENCRYPTED or UNENCRYPTED, this option determines whether the password is to be encrypted.

Value Range Default Set Classifications
Boolean on master

session

reload

pgstat_track_activity_query_size

Sets the maximum length limit for the query text stored in current_query column of the system catalog view pg_stat_activity. The minimum length is 1024 characters.

Value Range Default Set Classifications
integer 1024 local

system

restart

pljava_classpath

A colon (:) separated list of the jar files containing the Java classes used in any PL/Java functions. The jar files listed here must also be installed on all Greenplum hosts in the following location: $GPHOME/lib/postgresql/java/

Value Range Default Set Classifications
string   master

session

reload

pljava_statement_cache_size

Sets the size in KB of the JRE MRU (Most Recently Used) cache for prepared statements.

Value Range Default Set Classifications
number of kilobytes 10 master

system

restart

superuser

pljava_release_lingering_savepoints

If true, lingering savepoints used in PL/Java functions will be released on function exit. If false, savepoints will be rolled back.

Value Range Default Set Classifications
Boolean true master

system

restart

superuser

pljava_vmoptions

Defines the startup options for the Java VM.

Value Range Default Set Classifications
string -Xmx64M master

system

restart

superuser

port

The database listener port for a Greenplum instance. The master and each segment has its own port. Port numbers for the Greenplum system must also be changed in the gp_segment_configuration catalog. You must shut down your Greenplum Database system before changing port numbers.

Value Range Default Set Classifications
any valid port number 5432 local

system

restart

random_page_cost

Sets the planner's estimate of the cost of a nonsequentially fetched disk page. This is measured as a multiple of the cost of a sequential page fetch. A higher value makes it more likely a sequential scan will be used, a lower value makes it more likely an index scan will be used.

Value Range Default Set Classifications
floating point 100 master

session

reload

readable_external_table_timeout

When an SQL query reads from an external table, the parameter value specifies the amount of time in seconds that Greenplum Database waits before cancelling the query when data stops being returned from the external table.

The default value of 0, specifies no time out. Greenplum Database does not cancel the query.

If queries that use gpfdist run a long time and then return the error "intermittent network connectivity issues",you can specify a value for ternal_table_timeout. If no data is returned by gpfdist for the specified length of time, Greenplum Database cancels the query. master system reload

Value Range Default Set Classifications
integer >= 0 0 master

system

reload

repl_catchup_within_range

For Greenplum Database master mirroring, controls updates to the active master. If the number of WAL segment files that have not been processed by the walsender exceeds this value, Greenplum Database updates the active master.

If the number of segment files does not exceed the value, Greenplum Database blocks updates to the to allow the walsender process the files. If all WAL segments have been processed, the active master is updated.

Value Range Default Set Classifications
0 - 64 1 master

system

reload

superuser

replication_timeout

For Greenplum Database master mirroring, sets the maximum time in milliseconds that the walsender process on the active master waits for a status message from the walreceiver process on the standby master. If a message is not received, the walsender logs an error message.

The wal_receiver_status_interval controls the interval between walreceiver status messages.

Value Range Default Set Classifications
0 - INT_MAX 60000 ms (60 seconds) master

system

reload

superuser

regex_flavor

The 'extended' setting may be useful for exact backwards compatibility with pre-7.4 releases of PostgreSQL.

Value Range Default Set Classifications
advanced

extended

basic

advanced master

session

reload

resource_cleanup_gangs_on_wait

If a statement is submitted through a resource queue, clean up any idle query executor worker processes before taking a lock on the resource queue.

Value Range Default Set Classifications
Boolean on master

system

restart

resource_select_only

Sets the types of queries managed by resource queues. If set to on, then SELECT, SELECT INTO, CREATE TABLE AS SELECT, and DECLARE CURSOR commands are evaluated. If set to off INSERT, UPDATE, and DELETE commands will be evaluated as well.

Value Range Default Set Classifications
Boolean off master

system

restart

search_path

Specifies the order in which schemas are searched when an object is referenced by a simple name with no schema component. When there are objects of identical names in different schemas, the one found first in the search path is used. The system catalog schema, pg_catalog, is always searched, whether it is mentioned in the path or not. When objects are created without specifying a particular target schema, they will be placed in the first schema listed in the search path. The current effective value of the search path can be examined via the SQL function current_schemas(). current_schemas() shows how the requests appearing in search_path were resolved.

Value Range Default Set Classifications
a comma- separated list of schema names $user,public master

session

reload

seq_page_cost

Sets the planner's estimate of the cost of a disk page fetch that is part of a series of sequential fetches.

Value Range Default Set Classifications
floating point 1 master

session

reload

server_encoding

Reports the database encoding (character set). It is determined when the Greenplum Database array is initialized. Ordinarily, clients need only be concerned with the value of client_encoding.

Value Range Default Set Classifications
<system dependent> UTF8 read only

server_version

Reports the version of PostgreSQL that this release of Greenplum Database is based on.

Value Range Default Set Classifications
string 8.2.15 read only

server_version_num

Reports the version of PostgreSQL that this release of Greenplum Database is based on as an integer.

Value Range Default Set Classifications
integer 80215 read only

shared_buffers

Sets the amount of memory a Greenplum server instance uses for shared memory buffers. This setting must be at least 128 kilobytes and at least 16 kilobytes times max_connections.

The value of this parameter cannot exceed the value specified by the operating system parameter SHMMAX that specifies the maximum size of shared memory segment that can be allocated. See the Greenplum Database Installation Guide for information about the value for the parameter SHMMAX.

Value Range Default Set Classifications
integer > 16K * max_connections 125MB local

system

restart

shared_preload_libraries

A comma-separated list of shared libraries that are to be preloaded at server start. PostgreSQL procedural language libraries can be preloaded in this way, typically by using the syntax '$libdir/plXXX' where XXX is pgsql, perl, tcl, or python. By preloading a shared library, the library startup time is avoided when the library is first used. If a specified library is not found, the server will fail to start.

Value Range Default Set Classifications
    local

system

restart

ssl

Enables SSL connections.

Value Range Default Set Classifications
Boolean off master

system

restart

ssl_ciphers

Specifies a list of SSL ciphers that are allowed to be used on secure connections. See the openssl manual page for a list of supported ciphers.

Value Range Default Set Classifications
string ALL master

system

restart

standard_conforming_strings

Reports whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. The value is currently always off, indicating that backslashes are treated as escapes. It is planned that this will change to on in a future release when string literal syntax changes to meet the standard. Applications may check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported.

Value Range Default Set Classifications
Boolean of read only

statement_mem

Allocates segment host memory per query. The amount of memory allocated with this parameter cannot exceed max_statement_mem or the memory limit on the resource queue through which the query was submitted. When gp_resqueue_memory_policy =auto, statement_mem and resource queue memory limits control query memory usage.

Value Range Default Set Classifications
number of kilobytes 128MB master

session

reload

statement_timeout

Abort any statement that takes over the specified number of milliseconds. 0 turns off the limitation.

Value Range Default Set Classifications
number of milliseconds 0 master

session

reload

stats_queue_level

Collects resource queue statistics on database activity.

Value Range Default Set Classifications
Boolean off master

session

reload

superuser_reserved_connections

Determines the number of connection slots that are reserved for Greenplum Database superusers.

Value Range Default Set Classifications
integer < max_connections 3 local

system

restart

tcp_keepalives_count

How many keepalives may be lost before the connection is considered dead. A value of 0 uses the system default. If TCP_KEEPCNT is not supported, this parameter must be 0.

Use this parameter for all connections that are not between a primary and mirror segment. Use gp_filerep_tcp_keepalives_count for settings that are between a primary and mirror segment.

Value Range Default Set Classifications
number of lost keepalives 0 local

system

restart

tcp_keepalives_idle

Number of seconds between sending keepalives on an otherwise idle connection. A value of 0 uses the system default. If TCP_KEEPIDLE is not supported, this parameter must be 0.

Use this parameter for all connections that are not between a primary and mirror segment. Use gp_filerep_tcp_keepalives_idle for settings that are between a primary and mirror segment.

Value Range Default Set Classifications
number of seconds 0 local

system

restart

tcp_keepalives_interval

How many seconds to wait for a response to a keepalive before retransmitting. A value of 0 uses the system default. If TCP_KEEPINTVL is not supported, this parameter must be 0.

Use this parameter for all connections that are not between a primary and mirror segment. Use gp_filerep_tcp_keepalives_interval for settings that are between a primary and mirror segment.

Value Range Default Set Classifications
number of seconds 0 local

system

restart

temp_buffers

Sets the maximum number of temporary buffers used by each database session. These are session-local buffers used only for access to temporary tables. The setting can be changed within individual sessions, but only up until the first use of temporary tables within a session. The cost of setting a large value in sessions that do not actually need a lot of temporary buffers is only a buffer descriptor, or about 64 bytes, per increment. However if a buffer is actually used, an additional 8192 bytes will be consumed.

Value Range Default Set Classifications
integer 1024 master

session

reload

TimeZone

Sets the time zone for displaying and interpreting time stamps. The default is to use whatever the system environment specifies as the time zone. See Date/Time Keywords in the PostgreSQL documentation.

Value Range Default Set Classifications
time zone abbreviation   local

restart

timezone_abbreviations

Sets the collection of time zone abbreviations that will be accepted by the server for date time input. The default is Default, which is a collection that works in most of the world. Australia and India, and other collections can be defined for a particular installation. Possible values are names of configuration files stored in /share/postgresql/timezonesets/ in the installation directory.

Value Range Default Set Classifications
string Default master

session

reload

track_activities

Enables the collection of statistics on the currently executing command of each session, along with the time at which that command began execution. When enabled, this information is not visible to all users, only to superusers and the user owning the session. This data can be accessed via the pg_stat_activity system view.

Value Range Default Set Classifications
Boolean on master

session

reload

track_counts

Enables the collection of row and block level statistics on database activity. If enabled, the data that is produced can be accessed via the pg_stat and pg_statio family of system views.

Value Range Default Set Classifications
Boolean off local

system

restart

transaction_isolation

Sets the current transaction's isolation level.

Value Range Default Set Classifications
read committed

serializable

read committed master

session

reload

transaction_read_only

Sets the current transaction's read-only status.

Value Range Default Set Classifications
Boolean off master

session

reload

transform_null_equals

When on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. The correct SQL-spec-compliant behavior of expr = NULL is to always return null (unknown).

Value Range Default Set Classifications
Boolean off master

session

reload

unix_socket_directory

Specifies the directory of the UNIX-domain socket on which the server is to listen for connections from client applications.

Value Range Default Set Classifications
directory path unset local

system

restart

unix_socket_group

Sets the owning group of the UNIX-domain socket. By default this is an empty string, which uses the default group for the current user.

Value Range Default Set Classifications
UNIX group name unset local

system

restart

unix_socket_permissions

Sets the access permissions of the UNIX-domain socket. UNIX-domain sockets use the usual UNIX file system permission set. Note that for a UNIX-domain socket, only write permission matters.

Value Range Default Set Classifications
numeric UNIX file permission mode (as accepted by the chmod or umask commands) 511 local

system

restart

update_process_title

Enables updating of the process title every time a new SQL command is received by the server. The process title is typically viewed by the ps command.

Value Range Default Set Classifications
Boolean on local

system

restart

vacuum_cost_delay

The length of time that the process will sleep when the cost limit has been exceeded. 0 disables the cost-based vacuum delay feature.

Value Range Default Set Classifications
milliseconds < 0 (in multiples of 10) 0 local

system

restart

vacuum_cost_limit

The accumulated cost that will cause the vacuuming process to sleep.

Value Range Default Set Classifications
integer > 0 200 local

system

restart

vacuum_cost_page_dirty

The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again.

Value Range Default Set Classifications
integer > 0 20 local

system

restart

vacuum_cost_page_hit

The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page.

Value Range Default Set Classifications
integer > 0 1 local

system

restart

vacuum_cost_page_miss

The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content.

Value Range Default Set Classifications
integer > 0 10 local

system

restart

vacuum_freeze_min_age

Specifies the cutoff age (in transactions) that VACUUM should use to decide whether to replace transaction IDs with FrozenXID while scanning a table.

For information about VACUUM and transaction ID management, see "Managing Data" in the Greenplum Database Administrator Guide and the PostgreSQL documentation.

Value Range Default Set Classifications
integer 0-100000000000 100000000 local

system

restart

wal_receiver_status_interval

For Greenplum Database master mirroring, sets the interval in seconds between walreceiver process status messages that are sent to the active master. Under heavy loads, the time might be longer.

The value of replication_timeout controls the time that the walsender process waits for a walreceiver message.

Value Range Default Set Classifications
integer 0- INT_MAX/1000 10 sec master

system

reload

superuser

xid_stop_limit

The number of transaction IDs prior to the ID where transaction ID wraparound occurs. When this limit is reached, Greenplum Database stops creating new transactions to avoid data loss due to transaction ID wraparound.

Value Range Default Set Classifications
integer 10000000 - 2000000000 1000000000 local

system

restart

xid_warn_limit

The number of transaction IDs prior to the limit specified by xid_stop_limit. When Greenplum Database reaches this limit, it issues a warning to perform a VACUUM operation to avoid data loss due to transaction ID wraparound.

Value Range Default Set Classifications
integer 10000000 - 2000000000 500000000 local

system

restart