Postgres Pro
Downloads
Home   >   mailing lists

Thread: DB Connections in TIME_WAIT state

DB Connections in TIME_WAIT state

From
John Gateley
Date:
Hi, I'm using the Pg perl module to connect to Postgresql 8.1
via localhost (127.0.0.1) from webscripts. I'm noticing a lot
of TIME_WAIT socket connections:

tcp        0      0 127.0.0.1:39291         127.0.0.1:5432          TIME_WAIT
tcp        0      0 127.0.0.1:60720         127.0.0.1:5432          TIME_WAIT
tcp        0      0 127.0.0.1:60735         127.0.0.1:5432          TIME_WAIT
tcp        0      0 127.0.0.1:60769         127.0.0.1:5432          TIME_WAIT
tcp        0      0 127.0.0.1:39281         127.0.0.1:5432          TIME_WAIT
...

I think the number is high enough (200~250) that sometime the
server runs out of sockets.

What's the proper way to close the postgresql connection so that
it doesn't go into a TIME_WAIT state?

I tried:

use Pg;
my $Connection = Pg::connectdb($ConnectString);
... do some stuff
$Connection = 0;

That doesn't seem to close the connection.

Any ideas?

Thanks,

j
--
John Gateley <gateley@jriver.com>

Re: DB Connections in TIME_WAIT state

From
Tom Lane
Date:
John Gateley <gateley@jriver.com> writes:
> Hi, I'm using the Pg perl module to connect to Postgresql 8.1
> via localhost (127.0.0.1) from webscripts. I'm noticing a lot
> of TIME_WAIT socket connections:

> tcp        0      0 127.0.0.1:39291         127.0.0.1:5432          TIME_WAIT
> tcp        0      0 127.0.0.1:60720         127.0.0.1:5432          TIME_WAIT
> tcp        0      0 127.0.0.1:60735         127.0.0.1:5432          TIME_WAIT
> tcp        0      0 127.0.0.1:60769         127.0.0.1:5432          TIME_WAIT
> tcp        0      0 127.0.0.1:39281         127.0.0.1:5432          TIME_WAIT
> ...

This seems like a kernel bug to me.  The TCP stack ought to know it
doesn't need any shutdown delay on a local connection.

> I think the number is high enough (200~250) that sometime the
> server runs out of sockets.

Maybe you need to stop using so many connections --- quite aside
from any kernel issues, a database session isn't exactly cheap
to launch.  Consider some form of connection pooling.

            regards, tom lane