Thread: 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

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!

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

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

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

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

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.)

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

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