Обсуждение: 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

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

Turn on logging of all queries, sample for a few hours or one day.  Then
  run Practical Query Analyzer (PQA on pgfoundry.org) over it to get
aggregate query information.

Chris

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
>