Re: [GENERAL] Cannot restart postgresql when increasing max_connections
Joshua D. Drake spaketh: > On Thu, 2009-01-08 at 12:51 -0700, Gregory Williamson wrote: > > Thom Brown wrote: > > <...> > > > I actually mentioned pgPool II to my boss earlier, and it's > > something we > > > will have to seriously consider, but will have to do some research > > first. <...> > > Using pgPool means we have a max limit of 100 and have almost never > > gone near it -- usually we have 12-25 connections at once and load and > > throughput are higher than they were before. > > Not to disparage pgPool, but we have also had great results with > pgBouncer. I should have mentioned we quit while we ahead and never tried pgBouncer, which may be a better solution. (didn't meant to cast any mud!) And I realized belatedly that we use pgPool, a much simpler beast. We have looked at pgPool II but haven't implemented it. G
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
On Thu, 2009-01-08 at 12:51 -0700, Gregory Williamson wrote: > Thom Brown wrote: > <...> > > I actually mentioned pgPool II to my boss earlier, and it's > something we > > will have to seriously consider, but will have to do some research > first. > > We had an application ported from Informix which initially required > 1000+ connections to handle peak load (one of 4 servers pushed by two > application servers doing spatial queries). > > Using pgPool means we have a max limit of 100 and have almost never > gone near it -- usually we have 12-25 connections at once and load and > throughput are higher than they were before. Not to disparage pgPool, but we have also had great results with pgBouncer. Joshua D. Drake > -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
Thom Brown wrote: <...> > I actually mentioned pgPool II to my boss earlier, and it's something we > will have to seriously consider, but will have to do some research first. We had an application ported from Informix which initially required 1000+ connections to handle peak load (one of 4 servers pushed by two application servers doing spatial queries). Using pgPool means we have a max limit of 100 and have almost never gone near it -- usually we have 12-25 connections at once and load and throughput are higher than they were before. Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
Thom Brown wrote: > This database server is currently serving 3 very very busy multi-core web > servers, and we're about to add another 3 to help deal with the load. > > I'm afraid that upgrading is not an option at present because it's in > production. Upgrading within the 8.3 series should involve downtimes measured in seconds - it's just bugfixes so there's no need to dump and restore the database. > I've taken the suggestion of increasing shared buffers and now got > max_connections set to 1500 and shared_buffers set to about a gigabyte. That's a more sensible area for the shared_buffers value, but beware - if you ever have 1500 queries running at once there's a strong possibility that: 1. 1400 of them will take forever 2. You will run out of RAM and the machine will start swapping 3. Possibly leading to out-of-memory problems if you haven't disabled Linux's overcommit option 4. The out-of-memory process killer will start picking things to kill possibly the postmaster - not good. > I actually mentioned pgPool II to my boss earlier, and it's something we > will have to seriously consider, but will have to do some research first. You can do some very clever things with pgpool, but you might find pgbouncer simpler if you just need to spread the load. There's nothing to stop you introducing it step-by-step - the biggest win will be with the shortest queries, so you might be able to isolate part of your application and test it with that. If you've got 8 cores, peak performance will come with max_connections between 8 and 32 I'd guess. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
This database server is currently serving 3 very very busy multi-core web servers, and we're about to add another 3 to help deal with the load. I'm afraid that upgrading is not an option at present because it's in production. I've taken the suggestion of increasing shared buffers and now got max_connections set to 1500 and shared_buffers set to about a gigabyte. I actually mentioned pgPool II to my boss earlier, and it's something we will have to seriously consider, but will have to do some research first. Thanks Thom 2009/1/8 Richard Huxton > Thom Brown wrote: > > It is running on 64-bit Gentoo 2.6.25. > > > > You might be right about needing to increase the shared buffers. Thanks > for > > the suggestion. I'll have to give that a try > > If shared_buffers was so small that you can't handle the connections you > might benefit from one of the links on this page. > > http://wiki.postgresql.org/wiki/Performance_Optimization > > Try the first one, that gives you a good overview. > > Oh and if you're running a web-application consider connection pooling - > pgbouncer or pgpool. > > Oh, and upgrade from 8.3.1 to the latest in the 8.3 series at your > earliest convenience. > > http://www.postgresql.org/support/versioning > > -- > Richard Huxton > Archonet Ltd >
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
Thom Brown wrote: > It is running on 64-bit Gentoo 2.6.25. > > You might be right about needing to increase the shared buffers. Thanks for > the suggestion. I'll have to give that a try If shared_buffers was so small that you can't handle the connections you might benefit from one of the links on this page. http://wiki.postgresql.org/wiki/Performance_Optimization Try the first one, that gives you a good overview. Oh and if you're running a web-application consider connection pooling - pgbouncer or pgpool. Oh, and upgrade from 8.3.1 to the latest in the 8.3 series at your earliest convenience. http://www.postgresql.org/support/versioning -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
It is running on 64-bit Gentoo 2.6.25. You might be right about needing to increase the shared buffers. Thanks for the suggestion. I'll have to give that a try 2009/1/8 Harald Armin Massa > Thom, > > > I have a server running 8.3.1 with 16Gb of memory and 8x2.5Ghz cores. > The > > max_connections was set to 100 (the default), but we were getting denied > > connections because it had exceeded the max. We increased this to a > modest > > 250, stopped the service, and then tried to start. It wouldn't. We > stopped > > it several times, made sure all postgres-related processes were killed > off > > but nothing would make it start. Actually, it said it had started, but > it > > hadn't. When setting it back to 100 it was okay again. We tried the > same > > thing on another server, setting it to 1000, and that was fine. > > Maybe you are missing: > > # Note: Increasing max_connections costs ~400 bytes of shared memory per > # connection slot, plus lock space (see max_locks_per_transaction). You > might > # also need to raise shared_buffers to support more connections. > > To help debugging, your report is essentially missing the operating > system your computer is running on and the output from the log files. > If PostgreSQL does not start, it writes out a reason to its logfiles. > For example in default installations on Windows you will find your > logfiles within the PostgreSQL-Data-Directory in subdir pg_log > > best wishes, > > Harald > > -- > GHUM Harald Massa > persuadere et programmare > Harald Armin Massa > Spielberger Straße 49 > 70435 Stuttgart > 0173/9409607 > no fx, no carrier pigeon > - > EuroPython 2009 will take place in Birmingham - Stay tuned! >
Re: [GENERAL] Cannot restart postgresql when increasing max_connections
Thom, > I have a server running 8.3.1 with 16Gb of memory and 8x2.5Ghz cores. The > max_connections was set to 100 (the default), but we were getting denied > connections because it had exceeded the max. We increased this to a modest > 250, stopped the service, and then tried to start. It wouldn't. We stopped > it several times, made sure all postgres-related processes were killed off > but nothing would make it start. Actually, it said it had started, but it > hadn't. When setting it back to 100 it was okay again. We tried the same > thing on another server, setting it to 1000, and that was fine. Maybe you are missing: # Note: Increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You might # also need to raise shared_buffers to support more connections. To help debugging, your report is essentially missing the operating system your computer is running on and the output from the log files. If PostgreSQL does not start, it writes out a reason to its logfiles. For example in default installations on Windows you will find your logfiles within the PostgreSQL-Data-Directory in subdir pg_log best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cannot restart postgresql when increasing max_connections
Hi, I have a server running 8.3.1 with 16Gb of memory and 8x2.5Ghz cores. The max_connections was set to 100 (the default), but we were getting denied connections because it had exceeded the max. We increased this to a modest 250, stopped the service, and then tried to start. It wouldn't. We stopped it several times, made sure all postgres-related processes were killed off but nothing would make it start. Actually, it said it had started, but it hadn't. When setting it back to 100 it was okay again. We tried the same thing on another server, setting it to 1000, and that was fine. Am I missing something? Thom
