[GENERAL] A few basic troubleshooting questions

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?

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

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 Received on Tue Sep 25 10:34:49 2007