Thread: pgtop, display PostgreSQL processes in `top' style

Hi everybody,

I'm currently working on something like a `mytop' clone,
also written in Perl with DBI + DBD::Pg interface.

mytop is a command line utility that displays mysql
server status and clients connected modeled after
`top' unix utility. (http://mytop.sourceforge.net).

What I'd like to achieve is something like that, but
for PostgreSQL, of course.
Now the project is in the very early stages, but it
already does something useful with Pg backend versions 7.2+
with stats enabled. Obviously, it looks at pg_stat*
relations to extract some useful information.

There are some things that are nice in `mytop', and I
think it would be nice to have also in `pgtop', but I
don't know how to replicate them:

1) is it possible to know Pg backend uptime with
    SQL queries? Or must I look at postmaster.pid file?
    or even something else?

2) how can I know the ip addresses/hostnames of clients
    connecting to server?

3) Is there a way to know the number of queries performed
    against Pg backend (by each client)? Can I distinguish
    between selects / updates / inserts / copy / ... ?

If you want to take a look at it, point your
browser at http://search.cpan.org/dist/pgtop .

--
Cosimo

Cosimo Streppone <cosimo@streppone.it> writes:
> 1) is it possible to know Pg backend uptime with
>     SQL queries? Or must I look at postmaster.pid file?
>     or even something else?

I suppose you mean postmaster uptime, not the lifespan of your own
connection.  We don't track that at the moment, but IIRC there is
a patch in the queue to provide a function that returns this for 8.1.
For now you could perhaps look at "ps" output to see how long the
postmaster process has been running, but that's surely ugly and fraught
with pitfalls ...

Interesting thought here: should we track the postmaster process uptime,
or the time since the last database restart?  Not the same at all.
You could argue that from a reliability standpoint the latter is the
interesting number.

> 2) how can I know the ip addresses/hostnames of clients
>     connecting to server?

You can't (again, unless you want to parse "ps" output).  But I think
someone has submitted a patch to add such columns to the
pg_stat_activity view.  We need to figure out whether the visibility
of these columns needs to be restricted for security, but pending that
discussion it'll probably be in 8.1 in some form.

> 3) Is there a way to know the number of queries performed
>     against Pg backend (by each client)? Can I distinguish
>     between selects / updates / inserts / copy / ... ?

Nope, and nope.  We could perhaps teach the stats collector to count
querystrings it's received from each backend, which'd answer the first
one to a reasonable extent.  I'm unconvinced that we should expend the
overhead to be able to do the second.
        regards, tom lane

* Cosimo Streppone <cosimo@streppone.it> wrote:

Hi,

> I'm currently working on something like a `mytop' clone,
> also written in Perl with DBI + DBD::Pg interface.
> 
> mytop is a command line utility that displays mysql
> server status and clients connected modeled after
> `top' unix utility. (http://mytop.sourceforge.net).

Great thing. 

I'd like to invest some time in it, but I'd prefer coding 
it in java for several reasons.

Did you set up an mailing list for that project ?
(if not, I could offer to host it - its just some keystrokes 
away for me)


cu
-- 
---------------------------------------------------------------------Enrico Weigelt    ==   metux IT service phone:
+4936207 519931         www:       http://www.metux.de/ fax:       +49 36207 519932         email:
contact@metux.de
--------------------------------------------------------------------- Realtime Forex/Stock Exchange trading powered by
postgresSQL:))                                           http://www.fxignal.net/
 
---------------------------------------------------------------------

Hi, Cosimo,

Cosimo Streppone wrote:

> 1) is it possible to know Pg backend uptime with
>    SQL queries? Or must I look at postmaster.pid file?
>    or even something else?

In contrib, there's a function caled backend_pid() defined in
misc_utils.sql, it may be helpful for you.

markus

On May 2, 2005, at 6:09 PM, Markus Schaber wrote:
>
>> 1) is it possible to know Pg backend uptime with
>>    SQL queries? Or must I look at postmaster.pid file?
>>    or even something else?
>>
>
> In contrib, there's a function caled backend_pid() defined in
> misc_utils.sql, it may be helpful for you.
>
> markus
>

I wrote a small util called "pgtop" that does a top style listing on  
PG in terms of cpu. I also have one called pgiomonitor (that is on  
pgfoundry and mostly done) that does the same but shows you which  
tables are producing the most IO)

http://postgresql.jefftrout.com/pgtop.pl

it only runs on linux and it must run on the same box as pg itself.
You may want to look at it and see if you can get anything good out  
of it.

queries / second is tricky.  You could look at my pgspy utility but  
it is in C.  It can give you queries / second data
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Apr 30, 2005, at 15:10 , Cosimo Streppone wrote:

> 1) is it possible to know Pg backend uptime with
>    SQL queries? Or must I look at postmaster.pid file?
>    or even something else?

I think that the PID file is your best bet.

> 2) how can I know the ip addresses/hostnames of clients
>    connecting to server?

I believe that that information is in pg_stat_activity. See:
  http://www.postgresql.org/docs/current/static/monitoring.html

> 3) Is there a way to know the number of queries performed
>    against Pg backend (by each client)? Can I distinguish
>    between selects / updates / inserts / copy / ... ?

I think so, but you have to enable some postgresql.conf settings to  
get that information logged.
  http://www.postgresql.org/docs/8.0/static/runtime-config.html

Regards,

David