pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help specifying new machine
10+ messages / 7 participants
[nested] [flat]

* Help specifying new machine
@ 2004-08-09 16:56 Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-09 20:08 ` Re: Help specifying new machine Bill Montgomery <billm@lulu.com>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Rory Campbell-Lange @ 2004-08-09 16:56 UTC (permalink / raw)
  To: pgsql-performance

I'm thinking of upgrading an existing dedicated server and colocating my
own server.

The server is used for prototype systems running Postgresql, php and
apache. The largest database is presently under 10GB and I haven't had
any major performance problems. We expect to have to host larger data
sets in the next year and anticipate one or two databases reaching the
30GB mark in size. We write a lot of our webapps functionality using
pl/pgsql.

The present server is a 2GHz Pentium 4/512 KB cache with 2
software-raided ide disks (Maxtors) and 1GB of RAM.


I have been offered the following 1U server which I can just about
afford:

1U server
Intel Xeon 2.8GHz 512K cache                      1
512MB PC2100 DDR ECC Registered                   2
80Gb SATA HDD                                     4
4 port SATA card, 3 ware 8506-4                   1
3 year next-day hardware warranty                 1

There is an option for dual CPUs.

I intend to install the system (Debian testing) on the first disk and
run the other 3 disks under RAID5 and ext3.

I'm fairly ignorant about the issues relating to SATA vs SCSI and what
the best sort of RAM is for ensuring good database performance. I don't
require anything spectacular, just good speedy general performance.

I imagine dedicating around 25% of RAM to Shared Memory and 2-4% for
Sort memory.

Comments and advice gratefully received.
Rory

-- 
Rory Campbell-Lange 
<rory@campbell-lange.net>
<www.campbell-lange.net>



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
@ 2004-08-09 20:08 ` Bill Montgomery <billm@lulu.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Bill Montgomery @ 2004-08-09 20:08 UTC (permalink / raw)
  To: Rory Campbell-Lange <rory@campbell-lange.net>; +Cc: pgsql-performance

Rory,

Rory Campbell-Lange wrote:

>I'm thinking of upgrading an existing dedicated server and colocating my
>own server.
>
>The server is used for prototype systems running Postgresql, php and
>apache. The largest database is presently under 10GB and I haven't had
>any major performance problems. We expect to have to host larger data
>sets in the next year and anticipate one or two databases reaching the
>30GB mark in size. We write a lot of our webapps functionality using
>pl/pgsql.
>
>The present server is a 2GHz Pentium 4/512 KB cache with 2
>software-raided ide disks (Maxtors) and 1GB of RAM.
>
>
>I have been offered the following 1U server which I can just about
>afford:
>
>1U server
>Intel Xeon 2.8GHz 512K cache                      1
>512MB PC2100 DDR ECC Registered                   2
>80Gb SATA HDD                                     4
>4 port SATA card, 3 ware 8506-4                   1
>3 year next-day hardware warranty                 1
>
>There is an option for dual CPUs.
>
>I intend to install the system (Debian testing) on the first disk and
>run the other 3 disks under RAID5 and ext3.
>  
>
If you are going to spend your money anywhere, spend it on your disk 
subsystem. More or less, depending on the application, the bottleneck in 
your database performance will be the number of random IO operations per 
second (IOPS) your disk subsystem can execute. If you've got the money, 
get the largest (i.e. most spindles) RAID 10 (striped and mirrored) you 
can buy. If your budget doesn't permit RAID 10, RAID 5 is probably your 
next best bet.

>I'm fairly ignorant about the issues relating to SATA vs SCSI and what
>the best sort of RAM is for ensuring good database performance. I don't
>require anything spectacular, just good speedy general performance.
>  
>
Be sure that if you go with SATA over SCSI, the disk firmware does not 
lie about fsync(). Most consumer grade IDE disks will report to the OS 
that data is written to disk while it is still in the drive's cache. I 
don't know much about SATA disks, but I suspect they behave the same 
way. The problem with this is that if there is data that PostgreSQL 
thinks is written safely to disk, but is really still in the drive 
cache, and you lose power at that instant, you can find yourself with an 
inconsistent set of data that cannot be recovered from. SCSI disks, 
AFAIK, will always be truthful about fsync(), and you will never end up 
with data that you *thought* was written to disk, but gets lost on power 
failure.

>I imagine dedicating around 25% of RAM to Shared Memory and 2-4% for
>Sort memory.
>  
>
That is probably too much RAM to allocate to shm. Start with 10000 
buffers, benchmark your app, and slowly work up from there. NOTE: This 
advice will likely change with the introduction of ARC (adaptive 
caching) in 8.0; for now what will happen is that a large read of an 
infrequently accessed index or table will blow away all your shared 
memory buffers, and you'll end up with 25% of your memory filled with 
useless data. Better to let the smarter filesystem cache handle the bulk 
of your caching needs.

>Comments and advice gratefully received.
>Rory
>
>  
>
Best Luck,

Bill Montgomery



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
@ 2004-08-12 22:12 ` William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  1 sibling, 1 reply; 10+ messages in thread

From: William Yu @ 2004-08-12 22:12 UTC (permalink / raw)
  To: pgsql-performance

Rory Campbell-Lange wrote:

> The present server is a 2GHz Pentium 4/512 KB cache with 2
> software-raided ide disks (Maxtors) and 1GB of RAM.
> 
> 
> I have been offered the following 1U server which I can just about
> afford:
> 
> 1U server
> Intel Xeon 2.8GHz 512K cache                      1
> 512MB PC2100 DDR ECC Registered                   2
> 80Gb SATA HDD                                     4
> 4 port SATA card, 3 ware 8506-4                   1
> 3 year next-day hardware warranty                 1

You're not getting much of a bump with this server. The CPU is 
incrementally faster -- in the absolutely best case scenario where your 
queries are 100% cpu-bound, that's about ~25%-30% faster.

If you could use that money instead to upgrade your current server, 
you'd get a much bigger impact. Go for more memory and scsi (raid 
controllers w/ battery-backed cache).



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
@ 2004-08-13 06:29   ` Raoul Buzziol <raoulus@libero.it>
  2004-08-15 20:17     ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Raoul Buzziol @ 2004-08-13 06:29 UTC (permalink / raw)
  To: pgsql-performance

William Yu wrote:

> Rory Campbell-Lange wrote:
> 
>> The present server is a 2GHz Pentium 4/512 KB cache with 2
>> software-raided ide disks (Maxtors) and 1GB of RAM.
>> 
>> 
>> I have been offered the following 1U server which I can just about
>> afford:
>> 
>> 1U server
>> Intel Xeon 2.8GHz 512K cache                      1
>> 512MB PC2100 DDR ECC Registered                   2
>> 80Gb SATA HDD                                     4
>> 4 port SATA card, 3 ware 8506-4                   1
>> 3 year next-day hardware warranty                 1
> 
> You're not getting much of a bump with this server. The CPU is
> incrementally faster -- in the absolutely best case scenario where your
> queries are 100% cpu-bound, that's about ~25%-30% faster.

What about using Dual Athlon MP instead of a Xeon? Would be much less expensive,
but have higher performance (I think).

> 
> If you could use that money instead to upgrade your current server,
> you'd get a much bigger impact. Go for more memory and scsi (raid
> controllers w/ battery-backed cache).




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
@ 2004-08-15 20:17     ` William Yu <wyu@talisys.com>
  2004-08-18 15:18       ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  0 siblings, 1 reply; 10+ messages in thread

From: William Yu @ 2004-08-15 20:17 UTC (permalink / raw)
  To: pgsql-performance

>>You're not getting much of a bump with this server. The CPU is
>>incrementally faster -- in the absolutely best case scenario where your
>>queries are 100% cpu-bound, that's about ~25%-30% faster.
> 
> 
> What about using Dual Athlon MP instead of a Xeon? Would be much less expensive,
> but have higher performance (I think).

You're not going to be able to get a Dual Athlon MP for the same price 
as a single Xeon. A few years back, this was the case because Xeon CPUs 
& MBs had a huge premium over Athlon. This is no longer true mainly 
because the number of people carrying Athlon MP motherboards has dropped 
down drastically. Go to pricewatch.com and do a search for 760MPX -- you 
get a mere 8 entries. Not surprisingly because who would not want to 
spend a few pennies more for a much superior Dual Opteron? The few 
sellers you see now just keep stuff in inventory for people who need 
replacement parts for emergencies and are willing to pay up the nose 
because it is an emergency.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-15 20:17     ` Re: Help specifying new machine William Yu <wyu@talisys.com>
@ 2004-08-18 15:18       ` Raoul Buzziol <raoulus@libero.it>
  2004-08-18 16:12         ` Re: Help specifying new machine Rod Taylor <pg@rbt.ca>
  2004-08-19 04:26         ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Raoul Buzziol @ 2004-08-18 15:18 UTC (permalink / raw)
  To: pgsql-performance

> You're not going to be able to get a Dual Athlon MP for the same price
> as a single Xeon. A few years back, this was the case because Xeon CPUs
> & MBs had a huge premium over Athlon. This is no longer true mainly
> because the number of people carrying Athlon MP motherboards has dropped
> down drastically. Go to pricewatch.com and do a search for 760MPX -- you
> get a mere 8 entries. Not surprisingly because who would not want to
> spend a few pennies more for a much superior Dual Opteron? The few
> sellers you see now just keep stuff in inventory for people who need
> replacement parts for emergencies and are willing to pay up the nose
> because it is an emergency.

I saw pricewatch.com and you're right. 

I looked for some benchmarks, and I would know if I'm right on:
- Dual Opteron 246 have aproximately the same performance of a Dual Xeon 3Gh
(Opteron a little better)
- Opteron system equal or cheeper than Xeon system.

As I'm not a hardware expert I would know if my impressions were right.

Thanx, Raoul



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-15 20:17     ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-18 15:18       ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
@ 2004-08-18 16:12         ` Rod Taylor <pg@rbt.ca>
  1 sibling, 0 replies; 10+ messages in thread

From: Rod Taylor @ 2004-08-18 16:12 UTC (permalink / raw)
  To: Raoul Buzziol <raoulus@libero.it>; +Cc: pgsql-performance

On Wed, 2004-08-18 at 11:18, Raoul Buzziol wrote:
> > You're not going to be able to get a Dual Athlon MP for the same price
> > as a single Xeon. A few years back, this was the case because Xeon CPUs
> > & MBs had a huge premium over Athlon. This is no longer true mainly
> > because the number of people carrying Athlon MP motherboards has dropped
> > down drastically. Go to pricewatch.com and do a search for 760MPX -- you
> > get a mere 8 entries. Not surprisingly because who would not want to
> > spend a few pennies more for a much superior Dual Opteron? The few
> > sellers you see now just keep stuff in inventory for people who need
> > replacement parts for emergencies and are willing to pay up the nose
> > because it is an emergency.
> 
> I saw pricewatch.com and you're right. 
> 
> I looked for some benchmarks, and I would know if I'm right on:
> - Dual Opteron 246 have aproximately the same performance of a Dual Xeon 3Gh
> (Opteron a little better)
> - Opteron system equal or cheeper than Xeon system.

For PostgreSQL, Opteron might be a touch worse than Xeon for single
processor, little better for Dual, and a whole heck of a bunch better
for Quads -- but this depends on your specific work load as memory
bound, cpu bound, lots of float math, etc. work loads will perform
differently.

In general, an Opteron is a better bet simply because you can shove more
ram onto it (without workarounds), and you can't beat an extra 8GB ram
on an IO bound database (consider your datasize in 1 year).





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-15 20:17     ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-18 15:18       ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
@ 2004-08-19 04:26         ` William Yu <wyu@talisys.com>
  2004-08-19 05:49           ` Re: Help specifying new machine Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: William Yu @ 2004-08-19 04:26 UTC (permalink / raw)
  To: pgsql-performance

Raoul Buzziol wrote:
> I looked for some benchmarks, and I would know if I'm right on:
> - Dual Opteron 246 have aproximately the same performance of a Dual Xeon 3Gh
> (Opteron a little better)
> - Opteron system equal or cheeper than Xeon system.

In terms of general database performance, top of the line dual opteron 
will perform roughly the same as top of the line dual xeon. Assuming you 
just run in 32-bit mode. Throw in 64-bit mode, NUMA, etc, all bets are off.

In terms of Postgres database performance, Opteron *may* be the better 
CPU for this app but there's not enough data points yet. Here's a recent 
review at Anandtech showing Opteron 150 (2.4ghz) versus 64-bit Prescott 
3.6hgz with some very simple MySQL and Postgres benchmarks:

http://www.anandtech.com/linux/showdoc.aspx?i=2163&p=2

What is a slight lead in MySQL becomes a blowout in Postgres. Of course, 
this is just the 1MB cache model. I'm sure if you went with the 2MB or 
4MB models, the Xeons would come up much closer.

The really good sign in the above numbers though is that somebody 
finally included Postgres in their benchmark suite. :) We may be seeing 
more and more data points to evaluate hardware for Postgres in the near 
future.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-15 20:17     ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-18 15:18       ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-19 04:26         ` Re: Help specifying new machine William Yu <wyu@talisys.com>
@ 2004-08-19 05:49           ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-08-19 16:18             ` Re: Help specifying new machine Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2004-08-19 05:49 UTC (permalink / raw)
  To: William Yu <wyu@talisys.com>; +Cc: pgsql-performance

William Yu <wyu@talisys.com> writes:
> In terms of Postgres database performance, Opteron *may* be the better 
> CPU for this app but there's not enough data points yet. Here's a recent 
> review at Anandtech showing Opteron 150 (2.4ghz) versus 64-bit Prescott 
> 3.6hgz with some very simple MySQL and Postgres benchmarks:

> http://www.anandtech.com/linux/showdoc.aspx?i=2163&p=2

> What is a slight lead in MySQL becomes a blowout in Postgres.

This is really interesting.  We had previously seen some evidence that
the Xeon sucks at running Postgres, but I thought that the issues only
materialized with multiple CPUs (because what we were concerned about
was the cost of transferring cache lines across CPUs).  AFAICS this test
is using single CPUs.  Maybe there is more going on than we realized.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Help specifying new machine
  2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
  2004-08-12 22:12 ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-13 06:29   ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-15 20:17     ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-18 15:18       ` Re: Help specifying new machine Raoul Buzziol <raoulus@libero.it>
  2004-08-19 04:26         ` Re: Help specifying new machine William Yu <wyu@talisys.com>
  2004-08-19 05:49           ` Re: Help specifying new machine Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-19 16:18             ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Josh Berkus @ 2004-08-19 16:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; William Yu <wyu@talisys.com>; +Cc: pgsql-performance

Tom,

> This is really interesting.  We had previously seen some evidence that
> the Xeon sucks at running Postgres, but I thought that the issues only
> materialized with multiple CPUs (because what we were concerned about
> was the cost of transferring cache lines across CPUs).  AFAICS this test
> is using single CPUs.  Maybe there is more going on than we realized.

Aside from the fact that the Xeon architecture is a kludge?   

Intel really screwed up the whole Xeon line through some bad architectural 
decisions, and instead of starting over from scratch, "patched" the problem.  
The result has been sub-optimal Xeon performance for the last two years.

This is why AMD is still alive.   Better efficiency, lower cost.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2004-08-19 16:18 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-09 16:56 Help specifying new machine Rory Campbell-Lange <rory@campbell-lange.net>
2004-08-09 20:08 ` Bill Montgomery <billm@lulu.com>
2004-08-12 22:12 ` William Yu <wyu@talisys.com>
2004-08-13 06:29   ` Raoul Buzziol <raoulus@libero.it>
2004-08-15 20:17     ` William Yu <wyu@talisys.com>
2004-08-18 15:18       ` Raoul Buzziol <raoulus@libero.it>
2004-08-18 16:12         ` Rod Taylor <pg@rbt.ca>
2004-08-19 04:26         ` William Yu <wyu@talisys.com>
2004-08-19 05:49           ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-19 16:18             ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox