From 0f73aae13def660371c34c8feda6e684e6366bdb Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 22 Jan 2011 20:31:24 -0500 Subject: [PATCH] Allow the wal_buffers setting to be auto-tuned to a reasonable value. If wal_buffers is initially set to -1 (which is now the default), it's replaced by 1/32nd of shared_buffers, with a minimum of 8 (the old default) and a maximum of the XLOG segment size. The allowed range for manual settings is still from 4 up to whatever will fit in shared memory. Greg Smith, with implementation correction by me. --- doc/src/sgml/config.sgml | 25 ++++++++++++---- src/backend/access/transam/xlog.c | 41 ++++++++++++++++++++++++++- src/backend/utils/misc/guc.c | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- 4 files changed, 61 insertions(+), 9 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 8e2a2c5d73..570c7c3b7d 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1638,12 +1638,25 @@ 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 WAL data that has not yet been + written to disk. The default setting of -1 selects a size equal to + 1/32nd (about 3%) of , but not less + than 64kB nor more than the size of one WAL + segment, typically 16MB. This value can be set + manually if the automatic choice is too large or too small, + but any positive value less than 32kB will be + treated as 32kB. + This parameter can only be set at server start. + + + + The contents of the WAL 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 5b6a2302b1..85b2dcae07 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -69,7 +69,7 @@ /* User-settable parameters */ int CheckPointSegments = 3; int wal_keep_segments = 0; -int XLOGbuffers = 8; +int XLOGbuffers = -1; int XLogArchiveTimeout = 0; bool XLogArchiveMode = false; char *XLogArchiveCommand = NULL; @@ -4778,6 +4778,41 @@ GetSystemIdentifier(void) } /* + * Auto-tune the number of XLOG buffers. + * + * If the user-set value of wal_buffers is -1, we auto-tune to about 3% of + * shared_buffers, with a maximum of one XLOG segment and a minimum of 8 + * blocks (8 was the default value prior to PostgreSQL 9.1, when auto-tuning + * was added). We also clamp manually-set values to at least 4 blocks; prior + * to PostgreSQL 9.1, a minimum of 4 was enforced by guc.c, but since that + * is no longer possible, we just silently treat such values as a request for + * the minimum. + */ +static void +XLOGTuneNumBuffers(void) +{ + int xbuffers = XLOGbuffers; + char buf[32]; + + if (xbuffers == -1) + { + xbuffers = NBuffers / 32; + if (xbuffers > XLOG_SEG_SIZE / XLOG_BLCKSZ) + xbuffers = XLOG_SEG_SIZE / XLOG_BLCKSZ; + if (xbuffers < 8) + xbuffers = 8; + } + else if (xbuffers < 4) + xbuffers = 4; + + if (xbuffers != XLOGbuffers) + { + snprintf(buf, sizeof(buf), "%d", xbuffers); + SetConfigOption("wal_buffers", buf, PGC_POSTMASTER, PGC_S_OVERRIDE); + } +} + +/* * Initialization of shared memory for XLOG */ Size @@ -4785,6 +4820,10 @@ XLOGShmemSize(void) { Size size; + /* Figure out how many XLOG buffers we need. */ + XLOGTuneNumBuffers(); + Assert(XLOGbuffers > 0); + /* XLogCtl */ size = sizeof(XLogCtlData); /* xlblocks array */ diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index ffff601559..73b9f1b01c 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1765,7 +1765,7 @@ static struct config_int ConfigureNamesInt[] = GUC_UNIT_XBLOCKS }, &XLOGbuffers, - 8, 4, INT_MAX, NULL, NULL + -1, -1, INT_MAX, NULL, NULL }, { diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f436b83468..6c6f9a9a0d 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 -- 2.11.0