Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A286F2E2EEC for ; Mon, 28 Jan 2008 11:20:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 92463-06-4 for ; Mon, 28 Jan 2008 11:19:57 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.177]) by postgresql.org (Postfix) with ESMTP id 9EF522E2F14 for ; Mon, 28 Jan 2008 11:19:50 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so3304203waf.5 for ; Mon, 28 Jan 2008 07:19:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=jlCIRH2QzaJjxTNl91z7KvJz7hmd8XjO4X/UHrCNLS0=; b=v1a/YHhXbsQ/T9mL2YHpRaDWWIgwrHdQ1wbjK2Vb0CCscuraSYarHX7cHj1IHB/Ad5wROV4kBVrOhOwTWymWVhTSi5nURk8uqttZTjod0LvWXj6V8hdjbPeGteDtWVh5EG/ffX7XbNRAP6cF65gpYE/namLpVNTIRwvGjqYqfxs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=RijcsdD0JcCwoboiymxg9/b+iYrd9hP314Uh58X0633pwzFlrMp4u/j3euxxiuYCSE6zgm+T2oqD28Di8AspBckXNc0hw3BMvQODDgAijpAb4NFq698KETgI6fmrfU1h+niIpI3Zq0JGPLegqgM8BDXqRGCX0z1G0X24RglC1ww= Received: by 10.114.191.1 with SMTP id o1mr4393566waf.66.1201533588935; Mon, 28 Jan 2008 07:19:48 -0800 (PST) Received: by 10.114.133.5 with HTTP; Mon, 28 Jan 2008 07:19:48 -0800 (PST) Message-ID: Date: Mon, 28 Jan 2008 09:19:48 -0600 From: "Scott Marlowe" To: "Alex Hochberger" Subject: Re: Hard Drive Usage for Speeding up Big Queries Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20080125101607.45E7E2C9462@maia-2.hub.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/268 X-Sequence-Number: 28507 On Jan 28, 2008 7:54 AM, Alex Hochberger wrote: > We are trying to optimize our Database server without spending a > fortune on hardware. Here is our current setup. > > Main Drive array: 8x 750 GB SATA 2 Drives in a RAID 10 Configuration, > this stores the OS, Applications, and PostgreSQL Data drives. 3 TB > Array, 2 TB Parition for PostgreSQL. > Secondary drive array: 2x 36 GB SAS 15000 RPM Drives in a RAID 1 > Configuration: the pg_xlog directory, checkpoints set to use about 18 > GB max, this way when massive numbers of small writes occur, they > don't slow the system down. Drive failure loses no data. Checkpoints > will be another matter, hope to keep under control with bgwriter > tweaking. > SNIP > However, the joins of two 50GB tables really just can't be solved in > RAM without using drive space. My question is, can hardware speed > that up? Would putting a 400 GB SAS Drive (15000 RPM) in just to > handle PostgreSQL temp files help? Considering it would store "in > process" queries and not "completed transactions" I see no reason to > mirror the drive. If it fails, we'd simply unmount it, replace it, > then remount it, it could use the SATA space in the mean time. > > Would that speed things up, and if so, where in the drive mappings > should that partition go? Do you have a maintenance window to experiment in? Try putting it on the pg_xlog array to see if it speeds up the selects during one. Then you'll know. I'm thinking it will help a little, but there's only so much you can do with 50g result sets.