Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D393A2E011C for ; Mon, 28 Jan 2008 22:56:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 38469-06 for ; Mon, 28 Jan 2008 22:56:30 -0400 (AST) Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.185]) by postgresql.org (Postfix) with ESMTP id A640F2E00BC for ; Mon, 28 Jan 2008 22:56:42 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so1545034fka.3 for ; Mon, 28 Jan 2008 18:56:41 -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=JhUd19vR1ptelKn3UkbQte1B0KlTSo4SIszQZK1rnU4=; b=edXIOp/011s80EYNF5r6MjBWIUu42M/30yGfwaTcHiuDt2Tv9tQhajYKNh8ME613YXuubU+6KqTvU81RfXXDgfiqjJXD+0TGkIjClX+goLi1kjA+xaeftXxaORlOEScbNHds4B1aKq2lF49Iozm0qyq7JEdG6Emmw63jGKZWM6A= 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=n+pPlXbpIzvXH6xyfre8skP4J14i8EuxHCmelv8WtfKagkKIc74PK1cOmqMNMOpR5m+fZgJAwnw4fzz2GS8/iN357N8qbbeBp11RNcKcYFLMKTuDaBCU7zTCGQN6QJYG+l84wIgo90LtcmuVvAn24dZQNOPZwSjVY4hCPfFGxPg= Received: by 10.78.149.15 with SMTP id w15mr8556912hud.72.1201575400766; Mon, 28 Jan 2008 18:56:40 -0800 (PST) Received: by 10.78.40.5 with HTTP; Mon, 28 Jan 2008 18:56:40 -0800 (PST) Message-ID: Date: Mon, 28 Jan 2008 21:56:40 -0500 From: "Merlin Moncure" 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/278 X-Sequence-Number: 28517 On Jan 28, 2008 8: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. > > Now our "normal" activities are really fast. Scanning data, etc., all > runs pretty quickly. What is NOT fast is some of the massive > queries. We have some Updates with joins that match a 100m line table > with a 200m line table. Outside of custom coding pl/pgsql code that > creates the subfunction on the fly (which is up for consideration) to > try to keep the Matching joins to an O(n) problem from the current > O(n^2) one, we are looking at hardware as an option to help speed up > these big batch queries that sometimes run for 5-6 days. > Well, you have already put some thought into your hardware...but the awful truth is that the sata drives are just terrible at seeking once you start seeing significant numbers of page faults to disk, and getting killed on sorting on top of it. Maybe the best plan of attack here is to post some explain times, and the relevant query. Perhaps there are some optimizations in indexing strategies or other query tactics (the pl/pgsql function smells suspicious as you have already noted), and hopefully the giant sort can be optimized out. You have a nasty problem that may require some out of the box thinking, so the more information you can provide the better. merlin