High end server and storage for a PostgreSQL OLTP system

From: Cosimo Streppone <cosimo(at)streppone(dot)it>
To: Postgresql Performance list <pgsql-performance(at)postgresql(dot)org>
Subject: High end server and storage for a PostgreSQL OLTP system
Date: 2005-01-31 20:41:32
Message-ID: 41FE97FC.3060106@streppone.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Trevor Ball 2005-01-31 21:14:39 Index Slowing Insert >50x
Previous Message Tom Lane 2005-01-31 20:26:12 Re: Automagic tuning