Limiting Concurrent Connections in Greenplum Database

Limiting Concurrent Connections

To limit the number of active concurrent sessions to your Greenplum Database system, you can configure the max_connections server configuration parameter. This is a local parameter, meaning that you must set it in the postgresql.conf file of the master, the standby master, and each segment instance (primary and mirror). 

The value of max_connections on segments must be 5-10 times the value on the master.

When you set max_connections, you must also set the dependent parameter max_prepared_transactions. This value must be at least as large as the value of max_connections on the master, and segment instances should be set to the same value as the master.

For example:

In $MASTER_DATA_DIRECTORY/postgresql.conf (including standby master):

max_connections=100

max_prepared_transactions=100

In SEGMENT_DATA_DIRECTORY/postgresql.conf for all segment instances:

max_connections=500

max_prepared_transactions=100

To change the number of allowed connections

1.Stop your Greenplum Database system:

$ gpstop

2.On your master host, edit $MASTER_DATA_DIRECTORY/postgresql.conf and change the following two parameters:

max_connections (the number of active user sessions you want to allow plus the number of superuser_reserved_connections)

max_prepared_transactions (must be greater than or equal to max_connections)

3.On each segment instance, edit SEGMENT_DATA_DIRECTORY/postgresql.conf and and change the following two parameters:

max_connections (must be 5-10 times the value on the master)

max_prepared_transactions (must be equal to the value on the master)

4.Restart your Greenplum Database system:

$ gpstart

Note: Raising the values of these parameters may cause Greenplum Database to request more shared memory. To mitigate this effect, consider decreasing other memory-related parameters such as gp_cached_segworkers_threshold.