How do I check running queries and diagnose resource consumption issues for my Amazon RDS or Aurora PostgreSQL DB instance?
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 14431and the process is executing a SELECTstatement. 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 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?
PostgreSQL documentation for psql
PostgreSQL documentation for pg_stat_activity
Vídeos relacionados

Contenido relevante
- OFICIAL DE AWSActualizada hace 5 meses
- OFICIAL DE AWSActualizada hace 6 meses
- OFICIAL DE AWSActualizada hace un año
- OFICIAL DE AWSActualizada hace 6 meses