diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 8e2a2c5..bb67131 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1638,12 +1638,24 @@ SET ENABLE_SEQSCAN TO OFF; - The amount of memory used in shared memory for WAL data. The - default is 64 kilobytes (64kB). The setting need only - be large enough to hold the amount of WAL data generated by one - typical transaction, since the data is written out to disk at - every transaction commit. This parameter can only be set at server - start. + The amount of shared memory used for storing WAL data. The + default setting of -1 selects a size equal to 1/32 of + shared_buffers (about 3%), but not less than + 64kB or more than than the size of one WAL segment, + typically 16MB. This value may be manually + changed if it is too large or too small, but any positive value + less than 32kB will be treated as + 32kB. + + + + The contents of these buffers are written out to disk at every + transaction commit, so extremely large values are unlikely to + provide a significant benefit. However, setting this value to at + least a few megabytes can improve write performance on a busy + server where many clients are committing at once. The auto-tuning + selected by the default setting of -1 should give reasonable + results in most cases. diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 5b6a230..a5df3bb 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -69,7 +69,8 @@ /* User-settable parameters */ int CheckPointSegments = 3; int wal_keep_segments = 0; -int XLOGbuffers = 8; +int wal_buffers = -1; /* raw GUC setting */ +int XLOGbuffers = -1; /* buffers actually allocated */ int XLogArchiveTimeout = 0; bool XLogArchiveMode = false; char *XLogArchiveCommand = NULL; @@ -381,6 +382,7 @@ typedef struct XLogCtlData int XLogCacheBlck; /* highest allocated xlog buffer index */ TimeLineID ThisTimeLineID; TimeLineID RecoveryTargetTLI; + int wal_buffers; /* so backends can see auto-tuned value */ /* * archiveCleanupCommand is read from recovery.conf but needs to be in @@ -4778,6 +4780,47 @@ GetSystemIdentifier(void) } /* + * Auto-tune the number of XLOG buffers. + * + * When wal_buffers == -1, we auto-tune to about 3% of shared_buffers, with + * a maximum of one XLOG segment and a minimum of 8 blocks (the default value + * prior to PostgreSQL 9.1, when auto-tuning was added). We also clamp the + * manually set minimum to four blocks (prior to PostgreSQL 9.1, a smaller + * value would have been out of range, but since the minimum is now -1 for + * auto-tuning, we just silently treat such values as a request for the + * minimum). + */ +static void +XLOGTuneNumBuffers() +{ + XLOGbuffers = wal_buffers; + + if (XLOGbuffers == -1) + { + XLOGbuffers = NBuffers / 32; + if (XLOGbuffers > XLOG_SEG_SIZE / XLOG_BLCKSZ) + XLOGbuffers = XLOG_SEG_SIZE / XLOG_BLCKSZ; + if (XLOGbuffers < 8) + XLOGbuffers = 8; + } + else if (XLOGbuffers < 4) + XLOGbuffers = 4; +} + +/* + * Show the number of WAL buffers; we want this to display the auto-tuned value + * from shared memory. + */ +const char * +show_wal_buffers(void) +{ + static char buf[GUC_OUTPUT_BUFSIZE]; + + format_integer_guc_value(buf, XLogCtl->wal_buffers, GUC_UNIT_XBLOCKS); + return buf; +} + +/* * Initialization of shared memory for XLOG */ Size @@ -4785,6 +4828,10 @@ XLOGShmemSize(void) { Size size; + /* Figure out how many XLOG buffers we need. */ + XLOGTuneNumBuffers(); + Assert(XLOGbuffers > 0); + /* XLogCtl */ size = sizeof(XLogCtlData); /* xlblocks array */ @@ -4848,6 +4895,7 @@ XLOGShmemInit(void) XLogCtl->XLogCacheBlck = XLOGbuffers - 1; XLogCtl->SharedRecoveryInProgress = true; XLogCtl->Insert.currpage = (XLogPageHeader) (XLogCtl->pages); + XLogCtl->wal_buffers = XLOGbuffers; SpinLockInit(&XLogCtl->info_lck); InitSharedLatch(&XLogCtl->recoveryWakeupLatch); diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index ffff601..2f58f76 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1764,8 +1764,8 @@ static struct config_int ConfigureNamesInt[] = NULL, GUC_UNIT_XBLOCKS }, - &XLOGbuffers, - 8, 4, INT_MAX, NULL, NULL + &wal_buffers, + -1, -1, INT_MAX, NULL, show_wal_buffers }, { @@ -6743,7 +6743,7 @@ show_all_settings(PG_FUNCTION_ARGS) static char * _ShowOption(struct config_generic * record, bool use_units) { - char buffer[256]; + char buffer[GUC_OUTPUT_BUFSIZE]; const char *val; switch (record->vartype) @@ -6767,84 +6767,8 @@ _ShowOption(struct config_generic * record, bool use_units) val = (*conf->show_hook) (); else { - /* - * Use int64 arithmetic to avoid overflows in units - * conversion. - */ - int64 result = *conf->variable; - const char *unit; - - if (use_units && result > 0 && - (record->flags & GUC_UNIT_MEMORY)) - { - switch (record->flags & GUC_UNIT_MEMORY) - { - case GUC_UNIT_BLOCKS: - result *= BLCKSZ / 1024; - break; - case GUC_UNIT_XBLOCKS: - result *= XLOG_BLCKSZ / 1024; - break; - } - - if (result % KB_PER_GB == 0) - { - result /= KB_PER_GB; - unit = "GB"; - } - else if (result % KB_PER_MB == 0) - { - result /= KB_PER_MB; - unit = "MB"; - } - else - { - unit = "kB"; - } - } - else if (use_units && result > 0 && - (record->flags & GUC_UNIT_TIME)) - { - switch (record->flags & GUC_UNIT_TIME) - { - case GUC_UNIT_S: - result *= MS_PER_S; - break; - case GUC_UNIT_MIN: - result *= MS_PER_MIN; - break; - } - - if (result % MS_PER_D == 0) - { - result /= MS_PER_D; - unit = "d"; - } - else if (result % MS_PER_H == 0) - { - result /= MS_PER_H; - unit = "h"; - } - else if (result % MS_PER_MIN == 0) - { - result /= MS_PER_MIN; - unit = "min"; - } - else if (result % MS_PER_S == 0) - { - result /= MS_PER_S; - unit = "s"; - } - else - { - unit = "ms"; - } - } - else - unit = ""; - - snprintf(buffer, sizeof(buffer), INT64_FORMAT "%s", - result, unit); + format_integer_guc_value(buffer, *conf->variable, + use_units ? record->flags : 0); val = buffer; } } @@ -6898,6 +6822,84 @@ _ShowOption(struct config_generic * record, bool use_units) return pstrdup(val); } +/* + * Format an integer GUC value using appropriate units. + */ +void +format_integer_guc_value(char *buffer, int val, int flags) +{ + int64 result = val; + const char *unit; + + if (result > 0 && (flags & GUC_UNIT_MEMORY)) + { + switch (flags & GUC_UNIT_MEMORY) + { + case GUC_UNIT_BLOCKS: + result *= BLCKSZ / 1024; + break; + case GUC_UNIT_XBLOCKS: + result *= XLOG_BLCKSZ / 1024; + break; + } + + if (result % KB_PER_GB == 0) + { + result /= KB_PER_GB; + unit = "GB"; + } + else if (result % KB_PER_MB == 0) + { + result /= KB_PER_MB; + unit = "MB"; + } + else + { + unit = "kB"; + } + } + else if (result > 0 && (flags & GUC_UNIT_TIME)) + { + switch (flags & GUC_UNIT_TIME) + { + case GUC_UNIT_S: + result *= MS_PER_S; + break; + case GUC_UNIT_MIN: + result *= MS_PER_MIN; + break; + } + + if (result % MS_PER_D == 0) + { + result /= MS_PER_D; + unit = "d"; + } + else if (result % MS_PER_H == 0) + { + result /= MS_PER_H; + unit = "h"; + } + else if (result % MS_PER_MIN == 0) + { + result /= MS_PER_MIN; + unit = "min"; + } + else if (result % MS_PER_S == 0) + { + result /= MS_PER_S; + unit = "s"; + } + else + { + unit = "ms"; + } + } + else + unit = ""; + + snprintf(buffer, GUC_OUTPUT_BUFSIZE, INT64_FORMAT "%s", result, unit); +} /* * Attempt (badly) to detect if a proposed new GUC setting is the same diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f436b83..6c6f9a9 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -162,7 +162,7 @@ # fsync_writethrough # open_sync #full_page_writes = on # recover from partial page writes -#wal_buffers = 64kB # min 32kB +#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers # (change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index 74d3427..48eec74 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -193,6 +193,7 @@ extern XLogRecPtr XactLastRecEnd; extern int CheckPointSegments; extern int wal_keep_segments; extern int XLOGbuffers; +extern int wal_buffers; extern int XLogArchiveTimeout; extern bool XLogArchiveMode; extern char *XLogArchiveCommand; @@ -307,6 +308,7 @@ extern XLogRecPtr GetInsertRecPtr(void); extern XLogRecPtr GetFlushRecPtr(void); extern void GetNextXidAndEpoch(TransactionId *xid, uint32 *epoch); extern TimeLineID GetRecoveryTargetTLI(void); +extern const char *show_wal_buffers(void); extern void HandleStartupProcInterrupts(void); extern void StartupProcessMain(void); diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index c07f263..1f9c62e 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -294,6 +294,9 @@ extern bool set_config_option(const char *name, const char *value, extern char *GetConfigOptionByName(const char *name, const char **varname); extern void GetConfigOptionByNum(int varnum, const char **values, bool *noshow); extern int GetNumConfigOptions(void); +extern void format_integer_guc_value(char *buffer, int val, int flags); + +#define GUC_OUTPUT_BUFSIZE 256 extern void SetPGVariable(const char *name, List *args, bool is_local); extern void GetPGVariable(const char *name, DestReceiver *dest);