Disconnect database connection after idle timeout
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
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 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 >
Re: Disconnect database connection after idle timeout
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 mailto:p...@silevitch.com>> Date: Wednesday, November 12, 2014 at 11:53 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>>, modperl 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 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
Re: Disconnect database connection after idle timeout
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 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 > Date: Wednesday, November 12, 2014 at 11:53 PM > To: Xinhuan Zheng , modperl < > 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 > 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 >> > >
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 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 > 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 >> Date: Wednesday, November 12, 2014 at 11:53 PM >> To: Xinhuan Zheng , modperl < >> 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 >> 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 * *Customer Service:* 877.268.6667 supp...@lariat.co
Re: Disconnect database connection after idle timeout
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 mailto:j...@lariat.co>> Date: Thursday, November 13, 2014 at 10:01 AM To: Perrin Harkins mailto:phark...@gmail.com>> Cc: Xinhuan Zheng mailto:xzh...@christianbook.com>>, Paul Silevitch mailto:p...@silevitch.com>>, modperl 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 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 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 mailto:p...@silevitch.com>> Date: Wednesday, November 12, 2014 at 11:53 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>>, modperl 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 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 m
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 10:29 AM, Xinhuan Zheng wrote: > We don’t have any front end proxy. I think I see the problem... ;) If you use a front-end proxy so that your mod_perl servers are only handling mod_perl requests, and tune your configuration so that idle mod_perl servers don't sit around for long, that should avoid timeouts. Apache::DBI should also re-connect with no problems if a request comes in after a connection has timed out. If that isn't happening, make sure you are using Apache::DBI properly. Seriously, using a front-end proxy usually reduces the number of databases connections about 10 times. It's the easiest fix here by far. - Perrin
Re: Disconnect database connection after idle timeout
On 13/11/2014 15:43, Perrin Harkins wrote: On Thu, Nov 13, 2014 at 10:29 AM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: We don’t have any front end proxy. I think I see the problem... ;) If you use a front-end proxy so that your mod_perl servers are only handling mod_perl requests, and tune your configuration so that idle mod_perl servers don't sit around for long, that should avoid timeouts. Apache::DBI should also re-connect with no problems if a request comes in after a connection has timed out. If that isn't happening, make sure you are using Apache::DBI properly. Seriously, using a front-end proxy usually reduces the number of databases connections about 10 times. It's the easiest fix here by far. - Perrin From experience - and having chatted with our DBAs at work, with modern Oracle and with MySQL keeping persistent connections around is no real gain and usually lots of risks... We have a number of issues with inter-machine connection - as we have a number of firewalls in place... If the connection failure is not due to either end shutting down but the network failing in between Apache::DBI will (cannot) do the right thing - the ping check just hangs as it tries to the broken connection (and as it isn't connected it waits forever to respond) You are far better using non-persistent connections - but where we do use persistent connections (cache/session database) we set a timeout in the adaptor level which won't use a connection if it has been idle for more than a given period of time, but instead drops the connections and re-builds it Most of the DB servers now cache queries etc on the server so persistent connections are not required to take advantage of these. Additionally if you have a load balanced situation where you have a number of backends - each talking back to a large number of databases (at least in MySQL it is very easy to reach DB connection limits) 7 backends x 50 processes x 300 databases ~ 100,000 connections if each child process has a database connection to each database... perhaps this is a bit extreme (the project involved is a very complex genomic interface) but persistent connections used to be it's achilles heel... where we worry about database connection issues we often use "DBIx::Connector" as a better way to handle disconnects --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 11:29 AM, Dr James Smith wrote: > From experience - and having chatted with our DBAs at work, with modern > Oracle and with MySQL keeping persistent connections around is no real gain > and usually lots of risks It's certainly good to know how long it takes to get a fresh connection and consider whether you need persistent connections or not. Connecting tends to be fast on MySQL and caching is probably not needed unless you're running a very performance-sensitive site. The last time I worked with Oracle, connections were too slow to run without caching them. That was years ago though, and the situation may have improved. - Perrin
Re: Disconnect database connection after idle timeout
I guess we do need connection caching and have persistent connections. It is good in our situation. But I would feel oracle 11g connection pooling might be more appropriate option to handle idle connection time out issue. Having another tier (like DBD::Gofer) looks like really messy in infrastructure plus it’s not certain who is going to maintain that module’s quality. - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 11:42 AM To: Dr James Smith mailto:j...@sanger.ac.uk>> Cc: mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout On Thu, Nov 13, 2014 at 11:29 AM, Dr James Smith mailto:j...@sanger.ac.uk>> wrote: >From experience - and having chatted with our DBAs at work, with modern Oracle >and with MySQL keeping persistent connections around is no real gain and >usually lots of risks It's certainly good to know how long it takes to get a fresh connection and consider whether you need persistent connections or not. Connecting tends to be fast on MySQL and caching is probably not needed unless you're running a very performance-sensitive site. The last time I worked with Oracle, connections were too slow to run without caching them. That was years ago though, and the situation may have improved. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 10:43:35 -0500 Perrin Harkins wrote: > Apache::DBI should also re-connect with no problems if a request comes in > after a connection has timed out. If that isn't happening, make sure you > are using Apache::DBI properly. > Hi Perrin, Nobody suggested using connect_cached, where the documentations says : The cached database handle is replaced with a new connection if it has been disconnected or if the "ping" method fails Would that not solve OP's problem? -- Regards, Vincent Veyron https://libremen.com/ Legal case, contract and insurance claim management software
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 12:21 PM, Vincent Veyron wrote: > Nobody suggested using connect_cached, where the documentations says : > > The cached database handle is replaced with a new >connection if it has been disconnected or if the "ping" method fails > > Would that not solve OP's problem? Well, Apache::DBI should already be doing a ping and a successful re-connect if needed. If that isn't working with Apache::DBI, there's no reason to think it will work better with connect_cached. Also, Apache::DBI has some additional things in it (avoiding caching connections during startup, protection from dangling transactions at the end of a request) that might be needed. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 12:19 PM, Xinhuan Zheng wrote: > Having another tier (like DBD::Gofer) looks like really messy in > infrastructure plus it’s not certain who is going to maintain that module’s > quality. I'd only recommend trying it after you set up a front-end proxy, tune your mod_perl configuration, and use any Oracle tools available to you. - Perrin
Re: Disconnect database connection after idle timeout
Hi Perrin, I don’t quite understand what you mean by setting up a front-end proxy. What would you expect this “proxy” do? Does it take HTTP request? Thanks, - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 12:50 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>> Cc: Dr James Smith mailto:j...@sanger.ac.uk>>, mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout On Thu, Nov 13, 2014 at 12:19 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: Having another tier (like DBD::Gofer) looks like really messy in infrastructure plus it’s not certain who is going to maintain that module’s quality. I'd only recommend trying it after you set up a front-end proxy, tune your mod_perl configuration, and use any Oracle tools available to you. - Perrin
Re: Disconnect database connection after idle timeout
Yes, it's an HTTP proxy. It handles sending out the bytes to remote clients, so that your mod_perl server doesn't have to. A popular high-performance choice these days is nginx. There's some discussion of why to use a front-end proxy here: http://perl.apache.org/docs/1.0/guide/strategy.html - Perrin On Thu, Nov 13, 2014 at 2:12 PM, Xinhuan Zheng wrote: > Hi Perrin, > > I don’t quite understand what you mean by setting up a front-end proxy. > What would you expect this “proxy” do? Does it take HTTP request? > > Thanks, > - xinhuan > > From: Perrin Harkins > Date: Thursday, November 13, 2014 at 12:50 PM > To: Xinhuan Zheng > Cc: Dr James Smith , mod_perl list < > modperl@perl.apache.org> > Subject: Re: Disconnect database connection after idle timeout > >On Thu, Nov 13, 2014 at 12:19 PM, Xinhuan Zheng < > xzh...@christianbook.com> wrote: > >> Having another tier (like DBD::Gofer) looks like really messy in >> infrastructure plus it’s not certain who is going to maintain that module’s >> quality. > > > I'd only recommend trying it after you set up a front-end proxy, tune your > mod_perl configuration, and use any Oracle tools available to you. > > - Perrin >
Re: Disconnect database connection after idle timeout
That link is from the mod_perl 1.x documentation. Is this information still valid in mod_perl 2.x? On Thu, Nov 13, 2014 at 2:23 PM, Perrin Harkins wrote: > Yes, it's an HTTP proxy. It handles sending out the bytes to remote > clients, so that your mod_perl server doesn't have to. A popular > high-performance choice these days is nginx. > > There's some discussion of why to use a front-end proxy here: > http://perl.apache.org/docs/1.0/guide/strategy.html > > - Perrin > > On Thu, Nov 13, 2014 at 2:12 PM, Xinhuan Zheng > wrote: > >> Hi Perrin, >> >> I don’t quite understand what you mean by setting up a front-end proxy. >> What would you expect this “proxy” do? Does it take HTTP request? >> >> Thanks, >> - xinhuan >> >> From: Perrin Harkins >> Date: Thursday, November 13, 2014 at 12:50 PM >> To: Xinhuan Zheng >> Cc: Dr James Smith , mod_perl list < >> modperl@perl.apache.org> >> Subject: Re: Disconnect database connection after idle timeout >> >>On Thu, Nov 13, 2014 at 12:19 PM, Xinhuan Zheng < >> xzh...@christianbook.com> wrote: >> >>> Having another tier (like DBD::Gofer) looks like really messy in >>> infrastructure plus it’s not certain who is going to maintain that module’s >>> quality. >> >> >> I'd only recommend trying it after you set up a front-end proxy, tune >> your mod_perl configuration, and use any Oracle tools available to you. >> >> - Perrin >> > > -- John Dunlap *CTO | Lariat * *Direct:* *j...@lariat.co * *Customer Service:* 877.268.6667 supp...@lariat.co
Re: Disconnect database connection after idle timeout
Yes, it has nothing to do with the version of mod_perl. It's a general technique for any HTTP application server. - Perrin On Thu, Nov 13, 2014 at 2:33 PM, John Dunlap wrote: > That link is from the mod_perl 1.x documentation. Is this information > still valid in mod_perl 2.x? > > On Thu, Nov 13, 2014 at 2:23 PM, Perrin Harkins > wrote: > >> Yes, it's an HTTP proxy. It handles sending out the bytes to remote >> clients, so that your mod_perl server doesn't have to. A popular >> high-performance choice these days is nginx. >> >> There's some discussion of why to use a front-end proxy here: >> http://perl.apache.org/docs/1.0/guide/strategy.html >> >> - Perrin >> >> On Thu, Nov 13, 2014 at 2:12 PM, Xinhuan Zheng >> wrote: >> >>> Hi Perrin, >>> >>> I don’t quite understand what you mean by setting up a front-end >>> proxy. What would you expect this “proxy” do? Does it take HTTP request? >>> >>> Thanks, >>> - xinhuan >>> >>> From: Perrin Harkins >>> Date: Thursday, November 13, 2014 at 12:50 PM >>> To: Xinhuan Zheng >>> Cc: Dr James Smith , mod_perl list < >>> modperl@perl.apache.org> >>> Subject: Re: Disconnect database connection after idle timeout >>> >>>On Thu, Nov 13, 2014 at 12:19 PM, Xinhuan Zheng < >>> xzh...@christianbook.com> wrote: >>> >>>> Having another tier (like DBD::Gofer) looks like really messy in >>>> infrastructure plus it’s not certain who is going to maintain that module’s >>>> quality. >>> >>> >>> I'd only recommend trying it after you set up a front-end proxy, tune >>> your mod_perl configuration, and use any Oracle tools available to you. >>> >>> - Perrin >>> >> >> > > > -- > John Dunlap > *CTO | Lariat * > > *Direct:* > *j...@lariat.co * > > *Customer Service:* > 877.268.6667 > supp...@lariat.co >
Re: Disconnect database connection after idle timeout
Hi Perrin, Thanks for pointing out the document. We are using mod_perl enabled apache server for dynamic content. From the description of the document, the “proxy” server acts much like a memcache but it appears the difference is the “proxy” understands the HTTP protocol while memcache does not. We use load balancer in front of front end servers which can cache some static content. But load balancer has RAM limit. We cannot cache beyond that limit. memcache can cache dynamic content but it is not directly respond to HTTP request. Would you think this “proxy” server sitting in front of the front end server but behind the load balancer? - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 2:23 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>> Cc: mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout Yes, it's an HTTP proxy. It handles sending out the bytes to remote clients, so that your mod_perl server doesn't have to. A popular high-performance choice these days is nginx. There's some discussion of why to use a front-end proxy here: http://perl.apache.org/docs/1.0/guide/strategy.html - Perrin On Thu, Nov 13, 2014 at 2:12 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: Hi Perrin, I don’t quite understand what you mean by setting up a front-end proxy. What would you expect this “proxy” do? Does it take HTTP request? Thanks, - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 12:50 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>> Cc: Dr James Smith mailto:j...@sanger.ac.uk>>, mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout On Thu, Nov 13, 2014 at 12:19 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: Having another tier (like DBD::Gofer) looks like really messy in infrastructure plus it’s not certain who is going to maintain that module’s quality. I'd only recommend trying it after you set up a front-end proxy, tune your mod_perl configuration, and use any Oracle tools available to you. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 12:45:09 -0500 Perrin Harkins wrote: > Well, Apache::DBI should already be doing a ping and a successful > re-connect if needed. Haha, thanks for your answer. I have so much to learn... But then, what is the point of using connect_cached? -- Regards, Vincent Veyron https://libremen.com/ Legal case, contract and insurance claim management software
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 2:48 PM, Vincent Veyron wrote: > But then, what is the point of using connect_cached? You can use it outside of mod_perl. You can also use instead of Apache::DBI if you don't want the connection to be more explicit (instead of magically overriding the connect() call). Keep in mind, neither of these work unless you call DBI->connect()/connect_cached() at the beginning of every request. You can't just keep a DBI handle stashed somewhere and expect that to work. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 2:41 PM, Xinhuan Zheng wrote: > From the description of the document, the “proxy” server acts much like a > memcache but it appears the difference is the “proxy” understands the HTTP > protocol while memcache does not. Not exactly. While it is possible to do some caching in a proxy like this, the more important thing it does is buffering the content, and possibly serving static requests for JavaScript and images. That frees up the mod_perl processes to move on to another request. This is the same process model used by most other systems, like FastCGI or nginx + Starman. > Would you think this “proxy” server sitting in front of the front end > server but behind the load balancer? Yes, it goes between your load balancer and your mod_perl server. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 16:35:59 -0500 Perrin Harkins wrote: > > Keep in mind, neither of these work unless you call > DBI->connect()/connect_cached() at the beginning of every request. You > can't just keep a DBI handle stashed somewhere and expect that to work. > h... you're having me worried. I stash a reference to a DBI handle in pnotes during the HeaderParser phase of my requests; I then refer to this handle for every request in my PerlResponseHandlers. This seems to have been working fine for several months. Am I doing something wrong? -- Regards, Vincent Veyron https://libremen.com/ Legal case, contract and insurance claim management software
Re: Disconnect database connection after idle timeout
Hi Parrin, > While it is possible to do some caching in a proxy like this, the more > important thing it does is buffering the content, and possibly serving static > requests for JavaScript and images. That frees up the mod_perl processes to > move on to another request. I am questioning the value of this kind “proxy”. We have load balancer cache that can cache images and JavaScripts. This functions seems a bit duplicate. The value I am seeing is that because “proxy” understands the HTTP protocol so dynamic content can be served directly from “proxy” cache, without going through a front end server to memcache then a response back to a front end server to remote client. That’s the value I am seeing. I am not sure if this is the right understanding. - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 4:54 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>> Cc: mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout On Thu, Nov 13, 2014 at 2:41 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: >From the description of the document, the “proxy” server acts much like a >memcache but it appears the difference is the “proxy” understands the HTTP >protocol while memcache does not. Not exactly. While it is possible to do some caching in a proxy like this, the more important thing it does is buffering the content, and possibly serving static requests for JavaScript and images. That frees up the mod_perl processes to move on to another request. This is the same process model used by most other systems, like FastCGI or nginx + Starman. Would you think this “proxy” server sitting in front of the front end server but behind the load balancer? Yes, it goes between your load balancer and your mod_perl server. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 5:29 PM, Vincent Veyron wrote: > I stash a reference to a DBI handle in pnotes during the HeaderParser > phase of my requests; I then refer to this handle for every request in my > PerlResponseHandlers. > > This seems to have been working fine for several months. Am I doing > something wrong? > No, that's perfect. Since pnotes gets cleaned up at the end of every request, there's no danger of the handle sticking around. I assume you're calling DBI->connect() to get the handle in the HeaderParser phase, and using Apache::DBI? The dangerous thing is to put a DBI handle in a global and just keep using it on every request. If you put a DBI handle in a global (or a closure variable) make sure you clean it up at the end of every request. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, Nov 13, 2014 at 5:38 PM, Xinhuan Zheng wrote: > We have load balancer cache that can cache images and JavaScripts. This > functions seems a bit duplicate. It's not about caching. Here's a quote from that link I sent earlier: "Another drawback of this approach is that when serving output to a client with a slow connection, the huge mod_perl-enabled server process (with all of its system resources) will be tied up until the response is completely written to the client. While it might take a few milliseconds for your script to complete the request, there is a chance it will be still busy for some number of seconds or even minutes if the request is from a slow connection client." You might think everyone has fast connections now so this won't matter, but it does. It's especially bad if you have keep-alive on for the apache server running mod_perl, since that means your large mod_perl processes sit around for some extra time doing nothing, while holding onto their database connections. Install a front-end proxy, turn off keep-alive on your mod_perl and reduce your max idle servers, and watch what happens. - Perrin
Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 17:40:05 -0500
Perrin Harkins wrote:
>
> No, that's perfect. Since pnotes gets cleaned up at the end of every
> request, there's no danger of the handle sticking around. I assume you're
> calling DBI->connect() to get the handle in the HeaderParser phase, and
> using Apache::DBI?
>
> The dangerous thing is to put a DBI handle in a global and just keep using
> it on every request. If you put a DBI handle in a global (or a closure
> variable) make sure you clean it up at the end of every request.
>
I have a package that returns a DBI handle when called, using connect_cached,
without Apache::DBI (see package Marica::db_handle below)
For each request I do :
In the PerlHeaderParserHandler
my $dbh = Marica::db_handle::get_dbh_data( $database,
$r->pnotes('session')->{preferred_datestyle} ) ;
$r->pnotes( 'dbh' => $dbh ) ;
Then in the PerlResponseHandler
my $dbh = $r->pnotes('dbh') ;
my $data_set = $dbh->selectall_arrayref( $sql ) ;
It's very convenient, all my modules use this, and I only have one place to
edit the database connection if needed.
If I understand correctly, I could also use Apache::DBI and a persistent
connection for a similar result, modulo what you wrote in an earlier message
regarding the connection being more explicit (which I don't quite grasp, I'll
have to study on that)?
package Marica::db_handle ;
use strict ;
use warnings ;
sub get_dbh_data {
my $db_name = shift ;
#paramètre d'affichage des dates; les caches de connection en tiennent
compte
my $preferred_datestyle = shift || 'iso' ;
my $dbh = DBI->connect_cached( "DBI:Pg:dbname=$db_name", 'www-data', undef,
{
PrintError => 1,
RaiseError => 1,
AutoCommit => 1,
pg_bool_tf => 1,
private_preferred_datestyle => $preferred_datestyle } )
or die "Cannot connect to db: $DBI::errstr" ;
return $dbh ;
}
--
Salutations, Vincent Veyron
https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats
pour le service juridique
Re: Disconnect database connection after idle timeout
On Fri, Nov 14, 2014 at 7:58 AM, Vincent Veyron wrote: > If I understand correctly, I could also use Apache::DBI and a persistent > connection for a similar result, modulo what you wrote in an earlier > message regarding the connection being more explicit (which I don't quite > grasp, I'll have to study on that)? Right. Apache::DBI overrides DBI->connect() when loaded so that a normal connect() call gets a cached handle if available. What you're doing here looks fine. If you ever switch off AutoCommit in your code, I'd suggest adding a cleanup handler that checks if the handle is not currently in AutoCommit mode, and if not, issues a rollback and switches it to AutoCommit. That protects against an exception leaving your database handle in a bad state. (Apache::DBI tries to do that automatically, but it's not very smart about it. It only happens if you have AutoCommit off when you connect.) - Perrin
Re: Disconnect database connection after idle timeout
Hi Parrin, > the huge mod_perl-enabled server process (with all of its system resources) > will be tied up until the response is completely written to the client. While > it might take a few milliseconds for your script to complete the request, > there is a chance it will be still busy for some number of seconds or even > minutes if the request is from a slow connection client. Are you implying that the performance will be suffered when using mod_perl-enabled server processes as the front tier servers? - xinhuan From: Perrin Harkins mailto:phark...@gmail.com>> Date: Thursday, November 13, 2014 at 5:49 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>> Cc: mod_perl list mailto:modperl@perl.apache.org>> Subject: Re: Disconnect database connection after idle timeout On Thu, Nov 13, 2014 at 5:38 PM, Xinhuan Zheng mailto:xzh...@christianbook.com>> wrote: We have load balancer cache that can cache images and JavaScripts. This functions seems a bit duplicate. It's not about caching. Here's a quote from that link I sent earlier: "Another drawback of this approach is that when serving output to a client with a slow connection, the huge mod_perl-enabled server process (with all of its system resources) will be tied up until the response is completely written to the client. While it might take a few milliseconds for your script to complete the request, there is a chance it will be still busy for some number of seconds or even minutes if the request is from a slow connection client." You might think everyone has fast connections now so this won't matter, but it does. It's especially bad if you have keep-alive on for the apache server running mod_perl, since that means your large mod_perl processes sit around for some extra time doing nothing, while holding onto their database connections. Install a front-end proxy, turn off keep-alive on your mod_perl and reduce your max idle servers, and watch what happens. - Perrin
Re: Disconnect database connection after idle timeout
On Fri, 14 Nov 2014 08:30:18 -0500 Perrin Harkins wrote: > If you ever switch off AutoCommit in > your code, I'd suggest adding a cleanup handler that checks if the handle > is not currently in AutoCommit mode, and if not, issues a rollback and > switches it to AutoCommit. That protects against an exception leaving your > database handle in a bad state. (Apache::DBI tries to do that > automatically, but it's not very smart about it. It only happens if you > have AutoCommit off when you connect.) > Ha yes, I see some threads about this. Thanks for the heads up. -- Salutations, Vincent Veyron https://marica.fr/ Gestion des contentieux, des dossiers de sinistres assurance et des contrats pour le service juridique
Re: Disconnect database connection after idle timeout
On Fri, Nov 14, 2014 at 1:32 PM, Xinhuan Zheng wrote: > Are you implying that the performance will be suffered when using > mod_perl-enabled server processes as the front tier servers? Not performance, scalability. You can't handle as many requests per second if you use mod_perl without a front-end proxy. That's why people on this list with a large site use them, and why every similar system in Ruby, Python, and PHP also recommend separating your web front-end from your application server processes if you need to scale. - Perrin
Re: Disconnect database connection after idle timeout
What I don't understand is what you are worried about? Whether you are using Apache::DBI or David Wheelers DBIHandler (do I have that correct?) both will behave according to the configuration of the database and web server. Why not just let the servers decide whether to maintain or destroy a connection according to your configuration? Set your timeouts in both Oracle and Apache and let them deal with it On 11/13/2014 07:39 AM, Xinhuan Zheng wrote: < 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? No one cares about shutting down Oracle gracefully. I have not used "shutdown immediate", other than by mistake in probably 10 years. I have never successfully used "shutdown normal" Idle db connections cost almost nothing, establishing a connection is expensive. Warning: I am better at Oracle than perl, and I am not very good with either :) YMMV Dave - xinhuan From: Paul Silevitch mailto:p...@silevitch.com>> Date: Wednesday, November 12, 2014 at 11:53 PM To: Xinhuan Zheng mailto:xzh...@christianbook.com>>, modperl 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 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 -- Dave Morgan Senior Consultant, 100 Alberta Limited dave.mor...@100.com 403 399 2442
Re: Disconnect database connection after idle timeout
On 11/13/2014 09:42 AM, Perrin Harkins wrote: On Thu, Nov 13, 2014 at 11:29 AM, Dr James Smith mailto:j...@sanger.ac.uk>> wrote: From experience - and having chatted with our DBAs at work, with modern Oracle and with MySQL keeping persistent connections around is no real gain and usually lots of risks MySQL I don't know, Oracle, every connection should be persistent if you are making more than a single call The last time I worked with Oracle, connections were too slow to run without caching them. That was years ago though, and the situation may have improved. I hate to quote myself but . On 11/13/2014 08:00 AM, Dave Morgan wrote:> Idle db connections cost almost nothing, establishing a connection is expensive. Warning: I am better at Oracle than perl, and I am not very good with either :) Making a new connection to Oracle 11 or 12 is still painfully expensive. Dave -- Dave Morgan Senior Consultant, 100 Alberta Limited dave.mor...@100.com 403 399 2442
Re: Disconnect database connection after idle timeout
On Thu, 13 Nov 2014 10:43:35 -0500 Perrin Harkins wrote: > Apache::DBI should also re-connect with no problems if a request comes in > after a connection has timed out. If that isn't happening, make sure you > are using Apache::DBI properly. > Hi Perrin, Nobody suggested using connect_cached, where the documentations says : The cached database handle is replaced with a new connection if it has been disconnected or if the "ping" method fails Would that not solve OP's problem? -- Regards, Vincent Veyron https://libremen.com/ Legal case, contract and insurance claim management software
