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:[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 >
pgtrace
Description: pgtrace
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
