[pgpool-hackers: 3712] Re: discuss of pgpool enhancement

[Muhammad Usama]

Hi Jianshen,

I think it is a very good idea to have on-demand spawning of the
child processes and it will enable us to effectively configure the
Pgpool-II in environments where we get instantaneous connection spikes.
Currently we have to configure the Pgpool's num_init_children to a
value of "maximum number of connections expected" and most of the
time, 50 to 60 percent of child processes keep sitting idle and
consuming system resources.

Similarly, I also agree with having an option of the global connection
pool and I believe that will enable us to have less number of opened backend
connections and also in the future we can build a different type of pooling
options on that like transaction pooling and similar features.

IMHO we should take both of these features as a separate project.
We can start with on-demand child spawning feature and once we have
that in Pgpool-II we build the global connection pool option on top of that.

So if you are interested in working on that, you can send the proposal and
include the details like how are you planning to manage the
child-process-pool
and when will the Pgpool-II spawn and destroy the child processes?
My idea would be to make child-process-pool as much configurable as
possible.
Some of the configuration parameters I can think of for the purpose are.

CPP_batch_size                                     /* number of child
process we will spawn when required */

CPP_downscale_trigger                          /* number of idle child
process in Pgpool-II to start
                                                                * killing
the idle child process */

CPP_upscale_trigger                             /* number of idle child
process in Pgpool-II to start
                                                                * spawning
new child process */

CPP here stands for CHILD-PROCESS-POOL and these are just my thoughts and
you may want to choose
different names and/or different types of configurations altogether.

Looking forward to getting an actual proposal.

Thanks
Best regards
Muhammad Usama



On Mon, Jul 13, 2020 at 2:56 PM 周建身 <zhoujianshen at highgo.com> wrote:

> Hello Usama and Hackers,
>
>     I have tested the pgpool connection pool.And I think there are some
> parts need to be enhanced.
>
>     When you set the parameter num_init_children = 32.only 32 child
> processes will be forked and waiting for the connection from client.Each
> child process can only receive one client connection,therefore, only 32
> clients can connect to pgpool at the same time.The extra
> connections,before connection, can only wait for the previous connection to
> be disconnected.So,can we change the waiting connection structure of
> pgpool. When pgpool starts ,we can fork ten child processes to wait for the
> client to connect.When the child process receives the connection request,
> it creates a new child process to maintain the session connection.
>
>     there is also another one which should be enhance is the connection
> pool.Now, for each connection, the child process can only receive one
> client connection. Therefore, the connection pool in the child process does
> not play a global reuse effect.And each connection will re-initialize the
> connection pool. Therefore we should implement a global connection pool to
> achieve the effect of back end reuse.However ,we should confirm how many
> connections the global connection pool should maintain.And also we should
> confirm that if the connection pool is full,how should we respond to the
> arrival of new connections.I can come up with two kind of solutions.
>
>     The first one is waiting until the connection in the connection pool
> disconnected.and then receive the new connection.The second one,We should
> check the number of connection and  the last access time of the connection
> in connection pool.And we replace the connection which has the oldest
> access time in the connection pool to the new connection.or we periodic
> detection the access time of each connection,and throw away the connections
> whose access time exceed a certain value.then we can use the extra space in
> connection pool.
>
>     In my opinion，these two aspects need to be enhanced.How about your
> opinion.And what do you think we need to do to enhance these two
> aspects.Any suggestions and comments are welcome.
>
>
>
> Thanks
> Best regards
> Zhou Jianshen
> zhoujianshen at highgo.com
>
>
>
>
>
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200714/e4dee50a/attachment.html>