Stats are updated only after transaction ends. In case you have a really
long transaction you need something else. 

To help myself I made a little Perl utility to parse strace output. It
recognizes read/write calls, extracts file handle, finds the file name
using information in /proc filesystem, then uses oid2name utility to
translate file name to PostgreSQL relation name. See attachment.

It works well enough for me, but I didn't take time to polish it.
Basically it works with Linux /proc filesystem layout, expects
PostgreSQL data directory to be /home/postgres/data and oid2name in
/usr/lib/postgresql/bin. Usage is pgtrace <pid>.


> -----Original Message-----
> From: Jeff Frost [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, April 20, 2005 7:45 AM
> To:
> Subject: How to tell what your postgresql server is doing
> Is there a way to look at the stats tables and tell what is 
> jamming up your 
> postgres server the most?  Other than seeing long running 
> queries and watch 
> top, atop, iostat, vmstat in separate xterms...I'm wondering 
> if postgres keeps 
> some stats on what it spends the most time doing or if 
> there's a way to 
> extract that sort of info from other metrics it keeps in the 
> stats table?
> Maybe a script which polls the stats table and correlates the 
> info with stats 
> about the system in /proc?
> -- 
> Jeff Frost, Owner     <[EMAIL PROTECTED]>
> Frost Consulting, LLC
> Phone: 650-780-7908   FAX: 650-649-1954

Attachment: pgtrace
Description: pgtrace

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to