Re: help tuning queries on large database

On Jan 9, 2006, at 2:01 PM, Luke Lonergan wrote:
> Peter,
>
> On 1/9/06 9:23 AM, "peter royal" <peter(dot)royal(at)pobox(dot)com> wrote:
>
>> This is a 2-disk RAID0
>
> Your 2-disk results look fine - what about your 8-disk results?

after some further research the 2-disk RAID0 numbers are not bad.

I have a single drive of the same type hooked up to the SATA2 port on
the motherboard to boot from, and its performance numbers are (linux
2.6.15, ext3):

[root(at)bigboy ~]# time bash -c 'dd if=/dev/zero of=/tmp/bigfile bs=8k
count=1000000 && sync'
1000000+0 records in
1000000+0 records out

real 4m55.032s
user 0m0.256s
sys 0m47.299s
[root(at)bigboy ~]# time dd if=/tmp/bigfile bs=8k of=/dev/null
1000000+0 records in
1000000+0 records out

real 3m27.229s
user 0m0.156s
sys 0m13.377s

so, there is a clear advantage to RAID over a single drive.

now, some stats in a 8-disk configuration:

8-disk RAID0, ext3, 16k read-ahead

[root(at)bigboy /opt/pgdata]# time bash -c 'dd if=/dev/zero of=/opt/
pgdata/bigfile bs=8k count=1000000 && sync'
1000000+0 records in
1000000+0 records out

real 0m53.030s
user 0m0.204s
sys 0m42.015s

[root(at)bigboy /opt/pgdata]# time dd if=/opt/pgdata/bigfile bs=8k of=/
dev/null
1000000+0 records in
1000000+0 records out

real 0m23.232s
user 0m0.144s
sys 0m13.213s

8-disk RAID0, xfs, 16k read-ahead

[root(at)bigboy /opt/pgdata]# time bash -c 'dd if=/dev/zero of=/opt/
pgdata/bigfile bs=8k count=1000000 && sync'
1000000+0 records in
1000000+0 records out

real 0m32.177s
user 0m0.212s
sys 0m21.277s

[root(at)bigboy /opt/pgdata]# time dd if=/opt/pgdata/bigfile bs=8k of=/
dev/null
1000000+0 records in
1000000+0 records out

real 0m21.814s
user 0m0.172s
sys 0m13.881s

... WOW.. highly impressed with the XFS write speed! going to stick
with that!

Overall, I got a 50% boost in the overall speed of my test suite by
using XFS and the 16k read-ahead.

> Given that you want to run in production with RAID10, the most you
> should
> expect is 2x the 2-disk results using all 8 of your disks. If you
> want the
> best rate for production while preserving data integrity, I recommend
> running your Areca in RAID5, in which case you should expect 3.5x your
> 2-disk results (7 drives). You can assume you'll get that if you
> use XFS +
> readahead. OTOH - I'd like to see your test results anyway :-)

I've been avoiding RAID5 after reading how performance drops when a
drive is out/rebuilding. The performance benefit will outweigh the
cost I think.

Thanks for the help!
-pete

--
(peter.royal|osi)@pobox.com - http://fotap.org/~osi