Re: Changing the default configuration (was Re: [pgsql-advocacy]

On Tue, 11 Feb 2003, Tom Lane wrote:

> "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> > Is setting the max connections to something like 200 reasonable, or likely
> > to cause too many problems?
>
> That would likely run into number-of-semaphores limitations (SEMMNI,
> SEMMNS). We do not seem to have as good documentation about changing
> that as we do about changing the SHMMAX setting, so I'm not sure I want
> to buy into the "it's okay to expect people to fix this before they can
> start Postgres the first time" argument here.
>
> Also, max-connections doesn't silently skew your testing: if you need
> to raise it, you *will* know it.

True, but unfortunately, the time you usually learn that the first time is
when your web server starts issuing error messages about not being able to
connect to the database. i.e. it fails at the worst possible time.

OK. I just did some very simple testing in RH Linux 7.2 and here's what I
found about file handles: default max appears to be 8192 now, not 4096.

With max file handles set to 4096, I run out of handles when opening about
450 or more simultaneous connections. At 8192, the default for RH72, I
pretty much run out of memory on a 512 Meg box and start swapping
massively long before I can exhaust the file handle pool.

At 200 connections, I use about half of all my file descriptors out of
4096, which seems pretty safe to me.

Note that setting the max connections to 200 in the conf does NOT result
in huge allocations of file handles right away, but only while the
database is under load, so this leads us to the other possible problem,
that the database will exhaust file handles if we set this number too
high, as opposed to not being able to connect because it's too low.

I'm guessing that 200 or less is pretty safe on most modern flavors of
Unix, but I'm not one of those folks who keeps the older flavors happy
really, so I can't speak for them.

Back in the day, a P100 with 30 or 40 connections was a heavy load,
nowadays, a typical workstation has 512 Meg ram or more, and a 1.5+GHz
CPU, so I can see increasing this setting too. I'd rather the only issue
for the user be adjusting their kernel than having to up the connection
limit in postgresql. I can up the max file handles in Linux on the fly,
with no one noticeing it, I have to stop and restart postgresql to make
the max backends take affect, so that's another reason not to have too low
a limit.

Is there a place on the web somewhere that lists the default settings for
most major unixes for file handles, inodes, and shared memory?