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.
From: Jeff Frost [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 20, 2005 7:45 AM
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
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 http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 6: Have you searched our list archives?