From: | "Luke Lonergan" <llonergan(at)greenplum(dot)com> |
---|---|
To: | "peter royal" <peter(dot)royal(at)pobox(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: help tuning queries on large database |
Date: | 2006-01-08 18:42:31 |
Message-ID: | BFE69D17.1A1E8%llonergan@greenplum.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Peter,
On 1/6/06 2:59 PM, "peter royal" <peter(dot)royal(at)pobox(dot)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.
From what you describe, one of these is likely:
- hardware isn't configured properly or a driver problem.
- you need to use xfs and tune your Linux readahead
- Luke
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2006-01-08 21:35:11 | Re: help tuning queries on large database |
Previous Message | vimal.gupta | 2006-01-08 17:34:30 | Hanging Query |