[pgpool-hackers: 4013] Re: Proposal: add connection count to pcp_proc_info and show pool_process
KAWAMOTO Masaya
kawamoto at sraoss.co.jp
Mon Sep 6 13:17:52 JST 2021
Hi Ishii-san,
I was mistaken about the output of pcp_proc_info. My patch was behaving
as expected.
I updated the documents of pcp_proc_info, show pool_processes and
show pool_pools. Could you check them?
On Mon, 30 Aug 2021 17:38:47 +0900
KAWAMOTO Masaya <kawamoto �� sraoss.co.jp> wrote:
> 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
--
KAWAMOTO Masaya <kawamoto �� sraoss.co.jp>
SRA OSS, Inc. Japan
-------------- next part --------------
??????????????????????????????????????????...
????: pcp_proc_info_and_docs.diff
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20210906/cafb5645/attachment-0001.ksh>
More information about the pgpool-hackers
mailing list