ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: [SURVEY] How many connections are in your pool?
Date Wed, 21 Jan 2009 04:12:39 GMT
Absolutely.  In addition to general resource contention (CPU, Disk I/O
etc.), you also have to consider lock contention against the database tables
themselves.  Relational databases do not scale well in this regard.  Throw
as much CPU power and hardware against your database as you like, as soon as
you lock a table, the game is over, and everyone else has to wait.

But to address fatboysuns (and Rick's and Nathan's) question of:  "aren't
number of connections in a pool in relation to the number of parallel users
that access the application than the number of CPU cores in a database?"

The answer is no... historically databases allow for hundreds of concurrent
connections (Oracle defaults to 1000) because it was originally intended
that end users connect directly to the database.  SQL was originally
intended as an end-user command-line interface to the database.  Eventually
it was decided that it was too complex for end users, so we threw UIs at it
(anyone ever use Oracle Forms?)... still 1 user per connection.

But while they allowed 1000's of connections at a time, this did not imply
that ALL 1000 could be active with a transaction or a query at the same
time.  And back then, since the number of users was actually quite low, and
the speed at which the transactions occurred was generally limited by how
fast a person could enter data into a form, it was a fairly low risk.

But now with N-Tier architecture and web applications that service thousands
users, this does not mean that we can just bump the number of connections in
the pool up to 1000 and be done with it.

The number of effective transactions/queries allowed at any given time
should be constrained artificially to avoid creating too much contention on
the database resources.

I usually use 2 or 3 times the number of CPUs to:

 *  allow for some low level optimization of the threads, as it has to wait
for disk I/O and modern hardware allows for pretty deep pipelines of queued
"work",
 * allow for some opportunistic parallel processing (especially in databases
with LOTS of tables and mutually exclusive access to those tables),
 * latency if the Java app does have additional processing between
transaction steps (which should be avoided if at all possible).

2 - 3 times is reasonable, even up to 5 times.  If it was 10 times, I'd
start to wonder....

But over 100 times is terribly odd and I can't imagine how that could be
good for performance.  It seems to me it's just an opportunity for tons of
stale connections, wasted resources, deadlocking, and excessive resource
contention.

The best place to block is high in the app architecture.  On a 8 core app
server and an 8 core database server, I might allow 48 concurrent threads on
the app server (half of which will often be waiting for the DB at any given
time) and 24 on the database server.

Cheers,
Clinto

On Tue, Jan 20, 2009 at 1:12 PM, Nicholoz Koka Kiknadze
<kiknadze@gmail.com>wrote:

> Hi Sundar,
>
> I am not an hardware expert, but I suspect that even with modern dma access
> etc if you ask your CPU to process N database transactions (initiated by
> different users) in parallel it may take longer compared to when you ask it
> to do them consequently. So quite possible that pools with connection number
> > CPU number induce performence penalties. In other words the time your pool
> waits for a connection to get available in the pool is just caused by your
> hardware (CPU) beeing busy, so why add extra latency with extra pool code...
>
> Again, of course the logic can not applyed to long running transactions
> when CPU is idling in the midst of transaction waiting for e.g. extra user
> input.
>
>
> On Tue, Jan 20, 2009 at 2:50 PM, Sundar Sankar <fatboysuns@gmail.com>wrote:
>
>> Hi Clinton,
>>                   I apologize ahead, if I am missing or not getting
>> something right. As far as my understanding goes, arent number of
>> connections in a pool in relation to the number of parallel users that
>> access the application than the number of CPU cores in a database?
>>
>> Regards
>> S
>>
>>
>> On Tue, Jan 20, 2009 at 12:39 PM, Clinton Begin <clinton.begin@gmail.com>wrote:
>>
>>> It sounds like you're still using a "pool", but your max, min, idle, and
>>> active connections are all equal (i.e. 16).  Otherwise, how do you allocate
>>> connections to the incoming requests?
>>>
>>> Cheers,
>>> Clinton
>>>
>>>
>>> On Tue, Jan 20, 2009 at 12:33 PM, Nicholoz Koka Kiknadze <
>>> kiknadze@gmail.com> wrote:
>>>
>>>> Ours is an application that requires guaranteed response times under 50
>>>> ms, so:
>>>>
>>>> 1) We dropped using any kind of pool, so that
>>>> 2) number of constantly open connections equals to the number of
>>>> processors (16)
>>>>
>>>> 3) I know you were asking about pool, but still I dared to respond with
>>>> this no-pool variant because I think maybe what you are asking can be
>>>> reformulated as: is there any use of DB pool in a short lived transaction
>>>> scenario, or its better to have one connection per CPU. Testing our app made
>>>> us to drop using pool with TimesTen (in memory) database. Now I started to
>>>> suspect that using using db pool (I've mostly used dbcp ) in other less
>>>> demanding projects (but again w/o long running transactions) was just saving
>>>> development time (let pool handle concurrency issues), but not any
>>>> substantial performance gain. Wonder what others think...
>>>>
>>>>
>>>>
>>>>
>>>> On Tue, Jan 20, 2009 at 8:43 AM, Clinton Begin <clinton.begin@gmail.com
>>>> > wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> I've been studying a few large enterprise applications and have noticed
>>>>> an interesting trend... many of these apps have HUNDREDS of connections
>>>>> (like 600) available or even open in their connection pools...
>>>>>
>>>>> Survey Questions:
>>>>>
>>>>>   1. How many connections do you have available in your pool?
>>>>>   2. And if you know, how many CPU cores are available on your database
>>>>> server (or cluster)?
>>>>>   3. If you have 2x or 3x more connections than you do CPUs, do you
>>>>> have a reason that you could share?
>>>>>
>>>>> Cheers,
>>>>> Clinton
>>>>>
>>>>
>>>>
>>>
>>
>

Mime
View raw message