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