Обсуждение: which dual-CPU hardware/OS is fastest for PostgreSQL?

I'm sorry if there's a URL out there answering this, but I couldn't find it.

For those of us that need the best performance possible out of a
dedicated dual-CPU PostgreSQL server, what is recommended?

AMD64/Opteron or i386/Xeon?

Linux or FreeBSD or _?_

I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk performance.

Any hardware-comparison benchmarks out there showing the results for
different PostgreSQL setups?

Thanks!

Miles Keaton wrote:

>I'm sorry if there's a URL out there answering this, but I couldn't find it.
>
>For those of us that need the best performance possible out of a
>dedicated dual-CPU PostgreSQL server, what is recommended?
>
>AMD64/Opteron or i386/Xeon?
>
>
AMD64/Opteron

>Linux or FreeBSD or _?_
>
>

This is a religious question :)

>I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk performance.
>
>
And many, many disks -- yes.

Sincerely,

Joshua D. Drake


>Any hardware-comparison benchmarks out there showing the results for
>different PostgreSQL setups?
>
>Thanks!
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Quoth mileskeaton@gmail.com (Miles Keaton):
> I'm sorry if there's a URL out there answering this, but I couldn't
> find it.
>
> For those of us that need the best performance possible out of a
> dedicated dual-CPU PostgreSQL server, what is recommended?
>
> AMD64/Opteron or i386/Xeon?

Xeon sux pretty bad...

> Linux or FreeBSD or _?_

The killer question won't be of what OS is "faster," but rather of
what OS better supports the fastest hardware you can get your hands
on.

We tried doing some FreeBSD benchmarking on a quad-Opteron box, only
to discover that the fibrechannel controller worked in what amounted
to a "PAE-like" mode where it only talked DMA in a 32 bit manner.  We
might have found a more suitable controller, given time that was not
available.

A while back I tried to do some FreeBSD benchmarking on a quad-Xeon
box with 8GB of RAM.  I couldn't find _any_ RAID controller compatible
with that configuration, so FreeBSD wasn't usable on that hardware
unless I told the box to ignore half the RAM.

There lies the rub of the problem: you need to make sure all the vital
components are able to run "full blast" in order to maximize
performance.

The really high end SCSI controllers may only have supported drivers
for some specific set of OSes, and it seems to be pretty easy to put
together boxes where one or another component leaps into the "That
Doesn't Work!" category.

> I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk
> performance.

RAID controllers tend to use i960 or StrongARM CPUs that run at speeds
that _aren't_ all that impressive.  With software RAID, you can take
advantage of the _enormous_ increases in the speed of the main CPU.

I don't know so much about FreeBSD's handling of this, but on Linux,
there's pretty strong indication that _SOFTWARE_ RAID is faster than
hardware RAID.

It has the further merit that you're not dependent on some disk
formatting scheme that is only compatible with the model of RAID
controller that you've got, where if the controller breaks down, you
likely have to rebuild the whole array from scratch and your data is
toast.

The assumptions change if you're looking at really high end disk
arrays, but that's certainly another story.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://linuxfinances.info/info/finances.html
Real Programmers are surprised when the odometers in their cars don't
turn from 99999 to A0000.

Xeon sux pretty bad...

> Linux or FreeBSD or _?_

The killer question won't be of what OS is "faster," but rather of
what OS better supports the fastest hardware you can get your hands
on.

We tried doing some FreeBSD benchmarking on a quad-Opteron box, only
to discover that the fibrechannel controller worked in what amounted
to a "PAE-like" mode where it only talked DMA in a 32 bit manner.  We
might have found a more suitable controller, given time that was not
available.

A while back I tried to do some FreeBSD benchmarking on a quad-Xeon
box with 8GB of RAM.  I couldn't find _any_ RAID controller compatible
with that configuration, so FreeBSD wasn't usable on that hardware
unless I told the box to ignore half the RAM.

There lies the rub of the problem: you need to make sure all the vital
components are able to run "full blast" in order to maximize
performance.

The really high end SCSI controllers may only have supported drivers
for some specific set of OSes, and it seems to be pretty easy to put
together boxes where one or another component leaps into the "That
Doesn't Work!" category.

> I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk
> performance.

RAID controllers tend to use i960 or StrongARM CPUs that run at speeds
that _aren't_ all that impressive.  With software RAID, you can take
advantage of the _enormous_ increases in the speed of the main CPU.

I don't know so much about FreeBSD's handling of this, but on Linux,
there's pretty strong indication that _SOFTWARE_ RAID is faster than
hardware RAID.

It has the further merit that you're not dependent on some disk
formatting scheme that is only compatible with the model of RAID
controller that you've got, where if the controller breaks down, you
likely have to rebuild the whole array from scratch and your data is
toast.

The assumptions change if you're looking at really high end disk
arrays, but that's certainly another story.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://linuxfinances.info/info/finances.html
Real Programmers are surprised when the odometers in their cars don't
turn from 99999 to A0000.

>
>RAID controllers tend to use i960 or StrongARM CPUs that run at speeds
>that _aren't_ all that impressive.  With software RAID, you can take
>advantage of the _enormous_ increases in the speed of the main CPU.
>
>I don't know so much about FreeBSD's handling of this, but on Linux,
>there's pretty strong indication that _SOFTWARE_ RAID is faster than
>hardware RAID.
>
>
Unless something has changed though, you can't run raid 10
with linux software raid and raid 5 sucks for heavy writes.

J



>It has the further merit that you're not dependent on some disk
>formatting scheme that is only compatible with the model of RAID
>controller that you've got, where if the controller breaks down, you
>likely have to rebuild the whole array from scratch and your data is
>toast.
>
>The assumptions change if you're looking at really high end disk
>arrays, but that's certainly another story.
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Chris,

> I don't know so much about FreeBSD's handling of this, but on Linux,
> there's pretty strong indication that _SOFTWARE_ RAID is faster than
> hardware RAID.

Certainly better than an Adaptec.  But not necessarily better than a
medium-end RAID card, like an LSI.  It really depends on the quality of the
controller.

Also, expected concurrent activity should influence you.   On  a dedicated
database server, you'll seldom max out the CPU but will often max of the
disk, so the CPU required by software RAID is "free".   However, if you have
a Web/PG/E-mail box which frequently hits 100% CPU, then even a lower-end
RAID card can be beneficial simply by taking load off the CPU.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, Jan 10, 2005 at 08:31:22PM -0800, Joshua D. Drake wrote:
> Unless something has changed though, you can't run raid 10
> with linux software raid

Hm, why not? What stops you from making two RAID-0 devices and mirroring
those? (Or the other way round, I can never remember :-) )

/* Steinar */
--
Homepage: http://www.sesse.net/

...and on Mon, Jan 10, 2005 at 08:31:22PM -0800, Joshua D. Drake used the keyboard:
>
> >
> >RAID controllers tend to use i960 or StrongARM CPUs that run at speeds
> >that _aren't_ all that impressive.  With software RAID, you can take
> >advantage of the _enormous_ increases in the speed of the main CPU.
> >
> >I don't know so much about FreeBSD's handling of this, but on Linux,
> >there's pretty strong indication that _SOFTWARE_ RAID is faster than
> >hardware RAID.
> >
> >
> Unless something has changed though, you can't run raid 10
> with linux software raid and raid 5 sucks for heavy writes.
>
> J

Hello, Joshua.

Things have changed. :)

From 2.6.10's drivers/md/Kconfig:

config MD_RAID10
        tristate "RAID-10 (mirrored striping) mode (EXPERIMENTAL)"
        depends on BLK_DEV_MD && EXPERIMENTAL
        ---help---
          RAID-10 provides a combination of striping (RAID-0) and
          mirroring (RAID-1) with easier configuration and more flexable
          layout.
          Unlike RAID-0, but like RAID-1, RAID-10 requires all devices to
          be the same size (or atleast, only as much as the smallest device
          will be used).
          RAID-10 provides a variety of layouts that provide different levels
          of redundancy and performance.

          RAID-10 requires mdadm-1.7.0 or later, available at:

          ftp://ftp.kernel.org/pub/linux/utils/raid/mdadm/

There is a problem, however, that may render software RAID non-viable
though. According to one of my benchmarks, it makes up for an up to
10% increase in system time consumed under full loads, so if the original
poster's application is going to be CPU-bound, which might be the case,
as he is looking for a machine that's strong on the CPU side, that may
be the "too much" bit.

Of course, if Opteron is being chosen for the increase in the amount of
memory it can address, this is not the issue.

HTH,
--
    Grega Bremec
    gregab at p0f dot net

On 11 Jan 2005 04:25:04 GMT
Christopher Browne <cbbrowne@acm.org> wrote:
> Xeon sux pretty bad...
>
> > Linux or FreeBSD or _?_
>
> The killer question won't be of what OS is "faster," but rather of
> what OS better supports the fastest hardware you can get your hands
> on.

Well, if multiple OSs work on the hardware you like, there is nothing
wrong with selecting the fastest among them of course.  As for Linux or
FreeBSD, you may also want to consider NetBSD.  It seems that with the
latest releases of both, NetBSD outperforms FreeBSD in at least one
benchmark.

http://www.feyrer.de/NetBSD/gmcgarry/

The benchmarks were run on a single processor but you can always run the
benchmark on whatever hardware you select - assuming that it runs both.

Isn't there also a PostgreSQL specific benchmark available?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

> Subject: [PERFORM] which dual-CPU hardware/OS is fastest for
PostgreSQL?
>
> I'm sorry if there's a URL out there answering this, but I couldn't
find
> it.
>
> For those of us that need the best performance possible out of a
> dedicated dual-CPU PostgreSQL server, what is recommended?
>
> AMD64/Opteron or i386/Xeon?
>
> Linux or FreeBSD or _?_
>
> I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk
> performance.
>
> Any hardware-comparison benchmarks out there showing the results for
> different PostgreSQL setups?

My recommendation would be:
2 way or 4 way Opteron depending on needs (looking on a price for 4-way?
Go here: http://www.swt.com/qo3.html).  Go no less than Opteron 246.
Tyan motherboard
Serial ATA controller by 3ware (their latest escalade series size for
your needs) (if money is no object, go scsi).  Make sure you pick up the
bbu.
Redhat Linux FC3 x86-64
Good memory (DDR400 registered, at least)...lots of it.

You can get a two way rackmount for under 4000$.  You can get a 4-way
for under 10k$.  Make sure you pick up a rackmount case that has a
serial ATA backplane that supports led status light for disk drives, and
make sure you get the right riser, heh.

Merlin

while you weren't looking, Merlin Moncure wrote:

> 2 way or 4 way Opteron depending on needs (looking on a price for 4-way?
> Go here: http://www.swt.com/qo3.html).

Try also the Appro 1U 4-way Opteron server, at:
http://www.appro.com/product/server_1142h.asp

I specced a 4-way 842 (1.6 GHz: little to none of our db work is CPU
bound; there's just a lot of it going on at once) with 32G core for
within delta of what SWT wants /just/ for the 32G -- the price of the
box itself and anything else atop that.  Stepping up to a faster CPU
should increase the cost directly in line with the retail price for
the silicon.

We haven't yet ordered the machine (and the quote was from early last
month, so their prices will have fluctuated) and consequently, I can't
comment on their quality.  Their default warranty is three years,
"rapid exchange", though, and they offer on-site service for only
nominally more, IIRC.  Some slightly more than cursory googling hasn't
turned up anything overly negative, either.

As a 1U, the box has no appreciable storage of its own but we're
shopping for a competent, non bank-breaking fibre setup right now, so
that's not an issue for our situation.  While on the subject, anyone
here have anything to say about JMR fibre raid cabinets?
(Fibre-to-fibre, not fibre-to-SATA or the like.)

/rls

--
:wq

Rosser Schwarz <rosser.schwarz@gmail.com> writes:

> Try also the Appro 1U 4-way Opteron server, at:
> http://www.appro.com/product/server_1142h.asp

Back in the day, we used to have problems with our 1U dual pentiums. We
attributed it to heat accelerating failure. I would fear four opterons in 1U
would be damned hard to cool effectively, no?

--
greg

$4000 is not going to get you much disk - If you buy components from
the cheapest source I know (newegg.com) you end up around $5k with
14x36gig Raptor SATA drives and a 4U chasis with a 14xSATA built in
back plane packing 2x9500S AMCC Escalade RAID cards, which are
supported in Linux, 4Gig RAM and 2xOpteron 242.  If you are not CPU
bound, there isn't much point going to 246.   If you want SCSI, then
you will be paying more.  Check out rackmountmart.com for Chasises,
they have a nice 5U that has a 24xSATA backplane (We will be acquiring
this in the next few weeks).  If you really want to go nuts, they have
an 8U with 40xSATA backplane.

Alex Turner
NetEconomist


On Tue, 11 Jan 2005 08:33:09 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> > Subject: [PERFORM] which dual-CPU hardware/OS is fastest for
> PostgreSQL?
> >
> > I'm sorry if there's a URL out there answering this, but I couldn't
> find
> > it.
> >
> > For those of us that need the best performance possible out of a
> > dedicated dual-CPU PostgreSQL server, what is recommended?
> >
> > AMD64/Opteron or i386/Xeon?
> >
> > Linux or FreeBSD or _?_
> >
> > I'm assuming hardware RAID 10 on 15k SCSI drives is fastest disk
> > performance.
> >
> > Any hardware-comparison benchmarks out there showing the results for
> > different PostgreSQL setups?
>
> My recommendation would be:
> 2 way or 4 way Opteron depending on needs (looking on a price for 4-way?
> Go here: http://www.swt.com/qo3.html).  Go no less than Opteron 246.
> Tyan motherboard
> Serial ATA controller by 3ware (their latest escalade series size for
> your needs) (if money is no object, go scsi).  Make sure you pick up the
> bbu.
> Redhat Linux FC3 x86-64
> Good memory (DDR400 registered, at least)...lots of it.
>
> You can get a two way rackmount for under 4000$.  You can get a 4-way
> for under 10k$.  Make sure you pick up a rackmount case that has a
> serial ATA backplane that supports led status light for disk drives, and
> make sure you get the right riser, heh.
>
> Merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

> $4000 is not going to get you much disk - If you buy components from
> the cheapest source I know (newegg.com) you end up around $5k with
> 14x36gig Raptor SATA drives and a 4U chasis with a 14xSATA built in
> back plane packing 2x9500S AMCC Escalade RAID cards, which are
> supported in Linux, 4Gig RAM and 2xOpteron 242.  If you are not CPU
> bound, there isn't much point going to 246.   If you want SCSI, then
> you will be paying more.  Check out rackmountmart.com for Chasises,
> they have a nice 5U that has a 24xSATA backplane (We will be acquiring
> this in the next few weeks).  If you really want to go nuts, they have
> an 8U with 40xSATA backplane.
>
> Alex Turner
> NetEconomist

heh, our apps do tend to be CPU bound.  Generally, I think the extra CPU
horsepower is worth the investment until you get to the really high end
cpus.

I definitely agree with all your hardware choices though...seems like
you've hit the 'magic formula'.

Merlin

while you weren't looking, Greg Stark wrote:

> Back in the day, we used to have problems with our 1U dual pentiums. We
> attributed it to heat accelerating failure. I would fear four opterons in 1U
> would be damned hard to cool effectively, no?

Opterons actually run pretty coolly, comparatively.  If it's a big
concern, you can always drop a few more clams for the low-voltage
versions -- available in 1.4 and 2.0 GHz flavors, and of which I've
heard several accounts of their being run successfully /without/
active cooling -- or punt until later this year, when they ship
Winchester core Opterons (90nm SOI -- the current, uniprocessor
silicon fabbed with that process has some 3W heat dissipation idle,
~30W under full load; as a point of contrast, current 90nm P4s have
34W idle dissipation, and some 100W peak).

We have a number of 1U machines (P4s, I believe), and a Dell blade
server (six or seven P3 machines in a 3U cabinet) as our webservers,
and none of them seem to have any trouble with heat.  That's actually
a bigger deal than it might first seem, given how frighteningly
crammed with crap our machine room is.

/rls

--
:wq

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:

> heh, our apps do tend to be CPU bound.  Generally, I think the extra CPU
> horsepower is worth the investment until you get to the really high end
> cpus.

I find that while most applications I work with shouldn't be cpu intensive
they do seem end up being cpu bound quite frequently. What happens is that 90%
of the workload has a working set that fits in RAM. So the system ends up
being bound by the memory bus speed. That appears exactly the same as
cpu-bound, though I'm unclear whether increasing the cpu clock will help.

It's quite possible to have this situation at the same time as other queries
are i/o bound. It's quite common to have 95% of your workload be frequently
executed fast queries on commonly accessed data and 5% be bigger data
warehouse style queries that need to do large sequential reads.

Incidentally, the same was true for Oracle on Solaris. If we found excessive
cpu use typically meant some frequently executed query was using a sequential
scan on a small table. Small enough to fit in RAM but large enough to consume
lots of cycles reading it.

--
greg

Steinar H. Gunderson wrote:

>On Mon, Jan 10, 2005 at 08:31:22PM -0800, Joshua D. Drake wrote:
>
>
>>Unless something has changed though, you can't run raid 10
>>with linux software raid
>>
>>
>
>Hm, why not? What stops you from making two RAID-0 devices and mirroring
>those? (Or the other way round, I can never remember :-) )
>
>

O.k. that seems totally wrong ;) but yes your correct you could
probably do it.

Sincerely,

Josuha D. Drake



>/* Steinar */
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Infact the cache hit ratio that Oracle suggests is the minimum good
value is 95%.  Anything below that is bad news.  The reason is pretty
obvious - RAM transfer speed is around 3.2G/sec these days, whilst
even the best array isn't going to give more than 400MB/sec, and
that's not even starting to talk about seek time.  anything below 90%
is not going to keep even the best disc hardware saturated.  I know
that our dataset is 99% cached, and therefore better CPUs/better RAM
has a huge impact on overall performance.

Alex Turner
NetEconomist

On 11 Jan 2005 10:39:01 -0500, Greg Stark <gsstark@mit.edu> wrote:
>
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
>
> > heh, our apps do tend to be CPU bound.  Generally, I think the extra CPU
> > horsepower is worth the investment until you get to the really high end
> > cpus.
>
> I find that while most applications I work with shouldn't be cpu intensive
> they do seem end up being cpu bound quite frequently. What happens is that 90%
> of the workload has a working set that fits in RAM. So the system ends up
> being bound by the memory bus speed. That appears exactly the same as
> cpu-bound, though I'm unclear whether increasing the cpu clock will help.
>
> It's quite possible to have this situation at the same time as other queries
> are i/o bound. It's quite common to have 95% of your workload be frequently
> executed fast queries on commonly accessed data and 5% be bigger data
> warehouse style queries that need to do large sequential reads.
>
> Incidentally, the same was true for Oracle on Solaris. If we found excessive
> cpu use typically meant some frequently executed query was using a sequential
> scan on a small table. Small enough to fit in RAM but large enough to consume
> lots of cycles reading it.
>
> --
> greg
>
>

Alex Turner <armtuk@gmail.com> writes:

> Infact the cache hit ratio that Oracle suggests is the minimum good
> value is 95%.  Anything below that is bad news.

Well that seems very workload dependent. No amount of cache is going to be
able to achieve that for a DSS system chugging sequentially through terabytes
of data. Whereas for OLTP systems I would wouldn't be surprised to see upwards
of 99% hit rate.

Note that a high cache hit rate can also be a sign of a problem. After all, it
means the same data is being accessed repeatedly which implicitly means
something is being done inefficiently. For an SQL database it could mean the
query plans are suboptimal.

On several occasions we found Oracle behaving poorly despite excellent cache
hit rates because it was doing a sequential scan of a moderately sized table
instead of an index lookup. The table was small enough to fit in RAM but large
enough to consume a significant amount of cpu, especially with the query being
run thousands of times per minute.

--
greg

No - I agree - Analysis cache hit rate as a single indicator is
dangerous.  You can easily increase cache hit rate by de-optimizing a
good query so it uses more CPU cylces, and therefore has a higher
cache hit rate.  All information has to be taken as a whole when
performing optimization on a system.  Cache hit rate is just one
factor.  For data warehousing, it's obviously that you are going to
have a lower cache hit rate because you are often performing scans
across large data sets that will never fit in memory.  But for most
system, not necesarily just OLTP, a high cache hit ratio is
acheivable.  Cache hit ratio is just one small indication of
performance.

Relating to that - How to extract this kind of information from
postgresql?  Is there a way to get the cache hti ratio, or determine
the worst 10 queries in a database?

Alex Turner
NetEconomist


On 12 Jan 2005 12:25:23 -0500, Greg Stark <gsstark@mit.edu> wrote:
>
> Alex Turner <armtuk@gmail.com> writes:
>
> > Infact the cache hit ratio that Oracle suggests is the minimum good
> > value is 95%.  Anything below that is bad news.
>
> Well that seems very workload dependent. No amount of cache is going to be
> able to achieve that for a DSS system chugging sequentially through terabytes
> of data. Whereas for OLTP systems I would wouldn't be surprised to see upwards
> of 99% hit rate.
>
> Note that a high cache hit rate can also be a sign of a problem. After all, it
> means the same data is being accessed repeatedly which implicitly means
> something is being done inefficiently. For an SQL database it could mean the
> query plans are suboptimal.
>
> On several occasions we found Oracle behaving poorly despite excellent cache
> hit rates because it was doing a sequential scan of a moderately sized table
> instead of an index lookup. The table was small enough to fit in RAM but large
> enough to consume a significant amount of cpu, especially with the query being
> run thousands of times per minute.
>
> --
> greg
>
>

Joshua D. Drake wrote:
>>RAID controllers tend to use i960 or StrongARM CPUs that run at speeds
>>that _aren't_ all that impressive.  With software RAID, you can take
>>advantage of the _enormous_ increases in the speed of the main CPU.
>>
>>I don't know so much about FreeBSD's handling of this, but on Linux,
>>there's pretty strong indication that _SOFTWARE_ RAID is faster than
>>hardware RAID.
>>
>>
>
> Unless something has changed though, you can't run raid 10
> with linux software raid and raid 5 sucks for heavy writes.

You could always do raid 1 over raid 0, with newer kernels (2.6ish)
there is even a dedicated raid10 driver.

Jan

Hi,

From what I've been reading on the list for the last few months, adaptec
isn't that good when it comes to RAID controllers, but LSI keeps popping up.
Is there any particual models that are recommended as I'm in the market for
two new servers both with RAID controllers. The server specs I'm thinking
are as follows:

Box 1
Fedora 64bit core 3
4 GB RAM (2GB per CPU)
2 x Opteron CPU ???
Tyan K8S
LSI® 53C1030 U320 SCSI controller Dual-channel

Box 2
Fedora 64bit core 3
2 GB RAM (1GB per CPU)
2 x Opteron CPU ???
Tyan K8S
LSI® 53C1030 U320 SCSI controller Dual-channel

This motherboard has can "Connects to PCI-X Bridge A, LSI® ZCR (Zero Channel
RAID) support (SCSI Interface Steering Logic)". I believe this means I can
get a LSI MegaRAID 320-0 which a few have mentioned on the list
(http://www.lsilogic.com/products/megaraid/scsi_320_0.html). It supports
RAID 10 and supports battery backed cache. Anyone had any experience with
this?

Any other particular controller that people recommend? From what I've been
reading RAID 10 and battery backed cache sound like things I need. :)

Thanks,

Benjamin Wragg

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Tuesday, 11 January 2005 5:35 PM
To: pgsql-performance@postgresql.org
Cc: Christopher Browne
Subject: Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?

Chris,

> I don't know so much about FreeBSD's handling of this, but on Linux,
> there's pretty strong indication that _SOFTWARE_ RAID is faster than
> hardware RAID.

Certainly better than an Adaptec.  But not necessarily better than a
medium-end RAID card, like an LSI.  It really depends on the quality of the
controller.

Also, expected concurrent activity should influence you.   On  a dedicated
database server, you'll seldom max out the CPU but will often max of the
disk, so the CPU required by software RAID is "free".   However, if you have

a Web/PG/E-mail box which frequently hits 100% CPU, then even a lower-end
RAID card can be beneficial simply by taking load off the CPU.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 10/01/2005


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.11 - Release Date: 12/01/2005

Without starting too much controvesy I hope, I would seriously
recommend you evaluate the AMCC Escalade 9500S SATA controller.  It
has many of the features of a SCSI controler, but works with cheaper
drives, and for half the price or many SCSI controlers (9500S-8MI goes
for abour $500).  See http://plexq.com/~aturner/3ware.pdf for their 4
way, 8 way and 12 way RAID benchmarks including RAID 0, RAID 5 and
RAID 10.  If others have similar data, I would be very interested to
see how it stacks up against other RAID controllers.

Alex Turner
NetEconomist


On Fri, 14 Jan 2005 16:52:42 +1100, Benjamin Wragg <bwragg@tpg.com.au> wrote:
> Hi,
>
> From what I've been reading on the list for the last few months, adaptec
> isn't that good when it comes to RAID controllers, but LSI keeps popping up.
> Is there any particual models that are recommended as I'm in the market for
> two new servers both with RAID controllers. The server specs I'm thinking
> are as follows:
>
> Box 1
> Fedora 64bit core 3
> 4 GB RAM (2GB per CPU)
> 2 x Opteron CPU ???
> Tyan K8S
> LSI® 53C1030 U320 SCSI controller Dual-channel
>
> Box 2
> Fedora 64bit core 3
> 2 GB RAM (1GB per CPU)
> 2 x Opteron CPU ???
> Tyan K8S
> LSI® 53C1030 U320 SCSI controller Dual-channel
>
> This motherboard has can "Connects to PCI-X Bridge A, LSI® ZCR (Zero Channel
> RAID) support (SCSI Interface Steering Logic)". I believe this means I can
> get a LSI MegaRAID 320-0 which a few have mentioned on the list
> (http://www.lsilogic.com/products/megaraid/scsi_320_0.html). It supports
> RAID 10 and supports battery backed cache. Anyone had any experience with
> this?
>
> Any other particular controller that people recommend? From what I've been
> reading RAID 10 and battery backed cache sound like things I need. :)
>
> Thanks,
>
> Benjamin Wragg
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Josh Berkus
> Sent: Tuesday, 11 January 2005 5:35 PM
> To: pgsql-performance@postgresql.org
> Cc: Christopher Browne
> Subject: Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
>
> Chris,
>
> > I don't know so much about FreeBSD's handling of this, but on Linux,
> > there's pretty strong indication that _SOFTWARE_ RAID is faster than
> > hardware RAID.
>
> Certainly better than an Adaptec.  But not necessarily better than a
> medium-end RAID card, like an LSI.  It really depends on the quality of the
> controller.
>
> Also, expected concurrent activity should influence you.   On  a dedicated
> database server, you'll seldom max out the CPU but will often max of the
> disk, so the CPU required by software RAID is "free".   However, if you have
>
> a Web/PG/E-mail box which frequently hits 100% CPU, then even a lower-end
> RAID card can be beneficial simply by taking load off the CPU.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 10/01/2005
>
> --
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.6.11 - Release Date: 12/01/2005
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Jan Dittmer <j.dittmer@portrix.net> writes:

> You could always do raid 1 over raid 0, with newer kernels (2.6ish)
> there is even a dedicated raid10 driver.

Aren't you much better off doing raid 0 over raid 1?

With raid 1 over raid 0 you're mirroring two stripe sets. That means if any
drive from the first stripe set goes you lose the whole side of the mirror. If
any drive of the second stripe set goes you lost your array. Even if they're
not the same position in the array.

If you do raid 0 over raid 1 then you're striping a series of mirrored drives.
So if any drive fails you only lose that drive from the stripe set. If another
drive fails then you're ok as long as it isn't the specific drive that was
paired with the first failed drive.

--
greg

Alex wrote:
> Without starting too much controvesy I hope, I would seriously
> recommend you evaluate the AMCC Escalade 9500S SATA controller.  It
> has many of the features of a SCSI controler, but works with cheaper
> drives, and for half the price or many SCSI controlers (9500S-8MI goes
> for abour $500).  See http://plexq.com/~aturner/3ware.pdf for their 4
> way, 8 way and 12 way RAID benchmarks including RAID 0, RAID 5 and
> RAID 10.  If others have similar data, I would be very interested to
> see how it stacks up against other RAID controllers.

At the risk of shaming myself with another 'me too' post, I'd like to
say that my experiences back this up 100%.  The Escalade controllers are
excellent and the Raptor drives are fast and reliable (so far).  With
the money saved from going SCSI, instead of a RAID 5 a 10 could be built
for roughly the same price and capacity, guess which array is going to
be faster?

I think the danger about SATA is that many SATA components are not
server quality, so you have to be more careful about what you buy.  For
example, you can't just assume your SATA backplane has hot swap lights
(got bit by this one myself, heh).

Merlin

Merlin,

> I think the danger about SATA is that many SATA components are not
> server quality, so you have to be more careful about what you buy.  For
> example, you can't just assume your SATA backplane has hot swap lights
> (got bit by this one myself, heh).

Yeah, that's my big problem with anything IDE.    My personal experience of
failure rates for IDE drives, for example, is about 1 out of 10 fails in
service before it's a year old; SCSI has been more like 1 out of 50.

Also, while I've seen benchmarks like Escalade's, my real-world experience has
been that the full bi-directional r/w of SCSI means that it takes 2 SATA
drives to equal one SCSI drive in a heavy r/w application.   However, ODSL is
all SCSI so I don't have any numbers to back that up.

But one of my clients needs a new docs server, so maybe I can give an Escalade
a spin.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

> Merlin,
>
> > I think the danger about SATA is that many SATA components are not
> > server quality, so you have to be more careful about what you buy.  For
> > example, you can't just assume your SATA backplane has hot swap lights
> > (got bit by this one myself, heh).
>
> Yeah, that's my big problem with anything IDE.    My personal experience of
> failure rates for IDE drives, for example, is about 1 out of 10 fails in
> service before it's a year old; SCSI has been more like 1 out of 50.

Um. I'm pretty sure the actual hardware is just the same stuff. It's just the
interface electronics that change.

> Also, while I've seen benchmarks like Escalade's, my real-world experience has
> been that the full bi-directional r/w of SCSI means that it takes 2 SATA
> drives to equal one SCSI drive in a heavy r/w application.   However, ODSL is
> all SCSI so I don't have any numbers to back that up.

Do we know that these SATA/IDE controllers and drives don't "lie" about fsync
the way most IDE drives do? Does the controller just automatically disable the
write caching entirely?

I don't recall, did someone have a program that tested the write latency of a
drive to test this?

--
greg

Greg wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>
> > Merlin,
> >
> > > I think the danger about SATA is that many SATA components are not
> > > server quality, so you have to be more careful about what you buy.
> For
> > > example, you can't just assume your SATA backplane has hot swap
lights
> > > (got bit by this one myself, heh).
> >
> > Yeah, that's my big problem with anything IDE.    My personal
experience
> of
> > failure rates for IDE drives, for example, is about 1 out of 10
fails in
> > service before it's a year old; SCSI has been more like 1 out of 50.
>
> Um. I'm pretty sure the actual hardware is just the same stuff. It's
just
> the
> interface electronics that change.
>
> > Also, while I've seen benchmarks like Escalade's, my real-world
> experience has
> > been that the full bi-directional r/w of SCSI means that it takes 2
SATA
> > drives to equal one SCSI drive in a heavy r/w application.
However,
> ODSL is
> > all SCSI so I don't have any numbers to back that up.
>
> Do we know that these SATA/IDE controllers and drives don't "lie"
about
> fsync
> the way most IDE drives do? Does the controller just automatically
disable
> the
> write caching entirely?
>
> I don't recall, did someone have a program that tested the write
latency
> of a
> drive to test this?
>
> --
> greg

The Escalades, at least, work the way they are supposed to.  The raid
controller supports write back/write through.  Thus, you can leave fsync
on in pg with decent performance (not as good as fsync=off, though) and
count on the bbu to cover you in the event of a power failure.  Our
internal testing here confirmed the controller and the disks sync when
you tell them to (namely escalade/raptor).

Merlin

Greg Stark wrote:
> Jan Dittmer <j.dittmer@portrix.net> writes:
>
>
>>You could always do raid 1 over raid 0, with newer kernels (2.6ish)
>>there is even a dedicated raid10 driver.
>
>
> Aren't you much better off doing raid 0 over raid 1?
>
> With raid 1 over raid 0 you're mirroring two stripe sets. That means if any
> drive from the first stripe set goes you lose the whole side of the mirror. If
> any drive of the second stripe set goes you lost your array. Even if they're
> not the same position in the array.
>
> If you do raid 0 over raid 1 then you're striping a series of mirrored drives.
> So if any drive fails you only lose that drive from the stripe set. If another
> drive fails then you're ok as long as it isn't the specific drive that was
> paired with the first failed drive.


Ever heart of Murphy? :-) But of course you're right - I tend to mix up
the raid levels...

Jan

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:

> Alex wrote:
> > Without starting too much controvesy I hope, I would seriously
> > recommend you evaluate the AMCC Escalade 9500S SATA controller.
...
> At the risk of shaming myself with another 'me too' post, I'd like to
> say that my experiences back this up 100%.  The Escalade controllers are
> excellent and the Raptor drives are fast and reliable (so far).
...

I assume AMCC == 3ware now?

Has anyone verified that fsync is safe on these controllers? Ie, that they
aren't caching writes and "lying" about the write completing like IDE
drives often do by default?

--
greg

Greg Stark wrote:

>"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
>
>
>
>>Alex wrote:
>>
>>
>>>Without starting too much controvesy I hope, I would seriously
>>>recommend you evaluate the AMCC Escalade 9500S SATA controller.
>>>
>>>
>.
>
>
>>At the risk of shaming myself with another 'me too' post, I'd like to
>>say that my experiences back this up 100%.  The Escalade controllers are
>>excellent and the Raptor drives are fast and reliable (so far).
>>
>>
>.
>
>I assume AMCC == 3ware now?
>
>Has anyone verified that fsync is safe on these controllers? Ie, that they
>aren't caching writes and "lying" about the write completing like IDE
>drives oft
>
>

For those who speak highly of the Escalade controllers and/Raptor SATA
drives, how is the database being utilized, OLTP or primarily read
access? This is good information I am learning, but I also see the need
to understand the context of how the hardware is being used.

Steve Poe

> I assume AMCC == 3ware now?
>
> Has anyone verified that fsync is safe on these controllers? Ie, that they
> aren't caching writes and "lying" about the write completing like IDE
> drives often do by default?

The higher end AMCC/3ware controllers actually warn you about using
write-cache. You have to explicitly turn it on within the controller
bios.

They also have optional battery backed cache.

Sincerely,

Joshua D. Drake



>
--
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/

"Joshua D. Drake" <jd@commandprompt.com> writes:

> > I assume AMCC == 3ware now?
> >
> > Has anyone verified that fsync is safe on these controllers? Ie, that they
> > aren't caching writes and "lying" about the write completing like IDE
> > drives often do by default?
>
> The higher end AMCC/3ware controllers actually warn you about using
> write-cache. You have to explicitly turn it on within the controller
> bios.

Well that's a good sign.

But if they're using SATA drives my concern is that the drives themselves may
be doing some caching on their own. Has anyone verified that the controllers
are disabling the drive cache or issuing flushes or doing something else to be
sure to block the drives from caching writes?

--
greg

Greg Stark wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
> > > I assume AMCC == 3ware now?
> > >
> > > Has anyone verified that fsync is safe on these controllers? Ie, that they
> > > aren't caching writes and "lying" about the write completing like IDE
> > > drives often do by default?
> >
> > The higher end AMCC/3ware controllers actually warn you about using
> > write-cache. You have to explicitly turn it on within the controller
> > bios.
>
> Well that's a good sign.
>
> But if they're using SATA drives my concern is that the drives themselves may
> be doing some caching on their own. Has anyone verified that the controllers
> are disabling the drive cache or issuing flushes or doing something else to be
> sure to block the drives from caching writes?

I asked 3ware this at the Linuxworld Boston show and they said their
controller keeps the information in cache until they are sure it is on
the platters and not just in the disk cache, but that is far from a 100%
reliable report.

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

Anyone using power5 platform?  something like an ibm eserver p5 520
running red hat linux.
(http://www-1.ibm.com/servers/eserver/pseries/hardware/entry/520.html)?

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> I asked 3ware this at the Linuxworld Boston show and they said their
> controller keeps the information in cache until they are sure it is on
> the platters and not just in the disk cache, but that is far from a 100%
> reliable report.

Hm. Well, keeping it in cache is one thing. But what it needs to do is not
confirm the write to the host OS. Unless they want to sell their battery
backed unit which is an expensive add-on...

--
greg