Re: A few basic troubleshooting questions

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(at)myemma(dot)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