Re: Cleanup Handler, Class::DBI and Oracle

[prev] [thread] [next] [Date index for 2005/03/16]

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