Proof-of-concept for initdb-time shared_buffers selection - Mailing list pgsql-patches

The attached patch shows how initdb can dynamically determine reasonable
shared_buffers and max_connections settings that will work on the
current machine.  It consists of two trivial adjustments: one rips out
the "PrivateMemory" code, so that a standalone backend will allocate a
shared memory segment the same way as a postmaster would do, and the
second adds a simple test loop in initdb that sees how large a setting
will still allow the backend to start.

The patch isn't quite complete since I didn't bother adding the few
lines of sed hacking needed to actually insert the selected values into
the installed postgresql.conf file, but that's just another few minutes'
work.  Adjusting the documentation to match would take a bit longer.

We might also want to tweak initdb to print a warning message if it's
forced to select very small values, but I didn't do that yet.

Questions for the list:

1. Does this approach seem like a reasonable solution to our problem
of some machines having unrealistically small kernel limits on shared
memory?

2. If so, can I get away with applying this post-feature-freeze?  I can
argue that it's a bug fix, but perhaps some will disagree.

3. What should be the set of tested values?  I have it as
   buffers: first to work of 1000 900 800 700 600 500 400 300 200 100 50
   connections: first to work of 100 50 40 30 20 10
but we could certainly argue for different rules.

            regards, tom lane


*** src/backend/port/sysv_shmem.c.orig    Thu May  8 15:17:07 2003
--- src/backend/port/sysv_shmem.c    Fri Jul  4 14:47:51 2003
***************
*** 45,52 ****
  static void *InternalIpcMemoryCreate(IpcMemoryKey memKey, uint32 size);
  static void IpcMemoryDetach(int status, Datum shmaddr);
  static void IpcMemoryDelete(int status, Datum shmId);
- static void *PrivateMemoryCreate(uint32 size);
- static void PrivateMemoryDelete(int status, Datum memaddr);
  static PGShmemHeader *PGSharedMemoryAttach(IpcMemoryKey key,
                                          IpcMemoryId *shmid, void *addr);

--- 45,50 ----
***************
*** 243,283 ****
  }


- /* ----------------------------------------------------------------
-  *                        private memory support
-  *
-  * Rather than allocating shmem segments with IPC_PRIVATE key, we
-  * just malloc() the requested amount of space.  This code emulates
-  * the needed shmem functions.
-  * ----------------------------------------------------------------
-  */
-
- static void *
- PrivateMemoryCreate(uint32 size)
- {
-     void       *memAddress;
-
-     memAddress = malloc(size);
-     if (!memAddress)
-     {
-         fprintf(stderr, "PrivateMemoryCreate: malloc(%u) failed\n", size);
-         proc_exit(1);
-     }
-     MemSet(memAddress, 0, size);    /* keep Purify quiet */
-
-     /* Register on-exit routine to release storage */
-     on_shmem_exit(PrivateMemoryDelete, PointerGetDatum(memAddress));
-
-     return memAddress;
- }
-
- static void
- PrivateMemoryDelete(int status, Datum memaddr)
- {
-     free(DatumGetPointer(memaddr));
- }
-
-
  /*
   * PGSharedMemoryCreate
   *
--- 241,246 ----
***************
*** 289,294 ****
--- 252,260 ----
   * collision with non-Postgres shmem segments.    The idea here is to detect and
   * re-use keys that may have been assigned by a crashed postmaster or backend.
   *
+  * makePrivate means to always create a new segment, rather than attach to
+  * or recycle any existing segment.
+  *
   * The port number is passed for possible use as a key (for SysV, we use
   * it to generate the starting shmem key).    In a standalone backend,
   * zero will be passed.
***************
*** 323,342 ****

      for (;;NextShmemSegID++)
      {
-         /* Special case if creating a private segment --- just malloc() it */
-         if (makePrivate)
-         {
-             memAddress = PrivateMemoryCreate(size);
-             break;
-         }
-
          /* Try to create new segment */
          memAddress = InternalIpcMemoryCreate(NextShmemSegID, size);
          if (memAddress)
              break;                /* successful create and attach */

          /* Check shared memory and possibly remove and recreate */
!
          if ((hdr = (PGShmemHeader *) memAddress = PGSharedMemoryAttach(
                          NextShmemSegID, &shmid, UsedShmemSegAddr)) == NULL)
              continue;            /* can't attach, not one of mine */
--- 289,304 ----

      for (;;NextShmemSegID++)
      {
          /* Try to create new segment */
          memAddress = InternalIpcMemoryCreate(NextShmemSegID, size);
          if (memAddress)
              break;                /* successful create and attach */

          /* Check shared memory and possibly remove and recreate */
!
!         if (makePrivate)        /* a standalone backend shouldn't do this */
!             continue;
!
          if ((hdr = (PGShmemHeader *) memAddress = PGSharedMemoryAttach(
                          NextShmemSegID, &shmid, UsedShmemSegAddr)) == NULL)
              continue;            /* can't attach, not one of mine */
*** src/backend/utils/init/postinit.c.orig    Fri Jun 27 10:45:30 2003
--- src/backend/utils/init/postinit.c    Fri Jul  4 14:47:43 2003
***************
*** 176,187 ****
      {
          /*
           * We're running a postgres bootstrap process or a standalone backend.
!          * Create private "shmem" and semaphores.  Force MaxBackends to 1 so
!          * that we don't allocate more resources than necessary.
           */
-         SetConfigOption("max_connections", "1",
-                         PGC_POSTMASTER, PGC_S_OVERRIDE);
-
          CreateSharedMemoryAndSemaphores(true, MaxBackends, 0);
      }
  }
--- 176,183 ----
      {
          /*
           * We're running a postgres bootstrap process or a standalone backend.
!          * Create private "shmem" and semaphores.
           */
          CreateSharedMemoryAndSemaphores(true, MaxBackends, 0);
      }
  }
*** src/bin/initdb/initdb.sh.orig    Fri Jul  4 12:41:21 2003
--- src/bin/initdb/initdb.sh    Fri Jul  4 15:19:11 2003
***************
*** 579,584 ****
--- 579,618 ----

  ##########################################################################
  #
+ # DETERMINE PLATFORM-SPECIFIC CONFIG SETTINGS
+ #
+ # Use reasonable values if kernel will let us, else scale back
+
+ cp /dev/null "$PGDATA"/postgresql.conf
+
+ $ECHO_N "selecting default shared_buffers... "$ECHO_C
+
+ for nbuffers in 1000 900 800 700 600 500 400 300 200 100 50
+ do
+     TEST_OPT="$PGSQL_OPT -c shared_buffers=$nbuffers -c max_connections=5"
+     if "$PGPATH"/postgres $TEST_OPT template1 </dev/null >/dev/null 2>&1
+     then
+     break
+     fi
+ done
+
+ echo "$nbuffers"
+
+ $ECHO_N "selecting default max_connections... "$ECHO_C
+
+ for nconns in 100 50 40 30 20 10
+ do
+     TEST_OPT="$PGSQL_OPT -c shared_buffers=$nbuffers -c max_connections=$nconns"
+     if "$PGPATH"/postgres $TEST_OPT template1 </dev/null >/dev/null 2>&1
+     then
+     break
+     fi
+ done
+
+ echo "$nconns"
+
+ ##########################################################################
+ #
  # CREATE CONFIG FILES

  $ECHO_N "creating configuration files... "$ECHO_C