X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3B5AF475CCE for ; Thu, 20 Feb 2003 18:43:59 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 56645-07 for ; Thu, 20 Feb 2003 18:43:48 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 4661D475AAC for ; Thu, 20 Feb 2003 18:36:00 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h1L02w6P008714; Thu, 20 Feb 2003 19:02:59 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h1KNZip17942; Thu, 20 Feb 2003 18:35:44 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id CWVL16WQ; Thu, 20 Feb 2003 18:35:29 -0500 Subject: Re: Tuning scenarios (was Changing the default From: Robert Treat To: Josh Berkus Cc: pgsql-performance@postgresql.org In-Reply-To: <200302201433.02654.josh@agliodbs.com> References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local> <200302140936.02011.josh@agliodbs.com> <1045778701.14518.81.camel@camel> <200302201433.02654.josh@agliodbs.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 20 Feb 2003 18:35:44 -0500 Message-Id: <1045784144.15881.92.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/141 X-Sequence-Number: 1197 On Thu, 2003-02-20 at 17:33, Josh Berkus wrote: > > Robert, > > > > 1) Location of the pg_xlog for heavy-update databases. > > > > I see you put this up pretty high on the list. Do you feel this is the > > most important thing you can do? For example, if you had a two drive > > installation, would you load the OS and main database files on 1 disk > > and put the pg_xlog on the second disk above all other configurations? > > Yes, actually. On machines with 2 IDE disks, I've found that this can make > as much as 30% difference in speed of serial/large UPDATE statements. Do you know how well those numbers hold up under scsi and/ or raid based system? (I'd assume anyone doing serious work would run scsi) > > > Ideally I recommend 3 disks, one for os, one for data, one for xlog; but > > if you only had 2 would the added speed benefits be worth the additional > > recovery complexity (if you data/xlog are on the same disk, you have 1 > > point of failure, one disk for backing up) > > On the other hand, with the xlog on a seperate disk, the xlog and the database > disks are unlikely to fail at the same time. So I don't personally see it as > a recovery problem, but a benefit. > ok (playing a bit of devil's advocate here), but you have two possible points of failure, the data disk and the xlog disk. If either one goes, your in trouble. OTOH if you put the OS disk on one drive and it goes, your database and xlog are still safe on the other drive. Robert Treat