Tracking Postgres Process and Current Executing SQL

In the database server, check the SQL processes:

ps -aux | grep postgres

This will list all postgres processes. Pick the PID of the problematic process, e.g. PID with high %CPU or %mem for example:

postgres 5700 23.1 0.8 1521904 274756 ? Ss 10:26 9:25 postgres: USER dbname 10.0.0.x(500) idle
postgres 5701 23.1 0.8 1521908 274776 ? Ss 10:26 9:25 postgres: USER dbname 10.0.0.x(501) idle
postgres 5706 0.0 0.8 1520960 273752 ? Ss 10:26 0:01 postgres: USER dbname 10.0.0.x(502) idle
postgres 5708 0.0 0.2 1520588 84756 ? Ss 10:26 0:00 postgres: USER dbname 10.0.0.x(503) idle
postgres 5709 23.1 0.8 1521924 274836 ? Ss 10:26 9:25 postgres: USER dbname 10.0.0.x(504) idle

The above shows PID 5700, 5701, 5709 has high %CPU usage

login to postgres:
psql -U USER -d dbname

Run:

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
FROM pg_stat_activity where pid in (5700, 5701, 5709);

datname | pid | state | query | age -------------------+-------+-------+-------------------------------------------------------------
dbname | 5700 | idle | select ** | 00:00:02.249868
dbname | 5701 | idle | select ** | 00:00:01.242311
dbname | 5709 | idle | select ** | 00:00:04.032112 (3 rows)

This will show you the problematic SQL Statements with high CPU usage.

To track the Source of the process, e.g. which tomcat or nginx process is executing the SQL, extract the IPaddress and port from the result of ps aux | postgres above:

10.0.0.x(500)
10.0.0.x(501)
10.0.0.x(504)

The above shows that the SQL is executed from IP: 10.0.0.x and port: 500, 501, 504

Login to the server with IP 10.0.0.x, run:
netstat -anp | grep -E "500|501|504"

This will show you the process that's executing the SQL:

server# netstat -anp | grep 500
tcp 0 105 ::ffff:10.0.0.x:500 ::ffff:10.0.0.x:1234 ESTABLISHED 15679/java

This shows Java process 15679 is executing the SQL.

Be the first to comment

Leave a Reply

Your email address will not be published.


*