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:

• Getting a list of processes.
• Canceling a user session.
• Terminating a specific process.
• Closing a database connection.
• Load management.

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 waiting for 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 <concurrent_transaction_number>;
   

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