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?
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.
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%.
|postgres: master postgres 22.214.171.124(52003) SELECT ||457.66 MB||27.7 MB||95.15||2.78||unlimited|
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 | 126.96.36.199 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.
Important: Before terminating transactions, evaluate the potential impact that each transaction has on the state of your database and your application.
PostgreSQL documentation for psql
PostgreSQL documentation for pg_stat_activity