Number of connections exceeds [n]

What is this alert?

This alert is raised when the number of concurrent connections at the Greenplum Database master instance exceeds a specified number. The number specified should be set lower than the max_connections server configuration parameter so that when you receive the alert you can act before Greenplum Database begins to reject client connection requests. For example, if max_connections for the master is set to 100, you could set an alert to 80.

What to do

Look for idle or stale connections and terminate them

Users can connect to Greenplum Database using a client such as psql, execute queries and remain connected, but inactive, leaving the connection in an idle state. Greenplum Database eventually releases resources used by idle connections, but once the maximum number of allowed connections has been reached, new connection requests are denied.

Use the pg_stat_activity system view to find idle connections.

SELECT datname, procpid, sess_id, usename, current_query from pg_stat_activity;

Use the pg_cancel_backend(<PID>) function to cancel idle connections.

Determining how long a connection has been idle is not possible with just the information in the Greenplum Database 5.x system tables. You can, however, see this information by creating the session_level_memory_consumption view in the database. Follow the instructions at Viewing Session Memory Usage Information to create this view in each database.

After you install the session_level_memory_consumption view, a query like the following shows the idle connections with the length of time they have been idle.

SELECT a.datname, application_name, a.usename, a.sess_id, procpid, 
    now()-idle_start AS time_idle 
FROM pg_stat_activity a, session_state.session_level_memory_consumption b 
WHERE a.sess_id = b.sess_id AND b.idle_start < now();
ORDER BY time_idle DESC;