[pgpool-hackers: 4009] Re: Proposal: add connection count to pcp_proc_info and show pool_process
KAWAMOTO Masaya
kawamoto at sraoss.co.jp
Mon Aug 30 17:38:47 JST 2021
Hi, Ishii-san.
Thanks to your advice I was able to implement Process Status and Client idle
duration easily. I made the patch.
But I still have a problem...
I modified pcp_proc_info, show pool_processes and show pool_pools.
The execution results of each command are as follows. You can see that
some information which are Database, Username, Backend connection time,
Client connection time and so on have the same values in the same
process even though they are in different pools.
Please help me solve my problem.
■ pcp_proc_info
-bash-4.2$ /usr/local/bin/pcp_proc_info -p 11001 -av
Password:
Database :
Username :
Start time : 2021-08-30 08:07:39 (remaining: 1:17)
Client connection count : 0
Major : 0
Minor : 0
Backend connection time :
Client connection time :
Client idle duration : 0
Client disconnection time :
Pool Counter : 0
Backend PID : 0
Connected : 0
PID : 10389
Backend ID : 0
Status : Wait for connection
Database :
Username :
Start time : 2021-08-30 08:07:39 (remaining: 1:17)
Client connection count : 0
Major : 0
Minor : 0
Backend connection time :
Client connection time :
Client idle duration : 0
Client disconnection time :
Pool Counter : 0
Backend PID : 0
Connected : 0
PID : 10389
Backend ID : 1
Status : Wait for connection
Database : postgres
Username : postgres
Start time : 2021-08-30 08:07:39 (remaining: 1:17)
Client connection count : 0
Major : 3
Minor : 0
Backend connection time : 2021-08-30 08:07:59
Client connection time : 2021-08-30 08:07:59
Client idle duration : 0
Client disconnection time :
Pool Counter : 1
Backend PID : 10438
Connected : 1
PID : 10390
Backend ID : 0
Status : Idle
Database : postgres
Username : postgres
Start time : 2021-08-30 08:07:39 (remaining: 1:17)
Client connection count : 0
Major : 3
Minor : 0
Backend connection time : 2021-08-30 08:07:59
Client connection time : 2021-08-30 08:07:59
Client idle duration : 0
Client disconnection time :
Pool Counter : 1
Backend PID : 10439
Connected : 1
PID : 10390
Backend ID : 1
Status : Idle
■ show pool_processes
postgres=# \x
Expanded display is on.
postgres=# show pool_processes;
-[ RECORD 1 ]-----------+--------------------------------------
pool_pid | 10389
start_time | 2021-08-30 08:07:39 (remaining: 0:41)
client_connection_count | 0
database |
username |
backend_connection_time |
pool_counter |
status | Wait for connection
-[ RECORD 2 ]-----------+--------------------------------------
pool_pid | 10390
start_time | 2021-08-30 08:07:39 (remaining: 0:41)
client_connection_count | 0
database | postgres
username | postgres
backend_connection_time | 2021-08-30 08:07:59
pool_counter | 1
status | Execute command
■ show pool_pools
postgres=# show pool_pools;
-[ RECORD 1 ]-------------+--------------------------------------
pool_pid | 10389
start_time | 2021-08-30 08:07:39 (remaining: 0:49)
client_connection_count | 0
pool_id | 0
backend_id | 0
database |
username |
backend_connection_time |
client_connection_time |
client_disconnection_time |
client_idle_duration | 0
majorversion | 0
minorversion | 0
pool_counter | 0
pool_backendpid | 0
pool_connected | 0
status | Wait for connection
-[ RECORD 2 ]-------------+--------------------------------------
pool_pid | 10389
start_time | 2021-08-30 08:07:39 (remaining: 0:49)
client_connection_count | 0
pool_id | 0
backend_id | 1
database |
username |
backend_connection_time |
client_connection_time |
client_disconnection_time |
client_idle_duration | 0
majorversion | 0
minorversion | 0
pool_counter | 0
pool_backendpid | 0
pool_connected | 0
status | Wait for connection
-[ RECORD 3 ]-------------+--------------------------------------
pool_pid | 10390
start_time | 2021-08-30 08:07:39 (remaining: 0:49)
client_connection_count | 0
pool_id | 0
backend_id | 0
database | postgres
username | postgres
backend_connection_time | 2021-08-30 08:07:59
client_connection_time | 2021-08-30 08:07:59
client_disconnection_time |
client_idle_duration | 0
majorversion | 3
minorversion | 0
pool_counter | 1
pool_backendpid | 10438
pool_connected | 1
status | Execute command
-[ RECORD 4 ]-------------+--------------------------------------
pool_pid | 10390
start_time | 2021-08-30 08:07:39 (remaining: 0:49)
client_connection_count | 0
pool_id | 0
backend_id | 1
database | postgres
username | postgres
backend_connection_time | 2021-08-30 08:07:59
client_connection_time | 2021-08-30 08:07:59
client_disconnection_time |
client_idle_duration | 0
majorversion | 3
minorversion | 0
pool_counter | 1
pool_backendpid | 10439
pool_connected | 1
status | Execute command
On Thu, 15 Jul 2021 11:24:05 +0900 (JST)
Tatsuo Ishii <ishii �� sraoss.co.jp> wrote:
> 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 �� 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 �� pgpool.net
> >> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
> >
> >
> > --
> > KAWAMOTO Masaya <kawamoto �� sraoss.co.jp>
> > SRA OSS, Inc. Japan
--
KAWAMOTO Masaya <kawamoto �� sraoss.co.jp>
SRA OSS, Inc. Japan
-------------- next part --------------
??????????????????????????????????????????...
????: pcp_proc_info.diff
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20210830/115bf606/attachment-0001.ksh>
More information about the pgpool-hackers
mailing list