pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedConnection pooling - Number of connections
18+ messages / 9 participants
[nested] [flat]
* Connection pooling - Number of connections
@ 2014-03-21 14:49 Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Guillaume Smet @ 2014-03-21 14:49 UTC (permalink / raw)
To: pgsql-performance; +Cc: Brett Wooldridge <brett.wooldridge@gmail.com>
Hi all,
Brett Wooldridge, the creator of HikariCP [1] - a high performance
Java connection pool - is contemplating the idea to change the way
pooling is done in HikariCP and have a fixed-size pool of connections
always open.
No maxPoolSize, no minIdle, no minPoolSize, juste a poolSize parameter
which sets the size of the pool. At application startup, all the
connections are opened and maintained by the pool throughout the life
of the application.
The basic idea is that if you decide that your application might need
100 connections at time, you set poolSize to 100 and HikariCP
maintains 100 connections open.
I recall very old posts on this list where people were talking about
code paths sensitive to the number of connections open (or even
max_connections) and that it wasn't such a good idea to keep
connections open if they were not really needed.
As a lot of scalability work has been done since this (very old) time,
I was wondering if it was still the rule of thumb or if the idea of
Brett to completely simplify the connection management is the way to
go.
It seems that at least another pool implementation is going this way
so I thought it might be a good idea to have the opinion of the
database side of things. This way, it will be easier to take a well
informed decision.
Thanks in advance for your comments/advices.
--
Guillaume
[1] https://github.com/brettwooldridge/HikariCP
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 15:49 David Johnston <polobo@yahoo.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: David Johnston @ 2014-03-21 15:49 UTC (permalink / raw)
To: pgsql-performance
Guillaume Smet wrote
> Brett Wooldridge, the creator of HikariCP [1] - a high performance
> Java connection pool - is contemplating the idea to change the way
> pooling is done in HikariCP and have a fixed-size pool of connections
> always open.
>
> No maxPoolSize, no minIdle, no minPoolSize, juste a poolSize parameter
> which sets the size of the pool. At application startup, all the
> connections are opened and maintained by the pool throughout the life
> of the application.
>
> The basic idea is that if you decide that your application might need
> 100 connections at time, you set poolSize to 100 and HikariCP
> maintains 100 connections open.
>
> I recall very old posts on this list where people were talking about
> code paths sensitive to the number of connections open (or even
> max_connections) and that it wasn't such a good idea to keep
> connections open if they were not really needed.
>
> As a lot of scalability work has been done since this (very old) time,
> I was wondering if it was still the rule of thumb or if the idea of
> Brett to completely simplify the connection management is the way to
> go.
>
> It seems that at least another pool implementation is going this way
> so I thought it might be a good idea to have the opinion of the
> database side of things. This way, it will be easier to take a well
> informed decision.
The developer, not the pool implementer, is going to ultimately decide which
trade-offs to incur. Having a connection open, even if idle, consumes
resources and performance no matter how minimal.
Pool management does cost cycles as well so if one does not need pool
management then getting rid of it is probably worthwhile to them. The
question is whether you want to only meet the need of this specific user or
whether you want to provide them with flexibility.
If existing pool management implementations are reasonably well implemented
and efficient then focusing effort on a potentially under-served use-case
definitely has merit.
Consider this train-of-thought: no matter how large the pool size if you
are constantly keeping, say, 90% of the connections actively working then
having, on average, 10% of the connections sitting idle is probably not
going to be noticeable on the server and the reduction in overhead of
managing a pool is typically a net positive. Now, I had no clue what
percentage is actually true, or under what conditions and pool sizes it may
vary, but that is a calculation that someone deciding on between managed and
un-managed pools would need to make.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-Number-of-connections-tp5797025p5797030.h...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 16:05 Guillaume Smet <guillaume.smet@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 18+ messages in thread
From: Guillaume Smet @ 2014-03-21 16:05 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-performance
On Fri, Mar 21, 2014 at 4:49 PM, David Johnston <polobo@yahoo.com> wrote:
> Consider this train-of-thought: no matter how large the pool size if you
> are constantly keeping, say, 90% of the connections actively working then
> having, on average, 10% of the connections sitting idle is probably not
> going to be noticeable on the server and the reduction in overhead of
> managing a pool is typically a net positive. Now, I had no clue what
> percentage is actually true, or under what conditions and pool sizes it may
> vary, but that is a calculation that someone deciding on between managed and
> un-managed pools would need to make.
Sure.
The big question is if it is suited for general purpose or if having
100 connections open when 10 only are necessary at the time is causing
any unnecessary contention/spinlock issues/performance
overhead/whatever...
--
Guillaume
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 16:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 2 replies; 18+ messages in thread
From: Tom Lane @ 2014-03-21 16:17 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: David Johnston <polobo@yahoo.com>; pgsql-performance
Guillaume Smet <guillaume.smet@gmail.com> writes:
> On Fri, Mar 21, 2014 at 4:49 PM, David Johnston <polobo@yahoo.com> wrote:
>> Consider this train-of-thought: no matter how large the pool size if you
>> are constantly keeping, say, 90% of the connections actively working then
>> having, on average, 10% of the connections sitting idle is probably not
>> going to be noticeable on the server and the reduction in overhead of
>> managing a pool is typically a net positive. Now, I had no clue what
>> percentage is actually true, or under what conditions and pool sizes it may
>> vary, but that is a calculation that someone deciding on between managed and
>> un-managed pools would need to make.
> Sure.
> The big question is if it is suited for general purpose or if having
> 100 connections open when 10 only are necessary at the time is causing
> any unnecessary contention/spinlock issues/performance
> overhead/whatever...
It will cost you, in ProcArray scans for example. But lots-of-idle-
connections is exactly what a pooler is supposed to prevent. If you have
a server that can handle say 10 active queries, you should have a pool
size of 10, not 100. (If you have a server that can actually handle
100 active queries, I'd like to have your IT budget.)
The proposed design sounds fairly reasonable to me, as long as users are
clear on how to set the pool size --- and in particular that bigger is
not better. Clueless users could definitely shoot themselves in the
foot, though.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 17:51 Sethu Prasad <sethuprasad.in@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 18+ messages in thread
From: Sethu Prasad @ 2014-03-21 17:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guillaume Smet <guillaume.smet@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
Reaching the maxPoolSize from the minPoolSize means creating the
connections at the crucial moment where the client application is in the
desperate need of completing an important query/transaction which the
primary responsibility since it cannot hold the data collected.
So here the connection creation action is the costliest among all the other
management tasks. so keeping the connections ready is the best option.
poolSize parameter is very good in the sense when the application owner
know what is the optimal number to put, after having application
performance analysed with the history of previous settings and the
improvements made on it. server sizing always shows up in this sort of
analysis.
On Fri, Mar 21, 2014 at 9:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Guillaume Smet <guillaume.smet@gmail.com> writes:
> > On Fri, Mar 21, 2014 at 4:49 PM, David Johnston <polobo@yahoo.com>
> wrote:
> >> Consider this train-of-thought: no matter how large the pool size if
> you
> >> are constantly keeping, say, 90% of the connections actively working
> then
> >> having, on average, 10% of the connections sitting idle is probably not
> >> going to be noticeable on the server and the reduction in overhead of
> >> managing a pool is typically a net positive. Now, I had no clue what
> >> percentage is actually true, or under what conditions and pool sizes it
> may
> >> vary, but that is a calculation that someone deciding on between
> managed and
> >> un-managed pools would need to make.
>
> > Sure.
>
> > The big question is if it is suited for general purpose or if having
> > 100 connections open when 10 only are necessary at the time is causing
> > any unnecessary contention/spinlock issues/performance
> > overhead/whatever...
>
> It will cost you, in ProcArray scans for example. But lots-of-idle-
> connections is exactly what a pooler is supposed to prevent. If you have
> a server that can handle say 10 active queries, you should have a pool
> size of 10, not 100. (If you have a server that can actually handle
> 100 active queries, I'd like to have your IT budget.)
>
> The proposed design sounds fairly reasonable to me, as long as users are
> clear on how to set the pool size --- and in particular that bigger is
> not better. Clueless users could definitely shoot themselves in the
> foot, though.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 18:36 Guillaume Smet <guillaume.smet@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 18+ messages in thread
From: Guillaume Smet @ 2014-03-21 18:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Johnston <polobo@yahoo.com>; pgsql-performance
Hi Tom,
On Fri, Mar 21, 2014 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It will cost you, in ProcArray scans for example. But lots-of-idle-
> connections is exactly what a pooler is supposed to prevent. If you have
> a server that can handle say 10 active queries, you should have a pool
> size of 10, not 100. (If you have a server that can actually handle
> 100 active queries, I'd like to have your IT budget.)
>
> The proposed design sounds fairly reasonable to me, as long as users are
> clear on how to set the pool size --- and in particular that bigger is
> not better. Clueless users could definitely shoot themselves in the
> foot, though.
Yeah, well.
My understanding of what happened on the field is that people usually
set the pool size limit quite high because they don't want to
experience connection starvation even if there is a temporary slowdown
of their application/database.
Is the overhead of having 100 connections open noticeable or is it
better to not have them but not so bad to have them?
Thanks.
--
Guillaume
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 18:38 Guillaume Smet <guillaume.smet@gmail.com>
parent: Sethu Prasad <sethuprasad.in@gmail.com>
1 sibling, 0 replies; 18+ messages in thread
From: Guillaume Smet @ 2014-03-21 18:38 UTC (permalink / raw)
To: Sethu Prasad <sethuprasad.in@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Johnston <polobo@yahoo.com>; pgsql-performance
Hi Sethu,
On Fri, Mar 21, 2014 at 6:51 PM, Sethu Prasad <sethuprasad.in@gmail.com> wrote:
> So here the connection creation action is the costliest among all the other
> management tasks. so keeping the connections ready is the best option.
That's why you often have a minIdle parameter which allows to create
idle connections in advance.
> poolSize parameter is very good in the sense when the application owner know
> what is the optimal number to put, after having application performance
> analysed with the history of previous settings and the improvements made on
> it. server sizing always shows up in this sort of analysis.
It supposes that you do this job. From my experience, most of the "not
so demanding" apps are put into production without this sort of
detailed analysis.
You do it for your critical high throughput applications, not for the others.
That said, interesting discussion. Not exactly what I expected.
--
Guillaume
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 18:41 David Johnston <polobo@yahoo.com>
parent: Sethu Prasad <sethuprasad.in@gmail.com>
1 sibling, 0 replies; 18+ messages in thread
From: David Johnston @ 2014-03-21 18:41 UTC (permalink / raw)
To: pgsql-performance
Sethu Prasad wrote
> Reaching the maxPoolSize from the minPoolSize means creating the
> connections at the crucial moment where the client application is in the
> desperate need of completing an important query/transaction which the
> primary responsibility since it cannot hold the data collected.
One query is slowed down a little in the unusual situation where not enough
pooled connections are available. To fix that you want to slow down the
entire server all of the time? Really? And even if this is sometimes the
best option your assertion is unqualified so do you really think this is
best for everyone, always?
I think it is good to give developers options but if your situation is 10 /
100 then a fixed 100 connection pool is probably not the best configuration.
The question I'd ask is if you are developing a new driver what problem and
use-case are you trying to accommodate? For those in the general case a
resizing pool is probably the best bet. It will usually stabilize at
typical volume so that an optimum number of connections are maintained while
still allowing for some expansion in times of excess demand. A fixed size
pool would be something an experienced user would decide they need based
upon their usage patterns and need to eke out every last bit of performance
in the extremes situations while only trading a little bit of performance
when the connections are not maxed out.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-Number-of-connections-tp5797025p5797061.h...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-21 19:04 Claudio Freire <klaussfreire@gmail.com>
parent: Guillaume Smet <guillaume.smet@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Claudio Freire @ 2014-03-21 19:04 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Johnston <polobo@yahoo.com>; pgsql-performance
On Fri, Mar 21, 2014 at 3:36 PM, Guillaume Smet
<guillaume.smet@gmail.com> wrote:
> On Fri, Mar 21, 2014 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It will cost you, in ProcArray scans for example. But lots-of-idle-
>> connections is exactly what a pooler is supposed to prevent. If you have
>> a server that can handle say 10 active queries, you should have a pool
>> size of 10, not 100. (If you have a server that can actually handle
>> 100 active queries, I'd like to have your IT budget.)
>>
>> The proposed design sounds fairly reasonable to me, as long as users are
>> clear on how to set the pool size --- and in particular that bigger is
>> not better. Clueless users could definitely shoot themselves in the
>> foot, though.
>
> Yeah, well.
>
> My understanding of what happened on the field is that people usually
> set the pool size limit quite high because they don't want to
> experience connection starvation even if there is a temporary slowdown
> of their application/database.
My experience is that small transaction-mode connection pools used to
serve very quick queries can sometimes not fully use the hardware if
the connections aren't set in autocommit mode on the client side,
because the network roundtrips hold onto the server slot for a sizable
portion of the lifecycle.
So, my recommendation: use protocol-level autocommit for read-only
queries and cores+spindles workers - that will use your hardware
fully.
On Fri, Mar 21, 2014 at 3:41 PM, David Johnston <polobo@yahoo.com> wrote:
>> Reaching the maxPoolSize from the minPoolSize means creating the
>> connections at the crucial moment where the client application is in the
>> desperate need of completing an important query/transaction which the
>> primary responsibility since it cannot hold the data collected.
>
> One query is slowed down a little in the unusual situation where not enough
> pooled connections are available. To fix that you want to slow down the
> entire server all of the time? Really? And even if this is sometimes the
> best option your assertion is unqualified so do you really think this is
> best for everyone, always?
I don't think a variable connection pool makes any sense if you cast
deadlocks aside for a moment.
The only reason for connection starvation for a properly sized pool,
is hardware overload. When you have hardware overload, you really
don't want to throw more load at it, you want to let it cool down.
The solution, if you have many heavy, OLAP-style queries that block
the rest, is to have two pools, and size both so that you don't
overload (or at least you overload controlledly) the server. Send the
OLAP queries to one pool, and the OLTP queries to the other, and you
guarantee a smooth flow, even if you cannot guarantee 100% hardware
utilization and maximum thoughput both.
Now, if we consider deadlocks, you might have connection starvation
because all of the connections are waiting for an operation that is
deadlocked in application code (the deadlock cannot occur at the DB
level if you use transaction mode, but you can have open transactions
waiting on a mutex that is waiting for a connection). I've experienced
application-level deadlocks like these, and the solution for me has
always been to have a reasonable timeout and connections for a reserve
pool - when connections are waiting for more than X seconds (that one
considers abnormal given the knowledge you have about load
characteristics), grow the pool to free up resources and try to
dislodge the deadlocked application threads.
Sometimes, regular overload triggers the reserve pool, so you cannot
be too generous on the number of connections you'll have in reserve.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-22 11:26 Brett Wooldridge <brett.wooldridge@gmail.com>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Brett Wooldridge @ 2014-03-22 11:26 UTC (permalink / raw)
To: pgsql-performance
Hi, Brett Wooldridge here, one of the principals of HikariCP. I thought I'd
wade into the conversation pool a little myself if you guys don't mind.
Speaking to David's point...
>> Reaching the maxPoolSize from the minPoolSize means creating the
>> connections at the crucial moment where the client application is in the
>> desperate need of completing an important query/transaction which the
>> primary responsibility since it cannot hold the data collected.
This was one of the reasons I was proposing the fixed pool design. In my
experience, even in pools that maintain a minimum number of idle
connections, responding to spike demands is problematic. If you have a pool
with say 30 max. connections, and a 10 minimum idle connection goal, a
sudden spike demand for 20 connections means the pool can satisfy 10
instantly but then is left to [try to] establish 10 connections before the
application's connectionTimeout (read acquisition timeout from the pool) is
reached. This in turn generates a spike demand on the database slowing down
not only the connection establishments themselves but also slowing down the
completion of transactions that might actually return connections to the
pool.
As I think Tom noted is a slidestack I read somewhere, there is a "knee" in
the performance curve beyond which additional connections cause a drop in
TPS. While users think it is a good idea to have 10 idle connections but a
maxPoolSize of 100, the reality is, they can retire/reuse connections faster
with a much smaller maxPoolSize. And I didn't see a pool of a few dozen
connections actually impacting performance much when half of them are idle
and half are executing transactions (ie. the idle ones don't impact the
overall performance much).
Finally, one of my contentions was, either your database server has
resources or it doesn't. Either it has enough memory and processing power
for N connections or it doesn't. If the pool is set below, near, or at that
capacity what is the purpose of releasing connections in that case? Yes, it
frees up memory, but that memory is not really available for other use given
that at any instant the maximum capacity of the pool may be demanded.
Instead releasing resources only to try to reallocate them during a demand
peak seems counter-productive.
-Brett
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Connection-pooling-Number-of-connections-tp5797025p5797135.h...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Connection pooling - Number of connections
@ 2014-03-24 13:27 Brett Wooldridge <brett.wooldridge@gmail.com>
0 siblings, 3 replies; 18+ messages in thread
From: Brett Wooldridge @ 2014-03-24 13:27 UTC (permalink / raw)
To: pgsql-performance
Hi, Brett Wooldridge here, one of the principals of HikariCP. I thought
I'd wade into the conversation pool a little myself if you guys don't mind.
Speaking to David's point...
>> Reaching the maxPoolSize from the minPoolSize means creating the
>> connections at the crucial moment where the client application is in the
>> desperate need of completing an important query/transaction which the
>> primary responsibility since it cannot hold the data collected.
This was one of the reasons I was proposing the fixed pool design. In my
experience, even in pools that maintain a minimum number of idle
connections, responding to spike demands is problematic. If you have a
pool with say 30 max. connections, and a 10 minimum idle connection goal, a
sudden spike demand for 20 connections means the pool can satisfy 10
instantly but then is left to [try to] establish 10 connections before the
application's connectionTimeout (read acquisition timeout from the pool) is
reached. This in turn generates a spike demand on the database slowing
down not only the connection establishments themselves but also slowing
down the completion of transactions that might actually return connections
to the pool.
As I think Tom noted is a slidestack I read somewhere, there is a "knee" in
the performance curve beyond which additional connections cause a drop in
TPS. While users often think it is a good idea to have maxPoolSize of 100,
the reality is they can retire/reuse connections faster with a much smaller
pool. I didn't see a pool of a 2 or 3 dozen connections actually impacting
performance much when half of them are idle and half are executing
transactions (ie. the idle ones don't impact the overall performance much).
Finally, one of my contentions was, either your database server has
resources or it doesn't. Either it has enough memory and processing power
for N connections or it doesn't. If the pool is set below, near, or at
that capacity what is the purpose of releasing connections in that case?
Yes, it frees up memory, but that memory is not really available for other
use given that at any instant the maximum capacity of the pool may be
demanded.
Instead releasing resources only to try to reallocate them during a demand
peak seems counter-productive.
I'd appreciate any shared thoughts on my presuppositions.
-Brett
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-24 20:24 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Brett Wooldridge <brett.wooldridge@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Gavin Flower @ 2014-03-24 20:24 UTC (permalink / raw)
To: Brett Wooldridge <brett.wooldridge@gmail.com>; pgsql-performance
On 25/03/14 02:27, Brett Wooldridge wrote:
> Hi, Brett Wooldridge here, one of the principals of HikariCP. I
> thought I'd wade into the conversation pool a little myself if you
> guys don't mind.
>
> Speaking to David's point...
> >> Reaching the maxPoolSize from the minPoolSize means creating the
> >> connections at the crucial moment where the client application is in the
> >> desperate need of completing an important query/transaction which the
> >> primary responsibility since it cannot hold the data collected.
>
> This was one of the reasons I was proposing the fixed pool design. In
> my experience, even in pools that maintain a minimum number of idle
> connections, responding to spike demands is problematic. If you have
> a pool with say 30 max. connections, and a 10 minimum idle connection
> goal, a sudden spike demand for 20 connections means the pool can
> satisfy 10 instantly but then is left to [try to] establish 10
> connections before the application's connectionTimeout (read
> acquisition timeout from the pool) is reached. This in turn generates
> a spike demand on the database slowing down not only the connection
> establishments themselves but also slowing down the completion of
> transactions that might actually return connections to the pool.
>
> As I think Tom noted is a slidestack I read somewhere, there is a
> "knee" in the performance curve beyond which additional connections
> cause a drop in TPS. While users often think it is a good idea to
> have maxPoolSize of 100, the reality is they can retire/reuse
> connections faster with a much smaller pool. I didn't see a pool of a
> 2 or 3 dozen connections actually impacting performance much when half
> of them are idle and half are executing transactions (ie. the idle
> ones don't impact the overall performance much).
>
> Finally, one of my contentions was, either your database server has
> resources or it doesn't. Either it has enough memory and processing
> power for N connections or it doesn't. If the pool is set below,
> near, or at that capacity what is the purpose of releasing connections
> in that case? Yes, it frees up memory, but that memory is not really
> available for other use given that at any instant the maximum capacity
> of the pool may be demanded.
> Instead releasing resources only to try to reallocate them during a
> demand peak seems counter-productive.
>
> I'd appreciate any shared thoughts on my presuppositions.
>
> -Brett
>
Surely no code changes are required, as one can simply set the min and
max pool sizes to be the same?
Cheers,
Gavin
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-25 00:23 Brett Wooldridge <brett.wooldridge@gmail.com>
parent: Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 1 reply; 18+ messages in thread
From: Brett Wooldridge @ 2014-03-25 00:23 UTC (permalink / raw)
To: Gavin Flower <GavinFlower@archidevsys.co.nz>; +Cc: pgsql-performance
On Tue, Mar 25, 2014 at 5:24 AM, Gavin Flower <GavinFlower@archidevsys.co.nz
> wrote:
> Surely no code changes are required, as one can simply set the min and
> max pool sizes to be the same?
>
> Cheers,
> Gavin
>
To be sure it can be implemented that way, but its a question of design
targets. For example, if a pool is allowed to grow and shrink, the design
might encompass a pool of threads that try to maintain the
configured-minimum idle connections to respond to spike demands. And there
is the additional setting in the pool for users to [typically] misconfigure.
However, if the pool is fixed size, and attrition from the pool is only by
idle timeout (typically measured in minutes), the design does not need to
account for spike demand. Likely connections that have dropped out can
either be restored on-demand rather than something running constantly in
the background trying to maintain and idle level.
One of the attributes of HikariCP is a minimalistic set of configuration
options with sane defaults, and minimalistic code. There are many
competitor pools, offering dozens of settings ranging form banal to unsafe.
HikariCP initially even offered some of these options, but one-ny-one
they're getting the ax. More and more we're trying to look at what is the
true core functionality that user's need -- eliminate what is unnecessary
and easily misconfigured.
Thus a debate started over in our group, with some similar views as
expressed here (on both sides). Guillaume Smet was concerned about the
impact of idle connections on active ones in PostgreSQL (in a fixed pool
scenario) and wanted to ask some of the experts over here.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-25 01:42 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Brett Wooldridge <brett.wooldridge@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Gavin Flower @ 2014-03-25 01:42 UTC (permalink / raw)
To: Brett Wooldridge <brett.wooldridge@gmail.com>; +Cc: pgsql-performance
On 25/03/14 13:23, Brett Wooldridge wrote:
> On Tue, Mar 25, 2014 at 5:24 AM, Gavin Flower
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>>
> wrote:
>
>> Surely no code changes are required, as one can simply set the
>> min and max pool sizes to be the same?
> Cheers,
> Gavin
>
>
> To be sure it can be implemented that way, but its a question of
> design targets. For example, if a pool is allowed to grow and shrink,
> the design might encompass a pool of threads that try to maintain the
> configured-minimum idle connections to respond to spike demands. And
> there is the additional setting in the pool for users to [typically]
> misconfigure.
>
> However, if the pool is fixed size, and attrition from the pool is
> only by idle timeout (typically measured in minutes), the design does
> not need to account for spike demand. Likely connections that have
> dropped out can either be restored on-demand rather than something
> running constantly in the background trying to maintain and idle level.
>
> One of the attributes of HikariCP is a minimalistic set of
> configuration options with sane defaults, and minimalistic code.
> There are many competitor pools, offering dozens of settings ranging
> form banal to unsafe. HikariCP initially even offered some of these
> options, but one-ny-one they're getting the ax. More and more we're
> trying to look at what is the true core functionality that user's need
> -- eliminate what is unnecessary and easily misconfigured.
>
> Thus a debate started over in our group, with some similar views as
> expressed here (on both sides). Guillaume Smet was concerned about
> the impact of idle connections on active ones in PostgreSQL (in a
> fixed pool scenario) and wanted to ask some of the experts over here.
>
Would it be a valid option to switch in simpler code when min = max and
both could be set to the same default? This would allow more efficient
code to be run for a fixed pool size and allow a sane default, while
preserving the option to have a range, though obviously not as simple as
only allowing a fixed pool size in terms of code complexity.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-25 02:45 Brett Wooldridge <brett.wooldridge@gmail.com>
parent: Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 0 replies; 18+ messages in thread
From: Brett Wooldridge @ 2014-03-25 02:45 UTC (permalink / raw)
To: pgsql-performance
Sure. It's all just code. It's not particularly a question of efficiency,
I'm sure it could be made equally efficient. But "simpler" code-wise would
be not having two implementations, or not having one that is designed to
try to keep up with spike demands. The question for this group was really
around PostgreSQL performance regarding the impact of idle connections. If
there is a pool of 20 connections, and half of them are idle, what is the
impact performance-wise of the idle connections on the active connections?
On Tue, Mar 25, 2014 at 10:42 AM, Gavin Flower <
GavinFlower@archidevsys.co.nz> wrote:
> On 25/03/14 13:23, Brett Wooldridge wrote:
>
> On Tue, Mar 25, 2014 at 5:24 AM, Gavin Flower <
> GavinFlower@archidevsys.co.nz> wrote:
>
>> Surely no code changes are required, as one can simply set the min
>> and max pool sizes to be the same?
>>
>> Cheers,
>> Gavin
>>
>
> To be sure it can be implemented that way, but its a question of design
> targets. For example, if a pool is allowed to grow and shrink, the design
> might encompass a pool of threads that try to maintain the
> configured-minimum idle connections to respond to spike demands. And there
> is the additional setting in the pool for users to [typically] misconfigure.
>
> However, if the pool is fixed size, and attrition from the pool is only
> by idle timeout (typically measured in minutes), the design does not need
> to account for spike demand. Likely connections that have dropped out can
> either be restored on-demand rather than something running constantly in
> the background trying to maintain and idle level.
>
> One of the attributes of HikariCP is a minimalistic set of configuration
> options with sane defaults, and minimalistic code. There are many
> competitor pools, offering dozens of settings ranging form banal to unsafe.
> HikariCP initially even offered some of these options, but one-ny-one
> they're getting the ax. More and more we're trying to look at what is the
> true core functionality that user's need -- eliminate what is unnecessary
> and easily misconfigured.
>
> Thus a debate started over in our group, with some similar views as
> expressed here (on both sides). Guillaume Smet was concerned about the
> impact of idle connections on active ones in PostgreSQL (in a fixed pool
> scenario) and wanted to ask some of the experts over here.
>
> Would it be a valid option to switch in simpler code when min = max and
> both could be set to the same default? This would allow more efficient
> code to be run for a fixed pool size and allow a sane default, while
> preserving the option to have a range, though obviously not as simple as
> only allowing a fixed pool size in terms of code complexity.
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-27 00:35 Josh Berkus <josh@agliodbs.com>
parent: Brett Wooldridge <brett.wooldridge@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2014-03-27 00:35 UTC (permalink / raw)
To: Brett Wooldridge <brett.wooldridge@gmail.com>; pgsql-performance
On 03/24/2014 06:27 AM, Brett Wooldridge wrote:
> This was one of the reasons I was proposing the fixed pool design. In my
> experience, even in pools that maintain a minimum number of idle
> connections, responding to spike demands is problematic. If you have a
> pool with say 30 max. connections, and a 10 minimum idle connection goal, a
> sudden spike demand for 20 connections means the pool can satisfy 10
> instantly but then is left to [try to] establish 10 connections before the
> application's connectionTimeout (read acquisition timeout from the pool) is
> reached. This in turn generates a spike demand on the database slowing
> down not only the connection establishments themselves but also slowing
> down the completion of transactions that might actually return connections
> to the pool.
So what makes sense really depends on what your actual connection
pattern is. Idle connections aren't free; aside from PostgreSQL
lock-checking overhead, they hold on to any virtual memory allocated to
them when they were working. In the aggregate, this can add up to quite
a bit of memory, which can then cause the OS to decide not to cache some
data you could really use.
Now, if your peak is 100 connections and your median is 50, this doesn't
signify. But I know more than a few workloads where the peak is 1000
and the median is 25, and in that case you want to drop the idle
connections gradually. The key is to keep enough of a buffer of ready
connections to deal with the next peak when it comes in.
That also means that even if the pool is a fixed size, you want to
rotate in and out the actual sessions, so that they don't hang onto
maximum virtual memory indefinitely.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-29 21:20 Jeff Janes <jeff.janes@gmail.com>
parent: Brett Wooldridge <brett.wooldridge@gmail.com>
2 siblings, 0 replies; 18+ messages in thread
From: Jeff Janes @ 2014-03-29 21:20 UTC (permalink / raw)
To: Brett Wooldridge <brett.wooldridge@gmail.com>; +Cc: pgsql-performance
On Mon, Mar 24, 2014 at 6:27 AM, Brett Wooldridge <
brett.wooldridge@gmail.com> wrote:
> Hi, Brett Wooldridge here, one of the principals of HikariCP. I thought
> I'd wade into the conversation pool a little myself if you guys don't mind.
>
> Speaking to David's point...
> >> Reaching the maxPoolSize from the minPoolSize means creating the
> >> connections at the crucial moment where the client application is in
> the
> >> desperate need of completing an important query/transaction which the
> >> primary responsibility since it cannot hold the data collected.
>
> This was one of the reasons I was proposing the fixed pool design. In my
> experience, even in pools that maintain a minimum number of idle
> connections, responding to spike demands is problematic. If you have a
> pool with say 30 max. connections, and a 10 minimum idle connection goal, a
> sudden spike demand for 20 connections means the pool can satisfy 10
> instantly but then is left to [try to] establish 10 connections before the
> application's connectionTimeout (read acquisition timeout from the pool) is
> reached. This in turn generates a spike demand on the database slowing
> down not only the connection establishments themselves but also slowing
> down the completion of transactions that might actually return connections
> to the pool.
>
Do you have publishable test scripts to demonstrate this? It would be nice
for people to be able to try it out for themselves, on their own hardware,
to see what it does. I have seen some database products for which I would
not doubt this effect is real, having seen prodigiously long connection set
up times. But I'm skeptical that that is a meaningful problem for
PostgreSQL, at least not with the md5 authentication method.
> As I think Tom noted is a slidestack I read somewhere, there is a "knee"
> in the performance curve beyond which additional connections cause a drop
> in TPS. While users often think it is a good idea to have maxPoolSize of
> 100, the reality is they can retire/reuse connections faster with a much
> smaller pool. I didn't see a pool of a 2 or 3 dozen connections actually
> impacting performance much when half of them are idle and half are
> executing transactions (ie. the idle ones don't impact the overall
> performance much).
>
I think the knee applies mostly to active connections. I've seen no
indication of completely idle connections causing observable problems in
recent releases, until the number of them gets absurd.
And the location of the knee for the number of active connections is going
to depend greatly on the hardware and the work load.
> Finally, one of my contentions was, either your database server has
> resources or it doesn't. Either it has enough memory and processing power
> for N connections or it doesn't. If the pool is set below, near, or at
> that capacity what is the purpose of releasing connections in that case?
> Yes, it frees up memory, but that memory is not really available for other
> use given that at any instant the maximum capacity of the pool may be
> demanded.
> Instead releasing resources only to try to reallocate them during a demand
> peak seems counter-productive.
>
I pretty much agree with you on that. Most of the arguments I see for
getting rid of idle connections really seem to be arguments for lowering
the maximum number.
That said, I don't like it when people take away my options because they
think I might not be able to set them correctly. Just say the default value
for the min is the same as the max, and most people don't need to change
that. If you don't want to implement a feature, that is one thing, but to
take out a feature that already exists just because you think I'm not quite
clever enough to use it is quite another thing.
Also, often people don't have a realistic work-load generator with which to
test the maximum, or a budget to run such tests. They will have no idea
where to set it. A plausible thing to do in that case is to just set it
rather high and hope the actual usage never get high enough to test whether
the chosen value was correct. That would be more problematic if the pool
size is static.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Connection pooling - Number of connections
@ 2014-03-30 03:41 Brett Wooldridge <brett.wooldridge@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 18+ messages in thread
From: Brett Wooldridge @ 2014-03-30 03:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Sent from my iPhone
> On Mar 27, 2014, at 9:35 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> On 03/24/2014 06:27 AM, Brett Wooldridge wrote:
>> This was one of the reasons I was proposing the fixed pool design. In my
>> experience, even in pools that maintain a minimum number of idle
>> connections, responding to spike demands is problematic. If you have a
>> pool with say 30 max. connections, and a 10 minimum idle connection goal, a
>> sudden spike demand for 20 connections means the pool can satisfy 10
>> instantly but then is left to [try to] establish 10 connections before the
>> application's connectionTimeout (read acquisition timeout from the pool) is
>> reached. This in turn generates a spike demand on the database slowing
>> down not only the connection establishments themselves but also slowing
>> down the completion of transactions that might actually return connections
>> to the pool.
>
> Now, if your peak is 100 connections and your median is 50, this doesn't
> signify. But I know more than a few workloads where the peak is 1000
> and the median is 25, and in that case you want to drop the idle
> connections gradually.
In the end we've gone with a maxPoolSize + minIdle model where the default is that they are equal (fixed pool).
Though I won't dispute that such workloads (1000 active connections) exist, in that someone created it, I would love to hear their justification. Unless they have >128 CPU cores and solid state storage they are basically spinning their wheels.
> That also means that even if the pool is a fixed size, you want to
> rotate in and out the actual sessions, so that they don't hang onto
> maximum virtual memory indefinitely.
We do this, there is a maxLifeTime setting to rotate out connections.
-Brett
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2014-03-30 03:41 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-21 14:49 Connection pooling - Number of connections Guillaume Smet <guillaume.smet@gmail.com>
2014-03-21 15:49 ` David Johnston <polobo@yahoo.com>
2014-03-21 16:05 ` Guillaume Smet <guillaume.smet@gmail.com>
2014-03-21 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-03-21 17:51 ` Sethu Prasad <sethuprasad.in@gmail.com>
2014-03-21 18:38 ` Guillaume Smet <guillaume.smet@gmail.com>
2014-03-21 18:41 ` David Johnston <polobo@yahoo.com>
2014-03-21 18:36 ` Guillaume Smet <guillaume.smet@gmail.com>
2014-03-21 19:04 ` Claudio Freire <klaussfreire@gmail.com>
2014-03-22 11:26 ` Brett Wooldridge <brett.wooldridge@gmail.com>
2014-03-24 13:27 Connection pooling - Number of connections Brett Wooldridge <brett.wooldridge@gmail.com>
2014-03-24 20:24 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2014-03-25 00:23 ` Brett Wooldridge <brett.wooldridge@gmail.com>
2014-03-25 01:42 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2014-03-25 02:45 ` Brett Wooldridge <brett.wooldridge@gmail.com>
2014-03-27 00:35 ` Josh Berkus <josh@agliodbs.com>
2014-03-30 03:41 ` Brett Wooldridge <brett.wooldridge@gmail.com>
2014-03-29 21:20 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox