Re: PHP + Oracle + OCIPLogon

"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 - 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.