[pgpool-hackers: 3941] Re: Proposal: add connection count to pcp_proc_info and show pool_process

[Tatsuo Ishii]

>> While writing a blog:
>> https://pgsqlpgpool.blogspot.com/2020/12/timeouts-in-pgpool-ii-connections.html
>> 
>> I noticed that there's no metrics to know how many times connections
>> from client have been made to a Pgpool-II process in pcp_proc_info and
>> show pool_process. So I would like to propose to add that metrics to
>> the commands. This would be useful to know how many times clients
>> connect to Pgpool-II before they hit child_max_connections.

I add possible use cases for those metrics, especially related to those
Pgpool lifetime parameters.
-------------------------------------------------------------------------------------------------
child_life_time = 5min
                                   # Pool exits after being idle for this many seconds
child_max_connections = 0
                                   # Pool exits after receiving that many connections
                                   # 0 means no exit
connection_life_time = 0
                                   # Connection to backend closes after being idle for this many seconds
                                   # 0 means no close
client_idle_limit = 0

                                   # Client is disconnected after being idle for that many seconds
                                   # (even inside an explicit transactions!)
                                   # 0 means no disconnection
-------------------------------------------------------------------------------------------------

Note that you can already check "Start time" of pcp_proc_info out to
check if pgpool is reaching child_life_time. Maybe it would be
convenient for users to know the reamining time of child_life_time
something like:

Start time   : 2018-07-09 16:43:53 (remaining: 2:30)

I also notice that I haven't proposed metrics for
client_idle_limit. Maybe something like this?

- elapsed time (in seconds) since client is in idle state.

  proposed name: client_idle_duration

Another metrics that maybe useful is, pgpool's local port number. If
PostgreSQL is configured with log_connections, this helps to identify 

> After thinking more I would like to propose to add following metrix.
> 
> - how many times clients connected to this pgpool process.
>
>   proposed name: client_connection_count

Use case: to know how pgpool is close to child_max_connections.

> - last time when frontend connected to this pgpool process.
>   (or NULL is not connected yet)
> 
>   proposed name: client_connection_time

Use case: to know when the client connected to pgpool. If the user
uses his own connection pooling, this helps to know when the
connection was actually made from pgpool's point of view.

> - last time when frontend disconnected to this pgpool process.
>   (or NULL is not disconnected yet)
>
>   proposed name: client_disconnection_time

Use case: to know the client diconnected to pgpool. The use case is
same as above.

> - last time when pgpool connected to backend for this connection pool
>   (or NULL is not connected yet)
> 
>   proposed name: backend_connection_time

Oops. Actually we already have this:

Creation time: 2018-07-09 16:44:08

Usecase: knowing how pgpool is close to connection_life_time.

> - last time when pgpool disconnected to backend for this connection pool
>   (or NULL is not disconnected yet)

Usecase: to know when pgpool disconnected to PostgreSQL.

>   proposed name: backend_disconnection_time
> 
> - process status. possible values:
> 
>   WAIT_FOR_CONNECTION: waiting for connection from client
>   IDLE: idle (not in transaction)
>   IDLE_IN_TRANSACTION: idle (in transaction)
>   SELECT: and other command executing
> 
>   proposed name: status

Use case: we can know this from the ps output today but this will more
convenient for users.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp