Tom Lane wrote:
Leaving aside the question of max_connections, which seems to be the
most controversial, is there any objection to the proposal to increase
the settings tried for shared_buffers (up to 4000) and max_fsm_pages (up
to 200000) ? If not, I'll apply a patch for those changes shortly.
You probably need to fix the max-connections pass so that it applies the
same changes to max_fsm_pages as the second pass does --- otherwise, its
assumption that shared_buffers can really be set that way will be wrong.
Other than that I didn't see any problem with the shared_buffers part of
the patch.
revised patch attached, leaving max_connections alone except as above.
I'll apply this in a day or two, barring objection.
cheers
andrew
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 26 Dec 2005 22:44:09 -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
***************
*** 1084,1089 ****
--- 1085,1097 ----
}
/*
+ * max_fsm_pages setting used in both the shared_buffers and max_connections
+ * tests.
+ */
+
+ #define TEST_FSM(x) ( (x) > 1000 ? 50 * (x) : 20000 )
+
+ /*
* check how many connections we can sustain
*/
static void
***************
*** 1100,1111 ****
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,
! conns[i] * 5, conns[i],
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
if (status == 0)
--- 1108,1124 ----
for (i = 0; i < len; i++)
{
+ int test_buffs = conns[i] * 5;
+ int test_max_fsm = TEST_FSM(test_buffs);
+
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,
! test_buffs, conns[i],
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
if (status == 0)
***************
*** 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);
--- 1138,1167 ----
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 = TEST_FSM(bufs[i]);
+
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);
}
/*
--- 1171,1179 ----
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 ****
--- 1199,1207 ----
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);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
