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(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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