Re: Built-in connection pooling

On 01/19/2018 06:19 PM, Konstantin Knizhnik wrote:
> 
> 
> On 19.01.2018 20:03, Claudio Freire wrote:
>>
>>
>> On Fri, Jan 19, 2018 at 1:53 PM, Konstantin Knizhnik
>> <k.knizhnik@postgrespro.ru <mailto:k.knizhnik@postgrespro.ru>> wrote:
>>
>>
>>
>>     On 19.01.2018 19:28, Pavel Stehule wrote:
>>>
>>>
>>>             When I've been thinking about adding a built-in
>>>             connection pool, my
>>>             rough plan was mostly "bgworker doing something like
>>>             pgbouncer" (that
>>>             is, listening on a separate port and proxying everything
>>>             to regular
>>>             backends). Obviously, that has pros and cons, and
>>>             probably would not
>>>             work serve the threading use case well.
>>>
>>>
>>>         And we will get the same problem as with pgbouncer: one
>>>         process will not be able to handle all connections...
>>>         Certainly it is possible to start several such scheduling
>>>         bgworkers... But in any case it is more efficient to
>>>         multiplex session in backend themselves.
>>>
>>>
>>>     pgbouncer hold all time client connect. When we implement the
>>>     listeners, then all work can be done by worker processes not by
>>>     listeners.
>>>
>>
>>     Sorry, I do not understand your point.
>>     In my case pgbench establish connection to the pgbouncer only 
>>     once at the beginning of the test.
>>     And pgbouncer spends all time in context switches (CPU usage is
>>     100% and it is mostly in kernel space: top of profile are kernel
>>     functions).
>>     The same picture will be if instead of pgbouncer you will do such
>>     scheduling in one bgworker.
>>     For the modern systems are not able to perform more than several
>>     hundreds of connection switches per second.
>>     So with single multiplexing thread or process you can not get
>>     speed more than 100k, while at powerful NUMA system it is possible
>>     to achieve millions of TPS.
>>     It is illustrated by the results I have sent in the previous mail:
>>     by spawning 10 instances of pgbouncer I was able to receive 7
>>     times bigger speed.
>>
>>
>> I'm sure pgbouncer can be improved. I've seen async code handle
>> millions of packets per second (zmq), pgbouncer shouldn't be radically
>> different.
>>
>>
> With pgbouncer you will never be able to use prepared statements which
> slows down simple queries almost twice (unless my patch with
> autoprepared statements is committed).
> 

I don't see why that wouldn't be possible? Perhaps not for prepared
statements with simple protocol, but I'm pretty sure it's doable for
extended protocol (which seems like a reasonable limitation).

That being said, I think it's a mistake to turn this thread into a
pgbouncer vs. the world battle. I could name things that are possible
only with standalone connection pool - e.g. pausing connections and
restarting the database without interrupting the clients.

But that does not mean built-in connection pool is not useful.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services