[pgpool-hackers: 3829] Re: 答复: Re: discuss of pgpool enhancement
Tatsuo Ishii
ishii at sraoss.co.jp
Sat Sep 26 07:38:26 JST 2020
Thanks. I am going to look into this.
In the mean time there are some white space errors in the patch. I
would appreciate if you fix them.
$ git apply ~/on_demand_spawn.patch
/home/t-ishii/on_demand_spawn.patch:63: trailing whitespace.
/home/t-ishii/on_demand_spawn.patch:119: trailing whitespace.
/home/t-ishii/on_demand_spawn.patch:222: trailing whitespace.
int max_children; /* Maximum number of child to
/home/t-ishii/on_demand_spawn.patch:343: trailing whitespace.
/home/t-ishii/on_demand_spawn.patch:360: trailing whitespace.
warning: squelched 6 whitespace errors
warning: 11 lines add whitespace errors.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
From: 周建身 <zhoujianshen at highgo.com>
Subject: 答复: [pgpool-hackers: 3712] Re: discuss of pgpool enhancement
Date: Fri, 25 Sep 2020 10:28:34 +0000
Message-ID: <6d11923a4276453a8a89c4a87abb6958 at EX02.highgo.com>
> Hello Tatsuo and Usama,
> I have worked on this topic of on-demand spawning of child processes. In this patch ,I use parameter max_children，max_spare_children，min_spare_children
>
> # min_spare_children: minimum number of server processes which are kept spare
> # max_spare_children: maximum number of server processes which are kept spare
> # max_children: maximum number of server processes allowed to start
> My patch is in the attachment.
> Any comments and suggestions are welcome.
>
> Thanks
> Best regards
> Zhou Jianshen
> zhoujianshen at highgo.com
> ________________________________________
> 发件人: Tatsuo Ishii <ishii at sraoss.co.jp>
> 发送时间: 2020年7月14日 19:40
> 收件人: m.usama at gmail.com
> 抄送: 周建身; Muhammad; pgpool-hackers at pgpool.net
> 主题: Re: [pgpool-hackers: 3712] Re: discuss of pgpool enhancement
>
>> 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.
>
> Not sure abut this. Suppose we have num_init_children = 4 and max_pool
> = 1. We have 4 users u1, u2, u3 and u4. For simplicity, all those
> users connect to the same database.
>
> Current Pgpool-II:
> 1. u1 connects to pgpool child p1 and creates connection pool p_u1.
> 2. u2 connects to pgpool child p2 and creates connection pool p_u2.
> 3. u3 connects to pgpool child p3 and creates connection pool p_u3.
> 4. u4 connects to pgpool child p4 and creates connection pool p_u4.
>
> Pgpool-II with global connection pooling.
> 1. u1 connects to pgpool child p1 and creates connection pool p_u1.
> 2. u2 connects to pgpool child p2 and creates connection pool p_u2.
> 3. u3 connects to pgpool child p3 and creates connection pool p_u3.
> 4. u4 connects to pgpool child p4 and creates connection pool p_u4.
>
> So there's no difference with/without global connection pooling in the
> end.
>
> The case global connection when pooling wins would be, number of kind
> of users and concurrent connections are much smaller than
> num_init_children. For example, if there's only one user u1 and
> there's only one concurrent connections, we will have:
>
> Current Pgpool-II:
> 1. u1 connects to pgpool child p1 and creates connection pool p_u1.
> 2. u1 connects to pgpool child p2 and creates connection pool p_u2.
> 3. u1 connects to pgpool child p3 and creates connection pool p_u3.
> 4. u1 connects to pgpool child p4 and creates connection pool p_u4.
>
> Pgpool-II with global connection pooling.
> 1. u1 connects to pgpool child p1 and creates connection pool p_u1.
> 2. u1 connects to pgpool child p2 and reuses connection pool p_u1.
> 3. u1 connects to pgpool child p3 and reuses connection pool p_u1.
> 4. u1 connects to pgpool child p4 and reuses connection pool p_u1.
>
> Thus global connection pool wins having only 1 connection pool if
> number of kind of users and concurrent connections are much smaller
> than num_init_children.
>
> But question is, if we have only 1 concurrent session,
> num_init_children = 1 would be enough in the first place. In this case
> we will have similar result with current Pgpool-II.
>
> 1. u1 connects to pgpool child p1 and creates connection pool p_u1.
> 2. u1 connects to pgpool child p1 and reuses connection pool p_u1.
> 3. u1 connects to pgpool child p1 and reuses connection pool p_u1.
> 4. u1 connects to pgpool child p1 and reuses connection pool p_u1.
>
> So there's no point to have global connection pool here.
>
>> 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.
>
> Apache already has similar parameters:
>
> -------------------------------------------------------------------------
> # prefork MPM
> # StartServers: number of server processes to start
> # MinSpareServers: minimum number of server processes which are kept spare
> # MaxSpareServers: maximum number of server processes which are kept spare
> # MaxRequestWorkers: maximum number of server processes allowed to start
>
> StartServers 5
> MinSpareServers 5
> MaxSpareServers 10
> MaxRequestWorkers 150
> -------------------------------------------------------------------------
>
> I think our num_init_children looks similar to StartServers. So all we
> have to have are MinSpareServers, MaxSpareServers, and
> MaxRequestWorkers. (Probably we should rename them to more appropreate
> ones).
>
>> 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
>>>
More information about the pgpool-hackers
mailing list