General Performance questions
I have postgres set to a max connection limit of 512. I have nearly 30,000
users that will be taking a training course over the next 6 weeks. I have 3
web servers load balanced serving php content for this website. Last week I
had postgres set to a connection limit of 128 and that was reached. Since
then I have set it to 512 and not had a problem. However, I am unaware of
any good monitoring tools that let me see live connections to the database.
When I run netstat-c on the db server, it lets me see active connection in
real time. Heres an example:
Tcp 0 0 server:postgres 192.168.50.x:port number
ESTABLISHED
I will have anywhere from 5 to 7 of these going at any given time. However
most say TIME_WAIT instead of established.
I am assuming these are connections to the database that php is using to
post and retrieve data. I recently read where postgres uses one semaphore
per connection in sets of 16. With that being said is one of these
connections that I see in netstat-c actually representing 16 connections?
If that is the case, when 6 or 7 are being displayed at once, then that
would mean roughly 112 or so connections, which is great because it doesn't
even come close to my limit of 512. Can anyone confirm that what I am
thinking is right?
Also, is there a way to make the TIME_WAIT status shorter. It seems to
exist for about 25 seconds, then goes away. However, those are connections
that could easily be used.
Thank you,
Darryl
"Delao, Darryl W" <ddelao@ou.edu> writes:
I will have anywhere from 5 to 7 of these going at any given time. However
most say TIME_WAIT instead of established.
TIME_WAIT is a closed connection; the kernel is only remembering it for
a few seconds in case the other end requests a retransmission of the
last few outgoing bytes. This is not blocking you from creating new
sessions.
Better ways to keep track of active database sessions are grepping the
output of "ps" for postgres processes, or watching the pg_stat_activity
system view.
Also, is there a way to make the TIME_WAIT status shorter.
Not without violating the TCP specs.
regards, tom lane
I will have anywhere from 5 to 7 of these going at any given time. However
most say TIME_WAIT instead of established.
I am assuming these are connections to the database that php is using to
post and retrieve data.
No, those connections have been closed. TIME_WAIT is part of the TCP spec.
After a connection is closed the port will stay in a "TIME_WAIT" state for a
short time. This is a defensive mechanism that prevents that port from being
opened before enough time has elapsed to allow any stray delayed packets from
the old connection to be dealt with. Without this delay another connection
could be opened instantly and could incorrectly get a packet left over from
the old connection.
The only "live" connections are those listed as ESTABLISHED.
You didn't mention what version of pg you are using but if it is a recent
version try:
select * from pg_stat_database;
and look at the number of backends which you can get on a database by
database level.
You could get total connections with:
select sum(numbackends) from pg_stat_database;
While you are there check out all the other stuff you can see in the pg_stat*
tables.
Cheers,
Steve
Im looking at netstat on my db server and I see a few postgres connections.
I have heard that each of these connections actually represents 16
connections to postgres. Can anyone confirm this?
Import Notes
Resolved by subject fallback
"Delao, Darryl W" <ddelao@ou.edu> writes:
Im looking at netstat on my db server and I see a few postgres connections.
I have heard that each of these connections actually represents 16
connections to postgres. Can anyone confirm this?
Where did you hear that? It's nonsense.
regards, tom lane
On Mon, 10 Mar 2003, Tom Lane wrote:
"Delao, Darryl W" <ddelao@ou.edu> writes:
Im looking at netstat on my db server and I see a few postgres connections.
I have heard that each of these connections actually represents 16
connections to postgres. Can anyone confirm this?Where did you hear that? It's nonsense.
Well, it was over a year ago. basically what happened was that whenever i
set the value to be anything greater than 32, postmaster failed to start.
bring it back down to a value less than or equal to 32, and it started up
fine. after recompiling postgresql with a hardcoded value of 64, then i
was able to increase it in postgresql.conf.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lonni J Friedman 877-VALINUX x77159
Technical Support VA Software
Lonni J Friedman <lfriedman@vasoftware.com> writes:
Well, it was over a year ago. basically what happened was that whenever i
set the value to be anything greater than 32, postmaster failed to start.
Failed to start with what error message, exactly? It sounds to me like
you are describing a problem with unreasonably low kernel parameters,
not with Postgres. (In particular I'd wonder what SEMMAX is on your
system, though I suppose SHMMAX might be the issue too. See the Admin
Guide concerning care and feeding of kernel parameters.)
bring it back down to a value less than or equal to 32, and it started up
fine. after recompiling postgresql with a hardcoded value of 64, then i
was able to increase it in postgresql.conf.
Hardcoded value of what?
regards, tom lane
On Mon, 10 Mar 2003, Tom Lane wrote:
Lonni J Friedman <lfriedman@vasoftware.com> writes:
Well, it was over a year ago. basically what happened was that whenever i
set the value to be anything greater than 32, postmaster failed to start.Failed to start with what error message, exactly? It sounds to me like
i wish i remembered, but like i said, it was some time ago, so all i
recollect is the problem, not the details. sorry.
you are describing a problem with unreasonably low kernel parameters,
not with Postgres. (In particular I'd wonder what SEMMAX is on your
system, though I suppose SHMMAX might be the issue too. See the Admin
Guide concerning care and feeding of kernel parameters.)
i considered this, but the weird thing is that i know i didn't touch any
of the /proc bound kernel parms yet a recompile of postgres miraculously
resolved the problem.
bring it back down to a value less than or equal to 32, and it started up
fine. after recompiling postgresql with a hardcoded value of 64, then i
was able to increase it in postgresql.conf.Hardcoded value of what?
max_connections. once again, i dont' clearly remember the details. yea,i
know this doesn't shed any light on the problem. i just thought maybe
this was a known restriction. if you say its not any longer, i'll gladly
take your word on it. i'm no longer the admin of the box where this was
occuring last year.
thanks for your reply.