[Pgpool-hackers] Admin interface, statistics views, etc

[Guillaume Lelarge]

On Tue, 20 Jul 2010 08:07:25 +0900 (JST), Tatsuo Ishii
<ishii at sraoss.co.jp> wrote:
>> Le 19/07/2010 04:53, Tatsuo Ishii a écrit :
>> >> I've been thinking about the new release. I've always wanted to add
>> >> something in pgAdmin that could discover the "pgpool" server. There
>> >> are
>> >> two ways to do it: with a "SHOW pool_status", and with the
>> >> inet_server_addr() stored function. There should be a third in 9.1
>> >> with
>> >> the pg_stat_get_backend_server_port() stored function.
>> >>
>> >> I would like to see two kinds of informations: configuration (which
we
>> >> already have with the "SHOW pool_status" statement), and statistics
>> >> (which, AFAICT, aren't available). It would be great to have
something
>> >> like pg_stat_activity but at the pgpool level. I mean, the
>> >> pg_stat_activity stat view only display the IP address of the pgpool
>> >> server. So, it would be great to know where the real client is.
>> >> Something like:
>> >>
>> >> SELECT * FROM pool_stat_activity;
>> >>
>> >> -[ RECORD 1 ]----+--------------------------------
>> >> pool_pid         |
>> >> pool_id          |
>> >> pool_start       |
>> >> pgsql_hostname   | 127.0.0.1
>> >> pgsql_port       | 5432
>> >> pgsql_pid        |
>> >> working          | f
>> >>
>> >> pool_pid would be the PID of the pgPool child connected to the real
>> >> client. pool_id would by the pool number (connectionid could be a
>> >> better
>> >> name). pool_start would be the timestamp wrt to the start of pgPool
>> >> child process. pgsql_hostname, pgsql_port would identify the
>> >> PostgreSQL
>> >> server it is connected to. pgsql_pid would be the PID of the
postgres
>> >> backend process (this information would help to cross the
information
>> >> with pg_stat_activity). working would be a boolean value telling
which
>> >> pool_id is really working at the moment.
>> >>
>> >> There could be other interesting informations as the list of servers
>> >> (pool_stat_servers) which could tell us how many servers are
>> >> available,
>> >> which one are really available, how many queries were executed on
>> >> each, etc.
>> >>
>> >> Seems it would be a great project to add to the 3.0 release, don't
you
>> >> think?
>> >>
>> >> Other comments?
>> > 
>> > It seems like infomation you want is almost there if you use
>> > pcp_proc_count and pcp_proc_info:
>> > $ pcp_proc_count 10 localhost 9898 postgres hogehoge
>> > 3815
>> > 
>> > $ pcp_proc_info 10 localhost 9898 postgres hogehoge 3815
>> > postgres_db postgres 1150769932 1150767351 3 0 1
>> > 
>> > The result is in the following order:
>> > 1. connected database name
>> > 2. connected username
>> > 3. process start-up timestamp
>> > 4. connection created timestamp
>> > 5. protocol major version
>> > 6. protocol minor version
>> > 7. connection-reuse counter
>> > 
>> > So only missing data is pgsql_pid.  (pgsql_hostname and pgsql_port
can
>> > be obtained from pool_status)
>> > 
>> > I'm looking forward to add pgsql_pid to pcp_proc_info. What do you
>> > think?
>> 
>> Could be interesting, but not in my case. I want to make this kind of
>> information available in pgAdmin. And I don't see how I can have that
>> with the pcp_* commands. I can grab the code of the pcp_* commands, but
>> I will need to make another connection to something quite different
from
>> a PostgreSQL database. And nothing garanties that it will work the same
>> in other releases. Does the language between pgpool and pcp change
>> between release? I suppose so, but probably not to make them
>> incompatible? If it feels like it would be compatible, that's something
>> I can try, yes.
> 
> I would add pgsql_pid to pcp_proc_info anyway but in the mean time I
> understand your concern about pcp API (it's a shame that libppcp API
> is not documented anywhere BTW).

OK, that would be good to have (the psql_pid column).

> I admit that SELECT or SHOW is easier to use for appIications such as
> pgAdmin. So I do not object to add new SELECT or SHOW command you
> proposed. Would you like to create patches for this?

Yeah, I will work on it right away. My patches for pgAdmin can wait, but
the one for pgPool can't if you want to get a release out at the end of the
month. I can also work on the documentation for the libppcp API. But first,
the SHOW statement.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com