<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Jul 14, 2020 at 4:40 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> Hi Jianshen,<br>
> <br>
> I think it is a very good idea to have on-demand spawning of the<br>
> child processes and it will enable us to effectively configure the<br>
> Pgpool-II in environments where we get instantaneous connection spikes.<br>
> Currently we have to configure the Pgpool's num_init_children to a<br>
> value of "maximum number of connections expected" and most of the<br>
> time, 50 to 60 percent of child processes keep sitting idle and<br>
> consuming system resources.<br>
> <br>
> Similarly, I also agree with having an option of the global connection<br>
> pool and I believe that will enable us to have less number of opened backend<br>
> connections and also in the future we can build a different type of pooling<br>
> options on that like transaction pooling and similar features.<br>
<br>
Not sure abut this. Suppose we have num_init_children = 4 and max_pool<br>
= 1. We have 4 users u1, u2, u3 and u4. For simplicity, all those<br>
users connect to the same database.<br>
<br>
Current Pgpool-II:<br>
1. u1 connects to pgpool child p1 and creates connection pool p_u1.<br>
2. u2 connects to pgpool child p2 and creates connection pool p_u2.<br>
3. u3 connects to pgpool child p3 and creates connection pool p_u3.<br>
4. u4 connects to pgpool child p4 and creates connection pool p_u4.<br>
<br>
Pgpool-II with global connection pooling.<br>
1. u1 connects to pgpool child p1 and creates connection pool p_u1.<br>
2. u2 connects to pgpool child p2 and creates connection pool p_u2.<br>
3. u3 connects to pgpool child p3 and creates connection pool p_u3.<br>
4. u4 connects to pgpool child p4 and creates connection pool p_u4.<br>
<br>
So there's no difference with/without global connection pooling in the<br>
end.<br>
<br>
The case global connection when pooling wins would be, number of kind<br>
of users and concurrent connections are much smaller than<br>
num_init_children. For example, if there's only one user u1 and<br>
there's only one concurrent connections, we will have:<br>
<br>
Current Pgpool-II:<br>
1. u1 connects to pgpool child p1 and creates connection pool p_u1.<br>
2. u1 connects to pgpool child p2 and creates connection pool p_u2.<br>
3. u1 connects to pgpool child p3 and creates connection pool p_u3.<br>
4. u1 connects to pgpool child p4 and creates connection pool p_u4.<br>
<br>
Pgpool-II with global connection pooling.<br>
1. u1 connects to pgpool child p1 and creates connection pool p_u1.<br>
2. u1 connects to pgpool child p2 and reuses connection pool p_u1.<br>
3. u1 connects to pgpool child p3 and reuses connection pool p_u1.<br>
4. u1 connects to pgpool child p4 and reuses connection pool p_u1.<br>
<br>
Thus global connection pool wins having only 1 connection pool if<br>
number of kind of users and concurrent connections are much smaller<br>
than num_init_children.<br>
<br>
But question is, if we have only 1 concurrent session,<br>
num_init_children = 1 would be enough in the first place. In this case<br>
we will have similar result with current Pgpool-II.<br>
<br>
1. u1 connects to pgpool child p1 and creates connection pool p_u1.<br>
2. u1 connects to pgpool child p1 and reuses connection pool p_u1.<br>
3. u1 connects to pgpool child p1 and reuses connection pool p_u1.<br>
4. u1 connects to pgpool child p1 and reuses connection pool p_u1.<br>
<br>
So there's no point to have global connection pool here.<br></blockquote><div><br></div><div>Yes, I agree with the current architecture there is no real advantage of having a global</div><div>connection pool. </div><div><br></div><div>What I meant to say was: I think if we go with the on-demand child process spawning feature</div><div>then the global connection pool would have some clear advantages.</div><div><br></div><div>Since with on-demand child spawning feature implemented, Pgpool-II eventually will be spawning</div><div>and killing the child processes continuously (as per the connection load on the system).</div><div>And in that case, having a global connection cache would be helpful. because then we can</div><div>ensure that the backend connection cache keeps intact even with the child processes spawning</div><div>and getting killed continuously.</div><div>Secondly, I was thinking that having a local connection cache with on-demand child spawn feature</div><div>would make the child scale down logic much more complex and we would need to come up</div><div>with some algorithm that takes into account the child's connection cache state for selecting</div><div>the victim child processes to be killed.</div><div><br></div><div>But these are just the thoughts I had on top of my head</div><div>when I read the email and require more grooming. </div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
> IMHO we should take both of these features as a separate project.<br>
> We can start with on-demand child spawning feature and once we have<br>
> that in Pgpool-II we build the global connection pool option on top of that.<br>
> <br>
> So if you are interested in working on that, you can send the proposal and<br>
> include the details like how are you planning to manage the<br>
> child-process-pool<br>
> and when will the Pgpool-II spawn and destroy the child processes?<br>
> My idea would be to make child-process-pool as much configurable as<br>
> possible.<br>
> Some of the configuration parameters I can think of for the purpose are.<br>
> <br>
> CPP_batch_size /* number of child<br>
> process we will spawn when required */<br>
> <br>
> CPP_downscale_trigger /* number of idle child<br>
> process in Pgpool-II to start<br>
> * killing<br>
> the idle child process */<br>
> <br>
> CPP_upscale_trigger /* number of idle child<br>
> process in Pgpool-II to start<br>
> * spawning<br>
> new child process */<br>
> <br>
> CPP here stands for CHILD-PROCESS-POOL and these are just my thoughts and<br>
> you may want to choose<br>
> different names and/or different types of configurations altogether.<br>
<br>
Apache already has similar parameters:<br>
<br>
-------------------------------------------------------------------------<br>
# prefork MPM<br>
# StartServers: number of server processes to start<br>
# MinSpareServers: minimum number of server processes which are kept spare<br>
# MaxSpareServers: maximum number of server processes which are kept spare<br>
# MaxRequestWorkers: maximum number of server processes allowed to start<br>
<br>
StartServers 5<br>
MinSpareServers 5<br>
MaxSpareServers 10<br>
MaxRequestWorkers 150<br>
-------------------------------------------------------------------------<br>
<br>
I think our num_init_children looks similar to StartServers. So all we<br>
have to have are MinSpareServers, MaxSpareServers, and<br>
MaxRequestWorkers. (Probably we should rename them to more appropreate<br>
ones).<br>
<br></blockquote><div><br></div><div>Agreed. These are far better options then what I suggested. <a class="gmail_plusreply" id="plusReplyChip-2" href="mailto:zhoujianshen@highgo.com" tabindex="-1">@周建身</a> I think studying</div><div>the behavior of these Apache configurations can provide a good idea for implementing</div><div>the on-demand child spawn behavior for Pgpool-II</div><div><br></div><div>Thanks</div><div>Best Regards</div><div>Muhammad Usama</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
> Looking forward to getting an actual proposal.<br>
> <br>
> Thanks<br>
> Best regards<br>
> Muhammad Usama<br>
> <br>
> <br>
> <br>
> On Mon, Jul 13, 2020 at 2:56 PM 周建身 <<a href="mailto:zhoujianshen@highgo.com" target="_blank">zhoujianshen@highgo.com</a>> wrote:<br>
> <br>
>> Hello Usama and Hackers,<br>
>><br>
>> I have tested the pgpool connection pool.And I think there are some<br>
>> parts need to be enhanced.<br>
>><br>
>> When you set the parameter num_init_children = 32.only 32 child<br>
>> processes will be forked and waiting for the connection from client.Each<br>
>> child process can only receive one client connection,therefore, only 32<br>
>> clients can connect to pgpool at the same time.The extra<br>
>> connections,before connection, can only wait for the previous connection to<br>
>> be disconnected.So,can we change the waiting connection structure of<br>
>> pgpool. When pgpool starts ,we can fork ten child processes to wait for the<br>
>> client to connect.When the child process receives the connection request,<br>
>> it creates a new child process to maintain the session connection.<br>
>><br>
>> there is also another one which should be enhance is the connection<br>
>> pool.Now, for each connection, the child process can only receive one<br>
>> client connection. Therefore, the connection pool in the child process does<br>
>> not play a global reuse effect.And each connection will re-initialize the<br>
>> connection pool. Therefore we should implement a global connection pool to<br>
>> achieve the effect of back end reuse.However ,we should confirm how many<br>
>> connections the global connection pool should maintain.And also we should<br>
>> confirm that if the connection pool is full,how should we respond to the<br>
>> arrival of new connections.I can come up with two kind of solutions.<br>
>><br>
>> The first one is waiting until the connection in the connection pool<br>
>> disconnected.and then receive the new connection.The second one,We should<br>
>> check the number of connection and the last access time of the connection<br>
>> in connection pool.And we replace the connection which has the oldest<br>
>> access time in the connection pool to the new connection.or we periodic<br>
>> detection the access time of each connection,and throw away the connections<br>
>> whose access time exceed a certain value.then we can use the extra space in<br>
>> connection pool.<br>
>><br>
>> In my opinion，these two aspects need to be enhanced.How about your<br>
>> opinion.And what do you think we need to do to enhance these two<br>
>> aspects.Any suggestions and comments are welcome.<br>
>><br>
>><br>
>><br>
>> Thanks<br>
>> Best regards<br>
>> Zhou Jianshen<br>
>> <a href="mailto:zhoujianshen@highgo.com" target="_blank">zhoujianshen@highgo.com</a><br>
>><br>
>><br>
>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> pgpool-hackers mailing list<br>
>> <a href="mailto:pgpool-hackers@pgpool.net" target="_blank">pgpool-hackers@pgpool.net</a><br>
>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-hackers</a><br>
>><br>
</blockquote></div></div>