[pgpool-hackers: 3972] Re: Proposal: add connection count to pcp_proc_info and show pool_process
Tatsuo Ishii
ishii at sraoss.co.jp
Thu Jul 15 11:24:05 JST 2021
Hi Kawamoto-san,
Thnak you for the proposal.
> Hi,
>
> My ideas are summarized below.
> Do you think this?
>
> ★ is new.
>
> ■ pcp_proc_info
> Database : postgres
> Username : postgres
> Process Start time : 2021-07-07 07:35:41
I recommend to add "remaining time" which represents how long time
remains before child_life_time expires as I proposed elsewhere.
Start time : 2018-07-09 16:43:53 (remaining: 2:30)
Note that " (remaining: 2:30)" should not be displayed if
child_life_time = 0.
> ★Process counter : 1
"Process counter" sounds a little bit ambiguous (what "counter" for?)
I recommend "Client connection count" instead, which is derived
from my proposal: "client_connection_count".
> Major : 3
> Minor : 0
> ★Backend connection time : 2021-07-07 07:36:59
> ★Backend disconnection time :
> ★Client connection time : 2021-07-07 07:36:59
> ★Client disconnection time :
> ★Client idle duration : 00:00:10
> Pool Counter : 1
> Backend PID : 28457
> Connected : 1
> PID : 28324
> Backend ID : 0
> ★status : IDLE
>
> ■ Structures
> - POOL_REPORT_POOLS
> typedef struct
> {
> char pool_pid[POOLCONFIG_MAXCOUNTLEN + 1];
> char process_start_time[POOLCONFIG_MAXDATELEN + 1];
> ★ char process_counter[POOLCONFIG_MAXCOUNTLEN + 1];
> char pool_id[POOLCONFIG_MAXCOUNTLEN + 1];
> char backend_id[POOLCONFIG_MAXCOUNTLEN + 1];
> char database[POOLCONFIG_MAXIDENTLEN + 1];
> char username[POOLCONFIG_MAXIDENTLEN + 1];
> ★ char backend_connection_time[POOLCONFIG_MAXDATELEN + 1];
> ★ char backend_disconnection_time[POOLCONFIG_MAXDATELEN + 1];
> ★ char client_connection_time[POOLCONFIG_MAXDATELEN + 1];
> ★ char client_disconnection_time[POOLCONFIG_MAXDATELEN + 1];
> ★ char client_idle_duration[POOLCONFIG_MAXDATELEN + 1];
> char pool_majorversion[POOLCONFIG_MAXCOUNTLEN + 1];
> char pool_minorversion[POOLCONFIG_MAXCOUNTLEN + 1];
> char pool_counter[POOLCONFIG_MAXCOUNTLEN + 1];
> char pool_backendpid[POOLCONFIG_MAXCOUNTLEN + 1];
> char pool_connected[POOLCONFIG_MAXCOUNTLEN + 1];
> ★ char status[POOLCONFIG_MAXSTATUSLEN + 1];
> } POOL_REPORT_POOLS;
>
> - ProcessInfo on shmem
> typedef struct
> {
> pid_t pid; /* OS's process id */
> time_t start_time; /* fork() time */
> ConnectionInfo *connection_info; /* head of the connection info for
> * this process */
> ★ int process_counter; /* used counter */
I recommend to change to:
int client_connection_count; /* how many times clients connected to this process */
> char need_to_restart; /* If non 0, exit this child process as
> * soon as current session ends. Typical
> * case this flag being set is failback a
> * node in streaming replication mode. */
> } ProcessInfo;
>
> - ConnectionInfo on shmem
> typedef struct
> {
> int backend_id; /* backend id */
> char database[SM_DATABASE]; /* Database name */
> char user[SM_USER]; /* User name */
> int major; /* protocol major version */
> int minor; /* protocol minor version */
> int pid; /* backend process id */
> int key; /* cancel key */
> int counter; /* used counter */
> time_t create_time; /* connection creation time */
> ★ time_t backend_disconnection_time; /* backend disconnection time */
> ★ time_t client_connection_time; /* client connection time */
> ★ time_t client_disconnection_time; /* client last disconnection time */
> int load_balancing_node; /* load balancing node */
> char connected; /* True if frontend connected. Please note
> * that we use "char" instead of "bool". Since
> * 3.1, we need to export this structure so
> * that PostgreSQL C functions can use.
> * Problem is, PostgreSQL defines bool itself,
> * and if we use bool, the size of the
> * structure might be out of control of
> * pgpool-II. So we use "char" here. */
> volatile char swallow_termination;
> /*
> * Flag to mark that if the connection will be terminated by the backend.
> * it should not be treated as a backend node failure. This flag is used
> * to handle pg_terminate_backend()
> */
> ★ PoolStatus status;
> ★ time_t status_trans_idle;
> } ConnectionInfo;
>
> typedef enum
> {
> IN_PROGRESS,
> IDLE,
> IDLE_IN_TRANS,
> WAIT_FOR_CONNECTION
> } PoolStatus;
>
> ■ Timing to store parameteres
> - Process counter
> In do_child() (child.c).
> Currently, it is using local variable connections_count for max_init_children.
You mean child_max_connections rather than max_init_children?
> When Pgpool changes connection_count, also changes ProcessInfo.process_counter.
>
> - Backend disconnection time
> I think there are two timings that pgpool disconnects with the backend.
> - child_max_connections
> - child_life_time
After thinking more I realized Backend disconnection time is useless
because once backend disconnects to pgpool, the connection pool object
disappears and the backend disconnection time will disappear
altogether. I withdraw the proposal to add Backend disconnection time.
> - Client connection time
> In get_connection() function (pool_connection_pool.c)
> When Pgpool outputs log_connections, also stores time(NULL) in
> ConnectionInfo.client_connection_time.
>
> - Client disconnection time
> In do_child() function (do_child.c)
> When Pgpool outputs log_disconnections, also stores time(NULL) in
> ConnectionInfo.client_disconnection_time
>
> - Status
> My idea is to use the change POOL_SESSION_CONTEXT.in_progress and
> POOL_SESSION_CONTEXT.is_in_transaction as the trigger for ConnectionInfo.status
> transition IDLE/IDLE_IN_TRANS to IN_PROGRESS, IDLE to IDLE_INTRANS and vice versa.
>
> The start status is WAIT_FOR_CONNECTION.
> In new_connection() function, when Pgpool stores in ConnectionInfo.create_time,
> ConnectionInfo.status is set WAIT_FOR_CONNECTION.
>
> The next status is IDLE.
> In get_connection() function, when connected by client, WAIT_FOR_CONNECTION transition to IDLE.
What about capturing the timing of calling set_ps_display? It would be much easier.
> - Client idle duration
> Pgpool stores "time(NULL)" in ConnectionInfo.status_trans_idle when ConnectionInfo.status
> chenges IDLE or IDLE_IN_TRANS. Then, when creating POOL_REPORT_POOLS in getpool()
> function, if ConnectionInfo.status is IDLE or IDLE_IN_TRANS, Pgpool stores the
> defference from the current time in client_idle_duration.
I think you can inject a code for this here (src/protocol/pool_process_query.c:4759)
/* select timeout */
if (fds == 0)
{
if (*InRecovery == RECOVERY_INIT && pool_config->client_idle_limit > 0)
{
idle_count++;
if (idle_count > pool_config->client_idle_limit)
{
ereport(FRONTEND_ERROR,
(pool_error_code("57000"),
errmsg("unable to read data"),
errdetail("child connection forced to terminate due to client_idle_limit:%d is reached",
pool_config->client_idle_limit)));
}
}
You can use idle_count for your purpose.
> Best regard.
>
> On Wed, 23 Jun 2021 10:21:49 +0900 (JST)
> Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> >> 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
>> _______________________________________________
>> pgpool-hackers mailing list
>> pgpool-hackers at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>
>
> --
> KAWAMOTO Masaya <kawamoto at sraoss.co.jp>
> SRA OSS, Inc. Japan
More information about the pgpool-hackers
mailing list