Thread: Can postgresql accept mutliple connections in the same instance?
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. 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. Max connections is set to 500, the webpage for the server code initalises and then closes the connection when its done. Using netstat on open postgresql connections seems to match with what apache is showing as current connections. This seems to happen sometimes when the backups are going, which are done every hour though not always. Users notice this a lot between the hours of 6am to 10am which 1 in 20 connections failing to be established. Does pg_dumpall or pg_dump lock the database block access to the database so you cant create connections? 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? Do you have any ideas how I can test the database in a way to find out what could be causing this problem?
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
Attachment
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/
Re: Can postgresql accept mutliple connections in the same instance?
From
"Zeugswetter Andreas DAZ SD"
Date:
>> 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.