User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Sun, 08 Jan 2006 10:42:31 -0800
Subject: Re: help tuning queries on large database
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "peter royal" <peter.royal@pobox.com>,
	pgsql-performance@postgresql.org
Message-ID: <BFE69D17.1A1E8%llonergan@greenplum.com>
Thread-Topic: [PERFORM] help tuning queries on large database
Thread-Index: AcYTFQFEbemu7xT0Q2qfSxqTQl+g7gBbkbeJ
In-Reply-To: <FC9B7CC4-F469-4BF5-8862-332E6C26BEA5@pobox.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Peter,

On 1/6/06 2:59 PM, "peter royal" <peter.royal@pobox.com> wrote:

> I have experimented with having all 8 disks in a single RAID0 set, a
> single RAID10 set, and currently 4 RAID0 sets of 2 disks each. There
> hasn't been an appreciable difference in the overall performance of
> my test suite (which randomly generates queries like the samples
> below as well as a few other types. this problem manifests itself on
> other queries in the test suite as well).

Have you tested the underlying filesystem for it's performance?  Run this:
  time bash -c 'dd if=/dev/zero of=/my_file_system/bigfile bs=8k
count=<your_memory_size_in_GB * 250000> && sync'

Then run this:
  time dd if=/my_file_system/bigfile bs=8k of=/dev/null

And report the times here please.  With your 8 disks in any of the RAID0
configurations you describe, you should be getting 480MB/s.  In the RAID10
configuration you should get 240.

Note that ext3 will not go faster than about 300MB/s in our experience.  You
should use xfs, which will run *much* faster.

You should also experiment with using larger readahead, which you can
implement like this:
  blockdev --setra 16384 /dev/<my_block_device>

E.g. "blockdev --setra 16384 /dev/sda"

This will set the readahead of Linux block device reads to 16MB.  Using
3Ware's newest controllers we have seen 500MB/s + on 8 disk drives in RAID0
on CentOS 4.1 with xfs.  Note that you will need to run the "CentOS
unsupported kernel" to get xfs.

> So, my question is, is there anything I can do to boost performance
> with what I've got, or am I in a position where the only 'fix' is
> more faster disks? I can't think of any schema/index changes that
> would help, since everything looks pretty optimal from the 'explain
> analyze' output. I'd like to get a 10x improvement when querying from
> the 'cold' state.