Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 82022 invoked from network); 21 Jan 2009 05:48:07 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 21 Jan 2009 05:48:07 -0000 Received: (qmail 14665 invoked by uid 500); 21 Jan 2009 05:48:01 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 14652 invoked by uid 500); 21 Jan 2009 05:48:01 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 14643 invoked by uid 99); 21 Jan 2009 05:48:01 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 20 Jan 2009 21:48:01 -0800 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of fatboysuns@gmail.com designates 209.85.146.177 as permitted sender) Received: from [209.85.146.177] (HELO wa-out-1112.google.com) (209.85.146.177) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Jan 2009 05:47:54 +0000 Received: by wa-out-1112.google.com with SMTP id m28so812386wag.0 for ; Tue, 20 Jan 2009 21:47:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=W+i0aOdHLcjM0yPs2OuSIM822BX1WeybXZFmmcLY3kc=; b=oTK6MZABtG7bEqEd11TpPMPMuSe1ypZFF9S+OI6QxL92KzdaKW2tFMlUVNj87ASqp3 qR5W9rDlY84tTCcNOGghnoluFbaJlpZ9JQUl/kruojaE+pI7uAvST2aE66m4ePquUqn4 b2vFhW48YwRyx9V9PUbWhM8Q3dxmY5v6D9cLI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=J8DhL/pFej0mZRZA0d1gvhdGAYGlnVwtneVGRIx9gdlJpn/yBx9KsvciGgwvT/SBRy 5jC/Js3tqlwsMJC6yl68kXxwGyHgrCYDKbk7uLk+a9q5kgAmRvPIgiPWE28IHBdN4ZxQ asXG76rSx29YMDM7Aj4xUGk+RLrBmgCQETgfY= MIME-Version: 1.0 Received: by 10.114.198.5 with SMTP id v5mr1473195waf.131.1232516854023; Tue, 20 Jan 2009 21:47:34 -0800 (PST) In-Reply-To: <16178eb10901202012v5238f874n37693ea9d269888b@mail.gmail.com> References: <16178eb10901200543s160f8e78h62d2fd35487fb162@mail.gmail.com> <536e8800901201133w692015d5ia8882b1c144a45b8@mail.gmail.com> <16178eb10901201139u5f35c67bl74c41187e92e88c6@mail.gmail.com> <536e8800901201212k7505d48dgf2537076e3dccbbc@mail.gmail.com> <16178eb10901202012v5238f874n37693ea9d269888b@mail.gmail.com> Date: Tue, 20 Jan 2009 22:47:33 -0700 Message-ID: Subject: Re: [SURVEY] How many connections are in your pool? From: Sundar Sankar To: user-java@ibatis.apache.org Content-Type: multipart/alternative; boundary=0016363b7e9c4385bc0460f7b1fb X-Virus-Checked: Checked by ClamAV on apache.org --0016363b7e9c4385bc0460f7b1fb Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Thanks So much Clinton. That was terrific! -Sundar On Tue, Jan 20, 2009 at 9:12 PM, Clinton Begin 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 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 >> > 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 >>>>>> >>>>> >>>>> >>>> >>> >> > --0016363b7e9c4385bc0460f7b1fb Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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.&nb= sp; Relational databases do not scale well in this regard.  Throw as m= uch CPU power and hardware against your database as you like, as soon as yo= u 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 allo= w 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 no= t 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 quit= e low, and the speed at which the transactions occurred was generally limit= ed by how fast a person could enter data into a form, it was a fairly low r= isk.

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

The number of effective transactions/queries allowed at any given time shou= ld 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 pipe= lines of queued "work",
 * allow for some opportunistic p= arallel processing (especially in databases with LOTS of tables and mutuall= y exclusive access to those tables),
 * latency if the Java app does have additional processing between tra= nsaction 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 cou= ld be good for performance.  It seems to me it's just an opportuni= ty for tons of stale connections, wasted resources, deadlocking, and excess= ive resource contention.

The best place to block is high in the app architecture.  On a 8 c= ore app server and an 8 core database server, I might allow 48 concurrent t= hreads 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 acces= s 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 numb= er > CPU number induce performence penalties. In other words the time yo= ur 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 poo= l 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,
&n= bsp;            = ;     I apologize ahead, if I am missing or not getting= something right. As far as my understanding goes, arent number of connecti= ons in a pool in relation to the number of parallel users that access the a= pplication 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, ho= w do you allocate connections to the incoming requests?

Cheers,
<= font color=3D"#888888">Clinton


On Tue, Jan 20, 2009 at 12:33 PM, Nicholoz K= oka 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 c= onstantly open connections equals to the number of processors (16)

3) I know you were asking about pool, but still I dared to respond with thi= s no-pool variant because I think maybe what you are asking can be reformul= ated 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 d= rop 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 deman= ding projects (but again w/o long running transactions) was just saving dev= elopment 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, Cli= nton Begin <clinton.begin@gmail.com> wrote:
Hi all,

I've been studying a few large enterprise applications a= nd 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 ava= ilable in your pool? 
  2. And if you know, how many CPU core= s are available on your database server (or cluster)?
  3. If you h= ave 2x or 3x more connections than you do CPUs, do you have a reason that y= ou could share?

Cheers,
Clinton






--0016363b7e9c4385bc0460f7b1fb--