A few basic troubleshooting questions

[Kevin Kempter <kevin(at)kevinkempterllc(dot)com>]

Hi List;

I have a few basic troubleshooting questions...

1) If I have autovacuum turned on, how do I know which table is being vacuumed when in pg_stat_activity I only see VACUUM?

I've been using this query but it doesn't always work... is there a better way?

CREATE Temp table tmp_p as

SELECT

procpid from pg_stat_activity where current_query = 'VACUUM'

;

SELECT

relname as current_vacuum_activity

from pg_class where oid in

( select relation from pg_locks where pid = any (select procpid from tmp_p) )

;

2) if I see a 'ROLLBACK' in pg_stat_activity, how can I determine what query/update/etc is being rolled back?

3) How do I know for sure what processes are are waiting on a specific lock ? for example I have a process that has an ungranted lock on table X. Is there an easy way via pg_locks to determine which processes are waiting on the ungranted lock on table X?

4) How do I determine in general if the db has a memory bottleneck vs CPU bottleneck vs I/O bottleneck? I know about pg_statio, just not sure how to guage where the db is the most constrained.

Thanks in advance

/Kevin