Re: top for postgresql (ptop?)

Hi Mark,

I haven't yet looked at what you've done, but I'm an enthusiastic supporter
of this idea. We're looking to do something that will view running queries
and allow drill down into those executing at any given time, showing their
plans and some notion of what operators are being executed.

The idea of a ptop that shows running queries using a curses interface seems
like a great start.

Our needs for data warehousing workloads are going to be different from the
OLTP users - our queries hang around long enough to warrant a drill-down.

How far can you take the drill-down piece? Have you thought about how to
acquire the status in the executor yet? One strategy we've considered is to
use the same approach as "pstack" on Solaris - it takes a pid and dumps the
stack of a backend, which clearly shows which executor node is being worked
on currently. I think pstack uses dtrace underneath the hood...

- Luke

On 9/25/07 3:00 AM, "Mark Wong" <markwkm(at)gmail(dot)com> wrote:

> Hi everyone,
>
> I was playing with converting unixtop (the version of top used in
> FreeBSD) to only show PostgreSQL processes pulled from the
> pg_stat_activity table. I have a version that kind of works here:
>
> http://pgfoundry.org/frs/download.php/1468/ptop-3.6.1-pre6.tar.gz
>
> I've tried it on FreeBSD and Linux, not sure about other platforms
> though. So it looks a lot like top and can currently do a few simple
> things like display the current_query from pg_stat_activity for a
> given process, show the locks held by a process and on which tables,
> and show the query plan for the current query. It is a ways from
> polished (not really documented, etc.) but I wanted to see what people
> thought of a text/curses sort of monitoring tool like this. Maybe
> something to distribute with PostgreSQL? :)
>
> Forgive me if I didn't try out pgtop (the CPAN module.)
>
> Regards,
> Mark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster