Pivotal Knowledge Base

Follow

Increasing "max_connections" over 1000 leads to errors

Environment

Product Version
Pivotal Greenplum  All versions

Symptom

Pivotal Greenplum (GPDB) Admin has increased the parameters of max_connections in postgresql.conf file to more than 1000; because of this change the database does not start (shared memory allocation errors in the log file).

Cause

Some database parameters control the size of shared memory areas that database allocate. 'max_connections' is one of those parameters. Increasing max_connections will increase the allocation size of shared memory, which in turn will affect the total size of shared memory the instance allocates. If this amount is greater than allowed by OS parameters (SHMMAX, SHMALL), then the allocation will be refused and the instance will not be able to start.

GPDB was designed to be a Data Warehouse (DW) type of database system. In DW systems, there is usually a handful of large queries running (as opposed to OLTP systems where there are multiple quick statements running). Because of that GPDB is not optimized for handling thousands of connections simultaneously. 

Resolution

  • Immediate resolution is to decrease the value of "max_connections" and the instance will be able to start again.
  • If "max_connections" really needs to be that high, increase the SHMMAX and SHMALL OS parameter values, so that the shared memory allocation succeeds.

Note: If the cluster really needs to handle thousands of connections, it is recommended to install connection pooler in front of the database to handle the connections.

Comments

  • Avatar
    haizhu feng

    Hi luobo, I also meet this trouble.
    I have a greenplum cluster that contains of 20 segment nodes with mirror, master node and standby master node. The greenplum version is 4.3.11.0. OS is CentOS 6.5. The physical memory is 32GB on my greenplum nodes (master and segment nodes).
    I really need to set max_connection greater than 1000. Could you please guide me what parameters should be set and how to set ?
    I set the parameters 'kernel.shmmax=1600000000' 'kernel.shmall=4000000000' , then set 'shared_buffers=8GB', 'max_prepared_transactions=1000' and 'max_connections -v 2000 -m 1000 '. However, the gp cluster can't be started with the error "gpstart error:Do not have enough valid segments to start the array" until I decrease the above parameters to 'shared_buffers=250MB' 'max_prepared_trasactions=500' and 'max_connections -v 1500 -m 500 ' gradually.

Powered by Zendesk