Managing client processes and user sessions

Greenplum® launches a new internal process for each client connection. An administrator or any other user with the mdb_admin role can do the following:

• Get a list of processes.
• Cancel a user session.
• Terminate a specific process.
• Close a database connection.
• Manage load.

Getting a list of processesGetting a list of processes

Run a query to the mdb_toolkit.pg_stat_activity() function. The resulting list of processes will include user queries:

SELECT usename, pid, waiting, state, query, datname
FROM mdb_toolkit.pg_stat_activity();

Result:

 usename |  pid     | waiting | state  |         query          | datname
---------+----------+---------+--------+------------------------+---------
  sammy  |   31861  |    f    | idle   | SELECT * FROM testtbl; | testdb
  billy  |   31905  |    t    | active | SELECT * FROM topten;  | testdb

Where:

• usename: Username.

• pid: Process ID.

• waiting: Process is awaiting a transaction:

  • t: Yes.
  • f: No.

• state: Process state:

  • active: Running.
  • idle: Idle.

• query: Query.

• datname: Database name.

Canceling a user sessionCanceling a user session

1. Terminate the session process.
2. Terminate the DB connection.

Terminating a specific processTerminating a specific process

Run a query to the mdb_toolkit.gp_cancel_backend() function:

SELECT *
FROM mdb_toolkit.gp_cancel_backend(<process_ID>);

You can get the process ID with the list of cluster processes.

Closing a database connectionClosing a database connection

Run a query to the mdb_toolkit.gp_terminate_backend() function:

SELECT *
FROM mdb_toolkit.gp_terminate_backend(<process_ID>);

You can get the process ID with the list of cluster processes.

Load managementLoad management

1. Run a query to the gp_toolkit.gp_resgroup_config file to get the resource group name and find out the current number of concurrent transactions:

   SELECT groupname, concurrency
   FROM gp_toolkit.gp_resgroup_config;
   

2. Set the number of concurrent transactions for a resource group:

   ALTER RESOURCE GROUP <resource_group_name>
   SET concurrency <number_of_concurrent_transactions>;
   

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.