View live queries with pg_stat_activity

YugabyteDB supports the PostgreSQL pg_stat_activity view to analyze live queries. This view returns analytic and diagnostic information about active YugabyteDB server processes and queries. The view returns one row per server process, and displays information related to the current status of the database connection.

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

Supported fields

At a ysqlsh prompt, run the following meta-command to return the fields supported by pg_stat_activity:

yugabyte=# \d pg_stat_activity

The following table describes the fields and their values:

Field Type Description
datid oid Object identifier (OID) of the database to which the backend is connected.
datname name Name of the database to which the backend is connected.
pid integer Backend process ID.
usesysid oid The user's OID.
usename name The user's name.
application_name text Name of the application connected to this backend.
client_addr inet The client's IP address. Empty if the client is connected through a Unix socket on the server, or if this is an internal process such as autovacuum.
client_hostname text The client's hostname, as reported by a reverse DNS lookup of client_addr.
client_port integer TCP port the client is using for communication with the backend server. A value of -1 indicates a Unix socket.
backend_start timestampz Time at which the current backend process started.
xact_start timestampz Time at which the current transaction started, or null if no transaction is active. If the current query is the process's first transaction, this field is equivalent to the query_start field.
query_start timestampz Time at which the currently active query started. If the state field is not set to active, the query_start field indicates the time when the last query was started.
state_change timestampz Time at which the previous state changed.
wait_event_type text Type of event the backend is waiting for.
wait_event text Name of the event being waited for.
state text Current state of the backend. Valid values are active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, and disabled.
backend_xid xid This backend's top-level transaction identifier, if any.
backend_xmin xid The current backend's xmin horizon.
query text The last executed query. If state is active, this is the currently executing query. If state has a different value, this is the last executed query. By default, the query text is limited to the first 1,024 characters. Adjust the track_activity_query_size parameter to change the character limit.
backend_type text The current backend's type. Possible values are autovacuum launcher, autovacuum worker, background worker, background writer, client backend, checkpointer, startup, walreceiver, walsender, and walwriter.
allocated_mem_bytes bigint Heap memory usage in bytes of the backend process.
rss_mem_bytes bigint Resident Set Size of the backend process in bytes. It shows how much memory is allocated to the process and is in RAM. It does not include memory that is swapped out.

Examples

Get basic information

The following query returns basic information about active Yugabyte processes:

yugabyte=# SELECT datname, pid, application_name, state, query
    FROM pg_stat_activity;

 datname  |  pid  | application_name | state  |                                   query
----------+-------+------------------+--------+----------------------------------------------------------------------------
 yugabyte | 10027 | ysqlsh           | active | SELECT datname, pid, application_name, state, query FROM pg_stat_activity;
          | 10013 |                  |        |
(2 rows)

In this listing:

  • datname is the database connected to this process.
  • pid is the process ID.
  • application_name is the application connected to this process.
  • state is the operational condition of the process.
  • query is the latest query executed for this process.

Identify and terminate an open transaction

Often enough, you may need to identify long-running queries, because these queries could indicate deeper problems. The pg_stat_activity view can help identify these issues. In this example, you create an open transaction, identify it, and terminate it. The example uses the Retail Analytics sample dataset.

Create an open transaction

  1. Use the following query to return a row from the users table.

    yb_demo=# SELECT id, name, state
    FROM users
    WHERE id = 212;

     id  |     name      | state
-----+---------------+-------
 212 | Jacinthe Rowe | CO
(1 row)

  2. Update the state column value of this role with a transaction. The query is deliberately missing the END; statement to close the transaction.

    yb_demo=# BEGIN TRANSACTION;
    UPDATE users
        SET state = 'IA'
        WHERE id = 212;

    BEGIN
UPDATE 1

Find the open transaction

Because the transaction never ends, it wastes resources as an open process.

  1. Check the state of the transaction by opening another ysqlsh instance and finding information about this idle transaction with pg_stat_activity.

    yugabyte=# SELECT datname, pid, application_name, state, query
    FROM pg_stat_activity;

     datname  |  pid  | application_name |        state        |                                   query
----------+-------+------------------+---------------------+----------------------------------------------------------------------------
 yugabyte | 10381 | ysqlsh           | active              | SELECT datname, pid, application_name, state, query FROM pg_stat_activity;
 yb_demo  | 10033 | ysqlsh           | idle in transaction | UPDATE users SET state = 'IA' WHERE id = 212;
          | 10013 |                  |                     |
(3 rows)

  2. Find the idle transaction's PID. In the sample output in the previous step, it's PID 10033, in the second row.

Terminate the open transaction

  1. Terminate the idle transaction. Replace <pid> with the PID of the process to terminate.

    yugabyte=# SELECT pg_terminate_backend(<pid>);

     pg_terminate_backend
---------------------------
 t
(1 row)

  2. The pg_terminate_backend function returns t on success, and f on failure. Query pg_stat_activity again in the second terminal, and verify that the idle process has ended.

    yugabyte=# SELECT datname, pid, application_name, state, query
    FROM pg_stat_activity;

     datname  |  pid  | application_name | state  |                                   query
----------+-------+------------------+--------+----------------------------------------------------------------------------
 yugabyte | 10381 | ysqlsh           | active | SELECT datname, pid, application_name, state, query FROM pg_stat_activity;
          | 10013 |                  |        |
(2 rows)

You can run some time-related queries to help you identify long-running transactions. These are particularly helpful when there are a lot of open connections on that node.

Get a list of processes ordered by current txn_duration:

yugabyte=# SELECT datname, pid, application_name, state, query, now() - xact_start
    AS txn_duration
    FROM pg_stat_activity
    ORDER BY txn_duration desc;

 datname  |  pid  | application_name | state  |                                  query                                  | txn_duration
----------+-------+------------------+--------+-------------------------------------------------------------------------+--------------
 yugabyte | 17695 | ysqlsh           | idle   |                                                                         |
          | 17519 |                  |        |                                                                         |
 yugabyte | 17540 | ysqlsh           | active | SELECT datname, pid, application_name, state, query, now() - xact_start+| 00:00:00
          |       |                  |        |     AS txn_duration                                                    +|
          |       |                  |        |     FROM pg_stat_activity                                              +|
          |       |                  |        |     ORDER BY txn_duration desc;                                         |
(3 rows)

Get a list of processes where the current transaction has taken more than 1 minute:

yugabyte=# SELECT datname, pid, application_name, state, query, xact_start
    FROM pg_stat_activity
    WHERE now() - xact_start > '1 min';

 datname |  pid  | application_name |        state        |                     query                     |          xact_start
---------+-------+------------------+---------------------+-----------------------------------------------+------------------------------
 yb_demo | 10033 | ysqlsh           | idle in transaction | UPDATE users SET state = 'IA' WHERE id = 212; | 2021-05-06 15:26:28.74615-04
(1 row)

Learn more