 |
|
Oracle Tips by Burleson |
Inter- and Intranet
Connections
In recent years the internet and company
intranets have virtually taken over the domain of the Oracle NET8
and Oracle Net processes. Actually, I should say they have added to
the process stack. Let me explain. In an inter or intranet
connection an Application Server (usually Apache since that is what
is shipped with Oracle) is configured to listen for HTTP protocol
connection requests (usually on port 8088) when on is received it is
passed on as a connection to the database server (identified in a
DAD configuration file). Once the database server receives the
connection request it is processed identically as would be a client
server connection request.
The addition of the Application server adds
another layer of complexity to the management of the Oracle
connection process. If not properly setup and tuned the Apache
server can be a performance nightmare for the Oracle DBA. Usually
the Oracle DBA will be saddled with the care and feeding of the
Apache server since it is supplied with the Oracle software.
In dealing with several Apache server
installations I have come across a couple of tips that I want to
share in regards to Apache service performance. The techniques
appear valid on both HP and Sun installs but I have not seen the
problems discussed on Windows or Linux based platforms. In general
the problems deal with the usage of the KeepAlive variables and how
the clients, servers and application servers signal that a process
is finished.
Essentially, when the server closes a TCP
connection, it sends a packet with the FIN bit set to the client,
which then responds with a packet with the ACK bit set. The client
then sends a packet with the FIN bit set to the server, which
responds with an ACK and the connection is closed. The state that
the connection is in during the period between when the server gets
the ACK from the client and the server gets the FIN from the client
is known as FIN_WAIT_2. At least on HP and Solaris this final FIN
from the client doesn't seem to be being sent when the Apache server
is used. I discovered this first on an HP client running Oracle
Financials.
The server was a HP9000 L2000 with 4 gig of
memory and 4 processors. It was running 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 and would peak out memory and then CPU at 4
hour intervals when they 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.
The lack of the FIN_WAIT_2 processes from closing and the multiple
connection attempts to the clients filled process slots and ate
memory until reboot was required.
I edited the httpd.conf file and adjusted
MaxRequestsPerChild (which is actually MaxConnectionsPerChild) from
its default of 0, which means UNLIMITED, to 2500 (some people have
adjusted this down as far as 300). Each time a process connected the
memory leak caused the child process to grow until finally all
memory disappears (gee sounds like real kids...) This solved the
memory leak problem, but what about the FIN_WAIT_2 processes?
The killing of FIN_WAIT_2 depends on either
the KeepAlive option in httpd.conf (which defaults to ON 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,
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.
See Code Depot
www.oracle-script.com |
