[pgpool-hackers: 3968] Re: Proposal: add connection count to pcp_proc_info and show pool_process
KAWAMOTO Masaya
kawamoto at sraoss.co.jp
Wed Jul 14 17:23:06 JST 2021
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
★Process counter : 1
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 */
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.
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
- 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.
- 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.
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
More information about the pgpool-hackers
mailing list