Absurd Shared Memory Limits

Today, I fixed a problem.  Or at least, I think I fixed it.  Time will tell.  But Thom Brown seems pretty happy, and so does Dan Farina.  So let me tell you about it.  Here's the executive summary: assuming the patch I committed today holds up, PostgreSQL 9.3 will largely eliminate the need to fiddle with operating system shared memory limits.

A PostgreSQL database cluster involves multiple processes - one per session plus a few extras - that need to be able to communicate via a shared memory segment.  For historical reasons, most UNIX-like operating systems provide at least three different methods of creating a shared memory segment.  In the beginning, there was System V shared memory.  Then, a bunch of people got together and decided that they didn't like the System V shared memory interface very much, so they created a new interface called POSIX shared memory which did mostly the same thing - but not quite.  Both System V shared memory and POSIX shared memory involve creating named shared memory segments (though they use different naming conventions); to attach to one of these segments, you identify it by name.  At some point along the way, it became possible to create shared memory segments in yet another way using a system call named mmap() and passing it the options MAP_SHARED and MAP_ANONYMOUS.  Shared memory segments created this way don't have a name, so they can only be shared between a parent process and its descendents.

PostgreSQL uses System V shared memory, because it provides a feature that is available via neither of the other two systems: the ability to atomically determine the number of processes attached to the shared memory segment.  When the first PostgreSQL process attaches to the shared memory segment, it checks how many processes are attached.  If the result is anything other than "one", it knows that there's another copy of PostgreSQL running which is pointed at the same data directory, and it bails out.  This is really good, because having two PostgreSQL processes pointed at the same data directory at the same time is a sure way to corrupt your database.

Despite the fact that System V shared memory is the only available shared memory implementation that provides this feature, most operating system vendors frown on it, and encourage users to use the newer POSIX shared memory facilities instead.  They do this by limiting the amount of System V shared memory that can be allocated by default to absurdly small values, often 32MB.  On some older systems, you actually had to recompile the kernel to raise the limit; thankfully, on modern systems, it's usually as simple as editing /etc/sysctl.conf and running sysctl -f /etc/sysctl.conf to read the update settings.  Still, it poses a needless obstacle for new PostgreSQL users, who now have a choice between (1) terrible database performance and (2) fiddling with kernel settings that they don't understand.

In my opinion, the decision to tightly limit the amount of System V shared memory  that can be allocated is a poor one on the part of OS vendors.  POSIX shared memory and mmap's anonymous shared memory have much higher limits, or none at all; and as far as I can see, limiting System V shared memory makes things inconvenient for users of programs like PostgreSQL without any compensating advantage.

The good news is that the above-mentioned commit contains a workaround.  We allocate a very small System V shared memory segment (48 bytes, on the systems I tested; it could vary slightly by platform) which provides the interlock to prevent multiple instances of PostgreSQL from attaching to the same data directory at the same time, and allocate a large anonymous shared memory block for everything else.  Assuming the patch doesn't get reverted for one reason or another, this means that in PostgreSQL 9.3 it will be possible to start PostgreSQL on all platforms I'm familiar with - using an arbitrarily high shared_buffers setting - without any adjustment of default operating system limits.  That should hopefully make things easier for first-time users.

Here's a link to the thread on pgsql-hackers, for those wanting to read more.