How do I check running queries and diagnose resource consumption issues for my Amazon RDS for PostrgreSQL or Aurora PostgreSQL DB instance?

Resolution

Check running queries

Your user account must have the rds_superuser role to see all the processes that are running on a DB instance of Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible. Otherwise, pg_stat_activity shows those queries that are running for its own processes. For more information, see the PostgreSQL documentation for the Statistics Collector.

1.    Connect to the DB instance that's running Amazon RDS for PostgreSQL or Aurora PostgreSQL.

2.    Run the following command:

SELECT * FROM pg_stat_activity ORDER BY pid;

You can also modify this command to view the list of running queries. The queries are ordered by when the connections were established:

SELECT * FROM pg_stat_activity ORDER BY backend_start;

If the xact_start column value is null, then there's no transaction opened in that session:

SELECT * FROM pg_stat_activity ORDER BY xact_start;

Or, view the same list of running queries ordered by when the last query started:

SELECT * FROM pg_stat_activity ORDER BY query_start;

To see an aggregated view of the wait events, if there are any, run the following command:

select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

Diagnose resource consumption

You can use pg_stat_activity and Enhanced Monitoring to identify the query or process that's consuming large amounts of system resources. After you turn on Enhanced Monitoring, set the granularity to a level that's sufficient to see the information that you need to diagnose the issue. Then, review pg_stat_activity to see the current activities in your database. You can also review the Enhanced Monitoring metrics at that time.

1.    View the OS process list metric to identify the query that's consuming resources. In the following example, the process consumes about 95% of the CPU time on the RDS DB instance. The process ID (pid) of the process is 14431. The process is running a SELECT statement. You can also check the MEM% to see the usage of system memory.

2.    Connect to the DB instance that is running PostgreSQL or Aurora PostgreSQL.

3.    Run the following command to identify the current activity of the session:

SELECT * FROM pg_stat_activity WHERE pid = PID;

Note: Replace PID with the pid that you identified in the step 1.

4.    Check the result of the command:

datid            | 14008
datname          | postgres
pid              | 14431
usesysid         | 16394
usename          | master
application_name | psql
client_addr      | 27.0.3.145
client_hostname  |
client_port      | 52003
backend_start    | 2020-03-11 23:08:55.786031+00
xact_start       | 2020-03-11 23:12:16.960942+00
query_start      | 2020-03-11 23:12:16.960942+00
state_change     | 2020-03-11 23:12:16.960945+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 812
query            | SELECT COUNT(*) FROM columns c1, columns c2, columns c3, columns c4, columns c5;
backend_type     | client backend

To stop the process that is running the query, invoke the following query from another session. Replace PID with the pid of the process that you identified in step 3.

SELECT pg_terminate_backend(PID);

Important: Before you end transactions, evaluate the potential affect that each transaction has on the state of your database and your application.

Related information

How can I troubleshoot high CPU utilization for Amazon RDS or Aurora PostgreSQL?

Understanding PostgreSQL roles and permissions

Creating roles

psql (on the PostgreSQL website)

pg_stat_activity (on the PostgreSQL website)