Re: [GENERAL] 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

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

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

---------------------------(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