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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
