Re: Postgresql on software RAID

Hi all,

I am experimenting with a few OS's for my new hardware. I plan to have a
software RAID5 device for my pgsql data directory.

I have been experimenting with FreeBSD and with Linux, does anyone have any
thoughts on whether vinum RAID devices are better than Linux software RAID?
Or vice versa... Or no difference at all? Any gotchas I should bear in mind?

Thanks for any advice

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Tue, 16 Dec 2003, Adam Witney wrote:

>
> Hi all,
>
> I am experimenting with a few OS's for my new hardware. I plan to have a
> software RAID5 device for my pgsql data directory.
>
> I have been experimenting with FreeBSD and with Linux, does anyone have any
> thoughts on whether vinum RAID devices are better than Linux software RAID?
> Or vice versa... Or no difference at all? Any gotchas I should bear in mind?

For a lightly updated database, software RAID5 is a match for hardware
RAID5. however, as the number of updates / second increase, the hardware
RAID5 can easily outrun the software RAID5 while maintaining data reliably
IF it has a battery backed cache. This is because the hardware RAID
controller can respond immediately to fsync requests while holding the
actual data in battery backed cache waiting for the right opportunity to
flush said cache, and should a power outage occur, the raid5 hardware
controller will write the cache when the machine powers up.

Linux software RAID seems to have a parallelization issue when layering
RAID 1 or 5 on top of 0 (or vice versa). I'm not familiar enough with
BSD's RAID layer to comment on it in that light.

They (linux and BSD) seem to be competitive in terms of performance for
straight RAID5 or RAID1 though.

> I am experimenting with a few OS's for my new hardware. I plan to have a
> software RAID5 device for my pgsql data directory.
>
> I have been experimenting with FreeBSD and with Linux, does anyone have any
> thoughts on whether vinum RAID devices are better than Linux software RAID?
> Or vice versa... Or no difference at all? Any gotchas I should bear in mind?

I use Postgres w/Vinum setup to mirror 2 9gig SCSI Cheetahs and I have
no complaints. I bang on the db quite a bit (moderately busy site and
tons of data analysis) and performance is great. Ultimately I am going to
slap a hardware RAID card in there (need the immediate failover) but right
now software wise performance is great. I'd probably only recommend
doing mirroring in software though, RAID-5 is going to eat up alot more
CPU and disk writes.

--
"Don't 'kill -9' the SYSVMSG"
sumbry(at)sumbry(dot)com

On Tue, 16 Dec 2003 sumbry(at)sumbry(dot)com wrote:

> > I am experimenting with a few OS's for my new hardware. I plan to have a
> > software RAID5 device for my pgsql data directory.
> >
> > I have been experimenting with FreeBSD and with Linux, does anyone have any
> > thoughts on whether vinum RAID devices are better than Linux software RAID?
> > Or vice versa... Or no difference at all? Any gotchas I should bear in mind?
>
> I use Postgres w/Vinum setup to mirror 2 9gig SCSI Cheetahs and I have
> no complaints. I bang on the db quite a bit (moderately busy site and
> tons of data analysis) and performance is great. Ultimately I am going to
> slap a hardware RAID card in there (need the immediate failover) but right
> now software wise performance is great. I'd probably only recommend
> doing mirroring in software though, RAID-5 is going to eat up alot more
> CPU and disk writes.

Just a clarification, it only costs for writes. For reads, an N disk
RAID5 is exactly as fast as an N-1 disk RAID0. On a modern fast CPU
machine, with a small write load, CPU usage is usually very low, say <5%.

That said, my production databases usually sit on hardware RAID5 with
battery backed cache, build / test servers often run on software RAID5.
Money, meet mouth. :-)

Adam Witney wrote:
> Hi all,
>
> I am experimenting with a few OS's for my new hardware. I plan to have a
> software RAID5 device for my pgsql data directory.
>
> I have been experimenting with FreeBSD and with Linux, does anyone have any
> thoughts on whether vinum RAID devices are better than Linux software RAID?
> Or vice versa... Or no difference at all? Any gotchas I should bear in mind?
>

Of interest, there are a number of "old school" dba's of my aquantance
who swear vociferously whenever the words RAID 5 and Database are
mentioned in association with each other.

According to the theory they expound, a database with any significant
write activity whatsoever should never be on raid 5, but instead be on
raid 0+1.

I've never had a chance to benchmark a comparison between the two and
draw my own conclusions, but it might be interesting to do so.

-- Pat

When grilled further on (Tue, 16 Dec 2003 22:30:04 -0600),
Patrick Spinler <spinler(at)kmtel(dot)com> confessed:

>
> According to the theory they expound, a database with any significant
> write activity whatsoever should never be on raid 5, but instead be on
> raid 0+1.
>

Kind of related and a point of reference. We use ClearCase and have many
multiple Gb vob's(databases). We were using RAID-5, but had to back off to RAID
0+1 because of performance reasons (which was indicated in the manual, once you
read it...). This would happen around 1-2Gb's vob size. Our usage of CC
provides heavy writing activity to the underlying dB.

I don't know what kind of dB engine Atria->Rational->IBM has implemented
underneath, or even it it would look like a dB to someone who knew the
difference...

Cheers,
Rob

--
21:42:33 up 4 days, 13:15, 1 user, load average: 2.23, 2.10, 2.10

Patrick Spinler wrote:
> Adam Witney wrote:
> > Hi all,
> >
> > I am experimenting with a few OS's for my new hardware. I plan to have a
> > software RAID5 device for my pgsql data directory.
> >
> > I have been experimenting with FreeBSD and with Linux, does anyone have any
> > thoughts on whether vinum RAID devices are better than Linux software RAID?
> > Or vice versa... Or no difference at all? Any gotchas I should bear in mind?
> >
>
> Of interest, there are a number of "old school" dba's of my aquantance
> who swear vociferously whenever the words RAID 5 and Database are
> mentioned in association with each other.
>
> According to the theory they expound, a database with any significant
> write activity whatsoever should never be on raid 5, but instead be on
> raid 0+1.
>
> I've never had a chance to benchmark a comparison between the two and
> draw my own conclusions, but it might be interesting to do so.

I have heard you need +6 disks for RAID5 to be faster than Raid 0+1.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 16 Dec 2003, Robert Creager wrote:

> When grilled further on (Tue, 16 Dec 2003 22:30:04 -0600),
> Patrick Spinler <spinler(at)kmtel(dot)com> confessed:
>
> >
> > According to the theory they expound, a database with any significant
> > write activity whatsoever should never be on raid 5, but instead be on
> > raid 0+1.
> >
>
> Kind of related and a point of reference. We use ClearCase and have many
> multiple Gb vob's(databases). We were using RAID-5, but had to back off to RAID
> 0+1 because of performance reasons (which was indicated in the manual, once you
> read it...). This would happen around 1-2Gb's vob size. Our usage of CC
> provides heavy writing activity to the underlying dB.
>
> I don't know what kind of dB engine Atria->Rational->IBM has implemented
> underneath, or even it it would look like a dB to someone who knew the
> difference...

Just wondering, was that on hardware or software RAID5, and if hardware
did it have battery backed cache controllers? Makes a huge difference. I
would never use SW RAID5 for heavily written databases.

On 17/12/03 3:45 pm, "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:

> On Tue, 16 Dec 2003, Robert Creager wrote:
>
>> When grilled further on (Tue, 16 Dec 2003 22:30:04 -0600),
>> Patrick Spinler <spinler(at)kmtel(dot)com> confessed:
>>
>>>
>>> According to the theory they expound, a database with any significant
>>> write activity whatsoever should never be on raid 5, but instead be on
>>> raid 0+1.
>>>
>>
>> Kind of related and a point of reference. We use ClearCase and have many
>> multiple Gb vob's(databases). We were using RAID-5, but had to back off to
>> RAID
>> 0+1 because of performance reasons (which was indicated in the manual, once
>> you
>> read it...). This would happen around 1-2Gb's vob size. Our usage of CC
>> provides heavy writing activity to the underlying dB.
>>
>> I don't know what kind of dB engine Atria->Rational->IBM has implemented
>> underneath, or even it it would look like a dB to someone who knew the
>> difference...
>
> Just wondering, was that on hardware or software RAID5, and if hardware
> did it have battery backed cache controllers? Makes a huge difference. I
> would never use SW RAID5 for heavily written databases.

Hi Scott,

What level of activity would you call "heavily written"?

Thanks

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Wed, 17 Dec 2003, Adam Witney wrote:

> On 17/12/03 3:45 pm, "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
>
> > On Tue, 16 Dec 2003, Robert Creager wrote:
> >
> >> When grilled further on (Tue, 16 Dec 2003 22:30:04 -0600),
> >> Patrick Spinler <spinler(at)kmtel(dot)com> confessed:
> >>
> >>>
> >>> According to the theory they expound, a database with any significant
> >>> write activity whatsoever should never be on raid 5, but instead be on
> >>> raid 0+1.
> >>>
> >>
> >> Kind of related and a point of reference. We use ClearCase and have many
> >> multiple Gb vob's(databases). We were using RAID-5, but had to back off to
> >> RAID
> >> 0+1 because of performance reasons (which was indicated in the manual, once
> >> you
> >> read it...). This would happen around 1-2Gb's vob size. Our usage of CC
> >> provides heavy writing activity to the underlying dB.
> >>
> >> I don't know what kind of dB engine Atria->Rational->IBM has implemented
> >> underneath, or even it it would look like a dB to someone who knew the
> >> difference...
> >
> > Just wondering, was that on hardware or software RAID5, and if hardware
> > did it have battery backed cache controllers? Makes a huge difference. I
> > would never use SW RAID5 for heavily written databases.
>
> Hi Scott,
>
> What level of activity would you call "heavily written"?

More than a couple inserts a second. Or more specifically, depending on
the machine, when the CPU usage starts to climb, which really can vary a
lot from machine to machine. A machine with 4 2.8GHz CPUs could probably
handle a much higher write load than a single <1GHz machine.

Things like a data warehouse where you feed in streams slowly or at night
and then do huge selects work well on RAID5 sw. Transactional systems,
like billing or reservation systems should probably not be on sw RAID.

When grilled further on (Wed, 17 Dec 2003 08:45:43 -0700 (MST)),
"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> confessed:

>
> Just wondering, was that on hardware or software RAID5, and if hardware
> did it have battery backed cache controllers? Makes a huge difference. I
> would never use SW RAID5 for heavily written databases.
>

Hardware raid on Sun Ultra 4 and Ultra 250. I do not know what type of
controller, or if it's battery backed. It's up in the data center, and I've
never even seen them...

cheers,
rob

--
22:11:19 up 5 days, 13:43, 1 user, load average: 2.09, 2.16, 1.87

> I am experimenting with a few OS's for my new hardware. I plan to have a
> software RAID5 device for my pgsql data directory.
>
> I have been experimenting with FreeBSD and with Linux, does anyone have any
> thoughts on whether vinum RAID devices are better than Linux software RAID?
> Or vice versa... Or no difference at all? Any gotchas I should bear in mind?

You have to be more specific than just software RAID... IDE software
RAID helps with reliability, but it's murder on performance. Only one
command can be issued at a time with IDE, so your performance of RAID
1 is going to be half that of just writing to a single device. SCSI
on the other hand... SCSI is the only way to go if you're serious
about databases. -sc

--
Sean Chittenden

Dear Experts,

For what its worth : I have been running a postgresql database on a hardware
raid 5 (adaptec 2400A hardware card and 4 x 200GB WD IDE drives) for about
6months and in my (relatively low volume read write by transaction standards
described by others here) environment all has been smooth.

MItchell Laks

>On Thursday 18 December 2003 12:13 am, you wrote:
> When grilled further on (Wed, 17 Dec 2003 08:45:43 -0700 (MST)),
>
> "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> confessed:
> > Just wondering, was that on hardware or software RAID5, and if hardware
> > did it have battery backed cache controllers? Makes a huge difference.
> > I would never use SW RAID5 for heavily written databases.
>
> Hardware raid on Sun Ultra 4 and Ultra 250. I do not know what type of
> controller, or if it's battery backed. It's up in the data center, and
> I've never even seen them...
>
> cheers,
> rob

On 18/12/03 5:39 am, "Sean Chittenden" <sean(at)chittenden(dot)org> wrote:

>> I am experimenting with a few OS's for my new hardware. I plan to have a
>> software RAID5 device for my pgsql data directory.
>>
>> I have been experimenting with FreeBSD and with Linux, does anyone have any
>> thoughts on whether vinum RAID devices are better than Linux software RAID?
>> Or vice versa... Or no difference at all? Any gotchas I should bear in mind?
>
> You have to be more specific than just software RAID... IDE software
> RAID helps with reliability, but it's murder on performance. Only one
> command can be issued at a time with IDE, so your performance of RAID
> 1 is going to be half that of just writing to a single device. SCSI
> on the other hand... SCSI is the only way to go if you're serious
> about databases. -sc

These are Dell Poweredge 1750 and 2650 servers with SCSI disks

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.