Re: PHP + Oracle + OCIPLogon

On Fri, Jun 22, 2001 at 02:47:39AM +0300, Rouvas Stathis wrote: > "Thies C. Arntzen" wrote: > > please send me a "minimal" testcase that shows this > > behaviour! i'll look into that then. > > I said that I'll have it on Monday, but curiosity got the better of > me:-) > > So, I ran my test case and these are my findings, alogn with the test. > > Machine Configuration > Software: > - Apache.1.3.12:httpd.conf:MaxClients 150 > - OS:SuSE.Linux.6.4 (2.2.14) > - Ora:Oracle.8.1.6.ee (default install) > I believe that by default you have 50 cursors per connection without even looking at the rest: if MaxClients is higher than the number of sessions oracle allows -> you are toasted! you have to expect _one_ persistent connection to each oracle-instance you connect to per running apache child! in your case expect up to 150 single connection. if you cannot support 150 single connection to your oracle-server - a) decrease MacClients -or- b) up your oracle-session limit! please get back to me if this is indeed your problem - i'll investigate further after that! (i'll look thru the rest of your mail when i find some time - sorry, just getting up) tc > - PHP:php.4.0.3.pl1 build as module for Apache > Hardware: > - AMD:Athlon:500 > - RAM:384MB > - HD:IDE > > Test case (PHP script) > This code is typical of the way I handle connections to Oracle (except > that I use OciLogon instead of OciPLogon) > <code> > <?php > $oraconnections[0] = ''; > $orastatements[0] = ''; > > function newOracleConnection() { > global $oraconnections; > $c = ociplogon("scott","tiger","oracle"); > $oraconnections[] = $c; > return $c; > } > > function newOracleStmt($connection, $select, $execute) { > global $orastatements; > $c = ociparse($connection, $select); > $orastatements[] = $c; > if (strtoupper($execute)=='EXECUTE') { ociexecute($c); } > return $c; > } > > function cleanup() { > global $orastatements, $oraconnections; > reset($orastatements); > while (list($key,$val)=each($orastatements)) @OciFreeStatement($val); > reset($oraconnections); > while (list($key,$val)=each($oraconnections)) @OCILogoff($val); > } > > $cone = NewOracleConnection(); > $cone2 = NewOracleConnection(); > $cone3 = NewOracleConnection(); > > $s = "select table_name t from user_tables"; > $stmt = NewOracleStmt($cone,$s,''); > OciDefineByName($stmt,strtoupper('t'),$t); > OciExecute($stmt); > while (OciFetch($stmt)) echo "\n table_name=$t"; > OciFreeStatement($stmt); > > // -- in case we forget > cleanup(); > > echo "\n"; > ?> > </code> > The above code runs in a separate window ("test-w") using the following > command: > ab -n 1000 -c 50 > http://aspasia.mm.di.uoa.gr/~rouvas/testora.php > > In another window (lets call it "monitor") the following runs in bash: > <bash> > while [ -f afiedt.buf ]; do > echo -n httpd: ps -elf | grep httpd | wc -l; > echo -en " "oracle: ps -elf | grep oracle | wc -l; > uptime; > done > </bash> > At the beginning, monitor displays something like: > > httpd: 6 oracle: 15 1:50am up 8 days, 5:38, 8 users, load average: > 1.33, 1.95, 3.49 > > >From now on, I will refer to "httpd: 6" and "oracle: 17" parts of the > above message as "httpd-counter" and "oracle-counter" respectively. > > Let the test begin! > > Everything is happening on the same machine. > Since I have not recorded all number I will show only significant ones, > that is those that caused troubles. The rest will be displayed as dashes > ("-"). > > ab -n n1 -c n2 <url> httpd-counter oracle-counter Failed requests > 500 10 - - 0 > 500 20 - - 0 > 900 50 - ~52 0 > The last did not cause any troubles, but... it took a long time for > these oracle processes to die. > So, I rerun the last invocation (900,50) three times serially, i.e. I > waited for the previous one to finish before I fired the next one. > 900 50 - ~48 0 > 900 50 ~67 many > 900 50 ~65 many > Next I fired up the following from three different windows > simultaneously (almost) > > ab -n 1000 -c 50 <url> > > In the first run: http-counter : - > oracle-counter : (below 50) > failed requests : (none) > > I waited until http-counter:7 and oracle-counter:17 and rerun the three > simultaneous invocations. > > httpd-counter : - > oracle-counter : 52 > failed-requests : (none) > > Without waiting, I fired the three invocations again. > httpd-counter : 75 (max) > oracle-counter : 82 (max) > failed-requests: 550 (test-w-1), 544 (test-w-2) and 493 9 9(test-w-3) > > After more runs (and to make a long story short), I observed that no > matter what the concurrency level or the number of requests to the ab > were, if oracle-counter reached about 55, no more requests would be > served. > That, of course, is consistent with the number of Oracle cursors > available. > What is not consistence, hence I think that OciPlogon is unsuitable in > its current implementation, is that while it proved extremely difficult > to run out of connections within a *single* invocation of my test script > with ab, it was trivial to do so when either (a) you invoke ab without > waiting enough time for the oracle-counter (number of Oracle processes) > to decrease or (b) you do parallel invocations. > > Of course, (b) is to be expected. But I didn't expect (a). > If a small number of concurrency(10) is used, (a) is rather difficult to > achieve. > Almost any number bigger than that will raise the problem. > > I would expect, that in (a) (serial invocations), PHP to reuse existing > connections and the oracle-counter to remain relatively constant. > Instead, it gradually increases, untill it reaches about 55, which in my > case, is the upper limit. > > In real world scenarios, you don't have control over what is happening, > so you quickly run out of connections. > > In favor of OciPLogon, I have to state that while with OciLogon I got 3 > requests/second (max), using OciPlogon got me an impressive 42 > requests/second (when no failed requests where observed). > > My interpretation: > While PHP is running is doing a very good job of reusing active > connections. > When it is unloaded/stopped it "forgets" some of them and starts opening > some new ones. > That behaviour may be caused by Apache as some clients that were > originally spawned to handle the load, gradually die without killing the > oracle connections that have been associated with them. When a new > request is made, if an existing Apache client is able to handle it, the > connections associated with it are re-used. If a new one is spawned, > then a new set of connections is made. > Hence, the gradual increase of the oracle-counter. > > About the same behaviour is observed when you "forget" to OciLogout, > which is to be expected. > > In all cases, if OciLogon is used no number of failed requests has been > observed. > Except the significantly slower response time, I find using OciLogon > results in more stable programs and so far I think it is more suitable > for sites experiencing heavy loads. > > I'm really looking forward to your reply > > -Stathis. > > PS.: Since it is now 3 o'clock in the morning here, please excuse any > typos and lax use of > English.