Re: Limiting # of connections to remote Oracle

Stig Sćther Bakken wrote: > > ["Larry Osborn" <larry@napster.com>] > > Greetings all, > > We are having serious issues with PHP4.0.5 using OCI8 and Apache 1.3.17. > > > > We have 4 webservers that handle our various needs for oracle. Each server > > has its MaxSpareServers in the httpd.conf set to 100. So that turns out to > > be 400 connections to the Oracle database. We are not using persistent > > connections, because that makes the problem worse. Does anyone know of a > > way to limit the number of database connections without having to turn away > > users by limiting the number of apache processes? > > > > We are using php+apache+oci8 to subscribe and unsubscribe people to our > > mailing list. We have simple select and update queries using non-persistent > > connections. Im not too sure what other information I would need to > > provide, Im just a webdeveloper, not a sysadmin, so please bear that in mind > > :) > > > > Thanks for any help you can give me. > > Hi, > > Oracle is able to handle hundreds and even thousands of simultaneous > connections if you configure it right. Try setting up (or have your > DBA set up) the Oracle server in multithreaded mode if it isn't > already, and use the dispatcher and the listener. The dispatcher > implements connection pooling for you, sharing a small number of > Oracle server processes between a lot of connections. This is an > ideal setup for web applications, it also relieves the web application > from having to think about connection pooling (although I think plogon > will be slightly faster, we never tested without it). > > I recently did a test with this setup, and it gave us 200+ tps with > 500 "users". Apache and the Oracle server were running on the same > machine, with 2x733 P3s and 2GB RAM. > > There's a lot of knobs in the connection setup you can tune to make it > work for you: the number of simultaneous connections per dispatcher > process (we used 250), the number of dispatcher processes (we used 8) > and the number of shared Oracle server processes (I _think_ we used > 8). Our Apache config had MaxSpareServers set to 450, and during the > test it pretty much kept all Apache processes busy, just to give you > an idea of the volume this setup can handle. You could also use additional software like sqlRelay (www.firstworks.com I believe) This works a little like connection pooling mechanism. Btw, if You use it and find it good solution, could send word or two about Your impression? Remigiusz -- -------------------------------------------------------------------/\------ Remigiusz Sokolowski e-mail: rsokolowski@wp-sa.pl / \ -----------------------------------------------------------------/____\-----