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

[Tatsuo Ishii]

> 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).

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?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp