Josh Berkus suggested here that work_mem and maintenance_work_mem could
be auto-tuned like effective_cache_size:
http://www.postgresql.org/message-id/[email protected]
The attached patch implements this, closely matching the default values
for the default shared_buffers value:
test=> SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)
test=> SHOW work_mem;
work_mem
----------
1310kB
(1 row)
test=> SHOW maintenance_work_mem;
maintenance_work_mem
----------------------
20971kB
(1 row)
Previous defaults were 1MB and 16MB, but the new defaults don't match
exactly because our max_connections is a power of 10 (100), not a power
of 2 (128). Of course, if shared_buffer is 10x larger, those defaults
become 10x larger.
FYI, I based maintenance_work_mem's default on shared_buffers, not on
work_mem because it was too complex to change maintenance_work_mem when
someone changes work_mem.
I will work on auto-tuning temp_buffers next. Any other suggestions?
wal_buffers is already auto-tuned.
--
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..b1d6ba7
*** 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,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 <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
--- 1147,1154 ----
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 * <varname>shared_buffers</> / <varname>max_connections</>.
! 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..c6c2d63
*** 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,8979 ----
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 * 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", 16 * (NBuffers * BLCKSZ / 1024L) /
+ MaxConnections);
+ 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 * BLCKSZ / 1024L) / 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 = 16 * (NBuffers * BLCKSZ / 1024L) / MaxConnections;
+ }
+
+ /* 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
