pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Montgomery <billm@lulu.com>
To: Rory Campbell-Lange <rory@campbell-lange.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help specifying new machine
Date: Mon, 09 Aug 2004 16:08:40 -0400
Message-ID: <4117D9C8.5030207@lulu.com> (raw)
In-Reply-To: <20040809165657.GB15169@campbell-lange.net>
References: <20040809165657.GB15169@campbell-lange.net>

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



view thread (10+ messages)  latest in thread

Message-ID: <4117D9C8.5030207@lulu.com>
Permalink:  ../4117D9C8.5030207@lulu.com/
Also on:    postgresql.org/message-id/4117D9C8.5030207@lulu.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: billm@lulu.com, rory@campbell-lange.net
  Subject: Re: Help specifying new machine
  In-Reply-To: <4117D9C8.5030207@lulu.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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