Re: Changing the default configuration (was Re: - Mailing list pgsql-hackers

On Tue, 11 Feb 2003, Tom Lane wrote:

> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > ... If he starts running out of semaphores, that's a 
> > problem he can address while his database is still up and running in most 
> > operating systems, at least in the ones I use.
> 
> Back in the day, this took a kernel rebuild and system reboot to fix.
> If this has changed, great ... but on exactly which Unixen can you
> alter SEMMAX on the fly?

Tom, now you're making me all misty eyed for 14" platter 10 Meg hard 
drives and paper tape readers.  :-)

Seriously, I know Linux can change these on the fly, and I'm pretty sure 
Solaris can too.  I haven't played with BSD for a while so can't speak 
about that.  Anyone else know?

> > So, my main point is that any setting that requires you to shut down 
> > postgresql to make the change, we should pick a compromise value that 
> > means you never likely will have to shut down the database once you've 
> > started it up and it's under load.
> 
> When I started using Postgres, it did not allocate the max number of
> semas it might need at startup, but was instead prone to fail when you
> tried to open the 17th or 33rd or so connection.  It was universally
> agreed to be an improvement to refuse to start at all if we could not
> meet the specified max_connections setting.  I don't want to backtrack
> from that.  If we can up the default max_connections setting, great ...
> but let's not increase the odds of failing under load.

I don't want to backtrack either, and I prefer that we now grab the 
semaphores we need at startup.

Note that on a stock RH 72 box, the max number of 
backends you can startup before you exhaust semphores is 2047 backends, 
more than I'd ever want to try and run on normal PC hardware.  So, on a 
linux box 150 to 200 max backends comes no where near exhausting 
semaphores.

I imagine that any "joe average" who doesn't really understand sysadmin 
duties that well and is trying for the first time to install Postgresql 
WILL be doing so on one of three general platforms, Linux, BSD, or 
Windows.  As long as the initial settings use only 10% or so of the file 
handle and / or semaphore resources on each of those systems, we're 
probably safe.

64 or 128 seems like a nice power of two number that is likely to keep us 
safe on inital installs while forestalling problems with too many 
connections.

Just for score, here's the default max output of rh72's kernel config:

kernel.sem = 250        32000   32      128
fs.file-max = 8192

Note that while older kernels needed to have max inodes bumped up as well, 
nowadays that doesn't seem to be a problem, or they just set it really 
high and I can't hit the ceiling on my workstation without swap storms.

the definitions of the kernel.sem line are:

kernel.sem: max_sem_per_id max_sem_total max_ops_sem_call max_sem_ids

I'll try to get FreeBSD running today and see what research I can find on 
it, but 5.0 is likely to be a whole new beast for me, so if someone can 
tell us what the maxes are by default on different BSDs and what the 
settings are in postgresql that can exhaust them that gets us closer.

Like I've said before, anyone running HPUX, Irix, Solaris, or any other 
"Industrial Strength Unix" should already know to increase all these 
things and likely had to long before Postgresql showed up on their box, so 
a setting that keeps pgsql from coming up won't be likely, and if it 
happens, they'll most likely know how to handle it.

BSD and Linux users are more likely to contain the group of folks who 
don't know all this and don't ever want to (not that all BSD/Linux users 
are like that, just that the sub group mostly exists on those platforms, 
and windows as well.)  So the default settings really probably should be 
determined, for the most part, by the needs of those users.