Cleanup Handler, Class::DBI and Oracle

[David J Radunz <da...@staff.netspace.net.au>]

Hey All,

  I have a problem that for some reason at any given time there is 1200
connections open to our Oracle database. This of course means that
oracle keeps running out of memory which is bad. I know persistent
connections are good and all, but when they kill the database then it
becomes an issue. The reason there is 1200 connections is because we
have 7 servers that run between 150-300 child processes across 3
database schemas. Now I have turned off the usage of Apache::DBI however
the problem remains and I believe the cause is that Class::DBI acts in a
similar way to Apache::DBI and holds the connection open when it runs
under mod_perl.

  Having said all that, I think that it is possible to have Apache::DBI
running and continue to use Class::DBI. We have noticed that even if
there are only 30 child processes running, there are a lot more
connections to oracle than just the 30 child processes - Which makes me
think that when a child process is shut down in low-load times that it
does not disconnect from the database properly.

  My thought was that implementing a cleanup handler would be the best
option - But although I have read the docs I am not sure what cleanup
handler is best to use for 'child processes has been shut down' rather
than just 'entire server shut down' or 'connection has been dealt with,
now clean up. I figured the cleanup handler for 'entire server shut
down' (Apache::ServerUtil::server_shutdown_cleanup_register) would be
best, but I thought I would ask for some expert advice before fiddling
around.

Any advice would be much appreciated,

Cheers,

David

This email and any files transmitted with it are confidential and intended solely for the 
use of the individual or entity to whom they are addressed. Please notify the sender 
immediately by email if you have received this email by mistake and delete this email 
from your system. Please note that any views or opinions presented in this email are solely
 those of the author and do not necessarily represent those of the organisation. 
Finally, the recipient should check this email and any attachments for the presence of 
viruses. The organisation accepts no liability for any damage caused by any virus 
transmitted by this email. 

Re: Cleanup Handler, Class::DBI and Oracle

[Perrin Harkins <pe...@elem.com>]

David,

First things first: are you running proxy servers in front of these?
That is the most effective way to reduce the number of mod_perl
processes and thus the number of connections to Oracle.

> The reason there is 1200 connections is because we
> have 7 servers that run between 150-300 child processes across 3
> database schemas.

Do you have separate connections for each of those three schemas?  Maybe
you can avoid that by changing the schema setting dynamically, or using
table name prefixes everywhere.

>  Now I have turned off the usage of Apache::DBI however
> the problem remains and I believe the cause is that Class::DBI acts in a
> similar way to Apache::DBI and holds the connection open when it runs
> under mod_perl.

That's right, Class::DBI uses Ima::DBI which keeps the connection in a
closure.  To prevent this, you can override db_Main() in your Class::DBI
base class and make it do a fresh connection.  Check the mailing list
archives for more about this.

By the way, if your Oracle system is at all like the ones I've used, you
are not going to be happy with how long it takes to make a fresh
connection...

> Having said all that, I think that it is possible to have Apache::DBI
> running and continue to use Class::DBI. We have noticed that even if
> there are only 30 child processes running, there are a lot more
> connections to oracle than just the 30 child processes - Which makes me
> think that when a child process is shut down in low-load times that it
> does not disconnect from the database properly.

Or, more likely, that you are using more than one set of connection
parameters.  You can check this by dumping out the contents of
Apache::DBI's hash of connections at the end of your requests.

- Perrin