Managing client processes and user sessions

Yandex MPP Analytics for PostgreSQL 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.
• Manage load.
• Cancel a user session.

Getting a list of processesGetting a list of processes

Requests for lists of processes are different for Greenplum® and Apache Cloudberry™. Greenplum® uses the waiting field, while Apache Cloudberry™, the wait_event_type and wait_event fields.

Learn more about available database management systems in Yandex MPP Analytics for PostgreSQL.

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

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

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

 usename |    pid   | wait_event_type | wait_event | state  |             query              | datname
---------+----------+-----------------+------------+--------+--------------------------------+---------
  user1  |  1798921 |     Timeout     |  PgSleep   | active | SELECT pg_sleep(25);           |  mydb
  user2  |  1798938 |     Lock        |  advisory  | active | SELECT pg_advisory_lock(1234); |  mydb

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>;
   

Canceling a user sessionCanceling a user session

You can terminate a user session using one of the following methods:

1. Terminate the process containing the session.
2. Close the database connection.
3. Terminate all user sessions.

Terminating a processTerminating a process

Call 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

Call 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.

Terminating all user sessionsTerminating all user sessions

To terminate all user sessions in the cluster, call the mdb_toolkit.terminate_all_user_backends() function:

SELECT *
FROM mdb_toolkit.terminate_all_user_backends();

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

Apache® and Apache Cloudberry™ are registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries.