We don’t have any front end proxy. We don’t use DBD::Gofer nor PGBouncer. 
However, we do use Apache::DBI. The mod_perl application on our servers connect 
to database when they need to.  The database connection can be idle for a long 
time if there is no more requests then we’ll get TCP/IP timeout. We are seeing 
a lot of TNS timeout errors in oracle alert trace log. When that happens, the 
corresponding httpd process on the other hand is still there. If a request 
comes in and happens to use that httpd process (the other side oracle server 
process may have timed out already too), the customer would get “not connect to 
oracle” error. Other than that, having many idle oracle server processes for a 
long time is wasting of system resources like RAM. To address those issues, I 
would think implementing the idle TTL is appropriate. The oracle has 
implemented connection pooling in 11G. I would think this is to address the 
issue on their side. If the client implementation is too hard, then I guess the 
appropriate solution is to use Oracle 11g connection pooling. I just want to 
solicit other people’s opinion how to better address the issue.

- xinhuan

From: John Dunlap <j...@lariat.co<mailto:j...@lariat.co>>
Date: Thursday, November 13, 2014 at 10:01 AM
To: Perrin Harkins <phark...@gmail.com<mailto:phark...@gmail.com>>
Cc: Xinhuan Zheng <xzh...@christianbook.com<mailto:xzh...@christianbook.com>>, 
Paul Silevitch <p...@silevitch.com<mailto:p...@silevitch.com>>, modperl 
<modperl@perl.apache.org<mailto:modperl@perl.apache.org>>
Subject: Re: Disconnect database connection after idle timeout

We use PGBouncer on the web server(which handles keep-alive to the database) 
and then we use Apache::DBI across localhost to talk to PGBouncer.

On Thu, Nov 13, 2014 at 9:56 AM, Perrin Harkins 
<phark...@gmail.com<mailto:phark...@gmail.com>> wrote:
Hi,

Can you explain what problem you're trying to solve? Apache processes don't 
have the option of doing things when there is no request to serve, so you can't 
easily have them disconnect. It may be possible with alarms or cron jobs or 
something, but it's probably not a good idea.

If you tune your configuration to avoid leaving large numbers of servers idle, 
you should not have problems with unused connections. Also, make sure you are 
using a front-end proxy of some kind and not serving static HTTP requests from 
your mod_perl server.

If your problem is that you need more active connections than your server can 
handle, you could look at DBD::Gofer:
http://www.slideshare.net/Tim.Bunce/dbdgofer-200809

- Perrin

On Thu, Nov 13, 2014 at 9:39 AM, Xinhuan Zheng 
<xzh...@christianbook.com<mailto:xzh...@christianbook.com>> wrote:
Your understanding is correct. It’s what I am looking for. However, due to the 
apache forking child nature, I don’t feel comfortable using SIGALARM.

We use Apache::DBI. I would prefer having enhancement in this module. Currently 
the module is implementing apache process wide global cache for db connections, 
which we already use. But one missing piece in this module is to handle the TTL 
(time-to-live) for a cached db connection.  If TTL were implemented, the module 
would have to disconnect from db connection after a pre-defined timeout so the 
oracle server process could be shut down more gracefully. Would it be possible 
to implement that? Or is it too hard to implement?

- xinhuan

From: Paul Silevitch <p...@silevitch.com<mailto:p...@silevitch.com>>
Date: Wednesday, November 12, 2014 at 11:53 PM
To: Xinhuan Zheng <xzh...@christianbook.com<mailto:xzh...@christianbook.com>>, 
modperl <modperl@perl.apache.org<mailto:modperl@perl.apache.org>>
Subject: Re: Disconnect database connection after idle timeout

I don't fully understand your need here.  I'm going to give my best.

You could set an alarm in the cleanup handler that calls the disconnect after a 
specified amount of time.  If a new request comes in, you could cancel the 
alarm in a postreadrequest handler (or something early in the cycle).  To cover 
the race condition where the disconnect happens right before the cancel, you 
could check to make sure the database connection is active right after the 
cancel is called.

HTH,

Paul

On Wed, Nov 12, 2014 at 9:36 PM, Xinhuan Zheng 
<xzh...@christianbook.com<mailto:xzh...@christianbook.com>> wrote:
Hello,

I am having a database connection management question. Our apache+mod_perl 
application initiates a database connection request when it needs to then do 
data processing. Afterwards, if there is no more requests coming to this apache 
process, the database connection basically will be sitting there in idle state 
for quite a while until the OS TCP/IP idle timeout has reached. At that point 
the database would send a message into its alert log, telling that a connection 
timeout has occurred and the server process will be cleaned. I would like to 
figure out if mod_perl application can implement keep-alive timeout mechanism. 
The mod_perl would maintain the database connection after it finishes some 
processing until an idle timeout defined in the application has reached, for 
example, 5 minutes. Then the mod_perl would initiate a database disconnection 
request so the server process can be cleaned more gracefully. We are using 
Oracle 11g database. I knew in 11G oracle has implemented the connection 
pooling. I would think the oracle side connection pooling would be the server 
side maintaining the connection idle timeout. Would it be possible on the 
client side the mod_perl implement something like that? I just don’t know which 
side is more appropriate and how on the client side it can implement something 
like that.

Thanks,
- xinhuan





--
John Dunlap
CTO | Lariat

Direct:
j...@lariat.co<mailto:j...@lariat.co>

Customer Service:
877.268.6667
supp...@lariat.co<mailto:supp...@lariat.co>
[cid:ii_hwm90qu91_146b4d583171a8e6]

Reply via email to