Re: Built-in connection pooling

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Built-in connection pooling
Date: 2018-01-19 17:20:10
Message-ID: 7c5928b0-711e-8de7-7147-049a8e6a2f24@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 01/19/2018 06:07 PM, Konstantin Knizhnik wrote:
>
> ...
>
>>>> 3) Is there any sort of shrinking the pools? I mean, if the backend is
>>>> idle for certain period of time (or when we need backends for other
>>>> databases), does it get closed automatically?
>>>>
>>> When client is disconnected, client session is closed. But backen is not
>>> terminated even if there are no more sessions at this backend.
>>> It  was done intentionally, to avoid permanent spawning of new processes
>>> when there is one or few clients which frequently connect/disconnect to
>>> the database.
>>>
>> Sure, but it means a short peak will exhaust the backends indefinitely.
>> That's acceptable for a PoC, but I think needs to be fixed eventually.
>>
> Sorry, I do not understand it.
> You specify size of backends pool which will server client session.
> Size of this pool is chosen to provide the best performance at the
> particular system and workload.
> So number of backends will never exceed this optimal value even in case
> of "short peak".
> From my point of view terminating backends when there are no active
> sessions is wrong idea in any case, it was not temporary decision just
> for PoC.
>

That is probably true when there is just a single pool (for one
database/user). But when there are multiple such pools, it forces you to
keep the sum(pool_size) below max_connections. Which seems strange.

I do think the ability to evict backends after some timeout, or when
there is pressure in other pools (different user/database) is rather useful.

>>
>> Well, I haven't said it has to be single-threaded like pgbouncer. I
>> don't see why the bgworker could not use multiple threads internally (of
>> course, it'd need to be not to mess the stuff that is not thread-safe).
>>
>
> Certainly architecture with N multiple scheduling bgworkers and M
> executors (backends) may be more flexible
> than solution when scheduling is done in executor itself. But we will
> have to pay extra cost for redirection.
>
> I am not sure that finally it will allow to reach better performance.
> More flexible solution in many cases doesn't mean more efficient solution.
>

Sure, I wasn't really suggesting it's a clear win. I was responding to
your argument that pgbouncer in some cases reaches 100% CPU utilization
- that can be mitigated to a large extent by adding threads. Of course,
the cost for extra level of indirection is not zero.

regards

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2018-01-19 17:22:20 Re: Built-in connection pooling
Previous Message Konstantin Knizhnik 2018-01-19 17:19:28 Re: Built-in connection pooling