You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jmeter.apache.org by Felix Schumacher <fe...@internetallee.de> on 2016/01/02 16:38:11 UTC
doc for jdbc pool configuration
Hi all,
in our documentation there is one section about the sizing of the pool,
which I like to discuss:
"If you really want to use shared pooling (why?), then set the max count
to the same as the number of threads to ensure threads don't wait on
each other."
First: I think pooling is a valid option even for user-centric
scenarios. Think about simulating the sql requests of an application
server. In such a case a pool would have been used, so why not when
simulating it?
So for this part, I question the part "...(why?)..." and the "really" in
front of it.
Second: When a pool is used (at least the dbcp2 pool) the connections
seem to be stored in a stack like construct. So in a uncontended load
situation the pool will use only a fraction of the configured size and
in a contended situation with really many threads it will probably
overload the db.
So all in all I would rather change the sentence to something like "If
you want to use shared pooling, then set the max count to something
sensible".
Regards,
Felix
Re: doc for jdbc pool configuration
Posted by Philippe Mouawad <ph...@gmail.com>.
ok for me.
By the way, I think we should revisit the names of configuration options in
gui to be closer to modern pools and maybe enhance elements with more
options provided by dbcp.
It would make it useful for testing and sizing a servef pool.
Regards
On Saturday, January 2, 2016, Felix Schumacher <
felix.schumacher@internetallee.de> wrote:
> Hi all,
>
> in our documentation there is one section about the sizing of the pool,
> which I like to discuss:
>
> "If you really want to use shared pooling (why?), then set the max count
> to the same as the number of threads to ensure threads don't wait on each
> other."
>
> First: I think pooling is a valid option even for user-centric scenarios.
> Think about simulating the sql requests of an application server. In such a
> case a pool would have been used, so why not when simulating it?
>
> So for this part, I question the part "...(why?)..." and the "really" in
> front of it.
>
> Second: When a pool is used (at least the dbcp2 pool) the connections seem
> to be stored in a stack like construct. So in a uncontended load situation
> the pool will use only a fraction of the configured size and in a contended
> situation with really many threads it will probably overload the db.
>
> So all in all I would rather change the sentence to something like "If you
> want to use shared pooling, then set the max count to something sensible".
>
> Regards,
> Felix
>
--
Cordialement.
Philippe Mouawad.
Re: doc for jdbc pool configuration
Posted by Philippe Mouawad <ph...@gmail.com>.
On Tuesday, January 5, 2016, sebb <se...@gmail.com> wrote:
> On 5 January 2016 at 21:19, Felix Schumacher
> <felix.schumacher@internetallee.de <javascript:;>> wrote:
> > Am 04.01.2016 um 16:21 schrieb sebb:
> >>
> >> On 2 January 2016 at 15:38, Felix Schumacher
> >> <felix.schumacher@internetallee.de <javascript:;>> wrote:
> >>>
> >>> Hi all,
> >>>
> >>> in our documentation there is one section about the sizing of the pool,
> >>> which I like to discuss:
> >>>
> >>> "If you really want to use shared pooling (why?), then set the max
> count
> >>> to
> >>> the same as the number of threads to ensure threads don't wait on each
> >>> other."
> >>>
> >>> First: I think pooling is a valid option even for user-centric
> scenarios.
> >>> Think about simulating the sql requests of an application server. In
> such
> >>> a
> >>> case a pool would have been used, so why not when simulating it?
> >>>
> >>> So for this part, I question the part "...(why?)..." and the "really"
> in
> >>> front of it.
> >>
> >> The problem is that JMeter is then mainly testing the pool
> >> implementation rather than the server.
> >> Since the pool implementation cannot at present be replaced, that does
> >> not seem to be a useful approach.
> >
> > In my observations the actual pooling part in jmeter is rather low
> overhead
> > compared to jdbc or other jmeter code, so I think the pooling
> implementation
> > is not that relevant compared to the fact to use a pool.
> >
> > I have done some totally unscientific tests using a local postgresql
> > instance with a little database and a simple select (with two joins, so a
> > bit more involved than just "select 1"). The laptop had four cores and
> > enough memory not to swap.
> >
> > For 600 threads 100 loops and different pool sizes I got the following
> > results:
> >
> > pool size 0 (every thread has its own pool)
> > summary = 120000 in 00:02:28 = 811,0/s Avg: 463 Min: 2 Max: 17700
> > Err: 0 (0,00%)
> > memory went quite low and load went up to 400
> >
> > pool size 64
> > summary = 120000 in 00:02:22 = 844,8/s Avg: 638 Min: 2 Max: 18797
> > Err: 0 (0,00%)
> >
> > pool size 32
> > summary = 120000 in 00:02:22 = 843,7/s Avg: 633 Min: 2 Max: 15679
> > Err: 0 (0,00%)
> >
> > pool size 16
> > summary = 120000 in 00:02:23 = 840,9/s Avg: 653 Min: 2 Max: 13607
> > Err: 0 (0,00%)
> >
> > pool size 8
> > summary = 120000 in 00:02:22 = 842,8/s Avg: 653 Min: 2 Max: 16502
> > Err: 0 (0,00%)
> >
> > pool size 4
> > summary = 120000 in 00:02:22 = 843,7/s Avg: 674 Min: 2 Max: 8114
> Err:
> > 0 (0,00%)
> >
> > pool size 2
> > summary = 120000 in 00:02:49 = 709,6/s Avg: 801 Min: 2 Max: 9215
> Err:
> > 0 (0,00%)
> >
> > My interpretation is: a pool helps to keep the db happy, but it has to be
> > sized appropriately (whatever that means)
> >
> > I think the result would be even more in favour of the pool, if the db
> would
> > not have fitted in RAM and would induce a io bottleneck by the parallel
> db
> > processes. Plus, I had to configure my postgresql instance to allow more
> > than 100 simultaneous connections.
>
> I don't doubt that using a pool helps.
>
> >>
> >>
> >>> Second: When a pool is used (at least the dbcp2 pool) the connections
> >>> seem
> >>> to be stored in a stack like construct. So in a uncontended load
> >>> situation
> >>> the pool will use only a fraction of the configured size and in a
> >>> contended
> >>> situation with really many threads it will probably overload the db.
> >>
> >> That suggests that pooling should not be used by JMeter...
> >
> > Why? When we are not using a pool, JMeter will overload the db also.
> >>
> >>
> >>> So all in all I would rather change the sentence to something like "If
> >>> you
> >>> want to use shared pooling, then set the max count to something
> >>> sensible".
> >>
> >> I think we need to document why pooling is not in general a good idea
> >> for JMeter tests.
> >
> > I would rather document why and when pooling should be used.
> >
> > I have at least two use cases where pooling is useful:
> > * using the jdbc sampler to fill variables which are then used further
> in
> > the test
>
> That is possibly a valid use case, however this should be done in a
> setUp ThreadGroup, so performance is not so much of an issue.
> Besides, if the setup takes so long that it needs a pool, it should
> probably be done using a database bulk-load facility.
It can be more complex or require more 3rd party binaries.
Having the pool and multithreading the work can be interesting
> > * simulating the jdbc statements of an application server, which would
> use
> > a pool in itself
>
> In which case, it is important to be able to use the same pool as the
> application server.
you will be always limited by proprietary pools of comercial servers.
But if we add the 3 tested we cover:
- play2
- tomcat (jdbc and dbcp)
I think it's interesting enough.
Providing a factory to plug others would allow testing the ones we don't
want to distribute (c3p0...) or the commercial ones.
> >>
> >>
> >> However I would prefer to drop pooling support entirely unless the
> >> pool implementation can be provided by the user.
> >
> > The option of making the pool implementation configurable is open to us.
I agree
> >
> > Regards,
> > Felix
> >>
> >>
> >>> Regards,
> >>> Felix
> >
> >
>
--
Cordialement.
Philippe Mouawad.
Re: doc for jdbc pool configuration
Posted by sebb <se...@gmail.com>.
On 6 January 2016 at 05:37, Felix Schumacher
<fe...@internetallee.de> wrote:
>
>
> Am 5. Januar 2016 23:38:45 MEZ, schrieb sebb <se...@gmail.com>:
>>On 5 January 2016 at 21:19, Felix Schumacher
>><fe...@internetallee.de> wrote:
>>> Am 04.01.2016 um 16:21 schrieb sebb:
>>>>
>>>> On 2 January 2016 at 15:38, Felix Schumacher
>>>> <fe...@internetallee.de> wrote:
>>>>>
>>>>> Hi all,
>>>>>
>>>>> in our documentation there is one section about the sizing of the
>>pool,
>>>>> which I like to discuss:
>>>>>
>>>>> "If you really want to use shared pooling (why?), then set the max
>>count
>>>>> to
>>>>> the same as the number of threads to ensure threads don't wait on
>>each
>>>>> other."
>>>>>
>>>>> First: I think pooling is a valid option even for user-centric
>>scenarios.
>>>>> Think about simulating the sql requests of an application server.
>>In such
>>>>> a
>>>>> case a pool would have been used, so why not when simulating it?
>>>>>
>>>>> So for this part, I question the part "...(why?)..." and the
>>"really" in
>>>>> front of it.
>>>>
>>>> The problem is that JMeter is then mainly testing the pool
>>>> implementation rather than the server.
>>>> Since the pool implementation cannot at present be replaced, that
>>does
>>>> not seem to be a useful approach.
>>>
>>> In my observations the actual pooling part in jmeter is rather low
>>overhead
>>> compared to jdbc or other jmeter code, so I think the pooling
>>implementation
>>> is not that relevant compared to the fact to use a pool.
>>>
>>> I have done some totally unscientific tests using a local postgresql
>>> instance with a little database and a simple select (with two joins,
>>so a
>>> bit more involved than just "select 1"). The laptop had four cores
>>and
>>> enough memory not to swap.
>>>
>>> For 600 threads 100 loops and different pool sizes I got the
>>following
>>> results:
>>>
>>> pool size 0 (every thread has its own pool)
>>> summary = 120000 in 00:02:28 = 811,0/s Avg: 463 Min: 2 Max:
>>17700
>>> Err: 0 (0,00%)
>>> memory went quite low and load went up to 400
>>>
>>> pool size 64
>>> summary = 120000 in 00:02:22 = 844,8/s Avg: 638 Min: 2 Max:
>>18797
>>> Err: 0 (0,00%)
>>>
>>> pool size 32
>>> summary = 120000 in 00:02:22 = 843,7/s Avg: 633 Min: 2 Max:
>>15679
>>> Err: 0 (0,00%)
>>>
>>> pool size 16
>>> summary = 120000 in 00:02:23 = 840,9/s Avg: 653 Min: 2 Max:
>>13607
>>> Err: 0 (0,00%)
>>>
>>> pool size 8
>>> summary = 120000 in 00:02:22 = 842,8/s Avg: 653 Min: 2 Max:
>>16502
>>> Err: 0 (0,00%)
>>>
>>> pool size 4
>>> summary = 120000 in 00:02:22 = 843,7/s Avg: 674 Min: 2 Max:
>>8114 Err:
>>> 0 (0,00%)
>>>
>>> pool size 2
>>> summary = 120000 in 00:02:49 = 709,6/s Avg: 801 Min: 2 Max:
>>9215 Err:
>>> 0 (0,00%)
>>>
>>> My interpretation is: a pool helps to keep the db happy, but it has
>>to be
>>> sized appropriately (whatever that means)
>>>
>>> I think the result would be even more in favour of the pool, if the
>>db would
>>> not have fitted in RAM and would induce a io bottleneck by the
>>parallel db
>>> processes. Plus, I had to configure my postgresql instance to allow
>>more
>>> than 100 simultaneous connections.
>>
>>I don't doubt that using a pool helps.
>>
>>>>
>>>>
>>>>> Second: When a pool is used (at least the dbcp2 pool) the
>>connections
>>>>> seem
>>>>> to be stored in a stack like construct. So in a uncontended load
>>>>> situation
>>>>> the pool will use only a fraction of the configured size and in a
>>>>> contended
>>>>> situation with really many threads it will probably overload the
>>db.
>>>>
>>>> That suggests that pooling should not be used by JMeter...
>>>
>>> Why? When we are not using a pool, JMeter will overload the db also.
>>>>
>>>>
>>>>> So all in all I would rather change the sentence to something like
>>"If
>>>>> you
>>>>> want to use shared pooling, then set the max count to something
>>>>> sensible".
>>>>
>>>> I think we need to document why pooling is not in general a good
>>idea
>>>> for JMeter tests.
>>>
>>> I would rather document why and when pooling should be used.
>>>
>>> I have at least two use cases where pooling is useful:
>>> * using the jdbc sampler to fill variables which are then used
>>further in
>>> the test
>>
>>That is possibly a valid use case, however this should be done in a
>>setUp ThreadGroup, so performance is not so much of an issue.
>>Besides, if the setup takes so long that it needs a pool, it should
>>probably be done using a database bulk-load facility.
>
> I would use it like a csv reader on steroids and thus it wouldn't help to put it into a setup group.
I see. That makes more sense.
So you could configure the pool to have fewer than the number of
threads to reduce the max DB load.
Since the threads are presumably doing other things between JDBC
calls, it's unlikely that they will all be wanting to access the DB
simultaneously, so waits will be uncommon.
In this case the pool implementation is not important so long as it works OK.
Another approach would be to create a new test element and put the
pooling there instead.
That would likely be much easier to use.
For database population, where threads are likely to spend almost all
of their time updating the database, a pool won't really help.
Just configure the thread count to the max desired DB connections.
Pooling still does not make sense for testing app servers unless the
appropriate pool implementation can be specified.
>>
>>> * simulating the jdbc statements of an application server, which
>>would use
>>> a pool in itself
>>
>>In which case, it is important to be able to use the same pool as the
>>application server.
>
> It would be nicer for sure, but no pool would be even worse.
>
>>
>>>>
>>>>
>>>> However I would prefer to drop pooling support entirely unless the
>>>> pool implementation can be provided by the user.
>>>
>>> The option of making the pool implementation configurable is open to
>>us.
>>>
>>> Regards,
>>> Felix
>>>>
>>>>
>>>>> Regards,
>>>>> Felix
>>>
>>>
>
Re: doc for jdbc pool configuration
Posted by Felix Schumacher <fe...@internetallee.de>.
Am 5. Januar 2016 23:38:45 MEZ, schrieb sebb <se...@gmail.com>:
>On 5 January 2016 at 21:19, Felix Schumacher
><fe...@internetallee.de> wrote:
>> Am 04.01.2016 um 16:21 schrieb sebb:
>>>
>>> On 2 January 2016 at 15:38, Felix Schumacher
>>> <fe...@internetallee.de> wrote:
>>>>
>>>> Hi all,
>>>>
>>>> in our documentation there is one section about the sizing of the
>pool,
>>>> which I like to discuss:
>>>>
>>>> "If you really want to use shared pooling (why?), then set the max
>count
>>>> to
>>>> the same as the number of threads to ensure threads don't wait on
>each
>>>> other."
>>>>
>>>> First: I think pooling is a valid option even for user-centric
>scenarios.
>>>> Think about simulating the sql requests of an application server.
>In such
>>>> a
>>>> case a pool would have been used, so why not when simulating it?
>>>>
>>>> So for this part, I question the part "...(why?)..." and the
>"really" in
>>>> front of it.
>>>
>>> The problem is that JMeter is then mainly testing the pool
>>> implementation rather than the server.
>>> Since the pool implementation cannot at present be replaced, that
>does
>>> not seem to be a useful approach.
>>
>> In my observations the actual pooling part in jmeter is rather low
>overhead
>> compared to jdbc or other jmeter code, so I think the pooling
>implementation
>> is not that relevant compared to the fact to use a pool.
>>
>> I have done some totally unscientific tests using a local postgresql
>> instance with a little database and a simple select (with two joins,
>so a
>> bit more involved than just "select 1"). The laptop had four cores
>and
>> enough memory not to swap.
>>
>> For 600 threads 100 loops and different pool sizes I got the
>following
>> results:
>>
>> pool size 0 (every thread has its own pool)
>> summary = 120000 in 00:02:28 = 811,0/s Avg: 463 Min: 2 Max:
>17700
>> Err: 0 (0,00%)
>> memory went quite low and load went up to 400
>>
>> pool size 64
>> summary = 120000 in 00:02:22 = 844,8/s Avg: 638 Min: 2 Max:
>18797
>> Err: 0 (0,00%)
>>
>> pool size 32
>> summary = 120000 in 00:02:22 = 843,7/s Avg: 633 Min: 2 Max:
>15679
>> Err: 0 (0,00%)
>>
>> pool size 16
>> summary = 120000 in 00:02:23 = 840,9/s Avg: 653 Min: 2 Max:
>13607
>> Err: 0 (0,00%)
>>
>> pool size 8
>> summary = 120000 in 00:02:22 = 842,8/s Avg: 653 Min: 2 Max:
>16502
>> Err: 0 (0,00%)
>>
>> pool size 4
>> summary = 120000 in 00:02:22 = 843,7/s Avg: 674 Min: 2 Max:
>8114 Err:
>> 0 (0,00%)
>>
>> pool size 2
>> summary = 120000 in 00:02:49 = 709,6/s Avg: 801 Min: 2 Max:
>9215 Err:
>> 0 (0,00%)
>>
>> My interpretation is: a pool helps to keep the db happy, but it has
>to be
>> sized appropriately (whatever that means)
>>
>> I think the result would be even more in favour of the pool, if the
>db would
>> not have fitted in RAM and would induce a io bottleneck by the
>parallel db
>> processes. Plus, I had to configure my postgresql instance to allow
>more
>> than 100 simultaneous connections.
>
>I don't doubt that using a pool helps.
>
>>>
>>>
>>>> Second: When a pool is used (at least the dbcp2 pool) the
>connections
>>>> seem
>>>> to be stored in a stack like construct. So in a uncontended load
>>>> situation
>>>> the pool will use only a fraction of the configured size and in a
>>>> contended
>>>> situation with really many threads it will probably overload the
>db.
>>>
>>> That suggests that pooling should not be used by JMeter...
>>
>> Why? When we are not using a pool, JMeter will overload the db also.
>>>
>>>
>>>> So all in all I would rather change the sentence to something like
>"If
>>>> you
>>>> want to use shared pooling, then set the max count to something
>>>> sensible".
>>>
>>> I think we need to document why pooling is not in general a good
>idea
>>> for JMeter tests.
>>
>> I would rather document why and when pooling should be used.
>>
>> I have at least two use cases where pooling is useful:
>> * using the jdbc sampler to fill variables which are then used
>further in
>> the test
>
>That is possibly a valid use case, however this should be done in a
>setUp ThreadGroup, so performance is not so much of an issue.
>Besides, if the setup takes so long that it needs a pool, it should
>probably be done using a database bulk-load facility.
I would use it like a csv reader on steroids and thus it wouldn't help to put it into a setup group.
>
>> * simulating the jdbc statements of an application server, which
>would use
>> a pool in itself
>
>In which case, it is important to be able to use the same pool as the
>application server.
It would be nicer for sure, but no pool would be even worse.
>
>>>
>>>
>>> However I would prefer to drop pooling support entirely unless the
>>> pool implementation can be provided by the user.
>>
>> The option of making the pool implementation configurable is open to
>us.
>>
>> Regards,
>> Felix
>>>
>>>
>>>> Regards,
>>>> Felix
>>
>>
Re: doc for jdbc pool configuration
Posted by sebb <se...@gmail.com>.
On 5 January 2016 at 21:19, Felix Schumacher
<fe...@internetallee.de> wrote:
> Am 04.01.2016 um 16:21 schrieb sebb:
>>
>> On 2 January 2016 at 15:38, Felix Schumacher
>> <fe...@internetallee.de> wrote:
>>>
>>> Hi all,
>>>
>>> in our documentation there is one section about the sizing of the pool,
>>> which I like to discuss:
>>>
>>> "If you really want to use shared pooling (why?), then set the max count
>>> to
>>> the same as the number of threads to ensure threads don't wait on each
>>> other."
>>>
>>> First: I think pooling is a valid option even for user-centric scenarios.
>>> Think about simulating the sql requests of an application server. In such
>>> a
>>> case a pool would have been used, so why not when simulating it?
>>>
>>> So for this part, I question the part "...(why?)..." and the "really" in
>>> front of it.
>>
>> The problem is that JMeter is then mainly testing the pool
>> implementation rather than the server.
>> Since the pool implementation cannot at present be replaced, that does
>> not seem to be a useful approach.
>
> In my observations the actual pooling part in jmeter is rather low overhead
> compared to jdbc or other jmeter code, so I think the pooling implementation
> is not that relevant compared to the fact to use a pool.
>
> I have done some totally unscientific tests using a local postgresql
> instance with a little database and a simple select (with two joins, so a
> bit more involved than just "select 1"). The laptop had four cores and
> enough memory not to swap.
>
> For 600 threads 100 loops and different pool sizes I got the following
> results:
>
> pool size 0 (every thread has its own pool)
> summary = 120000 in 00:02:28 = 811,0/s Avg: 463 Min: 2 Max: 17700
> Err: 0 (0,00%)
> memory went quite low and load went up to 400
>
> pool size 64
> summary = 120000 in 00:02:22 = 844,8/s Avg: 638 Min: 2 Max: 18797
> Err: 0 (0,00%)
>
> pool size 32
> summary = 120000 in 00:02:22 = 843,7/s Avg: 633 Min: 2 Max: 15679
> Err: 0 (0,00%)
>
> pool size 16
> summary = 120000 in 00:02:23 = 840,9/s Avg: 653 Min: 2 Max: 13607
> Err: 0 (0,00%)
>
> pool size 8
> summary = 120000 in 00:02:22 = 842,8/s Avg: 653 Min: 2 Max: 16502
> Err: 0 (0,00%)
>
> pool size 4
> summary = 120000 in 00:02:22 = 843,7/s Avg: 674 Min: 2 Max: 8114 Err:
> 0 (0,00%)
>
> pool size 2
> summary = 120000 in 00:02:49 = 709,6/s Avg: 801 Min: 2 Max: 9215 Err:
> 0 (0,00%)
>
> My interpretation is: a pool helps to keep the db happy, but it has to be
> sized appropriately (whatever that means)
>
> I think the result would be even more in favour of the pool, if the db would
> not have fitted in RAM and would induce a io bottleneck by the parallel db
> processes. Plus, I had to configure my postgresql instance to allow more
> than 100 simultaneous connections.
I don't doubt that using a pool helps.
>>
>>
>>> Second: When a pool is used (at least the dbcp2 pool) the connections
>>> seem
>>> to be stored in a stack like construct. So in a uncontended load
>>> situation
>>> the pool will use only a fraction of the configured size and in a
>>> contended
>>> situation with really many threads it will probably overload the db.
>>
>> That suggests that pooling should not be used by JMeter...
>
> Why? When we are not using a pool, JMeter will overload the db also.
>>
>>
>>> So all in all I would rather change the sentence to something like "If
>>> you
>>> want to use shared pooling, then set the max count to something
>>> sensible".
>>
>> I think we need to document why pooling is not in general a good idea
>> for JMeter tests.
>
> I would rather document why and when pooling should be used.
>
> I have at least two use cases where pooling is useful:
> * using the jdbc sampler to fill variables which are then used further in
> the test
That is possibly a valid use case, however this should be done in a
setUp ThreadGroup, so performance is not so much of an issue.
Besides, if the setup takes so long that it needs a pool, it should
probably be done using a database bulk-load facility.
> * simulating the jdbc statements of an application server, which would use
> a pool in itself
In which case, it is important to be able to use the same pool as the
application server.
>>
>>
>> However I would prefer to drop pooling support entirely unless the
>> pool implementation can be provided by the user.
>
> The option of making the pool implementation configurable is open to us.
>
> Regards,
> Felix
>>
>>
>>> Regards,
>>> Felix
>
>
Re: doc for jdbc pool configuration
Posted by Felix Schumacher <fe...@internetallee.de>.
Am 04.01.2016 um 16:21 schrieb sebb:
> On 2 January 2016 at 15:38, Felix Schumacher
> <fe...@internetallee.de> wrote:
>> Hi all,
>>
>> in our documentation there is one section about the sizing of the pool,
>> which I like to discuss:
>>
>> "If you really want to use shared pooling (why?), then set the max count to
>> the same as the number of threads to ensure threads don't wait on each
>> other."
>>
>> First: I think pooling is a valid option even for user-centric scenarios.
>> Think about simulating the sql requests of an application server. In such a
>> case a pool would have been used, so why not when simulating it?
>>
>> So for this part, I question the part "...(why?)..." and the "really" in
>> front of it.
> The problem is that JMeter is then mainly testing the pool
> implementation rather than the server.
> Since the pool implementation cannot at present be replaced, that does
> not seem to be a useful approach.
In my observations the actual pooling part in jmeter is rather low
overhead compared to jdbc or other jmeter code, so I think the pooling
implementation is not that relevant compared to the fact to use a pool.
I have done some totally unscientific tests using a local postgresql
instance with a little database and a simple select (with two joins, so
a bit more involved than just "select 1"). The laptop had four cores and
enough memory not to swap.
For 600 threads 100 loops and different pool sizes I got the following
results:
pool size 0 (every thread has its own pool)
summary = 120000 in 00:02:28 = 811,0/s Avg: 463 Min: 2 Max: 17700
Err: 0 (0,00%)
memory went quite low and load went up to 400
pool size 64
summary = 120000 in 00:02:22 = 844,8/s Avg: 638 Min: 2 Max: 18797
Err: 0 (0,00%)
pool size 32
summary = 120000 in 00:02:22 = 843,7/s Avg: 633 Min: 2 Max: 15679
Err: 0 (0,00%)
pool size 16
summary = 120000 in 00:02:23 = 840,9/s Avg: 653 Min: 2 Max: 13607
Err: 0 (0,00%)
pool size 8
summary = 120000 in 00:02:22 = 842,8/s Avg: 653 Min: 2 Max: 16502
Err: 0 (0,00%)
pool size 4
summary = 120000 in 00:02:22 = 843,7/s Avg: 674 Min: 2 Max: 8114
Err: 0 (0,00%)
pool size 2
summary = 120000 in 00:02:49 = 709,6/s Avg: 801 Min: 2 Max: 9215
Err: 0 (0,00%)
My interpretation is: a pool helps to keep the db happy, but it has to
be sized appropriately (whatever that means)
I think the result would be even more in favour of the pool, if the db
would not have fitted in RAM and would induce a io bottleneck by the
parallel db processes. Plus, I had to configure my postgresql instance
to allow more than 100 simultaneous connections.
>
>> Second: When a pool is used (at least the dbcp2 pool) the connections seem
>> to be stored in a stack like construct. So in a uncontended load situation
>> the pool will use only a fraction of the configured size and in a contended
>> situation with really many threads it will probably overload the db.
> That suggests that pooling should not be used by JMeter...
Why? When we are not using a pool, JMeter will overload the db also.
>
>> So all in all I would rather change the sentence to something like "If you
>> want to use shared pooling, then set the max count to something sensible".
> I think we need to document why pooling is not in general a good idea
> for JMeter tests.
I would rather document why and when pooling should be used.
I have at least two use cases where pooling is useful:
* using the jdbc sampler to fill variables which are then used further
in the test
* simulating the jdbc statements of an application server, which would
use a pool in itself
>
> However I would prefer to drop pooling support entirely unless the
> pool implementation can be provided by the user.
The option of making the pool implementation configurable is open to us.
Regards,
Felix
>
>> Regards,
>> Felix
Re: doc for jdbc pool configuration
Posted by sebb <se...@gmail.com>.
On 4 January 2016 at 18:28, Philippe Mouawad <ph...@gmail.com> wrote:
> On Mon, Jan 4, 2016 at 4:21 PM, sebb <se...@gmail.com> wrote:
>
>> On 2 January 2016 at 15:38, Felix Schumacher
>> <fe...@internetallee.de> wrote:
>> > Hi all,
>> >
>> > in our documentation there is one section about the sizing of the pool,
>> > which I like to discuss:
>> >
>> > "If you really want to use shared pooling (why?), then set the max count
>> to
>> > the same as the number of threads to ensure threads don't wait on each
>> > other."
>> >
>> > First: I think pooling is a valid option even for user-centric scenarios.
>> > Think about simulating the sql requests of an application server. In
>> such a
>> > case a pool would have been used, so why not when simulating it?
>> >
>> > So for this part, I question the part "...(why?)..." and the "really" in
>> > front of it.
>>
>> The problem is that JMeter is then mainly testing the pool
>> implementation rather than the server.
>>
>
> Not from Felix notes on bug. But read below.
>
>> Since the pool implementation cannot at present be replaced, that does
>> not seem to be a useful approach.
>>
>
> I implemented 3 classes corresponding to 3 major pools (DBCP2, Tomcat JDBC,
> HikariCP)
>
However, there are lots of other pools.
>
>>
>> > Second: When a pool is used (at least the dbcp2 pool) the connections
>> seem
>> > to be stored in a stack like construct. So in a uncontended load
>> situation
>> > the pool will use only a fraction of the configured size and in a
>> contended
>> > situation with really many threads it will probably overload the db.
>>
>> That suggests that pooling should not be used by JMeter...
>>
>
> I don't share your opinion.
In the context of the dbcp2 pool, it's just an observation, not an opinion.
> I really think proposing the pool is useful.
What is the use case?
>>
>> > So all in all I would rather change the sentence to something like "If
>> you
>> > want to use shared pooling, then set the max count to something
>> sensible".
>>
>> I think we need to document why pooling is not in general a good idea
>> for JMeter tests.
>>
> Ok
>
>>
>> However I would prefer to drop pooling support entirely unless the
>> pool implementation can be provided by the user.
>>
>
> I don't share your opinion for many reasons:
>
> - First there is no much remaining job to make it possible to test other
> pool:
> - Propose a Factory
> - We could even embed the 2 others that were tested
> - We improved as we dropped a deprected library, why drop now which
> means more work for less feature
However it's not possible for the user to drop in a brand new pool
without writing a factory class.
Unlike (say) with JDBC or JSR-223, where the user can use any
implementation which has the bindings.
> - From users comments I tend to think this feature is useful and users
> are expecting it
Examples of such comments?
> - Finally , we spent Felix and I some time working on this on our
> personal time, dropping it is just a waste of our time and work, which is
> discouraging for me to be frank with you
I have had a look at the dev list archives and the last time the issue
was discussed (August 2012) I made it clear that it only made sense to
include pooling if the implementation was configurable.
And when I raised the issue on the user list as I recall there was no
feedback saying that the feature was needed.
It's unfortunate that I was away over Xmas otherwise I would have
raised my concerns a bit earlier.
However I don't see any recent discussion on pooling, so I'm not sure
how I could have raised the issue before work was started.
==
I still think that JBDC pooling is unnecessary.
I have yet to see a good use case.
Also if there is a use case, that it only makes sense to provide
pooling if the implementation is completely configurable.
>
>
>> > Regards,
>> > Felix
>>
>
>
>
> --
> Cordialement.
> Philippe Mouawad.
Re: doc for jdbc pool configuration
Posted by Philippe Mouawad <ph...@gmail.com>.
On Mon, Jan 4, 2016 at 4:21 PM, sebb <se...@gmail.com> wrote:
> On 2 January 2016 at 15:38, Felix Schumacher
> <fe...@internetallee.de> wrote:
> > Hi all,
> >
> > in our documentation there is one section about the sizing of the pool,
> > which I like to discuss:
> >
> > "If you really want to use shared pooling (why?), then set the max count
> to
> > the same as the number of threads to ensure threads don't wait on each
> > other."
> >
> > First: I think pooling is a valid option even for user-centric scenarios.
> > Think about simulating the sql requests of an application server. In
> such a
> > case a pool would have been used, so why not when simulating it?
> >
> > So for this part, I question the part "...(why?)..." and the "really" in
> > front of it.
>
> The problem is that JMeter is then mainly testing the pool
> implementation rather than the server.
>
Not from Felix notes on bug. But read below.
> Since the pool implementation cannot at present be replaced, that does
> not seem to be a useful approach.
>
I implemented 3 classes corresponding to 3 major pools (DBCP2, Tomcat JDBC,
HikariCP)
>
> > Second: When a pool is used (at least the dbcp2 pool) the connections
> seem
> > to be stored in a stack like construct. So in a uncontended load
> situation
> > the pool will use only a fraction of the configured size and in a
> contended
> > situation with really many threads it will probably overload the db.
>
> That suggests that pooling should not be used by JMeter...
>
I don't share your opinion. I really think proposing the pool is useful.
>
> > So all in all I would rather change the sentence to something like "If
> you
> > want to use shared pooling, then set the max count to something
> sensible".
>
> I think we need to document why pooling is not in general a good idea
> for JMeter tests.
>
Ok
>
> However I would prefer to drop pooling support entirely unless the
> pool implementation can be provided by the user.
>
I don't share your opinion for many reasons:
- First there is no much remaining job to make it possible to test other
pool:
- Propose a Factory
- We could even embed the 2 others that were tested
- We improved as we dropped a deprected library, why drop now which
means more work for less feature
- From users comments I tend to think this feature is useful and users
are expecting it
- Finally , we spent Felix and I some time working on this on our
personal time, dropping it is just a waste of our time and work, which is
discouraging for me to be frank with you
> > Regards,
> > Felix
>
--
Cordialement.
Philippe Mouawad.
Re: doc for jdbc pool configuration
Posted by sebb <se...@gmail.com>.
On 2 January 2016 at 15:38, Felix Schumacher
<fe...@internetallee.de> wrote:
> Hi all,
>
> in our documentation there is one section about the sizing of the pool,
> which I like to discuss:
>
> "If you really want to use shared pooling (why?), then set the max count to
> the same as the number of threads to ensure threads don't wait on each
> other."
>
> First: I think pooling is a valid option even for user-centric scenarios.
> Think about simulating the sql requests of an application server. In such a
> case a pool would have been used, so why not when simulating it?
>
> So for this part, I question the part "...(why?)..." and the "really" in
> front of it.
The problem is that JMeter is then mainly testing the pool
implementation rather than the server.
Since the pool implementation cannot at present be replaced, that does
not seem to be a useful approach.
> Second: When a pool is used (at least the dbcp2 pool) the connections seem
> to be stored in a stack like construct. So in a uncontended load situation
> the pool will use only a fraction of the configured size and in a contended
> situation with really many threads it will probably overload the db.
That suggests that pooling should not be used by JMeter...
> So all in all I would rather change the sentence to something like "If you
> want to use shared pooling, then set the max count to something sensible".
I think we need to document why pooling is not in general a good idea
for JMeter tests.
However I would prefer to drop pooling support entirely unless the
pool implementation can be provided by the user.
> Regards,
> Felix