Reducing size of connection pool to improve web application performance

I attended an Oracle sponsored day yesterday on Oracle’s engineered systems.  The main speaker was Tom Kyte who is a widely known Oracle performance expert.  After his talk was complete he graciously agreed to talk with me about performance issues with a web site that has hundreds of sessions to the database in a connection pool.  Tom said that the connection pool should be roughly twice the number of CPU cores and not many times that amount as they are in our case.  He recommended this Oracle produced youtube video that demonstrates that fewer sessions in a session pool produce better throughput:

http://www.youtube.com/watch?v=xNDnVOCdvQ0&feature=youtube_gdata_player

It isn’t immediately obvious to me why this is the case.

Tom Kyte recommended changing the application to use a smaller pool of connections or if that’s not possible to use shared servers.  In my case shared servers seems like a better option because it would take more effort to modify the application tier.  We could set the number of real connections to twice the number of cpus and allow the web servers to spin up hundereds of shared server connections instead of decidated server connections.  We need to test it to prove it out of course.  No guarantees until you test it in your own environment.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Reducing size of connection pool to improve web application performance

  1. Pingback: Faster commit time with fewer sessions | Bobby Durrett's DBA Blog

  2. Pingback: Shared servers results in lower CPU usage for top query in AWR | Bobby Durrett's DBA Blog

  3. Ciro Iriarte says:

    And the reference cpu count would be the one at the DB side or at the client side?

    • Bobby says:

      Ciro,

      The cpu count is on the database server. In our one production database server that uses shared servers we are now using a 3 to 1 ratio and it is working well. We have 12 cpus on our database server and one database instance with 36 shared servers. We started at 2 to 1 and bumped it up to 3 to 1.

      – Bobby

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.