ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sundar Sankar <fatboys...@gmail.com>
Subject Re: [SURVEY] How many connections are in your pool?
Date Wed, 21 Jan 2009 05:47:33 GMT
Thanks So much Clinton. That was terrific!

-Sundar

On Tue, Jan 20, 2009 at 9:12 PM, Clinton Begin <clinton.begin@gmail.com>wrote:

> 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