As an Oracle DBA you may also be tasked with
performance tuning of the apache server. This can be an issue since
there are very few documents about this. However, I suggest you
start with the installation guide from Oracle as it has many good
tips for tuning the apache side of things.
However, let?s look at an actual client and
tuning situation.
Test Case:
I arrived on site Monday. It is a HP9000 L2000
server with 4 gig of memory and 4 processors. It is running Oracle
8.1.6.1 and 11.5.2 of Apps on B11.0.0 U HPUX. When I got here they
could run 10 processes against the Oracle DB (no I am not
kidding...just 10!) and would peak out memory and then CPU at 4 hour
intervals when we would have to bounce the webserver/forms server
and do some zombie slaying to get back memory.
After a couple of false starts I did some web
research and found out that Apache clients may have memory leaks (of
course they blame it on the OS libraries) and JDBC may as well.
So...how to fix?
Well edited the httpd.conf file and adjusted
MaxRequestsPerChild (which is actually MaxConnectionsPerChild) from
its default of 0 which means UNLIMITED to 2500 (one source said as
low as 1200.) Each time a process connected the memory leak caused
the child process to grow until finally all memory disappears (gee
sounds like real kids...) Anyway...We also where experiencing large
numbers of net processes in the FIN_WAIT_2 state which means it
finished what it was doing, sent a fin signal to the client and got
no response (bad programming practice...Oracle take notice.)
The killing of FIN_WAIT_2 depends on either the
KeepAlive option in httpd.conf (which defaults to ON so no slaying)
or the TCP server value for tcp_keepalive_interval which defaults
usually to 7200000 milliseconds (2 hours.) I set KeepAlive to OFF
and the TCP setting to 45000 milliseconds. The problem was that the
quasi-zombies where hanging around for 2 hours holding resources
(they would retain their pid but the pid of the owner would switch
to 1 and the terminal to ?), but the rate at which they where being
killed was slower than the buildup rate. By adjusting the timing of
the kill process we balanced the create/destroy so that they didn't
build up past a certain point.
The final result was, I didn't have to restart
all day and we were able to serve 31+ clients with no dead processes
and the FIN_WAIT_2 processes leveled out at a value around 90 and
stayed there.
To see (on most UNIX versions) the processes in
fin_wait_2 just do a netstat and grep for WAIT:
$ netstat|grep ?I WAIT
and include the URL for the page.
