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.
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.
pg_stat_activity system view to find idle connections.
SELECT datname, procpid, sess_id, usename, current_query from pg_stat_activity;
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;