From: Luke Lonergan <llonergan@greenplum.com>
To: peter royal <peter.royal@pobox.com>
To: pgsql-performance@postgresql.org
Subject: Re: help tuning queries on large database
Date: Sun, 08 Jan 2006 10:42:31 -0800
Message-ID: <BFE69D17.1A1E8%llonergan@greenplum.com> (raw)
In-Reply-To: <FC9B7CC4-F469-4BF5-8862-332E6C26BEA5@pobox.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: llonergan@greenplum.com, peter.royal@pobox.com
Subject: Re: help tuning queries on large database
In-Reply-To: <BFE69D17.1A1E8%llonergan@greenplum.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox