Thanks So much Clinton. That was terrific! -Sundar
On Tue, Jan 20, 2009 at 9:12 PM, Clinton Begin <clinton.be...@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 < > kikna...@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 <fatboys...@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.be...@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 < >>>> kikna...@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.be...@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 >>>>>> >>>>> >>>>> >>>> >>> >> >
