Thread: 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?
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
On Sep 25, 2007, at 9:29 AM, Kevin Kempter wrote: > 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? For both 1) and 2), pg_stat_activity has more columns than just procpid. Here's a query I use to good effect for monitoring active queries: SELECT procpid, to_char((now() - query_start), 'DD HH:MI:SS') as query_time, client_addr, current_query FROM pg_stat_activity ORDER BY now() - query_start DESC > > 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? Yes, read the documentation on pg_locks: http://www.postgresql.org/ docs/8.2/interactive/view-pg-locks.html. Note that there pid corresponds to procpid in pg_stat_activity. > > 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. You will need OS tools to handle those metrics. Look into vmstat and ipcs for memory, iostat for I/O, and top for cpu. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com