On Thu, Oct 10, 2013 at 11:18:28AM -0700, Josh Berkus wrote:
> Bruce,
>
> >> That's way low, and frankly it's not worth bothering with this if all
> >> we're going to get is an incremental increase. In that case, let's just
> >> set the default to 4MB like Robert suggested.
> >
> > Uh, well, 100 backends at 6MB gives us 600MB, and if each backend uses
> > 3x work_mem, that gives us 1.8GB for total work_mem. This was based on
> > Andrew's concerns about possible over-commit of work_mem. I can of
> > course adjust that.
>
> That's worst-case-scenario planning -- the 3X work-mem per backend was:
> a) Solaris and
> b) data warehousing
>
> In a normal OLTP application each backend averages something like 0.25 *
> work_mem, since many queries use no work_mem at all.
>
> It also doesn't address my point that, if we are worst-case-scenario
> default-setting, we're going to end up with defaults which aren't
> materially different from the current defaults. In which case, why even
> bother with this whole exercise?
OK, here is an updated patch that is less conservative. FYI, this
thread has gone on for 80 messages, and I assume it will take many more
until we are done:
test=> SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)
test=> SHOW work_mem;
work_mem
----------
2621kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
10922kB
(1 row)
---------------------------------------------------------------------------
test=> SHOW shared_buffers;
shared_buffers
----------------
2GB
(1 row)
test=> SHOW work_mem;
work_mem
----------
41943kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
174762kB
(1 row)
---------------------------------------------------------------------------
test=> SHOW shared_buffers;
shared_buffers
----------------
8GB
(1 row)
test=> SHOW work_mem;
work_mem
----------
167772kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
699050kB
(1 row)
Patch attached.
--
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..2f00c74
*** 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 2 * <varname>shared_buffers</> /
! <varname>max_connections</>.
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..b1b694b
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*************** static struct config_int ConfigureNamesI
*** 1720,1727 ****
GUC_UNIT_KB
},
&work_mem,
! 1024, 64, MAX_KILOBYTES,
! NULL, NULL, NULL
},
{
--- 1720,1727 ----
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
},
/*
--- 1731,1738 ----
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.
*/
--- 4239,4246 ----
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 ****
--- 8887,8980 ----
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", (2L * NBuffers * BLCKSZ / 1024L) /
+ MaxConnections);
+ 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 / 4L) * BLCKSZ / 1024) /
+ 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 = (2L * NBuffers * BLCKSZ / 1024) / MaxConnections;
+ }
+
+ /* 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 / 4L) * BLCKSZ / 1024) / 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
