Re: hardware and For PostgreSQL

On 10/31/07, Ketema Harris <ketema@ketema.net> wrote:
> I am trying to build a very Robust DB server that will support 1000+
> concurrent users (all ready have seen max of 237 no pooling being
> used).  i have read so many articles now that I am just saturated.  I
> have a general idea but would like feedback from others.

Slow down, take a deep breath.  It's going to be ok.

You should definitely be looking at query pooling.  pgbouncer from
skype has gotten some good coverage lately.  I've used pgpool and
pgpool II with good luck myself.

> How does pg utilize multiple processors?  The more the better?
> Are queries spread across multiple processors?
> Is Pg 64 bit?
> If so what processors are recommended?

Generally more is better, up to a point.  PG runs one query per
processor max.  i.e. it doesn't spread a single query out over
multiple CPUs.

Yes, it's 64 bit, if you use a 64 bit version on a 64 bit OS.

Right now both Intel and AMD CPUs seem pretty good.

> RAM?  The more the merrier right?

That depends.  If 16 Gigs runs 33% faster than having 32 Gigs, the 16
Gigs will probably be better, especially if your data set fits in
16Gig.  But all things being equal, more memory = good.

> Understanding shmmax and the pg
> config file parameters for shared mem has to be adjusted to use it.

Don't forget all the other paramenters like work_mem and fsm settings.
 and regular vacuuming / autovacuuming

> Disks?  standard Raid rules right?  1 for safety 5 for best mix of
> performance and safety?

Neither of those is optimal for a transactional database.  5 isn't
particularly safe since two disks can kill your whole array.  RAID-10
is generally preferred, and RAID 50 or 6 can be a good choice.

> Any preference of SCSI over SATA? What about using a High speed
> (fibre channel) mass storage device?

What's most important is the quality of your controller.  A very high
quality SATA controller will beat a mediocre SCSI controller.  Write
back cache with battery backed cache is a must.  Escalade, Areca, LSI
and now apparently even Adaptec all have good controllers.  Hint:  If
it costs $85 or so, it's likely not a great choice for RAID.

I've seen many <$200 RAID controllers that were much better when you
turned off the RAID software and used kernel SW mode RAID instead
(witness Adaptec 14xx series)

Mass storage can be useful, especially if you need a lot of storage or
expansion ability.

> Who has built the biggest baddest Pg server out there and what do you
> use?

Not me, but we had a post from somebody with a very very very large
pgsql database on this list a few months ago...  Search the archives.