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