Can postgresql accept mutliple connections in the same instance?

On Fri, 2004-11-19 at 16:17 +1300, Dru wrote:

Though one of hte developers
tells me that sometimes it gets a link resource allocation error on
opening connections to the database server which i dont know if that is
related.

I'm not sure what you mean by a "link resource allocation error". Can
you provide the exact error message you get when a connection is
refused? (Try checking the PostgreSQL logfile.)

Max connections is set to 500, the webpage for the server code
initalises and then closes the connection when its done.

It might be worth considering connection pooling or persistent
connections, although that shouldn't be related to the specific problem
you're having.

Does pg_dumpall or pg_dump lock the database block access
to the database so you cant create connections?

No.

Could it be possible
postgresql can only create one connection at a time and if it gets two
requests for a connection at the same time it fails?

No.

-Neil

Dru <andru@treshna.com> writes:

I am running of postgresql database servers with generally 30-50 users
at a time per server. I have noticed one thing for web based databases
that they fail to initialse a pg_connection connection every now and
again and return no error message at all.

That's fairly hard to believe. I don't know of any failure paths that
won't log some traceable result *somewhere* --- if nothing gets returned
to the client, try looking in syslog or stderr output (you're not
sending postmaster stderr to /dev/null I hope), or at worst look for a
core dump file.

regards, tom lane

Neil Conway wrote:

On Fri, 2004-11-19 at 16:17 +1300, Dru wrote:

Though one of hte developers
tells me that sometimes it gets a link resource allocation error on
opening connections to the database server which i dont know if that is
related.

I'm not sure what you mean by a "link resource allocation error". Can
you provide the exact error message you get when a connection is
refused? (Try checking the PostgreSQL logfile.)

I'll get the developer to write down the exact error when it happens again.
I think he only gets it once or twice a week when using the database
server and grown accustomed to it. I myself when testing the database
server for errors dont get any at all when connections fail when they
shouldnt fail :( .

Max connections is set to 500, the webpage for the server code
initalises and then closes the connection when its done.

It might be worth considering connection pooling or persistent
connections, although that shouldn't be related to the specific problem
you're having.

I was using persistent connections initally and was getting this problem
so switched to non-persistant and made sure i was cleaning them up
afterwards
in case it helped solved this problem. I've replaced the db server and
webserver
also to try and resolve it but that had no effect. I initally thought
it might
be something to do with a kernel limit on sockets or something to that
effect.
These connections are all TCP/IP based.

Does pg_dumpall or pg_dump lock the database block access
to the database so you cant create connections?

No.

Ok that rules out that possibility then.

Could it be possible
postgresql can only create one connection at a time and if it gets two
requests for a connection at the same time it fails?

No.

Ok rules out that possibility also. Is there any stress testing
software for
postgresql to find out how and when it breaks? I wrote a simple script
to simulate 300 concurrent users on the webfrontend which breaks
the website real quick with 1 in 10 connections to db failing with no error
returned by connect just a dead connection. The website uses php,
the problem could be in the wrapper code for PHP though. I havnt
got much luck asking php developers about the problem though.

Tom Lane wrote:

Dru <andru@treshna.com> writes:

I am running of postgresql database servers with generally 30-50 users
at a time per server. I have noticed one thing for web based databases
that they fail to initialse a pg_connection connection every now and
again and return no error message at all.

That's fairly hard to believe. I don't know of any failure paths that
won't log some traceable result *somewhere* --- if nothing gets returned
to the client, try looking in syslog or stderr output (you're not
sending postmaster stderr to /dev/null I hope), or at worst look for a
core dump file.

regards, tom lane

I've spent ages going though logs and turning debugging to max. There is no
error message returned at all. The connection handle is returned as NULL.
This is in the php functions though so their pg_last_error() function may
not be catching all error messages but it seems it should pass on every
error. It is just really weird. The weirder thing i find is the fact it is
doing this a lot more often while backups are running than when they arn't
running. 80% of the time when it is reported by users a backup is running
at the same time.

I've spent ages going though logs and turning debugging to max. There
is no
error message returned at all. The connection handle is returned as
NULL.
This is in the php functions though so their pg_last_error() function may
not be catching all error messages but it seems it should pass on every
error. It is just really weird. The weirder thing i find is the fact
it is
doing this a lot more often while backups are running than when they
arn't
running. 80% of the time when it is reported by users a backup is
running
at the same time.

Could it be that your load is getting driven to high by lack of IO bandwidth
during a backup and your PHP connection is timing out?

J

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

On Fri, 2004-11-19 at 17:15 +1300, Dru wrote:

Ok rules out that possibility also. Is there any stress testing
software for postgresql to find out how and when it breaks?

Try contrib/pgbench.

The website uses php,
the problem could be in the wrapper code for PHP though. I havnt
got much luck asking php developers about the problem though.

pgbench uses libpq (i.e. the native C client interface to PostgreSQL) --
if you encounter connection failures using it, that will narrow down the
set of possible culprits. Since you don't get an error message in the
PostgreSQL logs when a connection is refused, it seems that the
connection attempt doesn't even make it as far as the postmaster, so I
would be skeptical of the software between the client and the backend
(e.g. PHP, perhaps some kernel/TCP weirdness, etc.).

-Neil

On Fri, Nov 19, 2004 at 05:15:54PM +1300, Dru wrote:

I'll get the developer to write down the exact error when it happens again.

It would be better to cut and paste the error message instead of
writing it down. What people think they see doesn't always match
what's on the screen.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

I am running of postgresql database servers with generally 30-50 users
at a time per server. I have noticed one thing for web based databases
that they fail to initialse a pg_connection connection every now and
again and return no error message at all.

I am thinking of the PG_SOMAXCONN maximum connects in the queue
of the listen socket. That would only be visible to the client app,
and would mainly happen when load is high so that postmaster cannot fork
new backends fast enough. And if the client does not properly report ...
IIRC some systems have a max of 5.

Andreas

Neil Conway wrote:

On Fri, 2004-11-19 at 17:15 +1300, Dru wrote:

Ok rules out that possibility also. Is there any stress testing
software for postgresql to find out how and when it breaks?

Try contrib/pgbench.

The website uses php,
the problem could be in the wrapper code for PHP though. I havnt
got much luck asking php developers about the problem though.

pgbench uses libpq (i.e. the native C client interface to PostgreSQL) --
if you encounter connection failures using it, that will narrow down the
set of possible culprits. Since you don't get an error message in the
PostgreSQL logs when a connection is refused, it seems that the
connection attempt doesn't even make it as far as the postmaster, so I
would be skeptical of the software between the client and the backend
(e.g. PHP, perhaps some kernel/TCP weirdness, etc.).

-Neil

I'll have a look around for kernel limits regarding socket opening etc.
It could be that thats causing the problems.

Zeugswetter Andreas DAZ SD wrote:

I am running of postgresql database servers with generally 30-50 users
at a time per server. I have noticed one thing for web based databases
that they fail to initialse a pg_connection connection every now and
again and return no error message at all.

I am thinking of the PG_SOMAXCONN maximum connects in the queue
of the listen socket. That would only be visible to the client app,
and would mainly happen when load is high so that postmaster cannot fork
new backends fast enough. And if the client does not properly report ...
IIRC some systems have a max of 5.

Andreas

PG_SOMAXCONN gets its defination from pg_config_manual.h?
I'm using debian and its still set to 10000 here.
I did check though the logs and didn't see any "failed to listen on
server socket:"
errors which i assume would be generated when it hits that max.
I am thinking the problem could lie with php though there code looks
very clear cut.
I'll add my own debugging output to php and track its connection process and
see i can find out where it fails.