On Wed, Oct 9, 2013 at 09:52:03AM -0700, Josh Berkus wrote:
> On 10/09/2013 09:30 AM, Stephen Frost wrote:
> >>> I went with shared_buffers because unlike the others, it is a fixed
> >>> > > allocation quantity, while the other are much more variable and harder
> >>> > > to set. I figured we could keep our 25% estimate of shared_buffers
> >>> > > and
> >>> > > everything else would fall in line.
> >>> > >
> >> >
> >> > I understand, but your proposal change a logic to opposite direction.
> >> > Maybe
> >> > better is wait to new GUC parameter, and then implement this feature, so
> >> > be
> >> > logical and simply understandable.
> > I disagree- having a better default than what we have now is going to
> > almost certainly be a huge improvement in the vast majority of cases.
> > How we arrive at the default isn't particularly relevant as long as we
> > document it. Users who end up using the default don't do so because
> > they read the docs and said "oh, yeah, the way they calculated the
> > default makes a lot of sense", then end up using it because they never
> > open the config file, at all.
>
> FWIW, I've been using the following calculations as "starting points"
> for work_mem with both clients and students. In 80-90% of cases, the
> user never adjusts the thresholds again, so I'd say that passes the test
> for a "good enough" setting.
OK, I have developed the attached patch based on feedback. I took into
account Andrew's concern that pooling might cause use of more work_mem
than you would expect in a typical session, and Robert's legitimate
concern about a destabalizing default for work_mem. I therefore went
with the shared_buffers/4 idea. Josh had some interesting calculations
for work_mem, but I didn't think the max value would work well as it
would confuse users and not be properly maintained by us as hardware
grew. I also think changing those defaults between major releases would
be perhaps destabilizing. Josh's observation that he rarely sees more
than 3x work_mem in a session helps put an upper limit on memory usage.
I did like Josh's idea about using autovacuum_max_workers for
maintenance_work_mem, though I used the shared_buffers/4 calculation.
Here are the defaults for two configurations; first, for the 128MB
default shared_buffers:
test=> SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)
test=> SHOW work_mem;
work_mem
----------
1095kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
10922kB
(1 row)
and for shared_buffers of 2GB:
test=> show shared_buffers;
shared_buffers
----------------
2GB
(1 row)
test=> SHOW work_mem;
work_mem
----------
6010kB
(1 row)
test=> SHOW maintenance_work_mem ;
maintenance_work_mem
----------------------
174762kB
(1 row)
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index e8e8e6f..31f1265
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*************** include 'filename'
*** 1121,1127 ****
<para>
Specifies the amount of memory to be used by internal sort operations
and hash tables before writing to temporary disk files. The value
! defaults to one megabyte (<literal>1MB</>).
Note that for a complex query, several sort or hash operations might be
running in parallel; each operation will be allowed to use as much memory
as this value specifies before it starts to write data into temporary
--- 1121,1128 ----
<para>
Specifies the amount of memory to be used by internal sort operations
and hash tables before writing to temporary disk files. The value
! defaults to <varname>shared_buffers</> / 4 /
! <varname>max_connections</> + 768kB.
Note that for a complex query, several sort or hash operations might be
running in parallel; each operation will be allowed to use as much memory
as this value specifies before it starts to write data into temporary
*************** include 'filename'
*** 1147,1153 ****
Specifies the maximum amount of memory to be used by maintenance
operations, such as <command>VACUUM</command>, <command>CREATE
INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
! to 16 megabytes (<literal>16MB</>). Since only one of these
operations can be executed at a time by a database session, and
an installation normally doesn't have many of them running
concurrently, it's safe to set this value significantly larger
--- 1148,1155 ----
Specifies the maximum amount of memory to be used by maintenance
operations, such as <command>VACUUM</command>, <command>CREATE
INDEX</>, and <command>ALTER TABLE ADD FOREIGN KEY</>. It defaults
! to <varname>shared_buffers</> / 4 /
! <varname>autovacuum_max_workers</>. Since only one of these
operations can be executed at a time by a database session, and
an installation normally doesn't have many of them running
concurrently, it's safe to set this value significantly larger
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
new file mode 100644
index 33efb3c..68af1dc
*** a/src/backend/utils/init/globals.c
--- b/src/backend/utils/init/globals.c
*************** int CTimeZone = 0;
*** 98,105 ****
bool enableFsync = true;
bool allowSystemTableMods = false;
! int work_mem = 1024;
! int maintenance_work_mem = 16384;
/*
* Primary determinants of sizes of shared-memory structures.
--- 98,105 ----
bool enableFsync = true;
bool allowSystemTableMods = false;
! int work_mem = -1;
! int maintenance_work_mem = -1;
/*
* Primary determinants of sizes of shared-memory structures.
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
new file mode 100644
index ddbeb34..29b1943
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 117,122 ****
--- 117,125 ----
#define S_PER_D (60 * 60 * 24)
#define MS_PER_D (1000 * 60 * 60 * 24)
+ /* This is the minimum added for auto-tuning of work_mem */
+ #define WORK_MEM_ADDITION_kB 768
+
/* XXX these should appear in other modules' header files */
extern bool Log_disconnections;
extern int CommitDelay;
*************** static struct config_int ConfigureNamesI
*** 1720,1727 ****
GUC_UNIT_KB
},
&work_mem,
! 1024, 64, MAX_KILOBYTES,
! NULL, NULL, NULL
},
{
--- 1723,1730 ----
GUC_UNIT_KB
},
&work_mem,
! -1, -1, MAX_KILOBYTES,
! check_work_mem, NULL, NULL
},
{
*************** static struct config_int ConfigureNamesI
*** 1731,1738 ****
GUC_UNIT_KB
},
&maintenance_work_mem,
! 16384, 1024, MAX_KILOBYTES,
! NULL, NULL, NULL
},
/*
--- 1734,1741 ----
GUC_UNIT_KB
},
&maintenance_work_mem,
! -1, -1, MAX_KILOBYTES,
! check_maintenance_work_mem, NULL, NULL
},
/*
*************** SelectConfigFiles(const char *userDoptio
*** 4239,4245 ****
pg_timezone_abbrev_initialize();
set_default_effective_cache_size();
!
/*
* Figure out where pg_hba.conf is, and make sure the path is absolute.
*/
--- 4242,4249 ----
pg_timezone_abbrev_initialize();
set_default_effective_cache_size();
! set_default_work_mems();
!
/*
* Figure out where pg_hba.conf is, and make sure the path is absolute.
*/
*************** show_log_file_mode(void)
*** 8886,8889 ****
--- 8890,8984 ----
return buf;
}
+ void
+ set_default_work_mems(void)
+ {
+ /*
+ * If the value of work_mem is -1, use the preferred
+ * auto-tune value.
+ */
+ if (work_mem == -1)
+ {
+ char buf[32];
+
+ snprintf(buf, sizeof(buf), "%ld", ((NBuffers / 4) * BLCKSZ / 1024L) /
+ MaxConnections + WORK_MEM_ADDITION_kB);
+ SetConfigOption("work_mem", buf, PGC_POSTMASTER, PGC_S_OVERRIDE);
+ }
+ Assert(work_mem > 0);
+
+ if (maintenance_work_mem == -1)
+ {
+ char buf[32];
+
+ snprintf(buf, sizeof(buf), "%ld", ((NBuffers / 4) * BLCKSZ / 1024L) /
+ autovacuum_max_workers);
+ SetConfigOption("maintenance_work_mem", buf, PGC_POSTMASTER, PGC_S_OVERRIDE);
+ }
+ Assert(maintenance_work_mem > 0);
+ }
+
+ /*
+ * GUC check_hook for work_mem
+ */
+ bool
+ check_work_mem(int *newval, void **extra, GucSource source)
+ {
+ /*
+ * -1 indicates a request for auto-tune.
+ */
+ if (*newval == -1)
+ {
+ /*
+ * If we haven't yet changed the boot_val default of -1, just let it
+ * be. We'll fix it later.
+ */
+ if (work_mem == -1)
+ return true;
+
+ /* Otherwise, substitute the auto-tune value */
+ *newval = ((NBuffers / 4) * BLCKSZ / 1024L) / MaxConnections +
+ WORK_MEM_ADDITION_kB;
+ }
+
+ /* set minimum? check maximum? */
+ if (*newval < 64)
+ *newval = 64;
+ if (*newval > MAX_KILOBYTES)
+ *newval = MAX_KILOBYTES;
+
+ return true;
+ }
+
+ /*
+ * GUC check_hook for maintenance_work_mem
+ */
+ bool
+ check_maintenance_work_mem(int *newval, void **extra, GucSource source)
+ {
+ /*
+ * -1 indicates a request for auto-tune.
+ */
+ if (*newval == -1)
+ {
+ /*
+ * If we haven't yet changed the boot_val default of -1, just let it
+ * be. We'll fix it later.
+ */
+ if (maintenance_work_mem == -1)
+ return true;
+
+ /* Otherwise, substitute the auto-tune value */
+ *newval = ((NBuffers / 4) * BLCKSZ / 1024L) / autovacuum_max_workers;
+ }
+
+ /* set minimum? check maximum? */
+ if (*newval < 1024)
+ *newval = 1024;
+ if (*newval > MAX_KILOBYTES)
+ *newval = MAX_KILOBYTES;
+
+ return true;
+ }
+
#include "guc-file.c"
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
new file mode 100644
index 70221f4..a8da95b
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 120,127 ****
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
! #work_mem = 1MB # min 64kB
! #maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Disk -
--- 120,127 ----
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
! #work_mem = -1 # min 64kB
! #maintenance_work_mem = -1 # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Disk -
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
new file mode 100644
index 3e981b3..f696eed
*** a/src/include/utils/guc.h
--- b/src/include/utils/guc.h
*************** extern void assign_search_path(const cha
*** 388,393 ****
--- 388,396 ----
extern bool check_wal_buffers(int *newval, void **extra, GucSource source);
extern bool check_effective_cache_size(int *newval, void **extra, GucSource source);
extern void set_default_effective_cache_size(void);
+ extern bool check_work_mem(int *newval, void **extra, GucSource source);
+ extern bool check_maintenance_work_mem(int *newval, void **extra, GucSource source);
+ extern void set_default_work_mems(void);
extern void assign_xlog_sync_method(int new_sync_method, void *extra);
#endif /* GUC_H */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
