Re: help tuning queries on large database

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