max_connections and max_prepared_transactions in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This document takes a look at the two GUC's (Grand Unified Configuration Settings): "max_connections" and "max_prepared_transactions".

Environment

Resolution

The "max_connections" parameter determines the maximum amount of concurrent connections to the database instance.

The value of "max_connections" on the Master determines how many client connections can connect to GPDB at one time.

The value of "max_connections" on segments needs to be 5-10 times greater than the value of  "max_connections" on the Master. This is because of the way GPDB uses segments to run the SQL statements.

The "max_prepared_transactions" parameter determines the maximum amount of transactions that can be in the prepared state simultaneously.

Note: The Master and segment values should be the same.

Additional Information

It is recommended that the value of "max_prepared_transactions", across all segments in the cluster, should be equal or greater than the value of "max_connections" on the Master instance. This will allow every session to have a transaction in a prepared state.

Both of these parameters control the size of shared memory areas. Increasing them (one or both) will increase the demand for shared memory for the server instance.

If the shared memory allocation size is greater than OS configured maximum values (SHMMAX, SHMALL), then the shared memory allocation will be refused and the server will not start. An error similar to the following will be printed in the <instance_data_directory>/pg_log/startup.log file:

2015-05-21 08:57:19.756559 PDT,,,p33326,th-1397552600,,,,0,,,seg-1,,,,,"FATAL","XX000","shmat(id=9109504) failed: Cannot allocate memory (pg_shmem.c:193)",,,,,,,,"InternalIpcMemoryCreate","pg_shmem.c",193