Re: performance tuning: shared_buffers, sort_mem; swap

"Thomas O'Connell" <tfo(at)monsterlabs(dot)com> writes:
> In article <27214(dot)1029257785(at)sss(dot)pgh(dot)pa(dot)us>,
> tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
>> Uh, which error exactly? AFAIK all the shared-memory-related messages
>> would only come out at postmaster startup, not during peak load. The
>> system is designed to ensure that those resources are all reserved
>> during postmaster start.

> Sorry, I should've included the whole error in the first place:

> "IpcMemoryCreate: shmget(key=5432001, size=72368128, 03600) failed:
> Invalid argument

Hmm. That's definitely a startup-time error. The only way that code
could be executed later than postmaster startup is if you suffer a
database crash and the postmaster is trying to reinitialize the system
with a fresh shared-memory arena. That would say that this isn't your
primary problem, but a consequence of a crash that'd already occurred.

I am curious why you'd get "Invalid argument" (EINVAL), as presumably
these are the same arguments that the kernel accepted on the previous
cycle of life. But that's probably not the issue to focus on.

My bet for the cause of the database crash is the same as Bruce's:
the Linux kernel is known to "kill -9" processes at random when it
gets under sufficiently heavy memory pressure. If it happens to select
a database backend to kill, the postmaster will interpret the backend's
unexpected exit as a crash, and will force a database restart. Perhaps
when the postmaster tries to reallocate the shmem segment a few
milliseconds later, the kernel still thinks it's under load and rejects
a shmem request that it'd normally have accepted. (That last bit is
just speculation though.)

Possible solutions: (a) buy more RAM and/or increase available swap
space (I'm not sure whether more swap, without more physical RAM,
actually helps; anyone know?); (b) reduce peak load by reducing
max_connections and/or scaling back your other servers; (c) switch to
another OS --- I don't think the *BSD kernels have this brain-damaged
idea about how to cope with low memory...

regards, tom lane