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

High end server and storage for a PostgreSQL OLTP system

From
Cosimo Streppone
Date:
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


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

From
Tom Lane
Date:
Cosimo Streppone <cosimo@streppone.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

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

From
"Jim C. Nasby"
Date:
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@decibel.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?"

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

From
Cosimo Streppone
Date:
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


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

From
Cosimo Streppone
Date:
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


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

From
"Jim C. Nasby"
Date:
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@decibel.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?"

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

From
Alex Turner
Date:
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@decibel.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@decibel.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@postgresql.org
>

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

From
"Merlin Moncure"
Date:
> Hi all,
> 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...

8.0 gives you savepoints.  While this may not seem like a big deal at
first, the ability to handle exceptions inside pl/pgsql functions gives
you much more flexibility to move code into the server.  Also, recent
versions of pl/pgsql give you more flexibility with cursors, incuding
returning them outside of the function.
Corollary: use pl/pgsql.  It can be 10 times or more faster than query
by query editing.

You also have the parse/bind interface.  This may not be so easily to
implement in your app, but if you are machine gunning your server with
queries, use parameterized prepared queries and reap 50% + performance,
meaning lower load and quicker transaction turnaround time.


Merlin

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

From
Cosimo Streppone
Date:
Merlin Moncure wrote:

> Corollary: use pl/pgsql.  It can be 10 times or more faster than query
> by query editing.

Merlin, thanks for your good suggestions.

By now, our system has never used "stored procedures" approach,
due to the fact that we're staying on the minimum common SQL features
that are supported by most db engines.
I realize though that it would provide an heavy performance boost.

> You also have the parse/bind interface

This is something I have already engineered in our core classes
(that use DBI + DBD::Pg), so that switching to 8.0 should
automatically enable the "single-prepare, multiple-execute" behavior,
saving a lot of query planner processing, if I understand correctly.

--
Cosimo


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

From
Cosimo Streppone
Date:
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


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

From
William Yu
Date:
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.)

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

From
Alex Turner
Date:
None - but I'll definately take a look..

Alex Turner
NetEconomist


On Tue, 01 Feb 2005 22:11:30 +0100, Cosimo Streppone
<cosimo@streppone.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
>
>

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

From
Bruce Momjian
Date:
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@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

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

From
William Yu
Date:
>>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.

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

From
Bruce Momjian
Date:
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@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

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

From
"Merlin Moncure"
Date:
> By now, our system has never used "stored procedures" approach,
> due to the fact that we're staying on the minimum common SQL features
> that are supported by most db engines.
> I realize though that it would provide an heavy performance boost.

I feel your pain.   Well, sometimes you have to bite the bullet and do a
couple of implementation specific hacks in especially time sensitive
components.

> > You also have the parse/bind interface
>
> This is something I have already engineered in our core classes
> (that use DBI + DBD::Pg), so that switching to 8.0 should
> automatically enable the "single-prepare, multiple-execute" behavior,
> saving a lot of query planner processing, if I understand correctly.

Yes. You save the planning step (which adds up, even for trivial plans).
The 'ExexPrepared' variant of prepared statement execution also provides
substantial savings (on server cpu load and execution time) because the
statement does not have to be parsed. Oh, and network traffic is reduced
correspondingly.

I know that the perl people were pushing for certain features into the
libpq library (describing prepared statements, IIRC).  I think this
stuff made it into 8.0...have no clue about DBD::pg.

If everything is working the way it's supposed to, 8.0 should be faster
than 7.1 (like, twice faster) for what you are probably trying to do.
If it isn't, something else is wrong and it's very likely a solvable
problem.

In short, in pg 8.0, statement by statement query execution is highly
optimizeable at the driver level, much more so than 7.1.  Law of
Unintended Consequences aside, this will translate into direct benefits
into your app if it uses this application programming model.

Merlin

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

From
William Yu
Date:
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.

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

From
Cosimo Streppone
Date:
Merlin Moncure wrote:

> > [...]
 > > (...DBI + DBD::Pg), so that switching to 8.0 should
> > automatically enable the "single-prepare, multiple-execute" behavior,
> > saving a lot of query planner processing, if I understand correctly.
>
> [...]
>
> I know that the perl people were pushing for certain features into the
> libpq library (describing prepared statements, IIRC).  I think this
> stuff made it into 8.0...have no clue about DBD::pg.

For the record: yes, DBD::Pg in CVS (> 1.32) has support
for server prepared statements.

> If everything is working the way it's supposed to, 8.0 should be faster
> than 7.1 (like, twice faster) for what you are probably trying to do.

In the next days I will be testing the entire application with the
same database only changing the backend from 7.1 to 8.0, so this is
a somewhat perfect condition to have a "real-world" benchmark
of Pg 8.0 vs 7.1.x performances.

--
Cosimo


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

From
Christopher Browne
Date:
pgman@candle.pha.pa.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.

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

From
Cosimo Streppone
Date:
Cosimo Streppone wrote:

> Merlin Moncure wrote:
>
> > If everything is working the way it's supposed to, 8.0 should be faster
> > than 7.1 (like, twice faster) for what you are probably trying to do.
>
> In the next days I will be testing the entire application with the
> same database only changing the backend from 7.1 to 8.0, so this is
> a somewhat perfect condition to have a "real-world" benchmark
> of Pg 8.0 vs 7.1.x performances.

The "next days" have come. I did a complete migration to Pg 8.0.1
from 7.1.3. It was a *huge* jump.
The application is exactly the same, also the database structure
is the same. I only dumped the entire 7.1.3 db, changed the backend
version, and restored the data in the 8.0.1 db.

The performance level of Pg 8 is at least *five* times higher
(faster!) than 7.1.3 in "query-intensive" transactions,
which is absolutely astounding.

In my experience, Pg8 handles far better non-unique indexes
with low cardinality built on numeric and integer types, which
is very common in our application.

--
Cosimo


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

From
Tom Lane
Date:
Cosimo Streppone <cosimo@streppone.it> writes:
> The performance level of Pg 8 is at least *five* times higher
> (faster!) than 7.1.3 in "query-intensive" transactions,
> which is absolutely astounding.

Cool.

> In my experience, Pg8 handles far better non-unique indexes
> with low cardinality built on numeric and integer types, which
> is very common in our application.

Yes, we've fixed a number of places where the btree code was inefficient
with large numbers of equal keys.  I'm not sure that that explains a
5x speedup all by itself, though.

            regards, tom lane