Limiting Concurrent Connections in Greenplum database

posted Sep 12, 2012, 11:35 AM by Sachchida Ojha
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):
In SEGMENT_DATA_DIRECTORY/postgresql.conf for all segment instances:
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.