Performance Tuning Oracle DB/Apache CGI

From: Keg (rhugga_at_yahoo.com)
Date: 08/30/04 

Date: 30 Aug 2004 13:45:38 -0700

We are using a biotech package that is essentially a collection of CGI
programs ran under apache (2.0.50) that establish TCP connections to
our Oracle database on the same subnet. The network is only at 5%
utilization. The oracle database has a trunked 2gb copper (X1150A's)
network pipe and the apache machine has a 1gb fiber network
connection. The oracle db is tuned and we can't find any bottlenecks
there. The SGA looks perfect as well as disk I/O. The tablespaces this
application uses are all on dedicated raid 0+1 volumes (with
data/index/temp all seperated) Our SAN is hardly sweating at about 10%
utilization. Our HBA's are in 66mhz PCI slots and also don't seem to
be heavily hit.

CPU/memory/disk I/O are hardly tasked on either machines. The apache
machine is a Sun 280 with 2x900 Mhz procs and 8gb RAM (solaris 9
64-bit). It is the only application running on this machine and
everything seems idle when these tasks are running. (The apache
processes and subsequent CGI processes never consume more than 1% cpu
usage) Also note that I also tried running the apache components on an
idle E450 with 4gb memory and 4x400 Mhz procs (Solaris 8 64-bit).

I am now looking at mpstat and really don't know what is acceptable
performance or not. Our oracle box is an E4500 with 10 procs/10 gb
memory (solaris 9 64-bit). Here is an mpstat snapshot during one of
the CGI activites:

db-0204:/u01/app/oracle/product/9.2.0.4/network/admin #mpstat 3 100
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys
wt idl
  0 9 0 2349 400 300 175 1 2 17 0 129 3 2
0 95
  1 11 0 2052 111 108 179 2 5 18 0 177 6 2
0 91
  4 9 0 1682 107 104 178 1 4 15 0 161 4 2
0 94
  5 10 0 2139 104 101 188 2 5 18 0 185 7 2
0 90
  8 9 0 1829 226 223 154 1 4 20 0 163 4 2
0 94
  9 10 0 1677 150 147 175 2 5 18 0 187 7 2
0 91
 12 10 0 1626 104 102 178 1 4 15 0 156 4 2
0 94
 13 10 0 2012 103 101 182 1 5 18 0 167 6 2
0 92
 14 10 0 1556 106 104 179 1 4 16 0 166 4 2
0 94
 15 10 0 1970 104 101 184 2 5 18 0 178 7 2
0 91
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys
wt idl
  0 0 0 2148 402 300 162 4 16 32 0 142 3 1
0 96
  1 0 0 1548 108 101 201 8 20 35 0 143 3 2
0 95
  4 0 0 2372 103 101 136 4 18 32 0 143 2 2
0 96
  5 0 0 29 105 100 160 5 17 35 0 168 3 0
0 97
  8 0 0 2457 116 109 92 7 18 50 0 287 16 2
0 82
  9 0 0 8 194 189 179 4 16 27 0 155 2 0
0 97
 12 0 0 5581 113 101 82 12 12 19 0 332 32 3
0 65
 13 2 0 1980 107 101 243 5 17 31 0 153 3 1
0 97
 14 0 0 13 107 102 147 4 17 32 0 285 3 1
0 96
 15 0 0 8044 112 100 74 11 10 23 0 418 46 7
0 48
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys
wt idl
  0 0 0 3759 414 300 180 18 32 52 0 442 21 2
0 77
  1 0 0 702 125 104 366 22 40 90 0 406 6 2
0 93
  4 0 0 2849 127 103 253 25 41 68 0 541 19 3
1 77
  5 0 0 48 116 101 272 17 46 80 0 428 6 6
0 88
  8 0 0 5641 150 125 215 27 42 82 0 674 35 5
0 60
  9 0 0 5258 207 191 141 16 25 42 0 392 31 4
0 65
 12 0 0 3750 122 104 342 21 43 70 0 416 6 4
0 90
 13 0 0 261 119 102 227 18 46 95 0 456 7 1
0 92
 14 0 1 1427 126 102 279 27 44 83 0 619 13 1
1 85
 15 1 1 1486 123 100 283 24 47 79 0 443 7 2
0 90
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys
wt idl
  0 0 0 9076 404 300 105 12 11 37 0 420 41 5
0 54
  1 0 0 163 106 102 145 4 20 31 0 151 1 1
0 98
  4 0 0 38 105 101 104 5 17 41 0 264 3 0
0 97
  5 0 0 9746 112 100 100 10 5 22 0 380 47 12
0 41
  8 0 0 40 112 109 178 3 15 32 0 162 3 0
0 97
  9 0 0 1875 196 194 227 4 20 72 0 150 1 2
0 97
 12 0 0 917 105 104 198 3 18 35 0 162 1 2
0 97
 13 0 0 999 106 101 157 4 17 26 0 106 1 1
0 98
 14 0 0 2078 109 103 142 6 14 26 0 169 6 1
0 93
 15 0 0 57 103 101 168 2 16 35 0 208 2 0
0 98

Does anyone see anything that could be a bottleneck in performance? I
believe their code is the culprit, but they claim a significant
difference in performance on a similiar test environment at their
site. (A sample data import that they say took 4 minuntes on their
testbed took 35 minutes on my systems)

Thanks.
rhugga

Relevant Pages

  • Re: dynamic lib ignored even after "found" in "install_driver(Oracle) failed: Cant load..." cgi prob
    ... the solution i found is this: i moved the directory containing libclntsh.so under /usr/lib, which is a location that apache seems to trust, as opposed to where i had originally placed this directory. ... so, if i knew then what i know now, here are the steps _I_ would use were i to install the oracle instantclient such that the DBD::Oracle module would work when used in an apache 2.0 CGI: ... i also could run it this way by placing tnsnames.ora in my home directory as .tnsnames.ora, but this location will do me no good when i want apache to run the script as a cgi. ... am i missing some sort of configuration detail regarding apache and permissions granted a cgi? ...
    (perl.dbi.users)
  • Re: CGIs & CSS - References
    ... my $q = new CGI; ... > If the link tag method is considered to be sufficient, then hey, I'm ... (Naturally the stylesheets and js files are in both ... This makes me think it's Apache. ...
    (perl.beginners)
  • Re: Apache and Perl in Windows
    ... The cgi scripts execute, ... I am studying a spider book that uses Perl so I ... > file in apache and rename the jar files in the server directory. ...
    (comp.lang.perl.misc)
  • Re: Perl and IIS - script runs but The page cannot be displayed
    ... stew asked about CGI: the long answer is to perldoc CGI. ... Both the IIS and perl docs where crappy. ... I would love to have linux and apache etc but I'm stuck with a windows ...
    (comp.lang.perl.misc)
  • Re: Apache Security Issue: File Access
    ... > I just recognized that with Apache configured for VirtualHosts, ... > - no one can ready files using a file system call in CGI or ASP ... Does the target program name have a / or .. ... Does the user who owns the target script exist on the system? ...
    (comp.security.unix)