Re: High end server and storage for a PostgreSQL OLTP system

Hi all,

I've been following this list for nearly a year now.
I've always managed to get PostgreSQL 7.1.x right for the job,
which in my case is a large and complex oltp system,
run under Pg for 6 years now.

We were already planning the switch from 7.1 to 7.4 (or even 8.0).
The last project we're facing with has a transaction volume that is
something we've never dealt with. By "transaction" I mean
something involving 10 to 10,000 (and more) sql queries
(a complex mix of insert/ update/ delete/ select).

I'd like to ask:

1) What kind of performance gain can I expect switching from
7.1 to 7.4 (or 8.0)? Obviously I'm doing my own testing,
but I'm not very impressed by 8.0 speed, may be I'm doing
testing on a low end server...

2) The goal is to make the db handle 100 tps (something like
100 users). What kind of server and storage should I provide?

The actual servers our application runs on normally have
2 Intel Xeon processors, 2-4 Gb RAM, RAID 0/1/5 SCSI
disk storage with hard drives @ 10,000 rpm

3) Highest I/O throughput SCSI adapters? Adaptec?

4) Is it correct to suppose that multiple RAID 1 arrays
can provide the fastest I/O ?
I usually reserve one RAID1 array to db data directory,
one RAID1 array to pg_xlog directory and one RAID1 array
for os and application needs.

5) OS and Pg specific tuning?
Usually I modify shared memory settings and most of postgresql.conf
available settings for 7.1, like `effective_cache', `shared_buffers',
`wal_buffers', `wal_files', and so on.

--
Cosimo

Cosimo Streppone <cosimo(at)streppone(dot)it> writes:
> 1) What kind of performance gain can I expect switching from
> 7.1 to 7.4 (or 8.0)? Obviously I'm doing my own testing,
> but I'm not very impressed by 8.0 speed, may be I'm doing
> testing on a low end server...

Most people report a noticeable speedup in each new release; we hit
different things in different releases, but usually at least one
performance gain is useful to any one person. For a jump as far as
from 7.1 to 8.0 I'm surprised that you're not seeing any gain at all.
What was your test case exactly? Have you perhaps tuned your app
so specifically to 7.1 that you need to detune it?

regards, tom lane

On Mon, Jan 31, 2005 at 09:41:32PM +0100, Cosimo Streppone wrote:
> 2) The goal is to make the db handle 100 tps (something like
> 100 users). What kind of server and storage should I provide?
>
> The actual servers our application runs on normally have
> 2 Intel Xeon processors, 2-4 Gb RAM, RAID 0/1/5 SCSI
> disk storage with hard drives @ 10,000 rpm

You might look at Opteron's, which theoretically have a higher data
bandwidth. If you're doing anything data intensive, like a sort in
memory, this could make a difference.

> 4) Is it correct to suppose that multiple RAID 1 arrays
> can provide the fastest I/O ?
> I usually reserve one RAID1 array to db data directory,
> one RAID1 array to pg_xlog directory and one RAID1 array
> for os and application needs.

RAID10 will be faster than RAID1. The key factor to a high performance
database is a high performance I/O system. If you look in the archives
you'll find people running postgresql on 30 and 40 drive arrays.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Tom Lane wrote:

> Cosimo writes:
>
>>1) What kind of performance gain can I expect switching from
>> 7.1 to 7.4 (or 8.0)? Obviously I'm doing my own testing,
>> but I'm not very impressed by 8.0 speed, may be I'm doing
>> testing on a low end server...
>
> Most people report a noticeable speedup in each new release
> [...]
> I'm surprised that you're not seeing any gain at all.
> What was your test case exactly? Have you perhaps tuned your app
> so specifically to 7.1 that you need to detune it?

We tend to use the lowest common SQL features that will allow
us to work with any db, so probably the problem is the opposite,
there is no pg-specific overtuning.

Also, the real pg load, that should be my ideal test case,
is somewhat difficult to reproduce (~ 50 users with handhelds
and browser clients).

Another good test is a particular procedure that opens
several (~1000) subsequent transactions, composed of many
repeated selection queries with massive write loads on 6/7
different tables, as big as 300/400k tuples.
Every transaction ends with either commit or rollback state

Indexing here should be ok, for I've analyzed every single query
also under database "stress".

Probably one big issue is that I need to vacuum/reindex too often
to keep db performances at a good(tm) level. I realize that this
has been addressed in several ways with newer PGs.

However, I need to do a lot of application and performance
tests and do them more seriously. Then I'll report the results here.

--
Cosimo

Jim C. Nasby wrote:

> On Mon, Jan 31, 2005 at 09:41:32PM +0100, Cosimo wrote:
>
> >2) The goal is to make the db handle 100 tps (something like
> > 100 users). What kind of server and storage should I provide?
>
> You might look at Opteron's, which theoretically have a higher data
> bandwidth. If you're doing anything data intensive, like a sort in
> memory, this could make a difference.

Would Opteron systems need 64-bit postgresql (and os, gcc, ...)
build to have that advantage?

> >4) Is it correct to suppose that multiple RAID 1 arrays
> > can provide the fastest I/O ?
> > I usually reserve one RAID1 array to db data directory,
> > one RAID1 array to pg_xlog directory and one RAID1 array
> > for os and application needs.
>
> RAID10 will be faster than RAID1.

Sorry Jim, by RAID10 you mean several raid1 arrays mounted on
different linux partitions? Or several raid1 arrays that
build up a raid0 array? In the latter case, who decides which
data goes in which raid1 array? Raid Adapter?

> The key factor to a high performance database is a high
> performance I/O system. If you look in the archives
> you'll find people running postgresql on 30 and 40
> drive arrays.

I'll do a search, thank you.

--
Cosimo

On Tue, Feb 01, 2005 at 07:35:35AM +0100, Cosimo Streppone wrote:
> >You might look at Opteron's, which theoretically have a higher data
> >bandwidth. If you're doing anything data intensive, like a sort in
> >memory, this could make a difference.
>
> Would Opteron systems need 64-bit postgresql (and os, gcc, ...)
> build to have that advantage?

Well, that would give you the most benefit, but the memory bandwidth is
still greater than on a Xeon. There's really no issue with 64 bit if
you're using open source software; it all compiles for 64 bits and
you're good to go. http://stats.distributed.net runs on a dual opteron
box running FreeBSD and I've had no issues.

> >RAID10 will be faster than RAID1.
>
> Sorry Jim, by RAID10 you mean several raid1 arrays mounted on
> different linux partitions? Or several raid1 arrays that
> build up a raid0 array? In the latter case, who decides which
> data goes in which raid1 array? Raid Adapter?

You should take a look around online for a description of raid types.

There's technically RAID0+1 and RAID1+0; one is a stripe of mirrored
drives (a RAID 0 built out of RAID 1s), the other is a mirror of two
RAID 0s. The former is much better; if you're lucky you can lose half
your drives without any data loss (if each dead drive is part of a
different mirror). Recovery is also faster.

You'll almost certainly be much happier with hardware raid instead of
software raid. stats.distributed.net runs a 3ware controller and SATA
drives.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

To be honest I've used compaq, dell and LSI SCSI RAID controllers and
got pretty pathetic benchmarks from all of them. The best system I
have is the one I just built:

2xOpteron 242, Tyan S2885 MoBo, 4GB Ram, 14xSATA WD Raptor drives:
2xRaid 1, 1x4 disk Raid 10, 1x6 drive Raid 10. 2x3ware (now AMCC)
Escalade 9500S-8MI.

This system with fsync on has managed 2500 insert transactions/sec
(granted they are simple transactions, but still).

RAID 10 is a stripe of mirrors. RAID 10 give you the best read and
write performance combined. RAID 5 gives very bad write perfomance,
but good read performance. With RAID 5 you can only loose a single
drive and rebuild times are slow. RAID 10 can loose up to have the
array depending on which drives without loosing data.

I would be interested in starting a site listing RAID benchmarks under
linux. If anyone is interested let me know. I would be interested in
at least some bonnie++ benchmarks, and perhaps other if people would
like.

Alex Turner
NetEconomist

On Tue, 1 Feb 2005 05:27:27 -0600, Jim C. Nasby <decibel(at)decibel(dot)org> wrote:
> On Tue, Feb 01, 2005 at 07:35:35AM +0100, Cosimo Streppone wrote:
> > >You might look at Opteron's, which theoretically have a higher data
> > >bandwidth. If you're doing anything data intensive, like a sort in
> > >memory, this could make a difference.
> >
> > Would Opteron systems need 64-bit postgresql (and os, gcc, ...)
> > build to have that advantage?
>
> Well, that would give you the most benefit, but the memory bandwidth is
> still greater than on a Xeon. There's really no issue with 64 bit if
> you're using open source software; it all compiles for 64 bits and
> you're good to go. http://stats.distributed.net runs on a dual opteron
> box running FreeBSD and I've had no issues.
>
> > >RAID10 will be faster than RAID1.
> >
> > Sorry Jim, by RAID10 you mean several raid1 arrays mounted on
> > different linux partitions? Or several raid1 arrays that
> > build up a raid0 array? In the latter case, who decides which
> > data goes in which raid1 array? Raid Adapter?
>
> You should take a look around online for a description of raid types.
>
> There's technically RAID0+1 and RAID1+0; one is a stripe of mirrored
> drives (a RAID 0 built out of RAID 1s), the other is a mirror of two
> RAID 0s. The former is much better; if you're lucky you can lose half
> your drives without any data loss (if each dead drive is part of a
> different mirror). Recovery is also faster.
>
> You'll almost certainly be much happier with hardware raid instead of
> software raid. stats.distributed.net runs a 3ware controller and SATA
> drives.
> --
> Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

Alex Turner wrote:

> To be honest I've used compaq, dell and LSI SCSI RAID controllers and
> got pretty pathetic benchmarks from all of them.

I also have seen average-low results for LSI (at least the 1020 card).

> 2xOpteron 242, Tyan S2885 MoBo, 4GB Ram, 14xSATA WD Raptor drives:
> 2xRaid 1, 1x4 disk Raid 10, 1x6 drive Raid 10. 2x3ware (now AMCC)
> Escalade 9500S-8MI.

Thanks, this is precious information.

> I would be interested in starting a site listing RAID benchmarks under
> linux. If anyone is interested let me know. I would be interested in
> at least some bonnie++ benchmarks, and perhaps other if people would
> like.

I have used also tiobench [http://tiobench.sourceforge.net/]
Any experience with it?

--
Cosimo

Jim C. Nasby wrote:
> On Tue, Feb 01, 2005 at 07:35:35AM +0100, Cosimo Streppone wrote:
>
>>>You might look at Opteron's, which theoretically have a higher data
>>>bandwidth. If you're doing anything data intensive, like a sort in
>>>memory, this could make a difference.
>>
>>Would Opteron systems need 64-bit postgresql (and os, gcc, ...)
>>build to have that advantage?
>
>
> Well, that would give you the most benefit, but the memory bandwidth is
> still greater than on a Xeon. There's really no issue with 64 bit if
> you're using open source software; it all compiles for 64 bits and
> you're good to go. http://stats.distributed.net runs on a dual opteron
> box running FreeBSD and I've had no issues.

You can get 64-bit Xeons also but it takes hit in the I/O department due
to the lack of a hardware I/O MMU which limits DMA transfers to
addresses below 4GB. This has a two-fold impact:

1) transfering data to >4GB require first a transfer to <4GB and then a
copy to the final destination.

2) You must allocate real memory 2X the address space of the devices to
act as bounce buffers. This is especially problematic for workstations
because if you put a 512MB Nvidia card in your computer for graphics
work -- you've just lost 1GB of memory. (I dunno how much the typical
SCSI/NIC/etc take up.)

None - but I'll definately take a look..

Alex Turner
NetEconomist

On Tue, 01 Feb 2005 22:11:30 +0100, Cosimo Streppone
<cosimo(at)streppone(dot)it> wrote:
> Alex Turner wrote:
>
> > To be honest I've used compaq, dell and LSI SCSI RAID controllers and
> > got pretty pathetic benchmarks from all of them.
>
> I also have seen average-low results for LSI (at least the 1020 card).
>
> > 2xOpteron 242, Tyan S2885 MoBo, 4GB Ram, 14xSATA WD Raptor drives:
> > 2xRaid 1, 1x4 disk Raid 10, 1x6 drive Raid 10. 2x3ware (now AMCC)
> > Escalade 9500S-8MI.
>
> Thanks, this is precious information.
>
> > I would be interested in starting a site listing RAID benchmarks under
> > linux. If anyone is interested let me know. I would be interested in
> > at least some bonnie++ benchmarks, and perhaps other if people would
> > like.
>
> I have used also tiobench [http://tiobench.sourceforge.net/]
> Any experience with it?
>
> --
> Cosimo
>
>

William Yu wrote:
> > Well, that would give you the most benefit, but the memory bandwidth is
> > still greater than on a Xeon. There's really no issue with 64 bit if
> > you're using open source software; it all compiles for 64 bits and
> > you're good to go. http://stats.distributed.net runs on a dual opteron
> > box running FreeBSD and I've had no issues.
>
> You can get 64-bit Xeons also but it takes hit in the I/O department due
> to the lack of a hardware I/O MMU which limits DMA transfers to
> addresses below 4GB. This has a two-fold impact:
>
> 1) transfering data to >4GB require first a transfer to <4GB and then a
> copy to the final destination.
>
> 2) You must allocate real memory 2X the address space of the devices to
> act as bounce buffers. This is especially problematic for workstations
> because if you put a 512MB Nvidia card in your computer for graphics
> work -- you've just lost 1GB of memory. (I dunno how much the typical
> SCSI/NIC/etc take up.)

I thought Intel was copying AMD's 64-bit API. Is Intel's
implementation as poor as you description? Does Intel have any better
64-bit offering other than the Itanium/Itanic?

--
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

>>You can get 64-bit Xeons also but it takes hit in the I/O department due
>>to the lack of a hardware I/O MMU which limits DMA transfers to
>>addresses below 4GB. This has a two-fold impact:
>>
>>1) transfering data to >4GB require first a transfer to <4GB and then a
>>copy to the final destination.
>>
>>2) You must allocate real memory 2X the address space of the devices to
>>act as bounce buffers. This is especially problematic for workstations
>>because if you put a 512MB Nvidia card in your computer for graphics
>>work -- you've just lost 1GB of memory. (I dunno how much the typical
>>SCSI/NIC/etc take up.)
>
>
> I thought Intel was copying AMD's 64-bit API. Is Intel's
> implementation as poor as you description? Does Intel have any better
> 64-bit offering other than the Itanium/Itanic?

Unfortunately, there's no easy way for Intel to have implemented a
64-bit IOMMU under their current restrictions. The memory controller
resides on the chipset and to upgrade the functionality significantly,
it would probably require changing the bus protocol. It's not that they
couldn't do it -- it would just require all Intel chipset/MB
vendors/partners to go through the process of creating & validating
totally new products. A way lengthier process than just producing 64-bit
CPUs that drop into current motherboards.

William Yu wrote:
> >>You can get 64-bit Xeons also but it takes hit in the I/O department due
> >>to the lack of a hardware I/O MMU which limits DMA transfers to
> >>addresses below 4GB. This has a two-fold impact:
> >>
> >>1) transfering data to >4GB require first a transfer to <4GB and then a
> >>copy to the final destination.
> >>
> >>2) You must allocate real memory 2X the address space of the devices to
> >>act as bounce buffers. This is especially problematic for workstations
> >>because if you put a 512MB Nvidia card in your computer for graphics
> >>work -- you've just lost 1GB of memory. (I dunno how much the typical
> >>SCSI/NIC/etc take up.)

When you say "allocate real memory 2X" are you saying that if you have
16GB of RAM only 8GB is actually usable and the other 8GB is for
bounce buffers, or is it just address space being used up?

--
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

Bruce Momjian wrote:
> William Yu wrote:
>
>>>>You can get 64-bit Xeons also but it takes hit in the I/O department due
>>>>to the lack of a hardware I/O MMU which limits DMA transfers to
>>>>addresses below 4GB. This has a two-fold impact:
>>>>
>>>>1) transfering data to >4GB require first a transfer to <4GB and then a
>>>>copy to the final destination.
>>>>
>>>>2) You must allocate real memory 2X the address space of the devices to
>>>>act as bounce buffers. This is especially problematic for workstations
>>>>because if you put a 512MB Nvidia card in your computer for graphics
>>>>work -- you've just lost 1GB of memory. (I dunno how much the typical
>>>>SCSI/NIC/etc take up.)
>
>
> When you say "allocate real memory 2X" are you saying that if you have
> 16GB of RAM only 8GB is actually usable and the other 8GB is for
> bounce buffers, or is it just address space being used up?
>

It's 2x the memory space of the devices. E.g. a Nvidia Graphics card w/
512MB of RAM would require 1GB of memory to act as bounce buffers. And
it has to be real chunks of memory in 64-bit mode since DMA transfer
must drop it into real memory in order to then be copied to > 4GB.

pgman(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian) wrote:
> William Yu wrote:
>> > Well, that would give you the most benefit, but the memory
>> > bandwidth is still greater than on a Xeon. There's really no
>> > issue with 64 bit if you're using open source software; it all
>> > compiles for 64 bits and you're good to
>> > go. http://stats.distributed.net runs on a dual opteron box
>> > running FreeBSD and I've had no issues.
>>
>> You can get 64-bit Xeons also but it takes hit in the I/O
>> department due to the lack of a hardware I/O MMU which limits DMA
>> transfers to addresses below 4GB. This has a two-fold impact:
>>
>> 1) transfering data to >4GB require first a transfer to <4GB and
>> then a copy to the final destination.
>>
>> 2) You must allocate real memory 2X the address space of the
>> devices to act as bounce buffers. This is especially problematic
>> for workstations because if you put a 512MB Nvidia card in your
>> computer for graphics work -- you've just lost 1GB of memory. (I
>> dunno how much the typical SCSI/NIC/etc take up.)
>
> I thought Intel was copying AMD's 64-bit API. Is Intel's
> implementation as poor as you description? Does Intel have any better
> 64-bit offering other than the Itanium/Itanic?

From what I can see, the resulting "copy of AMD64" amounts to little
more than rushing together a project to glue a bag on the side of a
Xeon chip with some 64 bit parts in it.

I see no reason to expect what is only billed as an "extension
technology" <http://www.eweek.com/article2/0,1759,1545734,00.asp> to
alleviate the deeply rooted memory bandwidth problems seen on Xeon.
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/advocacy.html
Q: What does the function NULL do?
A: The function NULL tests whether or not its argument is NIL or not. If
its argument is NIL the value of NULL is NIL.
-- Ken Tracton, Programmer's Guide to Lisp, page 73.