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

Last updated: 2020-03-26

I need to see which queries are actively running on an Amazon Relational Database Service (Amazon RDS) or Amazon Aurora PostgreSQL DB instance. How can I do this?

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 14431 and the process is executing a SELECT statement. You can also see the usage of system memory by checking the MEM%.

NAME VIRT RES CPU% MEM% VMLIMIT
postgres: master postgres 27.0.3.145(52003) SELECT [14431] 457.66 MB 27.7 MB 95.15 2.78 unlimited

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 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 executing the query, invoke the following query from another session. Be sure to replace PID with 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.


Did this article help you?

Anything we could improve?


Need more help?