Modifying Database Parameters

gp_vmem_protect_limit

Total amount of memory allocated by each segment database to all running queries

Physical memory of each node/Number of primary segments on each node

shared_buffers

Memory buffer for drive read and write

Do not set this parameter to a large value.

effective_cache_size

Sets the assumption about the effective size of the drive cache that is available to a single query for the optimizer (planner). This is factored into estimates of the cost of using an index.

A larger value makes it more likely that index scans will be used.

work_mem

Size of the memory for sorting in each segment

Set it based on the active segment on each node. If the value is too large, swap occurs.

temp_buffers

Size of the temporary buffer for storing temporary data accessed by the database

If you need to access a large temporary table, increase the value to improve performance.

gp_fts_probe_threadcount

Number of threads for fault detection

The value is greater than or equal to the number of segments on each node.

gp_hashjoin_tuples_per_bucket

Target density of the hash table used by HashJoin operations

A smaller value tends to produce larger hash tables, which increases join performance.

gp_interconnect_setup_timeout

Amount of time to wait for the Greenplum database interconnect to complete setup before it times out

Set it to a large value in a heavy-load environment.

gp_statement_mem

Total memory that can be used by a single query in the segment database

Recommended value: (gp_vmem_protect_limit x 0.9)/Maximum number of queries in the segment database

gp_workfile_limit_files_per_query

If the memory allocated for SQL query is insufficient, the Greenplum creates overflow files. This parameter specifies the number of overflow files that can be created for a query.

If the number of overflow files exceeds the upper limit, you are advised to optimize the SQL statement, data distribution policy, and memory configuration first.

max_connections

Maximum number of connections

Segment instances should allow 5 to 10 times the amount as the master.

gp_set_proc_affinity

Controls the CPU affinity between the master node processes and the segment node processes.

You are advised to set it to on so that core binding is automatically performed when the database is started.