From: Greg Smith <greg@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: auto-sizing wal_buffers
Date: Sat, 15 Jan 2011 01:51:50 -0500
Message-ID: <4D314406.3000703@2ndquadrant.com> (raw)
In-Reply-To: <20767.1294964709@sss.pgh.pa.us>
References: <AANLkTik3mZ3sTfbHQW-1B44NRMkUzRsMnPb8g-HtttVv@mail.gmail.com>
<AANLkTimQ3+osgAEH4meS97FmLN-4evSTS2W6fctov=jY@mail.gmail.com>
<AANLkTinhU9EEPGM29S+KH=qK4t-mLhBBNAaLCGQSx-4y@mail.gmail.com>
<20767.1294964709@sss.pgh.pa.us>
Tom Lane wrote:
> I think we need to keep the override capability until the autotune
> algorithm has proven itself in the field for a couple of years.
>
> I agree with Josh that a negative value should be used to select the
> autotune method.
>
Agreed on both fronts. Attached patch does the magic. Also available
in branch "walbuffers" from git://github.com/greg2ndQuadrant/postgres.git
By changing only shared_buffers I get the following quite reasonable
automatic behavior:
$ psql -c "SELECT name,unit,boot_val,setting,current_setting(name) FROM
pg_settings WHERE name IN ('wal_buffers','shared_buffers')"
name | unit | boot_val | setting | current_setting
----------------+------+----------+---------+-----------------
shared_buffers | 8kB | 1024 | 3072 | 24MB
wal_buffers | 8kB | -1 | 96 | 768kB
shared_buffers | 8kB | 1024 | 4096 | 32MB
wal_buffers | 8kB | -1 | 128 | 1MB
shared_buffers | 8kB | 1024 | 16384 | 128MB
wal_buffers | 8kB | -1 | 512 | 4MB
shared_buffers | 8kB | 1024 | 131072 | 1GB
wal_buffers | 8kB | -1 | 2048 | 16MB
shared_buffers | 8kB | 1024 | 262144 | 2GB
wal_buffers | 8kB | -1 | 2048 | 16MB
If you've set it to the auto-tuning behavior, you don't see that setting
of -1 in the SHOW output; you see the value it's actually been set to.
The only way to know that was set automatically is to look at boot_val
as I've shown here. I consider this what admins would prefer, as the
easy way to expose the value that was used. I would understand if
people considered it a little odd though. Since you can't change it
without a postgresql.conf edit and a server start anyway, and it's
tersely documented in the sample postgresql.conf what -1 does, I don't
see this being a problem for anyone in the field.
To try and clear up some of the confusion around how the earlier
documentation suggests larger values of this aren't needed, I added the
following updated description of how this has been observed to work for
admins in practice:
! Since the data is written out to disk at every transaction commit,
! the setting many only need to be be large enough to hold the
amount
! of WAL data generated by one typical transaction. Larger values,
! typically at least a few megabytes, can improve write performance
! on a busy server where many clients are committing at once.
! Extremely large settings are unlikely to provide additional
benefit.
And to make this easy as possible to apply if I got this right, here's
some proposed commit text:
Automatically set wal_buffers to be proportional
to the size of shared_buffers. Make it 1/32
as large when the auto-tuned behavior, which
is the default and set with a value of -1,
is used. The previous default of 64kB is still
enforced as a minimum value. The maximum
automatic value is limited to 16MB.
(Note that this not exactly what I put in my own commit message if you
grab from my repo, that had a typo)
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/booksAttachments:auto-wal-buffers-v1.patch (text/x-patch, 5.0K) downloaddiff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 8e2a2c5..c3f5632 100644
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*************** SET ENABLE_SEQSCAN TO OFF;
*** 1638,1649 ****
</indexterm>
<listitem>
<para>
! The amount of memory used in shared memory for WAL data. The
! default is 64 kilobytes (<literal>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.
</para>
<para>
--- 1638,1659 ----
</indexterm>
<listitem>
<para>
! The amount of shared memory used for storing WAL data. The
! default setting of -1 adjusts this automatically based on the size
! of <varname>shared_buffers</varname>, making it 1/32 (about 3%) of
! the size of that normally larger shared memory block. Automatically
! set values are limited to a maximum of 16 megabytes
! (<literal>16MB</>), sufficient to hold one WAL segment worth of data.
! The smallest allowable setting is 64 kilobytes (<literal>64kB</>).
! </para>
!
! <para>
! Since the data is written out to disk at every transaction commit,
! the setting many only need to be be large enough to hold the amount
! of WAL data generated by one typical transaction. Larger values,
! typically at least a few megabytes, can improve write performance
! on a busy server where many clients are committing at once.
! Extremely large settings are unlikely to provide additional benefit.
</para>
<para>
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.cindex b49b933..060e627 100644
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 68,74 ****
/* User-settable parameters */
int CheckPointSegments = 3;
int wal_keep_segments = 0;
! int XLOGbuffers = 8;
int XLogArchiveTimeout = 0;
bool XLogArchiveMode = false;
char *XLogArchiveCommand = NULL;
--- 68,75 ----
/* User-settable parameters */
int CheckPointSegments = 3;
int wal_keep_segments = 0;
! int XLOGbuffers = -1;
! int XLOGbuffersMin = 8;
int XLogArchiveTimeout = 0;
bool XLogArchiveMode = false;
char *XLogArchiveCommand = NULL;
*************** GetSystemIdentifier(void)
*** 4779,4789 ****
--- 4780,4812 ----
/*
* Initialization of shared memory for XLOG
*/
+ + void XLOGTuneNumBuffers(void)+ {+ /*+ * If automatic setting was requested, use about 3% as much memory as+ * requested for the buffer cache. Clamp the automatic maximum to the+ * size of one 16MB XLOG segment, while still allowing a larger manual+ * setting.+ */ + if (XLOGbuffers == -1)+ {+ XLOGbuffers = NBuffers / 32;+ if (XLOGbuffers > 2048)+ XLOGbuffers = 2048;+ }+ /* Enforce a 64KB minimum */+ if (XLOGbuffers < XLOGbuffersMin)+ XLOGbuffers = XLOGbuffersMin;+ }+
Size
XLOGShmemSize(void)
{
Size size;
+ XLOGTuneNumBuffers();+
/* XLogCtl */
size = sizeof(XLogCtlData);
/* xlblocks array */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.cindex 942acb9..2421460 100644
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*************** static struct config_int ConfigureNamesI
*** 1766,1772 ****
GUC_UNIT_XBLOCKS
},
&XLOGbuffers,
! 8, 4, INT_MAX, NULL, NULL
},
{
--- 1766,1772 ----
GUC_UNIT_XBLOCKS
},
&XLOGbuffers,
! -1, -1, INT_MAX, NULL, NULL
},
{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sampleindex f436b83..6c6f9a9 100644
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 162,168 ****
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
! #wal_buffers = 64kB # min 32kB
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
--- 162,168 ----
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
! #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.hindex e9d8d15..ed7a32a 100644
*** a/src/include/access/xlog.h
--- b/src/include/access/xlog.h
*************** extern void GetXLogReceiptTime(Timestamp
*** 293,298 ****
--- 293,299 ----
extern void UpdateControlFile(void);
extern uint64 GetSystemIdentifier(void);
+ extern void XLOGTuneNumBuffers(void);
extern Size XLOGShmemSize(void);
extern void XLOGShmemInit(void);
extern void BootStrapXLOG(void);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: greg@2ndquadrant.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, magnus@hagander.net
Subject: Re: auto-sizing wal_buffers
In-Reply-To: <4D314406.3000703@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox