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.royal@pobox.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@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@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@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@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@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@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