On Sat, 24 Dec 2005, Ron wrote:

> At 02:50 PM 12/24/2005, Frank Wiles wrote:
>> Juan Casero <caseroj@comcast.net> wrote:
>>
>> > Sorry folks. I had a couple of glasses of wine as I wrote this.
>> > Anyway I originally wanted the box to have more than two drives so I
>> > could do RAID 5 but that is going to cost too much. Also, contrary
>> > to my statement below it seems to me I should run the 32 bit
>> > postgresql server on the 64 bit kernel. Would you agree this will
>> > probably yield the best performance? I know it depends alot on the
>> > system but for now this database is about 20 gigabytes. Not too large
>> > right now but it may grow 5x in the next year.
>>
>> You definitely DO NOT want to do RAID 5 on a database server. That
>> is probably the worst setup you could have, I've seen it have lower
>> performance than just a single hard disk.
>>
>> RAID 1 and RAID 1+0 are optimal, but you want to stay far away from
>> RAID 5. IMHO RAID 5 is only useful on near line backup servers or
>> Samba file servers where space is more important than speed.
> That's a bit misleading. RAID 5 excels when you want read speed but don't
> care as much about write speed. Writes are typical ~2/3 the speed of reads
> on a typical decent RAID 5 set up.
>
> So if you have tables that are read often and written to rarely or not at
> all, putting them on RAID 5 is optimal. In both data mining like and OLTP
> like apps there are usually at least some such tables.

raid 5 is bad for random writes as you state, but how does it do for
sequential writes (for example data mining where you do a large import at
one time, but seldom do other updates). I'm assuming a controller with a
reasonable amount of battery-backed cache.

David Lang

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

At 04:42 PM 12/24/2005, Joshua D. Drake wrote:


>>If you've got the budget or are dealing with small enough physical
>>storage needs, by all means use RAID 10. OTOH, if you are dealing
>>with large enterprise class apps like Sarbanes Oxley compliance,
>>medical and/or insurance, etc, etc, the storage needs can get so
>>large that RAID 10 for everything or even most things is not
>>possible. Even if economically feasible.
>>
>>RAID levels are like any other tool. Each is useful in the proper
>>circumstances.
>There is also RAID 50 which is quite nice.
The "quite nice" part that Joshua is referring to is that RAID 50
gets most of the write performance of RAID 10 w/o using nearly as
many HD's as RAID 10. OTOH, there still is a significant increase in
the number of HD's used, and that means MBTF's become more frequent
but you are not getting protection levels you would with RAID 10.

IME RAID 50 gets mixed reviews. My two biggest issues are
a= Admin of RAID 50 is more complex than the other commonly used
versions (1, 10, 5, and 6)
b= Once a HD failure takes place, you suffer a _permenent_
performance drop, even after the automatic volume rebuild, until you
take the entire RAID 50 array off line, reinitialize it, and rebuild
it from scratch.

IME "a" and "b" make RAID 50 inappropriate for any but the biggest
and most dedicated of DB admin groups.

YMMV,
Ron



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

David Lang wrote:
> raid 5 is bad for random writes as you state, but how does it do for
> sequential writes (for example data mining where you do a large import
> at one time, but seldom do other updates). I'm assuming a controller
> with a reasonable amount of battery-backed cache.

Random write performance (small block that only writes to 1 drive):
1 write requires N-1 reads + N writes --> 1/2N-1 %

Sequential write performance (write big enough block to use all N drives):
N-1 Write requires N writes --> N-1/N %

Assuming enough cache so all reads/writes are done in 1 transaction +
onboard processor calcs RAID parity fast enough to not cause an extra delay.

At 04:54 PM 12/24/2005, David Lang wrote:

>raid 5 is bad for random writes as you state, but how does it do for
>sequential writes (for example data mining where you do a large
>import at one time, but seldom do other updates). I'm assuming a
>controller with a reasonable amount of battery-backed cache.
The issue with RAID 5 writes centers on the need to recalculate
checksums for the ECC blocks distributed across the array and then
write the new ones to physical media.

Caches help, and the bigger the cache the better, but once you are
doing enough writes fast enough (and that doesn't take much even with
a few GBs of cache) the recalculate-checksums-and-write-new-ones
overhead will decrease the write speed of real data. Bear in mind
that the HD's _raw_ write speed hasn't been decreased. Those HD's
are pounding away as fast as they can for you. Your _effective_ or
_data level_ write speed is what decreases due to overhead.

Side Note: people often forget the other big reason to use RAID 10
over RAID 5. RAID 5 is always only 2 HD failures from data
loss. RAID 10 can lose up to 1/2 the HD's in the array w/o data loss
unless you get unlucky and lose both members of a RAID 1 set.

This can be seen as an example of the classic space vs. time trade
off in performance tuning. You can use 2x the HDs you need and
implement RAID 10 for best performance and reliability or you can
dedicate less HD's to RAID and implement RAID 5 for less (write)
performance and lower reliability.

TANSTAAFL.
Ron Peacetree



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi,

> b= Once a HD failure takes place, you suffer a _permenent_ performance
> drop, even after the automatic volume rebuild, until you take the entire
> RAID 50 array off line, reinitialize it, and rebuild it from scratch.

Where did you get that crazy idea? When you have replaced the drive and the
RAID is rebuilt, you have exactly the same situation as before the drive
failed. Why would you get less performance?
Sander.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Sat, Dec 24, 2005 at 05:45:20PM -0500, Ron wrote:
>Caches help, and the bigger the cache the better, but once you are
>doing enough writes fast enough (and that doesn't take much even with
>a few GBs of cache) the recalculate-checksums-and-write-new-ones
>overhead will decrease the write speed of real data. Bear in mind
>that the HD's _raw_ write speed hasn't been decreased. Those HD's
>are pounding away as fast as they can for you. Your _effective_ or
>_data level_ write speed is what decreases due to overhead.

You're overgeneralizing. Assuming a large cache and a sequential write,
there's need be no penalty for raid 5. (For random writes you may
need to read unrelated blocks in order to calculate parity, but for
large sequential writes the parity blocks should all be read from
cache.) A modern cpu can calculate parity for raid 5 on the order of
gigabytes per second, and even crummy embedded processors can do
hundreds of megabytes per second. You may have run into some lousy
implementations, but you should be much more specific about what
hardware you're talking about instead of making sweeping
generalizations.

>Side Note: people often forget the other big reason to use RAID 10
>over RAID 5. RAID 5 is always only 2 HD failures from data
>loss. RAID 10 can lose up to 1/2 the HD's in the array w/o data loss
>unless you get unlucky and lose both members of a RAID 1 set.

IOW, your RAID 10 is only 2 HD failures from data loss also. If that's
an issue you need to go with RAID 6 or add another disk to each mirror.

Mike Stone

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

It's irrelavent what controller, you still have to actualy write the
parity blocks, which slows down your write speed because you have to
write n+n/2 blocks. instead of just n blocks making the system write
50% more data.

RAID 5 must write 50% more data to disk therefore it will always be slower.

Alex.

On 12/25/05, Michael Stone <mstone+postgres@mathom.us> wrote:
> On Sat, Dec 24, 2005 at 05:45:20PM -0500, Ron wrote:
> >Caches help, and the bigger the cache the better, but once you are
> >doing enough writes fast enough (and that doesn't take much even with
> >a few GBs of cache) the recalculate-checksums-and-write-new-ones
> >overhead will decrease the write speed of real data. Bear in mind
> >that the HD's _raw_ write speed hasn't been decreased. Those HD's
> >are pounding away as fast as they can for you. Your _effective_ or
> >_data level_ write speed is what decreases due to overhead.
>
> You're overgeneralizing. Assuming a large cache and a sequential write,
> there's need be no penalty for raid 5. (For random writes you may
> need to read unrelated blocks in order to calculate parity, but for
> large sequential writes the parity blocks should all be read from
> cache.) A modern cpu can calculate parity for raid 5 on the order of
> gigabytes per second, and even crummy embedded processors can do
> hundreds of megabytes per second. You may have run into some lousy
> implementations, but you should be much more specific about what
> hardware you're talking about instead of making sweeping
> generalizations.
>
> >Side Note: people often forget the other big reason to use RAID 10
> >over RAID 5. RAID 5 is always only 2 HD failures from data
> >loss. RAID 10 can lose up to 1/2 the HD's in the array w/o data loss
> >unless you get unlucky and lose both members of a RAID 1 set.
>
> IOW, your RAID 10 is only 2 HD failures from data loss also. If that's
> an issue you need to go with RAID 6 or add another disk to each mirror.
>
> Mike Stone
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Mon, 26 Dec 2005, Alex Turner wrote:

> It's irrelavent what controller, you still have to actualy write the
> parity blocks, which slows down your write speed because you have to
> write n+n/2 blocks. instead of just n blocks making the system write
> 50% more data.
>
> RAID 5 must write 50% more data to disk therefore it will always be slower.

raid5 writes n+1 blocks not n+n/2 (unless n=2 for a 3-disk raid). you can
have a 15+1 disk raid5 array for example

however raid1 (and raid10) have to write 2*n blocks to disk. so if you are
talking about pure I/O needed raid5 wins hands down. (the same 16 drives
would be a 8+8 array)

what slows down raid 5 is that to modify a block you have to read blocks
from all your drives to re-calculate the parity. this interleaving of
reads and writes when all you are logicly doing is writes can really hurt.
(this is why I asked the question that got us off on this tangent, when
doing new writes to an array you don't have to read the blocks as they are
blank, assuming your cacheing is enough so that you can write blocksize*n
before the system starts actually writing the data)

David Lang

> Alex.
>
> On 12/25/05, Michael Stone <mstone+postgres@mathom.us> wrote:
>> On Sat, Dec 24, 2005 at 05:45:20PM -0500, Ron wrote:
>>> Caches help, and the bigger the cache the better, but once you are
>>> doing enough writes fast enough (and that doesn't take much even with
>>> a few GBs of cache) the recalculate-checksums-and-write-new-ones
>>> overhead will decrease the write speed of real data. Bear in mind
>>> that the HD's _raw_ write speed hasn't been decreased. Those HD's
>>> are pounding away as fast as they can for you. Your _effective_ or
>>> _data level_ write speed is what decreases due to overhead.
>>
>> You're overgeneralizing. Assuming a large cache and a sequential write,
>> there's need be no penalty for raid 5. (For random writes you may
>> need to read unrelated blocks in order to calculate parity, but for
>> large sequential writes the parity blocks should all be read from
>> cache.) A modern cpu can calculate parity for raid 5 on the order of
>> gigabytes per second, and even crummy embedded processors can do
>> hundreds of megabytes per second. You may have run into some lousy
>> implementations, but you should be much more specific about what
>> hardware you're talking about instead of making sweeping
>> generalizations.
>>
>>> Side Note: people often forget the other big reason to use RAID 10
>>> over RAID 5. RAID 5 is always only 2 HD failures from data
>>> loss. RAID 10 can lose up to 1/2 the HD's in the array w/o data loss
>>> unless you get unlucky and lose both members of a RAID 1 set.
>>
>> IOW, your RAID 10 is only 2 HD failures from data loss also. If that's
>> an issue you need to go with RAID 6 or add another disk to each mirror.
>>
>> Mike Stone
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Yes, but those blocks in RAID 10 are largely irrelevant as they are to
independant disks. In RAID 5 you have to write parity to an 'active'
drive that is part of the stripe. (They are irrelevant unless of
course you are maxing out your SCSI bus - yet another reason why SATA
can be faster than SCSI, particularly in RAID 10, every channel is
independant).

Sorry - my math for RAID 5 was a bit off - I don't know why I was
considering only a three dirve situation - which is the worst. It's
n+1 you are right. still, for small arrays thats a big penalty.
Still, there is definately a penatly contrary to the assertion of the
orignal poster.

I agree totally that the read+parity-calc+write in the worst case is
totaly bad, which is why I alway recommend people should _never ever_
use RAID 5. In this day and age of large capacity chassis, and large
capacity SATA drives, RAID 5 is totally inapropriate IMHO for _any_
application least of all databases.

In reality I have yet to benchmark a system where RAID 5 on the same
number of drives with 8 drives or less in a single array beat a RAID
10 with the same number of drives. I would definately be interested
in a SCSI card that could actualy achieve the theoretical performance
of RAID 5 especially under Linux.

With RAID 5 you get to watch you system crumble and fail when a drive
fails and the array goes into a failed state. It's just not worth it.

Alex.


On 12/26/05, David Lang <dlang@invendra.net> wrote:
> On Mon, 26 Dec 2005, Alex Turner wrote:
>
> > It's irrelavent what controller, you still have to actualy write the
> > parity blocks, which slows down your write speed because you have to
> > write n+n/2 blocks. instead of just n blocks making the system write
> > 50% more data.
> >
> > RAID 5 must write 50% more data to disk therefore it will always be slower.
>
> raid5 writes n+1 blocks not n+n/2 (unless n=2 for a 3-disk raid). you can
> have a 15+1 disk raid5 array for example
>
> however raid1 (and raid10) have to write 2*n blocks to disk. so if you are
> talking about pure I/O needed raid5 wins hands down. (the same 16 drives
> would be a 8+8 array)
>
> what slows down raid 5 is that to modify a block you have to read blocks
> from all your drives to re-calculate the parity. this interleaving of
> reads and writes when all you are logicly doing is writes can really hurt.
> (this is why I asked the question that got us off on this tangent, when
> doing new writes to an array you don't have to read the blocks as they are
> blank, assuming your cacheing is enough so that you can write blocksize*n
> before the system starts actually writing the data)
>
> David Lang
>
> > Alex.
> >
> > On 12/25/05, Michael Stone <mstone+postgres@mathom.us> wrote:
> >> On Sat, Dec 24, 2005 at 05:45:20PM -0500, Ron wrote:
> >>> Caches help, and the bigger the cache the better, but once you are
> >>> doing enough writes fast enough (and that doesn't take much even with
> >>> a few GBs of cache) the recalculate-checksums-and-write-new-ones
> >>> overhead will decrease the write speed of real data. Bear in mind
> >>> that the HD's _raw_ write speed hasn't been decreased. Those HD's
> >>> are pounding away as fast as they can for you. Your _effective_ or
> >>> _data level_ write speed is what decreases due to overhead.
> >>
> >> You're overgeneralizing. Assuming a large cache and a sequential write,
> >> there's need be no penalty for raid 5. (For random writes you may
> >> need to read unrelated blocks in order to calculate parity, but for
> >> large sequential writes the parity blocks should all be read from
> >> cache.) A modern cpu can calculate parity for raid 5 on the order of
> >> gigabytes per second, and even crummy embedded processors can do
> >> hundreds of megabytes per second. You may have run into some lousy
> >> implementations, but you should be much more specific about what
> >> hardware you're talking about instead of making sweeping
> >> generalizations.
> >>
> >>> Side Note: people often forget the other big reason to use RAID 10
> >>> over RAID 5. RAID 5 is always only 2 HD failures from data
> >>> loss. RAID 10 can lose up to 1/2 the HD's in the array w/o data loss
> >>> unless you get unlucky and lose both members of a RAID 1 set.
> >>
> >> IOW, your RAID 10 is only 2 HD failures from data loss also. If that's
> >> an issue you need to go with RAID 6 or add another disk to each mirror.
> >>
> >> Mike Stone
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 6: explain analyze is your friend
> >>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Mon, 26 Dec 2005, Alex Turner wrote:

>
> Yes, but those blocks in RAID 10 are largely irrelevant as they are to
> independant disks. In RAID 5 you have to write parity to an 'active'
> drive that is part of the stripe. (They are irrelevant unless of
> course you are maxing out your SCSI bus - yet another reason why SATA
> can be faster than SCSI, particularly in RAID 10, every channel is
> independant).

I don't understand your 'active' vs 'inactive' drive argument, in raid 1
or 1+0 all drives are active.

with good components you need to worry about maxing out your PCI bus as
much as any other one (this type of thing is where the hardware raid has a
definante advantage since the card handles the extra I/O, not your system)

> Sorry - my math for RAID 5 was a bit off - I don't know why I was
> considering only a three dirve situation - which is the worst. It's
> n+1 you are right. still, for small arrays thats a big penalty.
> Still, there is definately a penatly contrary to the assertion of the
> orignal poster.
>
> I agree totally that the read+parity-calc+write in the worst case is
> totaly bad, which is why I alway recommend people should _never ever_
> use RAID 5. In this day and age of large capacity chassis, and large
> capacity SATA drives, RAID 5 is totally inapropriate IMHO for _any_
> application least of all databases.
>
> In reality I have yet to benchmark a system where RAID 5 on the same
> number of drives with 8 drives or less in a single array beat a RAID
> 10 with the same number of drives. I would definately be interested
> in a SCSI card that could actualy achieve the theoretical performance
> of RAID 5 especially under Linux.

but it's not a 'same number of drives' comparison you should be makeing.

if you have a 8 drive RAID5 array you need to compare it with a 14 drive
RAID1/10 array.

> With RAID 5 you get to watch you system crumble and fail when a drive
> fails and the array goes into a failed state. It's just not worth it.

speed is worth money (and therefor number of drives) in some cases, but
not in all cases. also the speed penalty when you have a raid drive fail
varies based on your controller

it's wrong to flatly rule out any RAID configuration, they all have their
place and the important thing is to understand what the advantages and
disadvantages are for each of them so you can know when to use each one.

for example I have a situation I am looking at where RAID0 is looking
appropriate for a database (a multi-TB array that gets completely reloaded
every month or so as data expires and new data is loaded from the
authoritative source, adding another 16 drives to get redundancy isn't
reasonable)

David Lang

> Alex.
>
>
> On 12/26/05, David Lang <dlang@invendra.net> wrote:
>> On Mon, 26 Dec 2005, Alex Turner wrote:
>>
>>> It's irrelavent what controller, you still have to actualy write the
>>> parity blocks, which slows down your write speed because you have to
>>> write n+n/2 blocks. instead of just n blocks making the system write
>>> 50% more data.
>>>
>>> RAID 5 must write 50% more data to disk therefore it will always be slower.
>>
>> raid5 writes n+1 blocks not n+n/2 (unless n=2 for a 3-disk raid). you can
>> have a 15+1 disk raid5 array for example
>>
>> however raid1 (and raid10) have to write 2*n blocks to disk. so if you are
>> talking about pure I/O needed raid5 wins hands down. (the same 16 drives
>> would be a 8+8 array)
>>
>> what slows down raid 5 is that to modify a block you have to read blocks
>> from all your drives to re-calculate the parity. this interleaving of
>> reads and writes when all you are logicly doing is writes can really hurt.
>> (this is why I asked the question that got us off on this tangent, when
>> doing new writes to an array you don't have to read the blocks as they are
>> blank, assuming your cacheing is enough so that you can write blocksize*n
>> before the system starts actually writing the data)
>>
>> David Lang
>>
>>> Alex.
>>>
>>> On 12/25/05, Michael Stone <mstone+postgres@mathom.us> wrote:
>>>> On Sat, Dec 24, 2005 at 05:45:20PM -0500, Ron wrote:
>>>>> Caches help, and the bigger the cache the better, but once you are
>>>>> doing enough writes fast enough (and that doesn't take much even with
>>>>> a few GBs of cache) the recalculate-checksums-and-write-new-ones
>>>>> overhead will decrease the write speed of real data. Bear in mind
>>>>> that the HD's _raw_ write speed hasn't been decreased. Those HD's
>>>>> are pounding away as fast as they can for you. Your _effective_ or
>>>>> _data level_ write speed is what decreases due to overhead.
>>>>
>>>> You're overgeneralizing. Assuming a large cache and a sequential write,
>>>> there's need be no penalty for raid 5. (For random writes you may
>>>> need to read unrelated blocks in order to calculate parity, but for
>>>> large sequential writes the parity blocks should all be read from
>>>> cache.) A modern cpu can calculate parity for raid 5 on the order of
>>>> gigabytes per second, and even crummy embedded processors can do
>>>> hundreds of megabytes per second. You may have run into some lousy
>>>> implementations, but you should be much more specific about what
>>>> hardware you're talking about instead of making sweeping
>>>> generalizations.
>>>>
>>>>> Side Note: people often forget the other big reason to use RAID 10
>>>>> over RAID 5. RAID 5 is always only 2 HD failures from data
>>>>> loss. RAID 10 can lose up to 1/2 the HD's in the array w/o data loss
>>>>> unless you get unlucky and lose both members of a RAID 1 set.
>>>>
>>>> IOW, your RAID 10 is only 2 HD failures from data loss also. If that's
>>>> an issue you need to go with RAID 6 or add another disk to each mirror.
>>>>
>>>> Mike Stone
>>>>
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 6: explain analyze is your friend
>>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 4: Have you searched our list archives?
>>>
>>> http://archives.postgresql.org
>>>
>>
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match