Thread: Very long time to commit or close connections
Hi everyone, I'm running into a rather serious problem in a production application using PostgreSQL. We've got about a connection every 4 seconds being created to the database. Most of the time, this causes no problems at all. Occasionally, though, with no apparent reason, the database will act up for some time (as little as 10 minutes, or as long as 45 minutes). During that time, it accepts connections and runs queries and statements with no problems, but hangs when closing the connections. After the time period expires, the database will wake up and all connections that have been waiting will simultaneously return from the JDBC Connection.close() method. Unfortunately, the problem is only occurring at a remote customer site to which we have limited access, and sometimes requires multiple days of running time to reproduce. As a result, it's hard to see what's happening at the instant this happens. I'm trying to gather more information, but don't have it right now. So right now, my question is this: does this sound familiar to anyone, and what information would you look for in troubleshooting this? -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation
"Chris Smith" <cdsmith@twu.net> writes:
> We've got about a connection every 4 seconds being created to the database.
> Most of the time, this causes no problems at all. Occasionally, though, with
> no apparent reason, the database will act up for some time (as little as 10
> minutes, or as long as 45 minutes). During that time, it accepts connections
> and runs queries and statements with no problems, but hangs when closing the
> connections. After the time period expires, the database will wake up and all
> connections that have been waiting will simultaneously return from the JDBC
> Connection.close() method.
There are not that many things happening during connection close. Does
your app use temp tables? LISTEN/NOTIFY?
> So right now, my question is this: does this sound familiar to anyone, and
> what information would you look for in troubleshooting this?
Nope ... how about looking at the contents of the pg_locks system view
when this happens? What external conditions correspond to the instant
when the hangs are released? (I do not believe that it just happens by
magic --- look for background cron jobs starting or finishing at that
time, eg a cron job that issues VACUUMs.)
What PG version is this, anyway?
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Chris Smith wrote: | Hi everyone, | | I'm running into a rather serious problem in a production application using | PostgreSQL. | | We've got about a connection every 4 seconds being created to the database. | Most of the time, this causes no problems at all. Occasionally, though, with | no apparent reason, the database will act up for some time (as little as 10 | minutes, or as long as 45 minutes). During that time, it accepts connections | and runs queries and statements with no problems, but hangs when closing the | connections. After the time period expires, the database will wake up and all | connections that have been waiting will simultaneously return from the JDBC | Connection.close() method. | | Unfortunately, the problem is only occurring at a remote customer site to | which we have limited access, and sometimes requires multiple days of running | time to reproduce. As a result, it's hard to see what's happening at the | instant this happens. I'm trying to gather more information, but don't have | it right now. Could this be related to a network problem ? Like RST not received by the client, making it believe the server did not acknowledge the connection.close(), eating up the server's resources ? No firewall/VPN gateways between you and your customer ? I've seen this kind of behaviour in the past (with oracle), and tweaking our firewall rules solved the case - just imagine how much time we've spent before finding out :-( -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAC5zztchshDF9KNYRAvkSAJ9ndSV55XopKZ3fr6MHfnia2J0erACeIc1X NKc59romT8NqbtHQ6mxrppo= =LVUR -----END PGP SIGNATURE-----
Erwin, > Could this be related to a network problem ? > > Like RST not received by the client, making it believe the server did > not acknowledge the connection.close(), eating up the server's resources > ? No firewall/VPN gateways between you and your customer ? Nice thought, but the app and database are on the same box, and communicating via TCP/IP on the localhost address. I'm not sure it's even possible to set up a firewall there, and I'm certain it hasn't been done. Thanks, -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation
Oops, meant to copy the list on this... ----- Original Message ----- From: "Chris Smith" <cdsmith@twu.net> To: "Tom Lane" <tgl@sss.pgh.pa.us> Sent: Monday, January 19, 2004 8:01 AM Subject: Re: [GENERAL] Very long time to commit or close connections > > There are not that many things happening during connection close. Does > > your app use temp tables? LISTEN/NOTIFY? > > Nope. It's actually a very simple application. It uses plain select, insert, > update, and delete statements with nothing particularly unusual beyond that. > > > Nope ... how about looking at the contents of the pg_locks system view > > when this happens? What external conditions correspond to the instant > > when the hangs are released? (I do not believe that it just happens by > > magic --- look for background cron jobs starting or finishing at that > > time, eg a cron job that issues VACUUMs.) > > Hmm... I will definitely look. I pretty much know that it's not a vacuum. I > suppose I could have our system monitor insert information from the 'ps' > utility when there are outstanding connections. > > > What PG version is this, anyway? > > 7.3.2. We could upgrade within the 7.3 series, but have not qualified the > application on 7.4 at this point. (there are complications, mostly because of > a patch we apply to the JDBC drivers, which change for 7.4). > > -- > www.designacourse.com > The Easiest Way to Train Anyone... Anywhere. > > Chris Smith - Lead Software Developer/Technical Trainer > MindIQ Corporation >