X-Original-To: pgsql-advocacy@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 169E8474E61; Thu, 13 Feb 2003 21:29:22 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 45AFE474E5C; Thu, 13 Feb 2003 21:29:21 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1E2TH5u003332; Thu, 13 Feb 2003 21:29:18 -0500 (EST) To: "Christopher Kings-Lynne" Cc: "Hackers" , pgsql-performance@postgresql.org, "Advocacy" Subject: Re: [HACKERS] More benchmarking of wal_buffers In-reply-to: References: Comments: In-reply-to "Christopher Kings-Lynne" message dated "Thu, 13 Feb 2003 13:16:16 +0800" Date: Thu, 13 Feb 2003 21:29:17 -0500 Message-ID: <3331.1045189757@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/69 X-Sequence-Number: 827 "Christopher Kings-Lynne" writes: > I've just spent the last day and a half trying to benchmark our new database > installation to find a good value for wal_buffers. The quick answer - there > isn't, just leave it on the default of 8. I don't think this is based on a useful test for wal_buffers. The wal_buffers setting only has to be large enough for the maximum amount of WAL log data that your system emits between commits, because a commit (from anyone) is going to flush the WAL data to disk (for everyone). So a benchmark based on short transactions is just not going to show any benefit to increasing the setting. Benchmarking, say, the speed of massive COPY IN operations might show some advantage to larger wal_buffers. Although I'm not real sure that it'll make any difference for any single-backend test. It's really just the case where you have concurrent transactions that all make lots of updates before committing that's likely to show a win. > One proof that has come out of this is that wal_buffers does not affect > SELECT only performance in any way. Coulda told you that without testing ;-). Read-only transactions emit no WAL entries. regards, tom lane