Re: shared server vs. connection pooling - speed, scalability - some questions

MTNorman wrote:
> If your apps create a new connection for every query - then you
> already know your biggest performance and scalability issue.

Yes. That's why we went to mod-perl with apache in order to cache connections. This allows us to re-use a connection for the lifetime of the apache child process - many pages without having to create a connection.

> Shared server works very well for transaction processing type
> connections. Remember the connection pool is built at the time the
> session is created. As long as no connection in the pool actively
> uses the connection for any lengthy duration, every thing works
> well.
>
> What you probably discovered in your earlier implementation effort is
> that shared server does not work at all well if any one connection in
> the pool is active for any duration of more than 1 second. All the
> other connections in the pool are effectively shut off from database
> resources while they wait for the long running transaction to
> complete. Batch processes (including any reports that run more than
> 1-2 seconds) should use dedicated connections.

Yes, I found references to this in my reading. I intend to discuss this with our dba.

> You can use the sqlnet.ora file to set the shared or dedicated
> connection mode, or you can define multiple service names for the
> database to allow most connections to use shared server and your batch
> and reporting processes to run on dedicated connections.
>
> In my experience, a good shared server implementation (where all the
> long running processes are correctly running on dedicated connections)
> has better overall performance than a dedicated server setup that is
> nearly at the hardware and OS limits.
>
> BTW - you don't state how many connections are driving the shared
> server consideration. Many DBAs do not even consider shared server
> until at least 1,000 concurrent connections are reached - assuming
> adequate RAM is available on a dedicated database server. You won't
> find as many articles on shared server as you could find on the former
> MTS (multi threaded server). Cheaper RAM, high OS process limits, and
> application server connection pooling have made this option much less
> needed than it was when 8i was the latest and greatest.

We've upped the max connections to somewhere around 1500. We only have a problem when something causes the database to respond slowly (conflicts, bad indices, etc.) As requests to the database back up, the web server processes have to wait, which causes more web server processes to be spawned to handle page requests, which leads to more database requests and eventually, things go to hell :-)

Thanks for your comments. They are quite useful to help me understand what I'm dealing with.

Jim Received on Tue Feb 27 2007 - 22:06:11 CST