By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

Very long time to commit or close connections

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Chris Smith" <cd*****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #2

P: n/a
-----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

iD8DBQFAC5zztchshDF9KNYRAvkSAJ9ndSV55XopKZ3fr6MHfn ia2J0erACeIc1X
NKc59romT8NqbtHQ6mxrppo=
=LVUR
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #3

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.