Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 577301336B42 for ; Sat, 15 Jan 2011 02:52:09 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 15933-04 for ; Sat, 15 Jan 2011 06:52:01 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.187]) by mail.postgresql.org (Postfix) with ESMTP id 5EE54133616B for ; Sat, 15 Jan 2011 02:52:01 -0400 (AST) Received: from [192.168.1.7] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis) id 0MWySC-1PjTf9231w-00WXEN; Sat, 15 Jan 2011 07:51:52 +0100 Message-ID: <4D314406.3000703@2ndquadrant.com> Date: Sat, 15 Jan 2011 01:51:50 -0500 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Tom Lane CC: Robert Haas , Magnus Hagander , pgsql-hackers@postgresql.org Subject: Re: auto-sizing wal_buffers References: <20767.1294964709@sss.pgh.pa.us> In-Reply-To: <20767.1294964709@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------030206070002060702060309" X-Provags-ID: V02:K0:LY0fgA0h26YCnDR4l0SIFFPSFVRCrZPGvlontS6uipU aK2XFwtFqgPcs5rsH21o89viflqtXQxUKb3hCgs7DgtDWNhKGq NnzowEx2PfIIRa1kT6saCqTap9EOOuJQz6HNDt7gOj9FjnoYav d4cJlgPCWbTDZIUUBqErOR50hQ9E0bE7ciVMrk9yR1ZqPMV6Hu bovlJ0U5/v7Uk6xkkRZdglhXOchAzTWl7ZMIzk9bMg= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201101/1220 X-Sequence-Number: 179057 This is a multi-part message in MIME format. --------------030206070002060702060309 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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/books --------------030206070002060702060309 Content-Type: text/x-patch; name="auto-wal-buffers-v1.patch" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="auto-wal-buffers-v1.patch" diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 8e2a2c5..c3f5632 100644 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** SET ENABLE_SEQSCAN TO OFF; *** 1638,1649 **** ! 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. --- 1638,1659 ---- ! The amount of shared memory used for storing WAL data. The ! default setting of -1 adjusts this automatically based on the size ! of shared_buffers, 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 ! (16MB), sufficient to hold one WAL segment worth of data. ! The smallest allowable setting is 64 kilobytes (64kB). ! ! ! ! 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. diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 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.c index 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.sample index 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.h index 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); --------------030206070002060702060309--