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

Resolution

Check running queries

Your user account must be granted the rds_superuser role to see all the processes that are running on a DB instance of RDS for PostgreSQL or Aurora PostgreSQL. Otherwise, pg_stat_activity shows only 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 is running 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 ordered by when the connections were established:

SELECT * FROM pg_stat_activity ORDER BY backend_start;

If the column value is null, then there is 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 was executed:

SELECT * FROM pg_stat_activity ORDER BY query_start;

For 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

By using pg_stat_activity and enabling Enhanced Monitoring, you can identify the query/process that is consuming large amounts of system resources. After enabling Enhanced Monitoring, be sure that the granularity set is sufficient to see the information that you need to diagnose the issue. Then, you can check pg_stat_activity to see the current activities in your database and the Enhanced Monitoring metrics at that time.

1.    Identify the query that is consuming resources by viewing the OS process list metric. In the following example, the process is consuming about 95% of the CPU time on the RDS DB instance. The process ID (pid) of the process is 14431and the process is executing a SELECTstatement. You can also see the usage of system memory by checking the MEM%.

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

3.    Identify the current activity of the session by running the following command:

SELECT * FROM pg_stat_activity WHERE pid = PID;

**Note:**Replace PIDwith 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 executing the query, invoke the following query from another session. Be sure to replace PIDwith the pid of the process that you identified in step 3.

SELECT pg_terminate_backend(PID);

Important: Before terminating transactions, evaluate the potential impact 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 Amazon Aurora PostgreSQL?

Creating roles

Enhanced Monitoring

PostgreSQL documentation for psql

PostgreSQL documentation for pg_stat_activity