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>.
Tambet
> -----Original Message-----
> From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
> Sent: Wednesday, April 20, 2005 7:45 AM
> To: pgsql-performance@postgresql.org
> 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 <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>