Re: default resource limits - Mailing list pgsql-patches

er patch attached this time

Andrew Dunstan wrote:

>
>
> I wrote:
>
>>
>>
>> Tom Lane wrote:
>>
>>> Andrew Dunstan <andrew@dunslane.net> writes:
>>>
>>>
>>>> Nearly everyone seems to agree that the default for max_fsm_pages
>>>> is woefully low, so I would like to have the default for this set
>>>> unconditionally to 200,000 rather than 20,000. The cost would be
>>>> just over 1Mb of shared memory, if the docs are correct.
>>>> Alternatively, we could put this into the mix that is calculated by
>>>> initdb, scaling it linearly with shared_buffers (but with the
>>>> default still at 200,000).
>>>>
>>>
>>>
>>>
>>>
>>>
>>>> I would also like to propose a more modest increase in
>>>> max_connections and shared_buffers by a factor of 3.
>>>>
>>>
>>>
>>>
>>> I don't mind having initdb try larger values to see if they work, but
>>> if you are suggesting that we try to force adoption of larger settings
>>> I'll resist it.
>>>
>>>
>>
>> OK, works for me. The only thing I suggested might be set in stone
>> was max_fsm_pages; I always envisioned the others being tested as now
>> by initdb.
>>
>>> "Factor of three" seems mighty weird.  The existing numbers (100 and
>>> 1000)
>>> at least have the defensibility of being round.
>>>
>>>
>>>
>>
>> What numbers would you like? If what I suggested seems odd, how about
>> targets of 400 connections, 4000 shared_buffers and 200,000
>> max_fsm_pages?
>>
>>
>
>
> Here's a patch that does what I had in mind. On my modest workstation
> it tops out at 400 connections and 2500/125000
> shared_buffers/max_fsm_pages. An idle postmaster with these settings
> consumed less than 4% of the 380Mb of memory, according to top, making
> it still dwarfed by X, mozilla, apache and amavisd among other memory
> hogs.
>
> Comments welcome.
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.101
diff -c -r1.101 initdb.c
*** src/bin/initdb/initdb.c    9 Dec 2005 15:51:14 -0000    1.101
--- src/bin/initdb/initdb.c    23 Dec 2005 20:29:15 -0000
***************
*** 120,125 ****
--- 120,126 ----
  /* defaults */
  static int    n_connections = 10;
  static int    n_buffers = 50;
+ static int  n_fsm_pages = 20000;

  /*
   * Warning messages for authentication methods
***************
*** 1090,1096 ****
  test_connections(void)
  {
      char        cmd[MAXPGPATH];
!     static const int conns[] = {100, 50, 40, 30, 20, 10};
      static const int len = sizeof(conns) / sizeof(int);
      int            i,
                  status;
--- 1091,1098 ----
  test_connections(void)
  {
      char        cmd[MAXPGPATH];
!     static const int conns[] = {400, 300, 250, 200, 150,
!                                 100, 50, 40, 30, 20, 10};
      static const int len = sizeof(conns) / sizeof(int);
      int            i,
                  status;
***************
*** 1125,1146 ****
  test_buffers(void)
  {
      char        cmd[MAXPGPATH];
!     static const int bufs[] = {1000, 900, 800, 700, 600, 500,
!     400, 300, 200, 100, 50};
      static const int len = sizeof(bufs) / sizeof(int);
      int            i,
!                 status;

!     printf(_("selecting default shared_buffers ... "));
      fflush(stdout);

      for (i = 0; i < len; i++)
      {
          snprintf(cmd, sizeof(cmd),
                   "%s\"%s\" -boot -x0 %s "
                   "-c shared_buffers=%d -c max_connections=%d template1 "
                   "< \"%s\" > \"%s\" 2>&1%s",
                   SYSTEMQUOTE, backend_exec, boot_options,
                   bufs[i], n_connections,
                   DEVNULL, DEVNULL, SYSTEMQUOTE);
          status = system(cmd);
--- 1127,1155 ----
  test_buffers(void)
  {
      char        cmd[MAXPGPATH];
!     static const int bufs[] = {
!       4000, 3500, 3000, 2500, 2000, 1500,
!       1000, 900, 800, 700, 600, 500,
!       400, 300, 200, 100, 50
!     };
      static const int len = sizeof(bufs) / sizeof(int);
      int            i,
!                 status,
!                 test_max_fsm_pages;

!     printf(_("selecting default shared_buffers/max_fsm_pages ... "));
      fflush(stdout);

      for (i = 0; i < len; i++)
      {
+         test_max_fsm_pages = bufs[i] > 1000 ? 50 * bufs[i] : 20000;
          snprintf(cmd, sizeof(cmd),
                   "%s\"%s\" -boot -x0 %s "
+                  "-c max_fsm_pages=%d "
                   "-c shared_buffers=%d -c max_connections=%d template1 "
                   "< \"%s\" > \"%s\" 2>&1%s",
                   SYSTEMQUOTE, backend_exec, boot_options,
+                  test_max_fsm_pages,
                   bufs[i], n_connections,
                   DEVNULL, DEVNULL, SYSTEMQUOTE);
          status = system(cmd);
***************
*** 1150,1157 ****
      if (i >= len)
          i = len - 1;
      n_buffers = bufs[i];

!     printf("%d\n", n_buffers);
  }

  /*
--- 1159,1167 ----
      if (i >= len)
          i = len - 1;
      n_buffers = bufs[i];
+     n_fsm_pages = test_max_fsm_pages;

!     printf("%d/%d\n", n_buffers, n_fsm_pages);
  }

  /*
***************
*** 1177,1182 ****
--- 1187,1195 ----
      snprintf(repltok, sizeof(repltok), "shared_buffers = %d", n_buffers);
      conflines = replace_token(conflines, "#shared_buffers = 1000", repltok);

+     snprintf(repltok, sizeof(repltok), "max_fsm_pages = %d", n_fsm_pages);
+     conflines = replace_token(conflines, "#max_fsm_pages = 20000", repltok);
+
  #if DEF_PGPORT != 5432
      snprintf(repltok, sizeof(repltok), "#port = %d", DEF_PGPORT);
      conflines = replace_token(conflines, "#port = 5432", repltok);