X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 10D7B9FA5DD for ; Fri, 9 Jun 2006 17:21:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 85023-03 for ; Fri, 9 Jun 2006 17:21:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id D959C9FA1A8 for ; Fri, 9 Jun 2006 17:21:23 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Fri, 9 Jun 2006 20:21:22 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 09 Jun 2006 15:21:22 -0500 Subject: Re: pg_xlog on data partition with BBU RAID From: Scott Marlowe To: "Jim C. Nasby" Cc: pgsql-performance@postgresql.org In-Reply-To: <20060609194116.GD57289@pervasive.com> References: <20060609194116.GD57289@pervasive.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1149884482.27878.4.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 09 Jun 2006 15:21:22 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/114 X-Sequence-Number: 19471 On Fri, 2006-06-09 at 14:41, Jim C. Nasby wrote: > AFAIK, the reason why seperating pg_xlog from the base files provides so > much performance is because the latency on pg_xlog is critical: a > transaction can't commit until all of it's log data is written to disk > via fsync, and if you're trying to fsync frequently on the same drive as > the data tables are on, you'll have a big problem with the activity on > the data drives competing with trying to fsync pg_xlog rapidly. > > But if you have a raid array with a battery-backed controller, this > shouldn't be anywhere near as big an issue. The fsync on the log will > return very quickly thanks to the cache, and the controller is then free > to batch up writes to pg_xlog. Or at least that's the theory. > > Has anyone actually done any testing on this? Specifically, I'm > wondering if the benefit of adding 2 more drives to a RAID10 outweighs > whatever penalties there are to having pg_xlog on that RAID10 with all > the rest of the data. I tested it WAY back when 7.4 first came out on a machine with BBU, and it didn't seem to make any difference HOW I set up the hard drives, RAID-5, 1+0, 1 it was all about the same. With BBU the transactions per second varied very little. If I recall correctly, it was something like 600 or so tps with pgbench (scaling and num clients was around 20 I believe) It's been a while. In the end, that server ran with a pair of 18 Gig drives in a RAID-1 and was plenty fast for what we used it for. Due to corporate shenanigans it was still running pgsql 7.2.x at the time. ugh. I've not got access to a couple of Dell servers I might be able to test this on... After our security audit maybe.