Pivotal Knowledge Base

Follow

"max_connections" & "max_prepared_transactions"

Environment

Product Version
Pivotal Greenplum All Versions

Purpose

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

Solution

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

The value of "max_connections" on 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 master, because of the way GPDB uses segments to run the SQL statements.

"max_prepared_transactions" parameter determines the maximum amount of transactions that can be in prepared state simultaneously. Master and segment values should be the same.

Note:

  1. The recommendation for the relation of values between these two parameters is 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 at least that every session can have a transaction in prepared state.
  2. Both of these parameters control sizes of shared memory areas, so increasing them (one or the other or both) will increase the demand for shared memory for the server instance. If the shared memory allocation size is greater than OS configured maximums (SHMMAX, SHMALL), then the shared memory allocation will be refused and the server will not start. 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

Comments

  • Avatar
    Stephen Carter

    Hi Lubo, could you give some examples of why an admin might need to increase max_connections /max_prepared_transactions?

Powered by Zendesk