pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Balazs Wellisch <balazs@neusolutions.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hardware performance
Date: Wed, 16 Jul 2003 21:52:13 -0700
Message-ID: <3F162B7D.1020106@joeconway.com> (raw)
In-Reply-To: <004b01c34c0f$256bfb80$0600a8c0@home01>
References: <004b01c34c0f$256bfb80$0600a8c0@home01>

Balazs Wellisch wrote:
> first of all I'd like to thank everyone who responded to my earlier
> post. I have a much better understanding of postgres performance
> tuning now. In case anyone's interested we've decided to go with RH9
> and PostgreSQL 7.3 and we'll do the OS and DB tuning ourselves.
> (should be a good learning experience)

Good choice! I think you'll find that this list will be a great resource 
as you learn. One point here is that you should use 7.3.3 (latest 
release version) instead of the version of Postgres in the distribution. 
Also, you might want to rebuild the RPMs from source using
"--target i686".

> We have the budget for 5 drives. Does anyone have any real world
> experience with what hard drive configuration works best for
> postgres? This is going to be a dedicated DB server. There are going
> to be a large number of transactions being written to the database.

To an extent it depends on how big the drives are and how large you 
expect the database to get. For maximal performance you want RAID 1+0 
for data and WAL; and you want OS, data, and WAL each on their own 
drives. So with 5 drives one possible configuration is:

1 drive OS: OS on it's own drive makes it easy to upgrade, or restore 
the OS from CD if needed
2 drives, RAID 1+0: WAL
2 drives, RAID 1+0: data

But I've seem reports that with fast I/O subsystems, there was no 
measurable difference with WAL separated from data. And to be honest, 
I've never personally found it necessary to separate WAL from data. You 
may want to test with WAL on the same volume as the data to see if there 
is enough difference to warrant separating it or not given your load and 
your actual hardware. If not, use 1 OS drive and 4 RAID 1+0 drives as 
one volume.

You never want find any significant use of hard disk based swap space -- 
if you see that, you are probably misconfigured, and performance will be 
poor no matter how you've set up the drives.

> And there will be some moderately complex queries run concurrently to
> present this information in the form of various reports on the web.

Once you have some data on your test server, and you have complex 
queries to tune, there will be a few details you'll get asked every time 
if you don't provide them when posting a question to the list:

1) Have you been running VACUUM and ANALYZE (or VACUUM ANALYZE) at
    appropriate intervals?
2) What are the table definitions and indexes for all tables involved?
3) What is the output of EXPLAIN ANALYZE?

HTH,

Joe





view thread (20+ messages)  latest in thread

Message-ID: <3F162B7D.1020106@joeconway.com>
Permalink:  ../3F162B7D.1020106@joeconway.com/
Also on:    postgresql.org/message-id/3F162B7D.1020106@joeconway.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mail@joeconway.com, balazs@neusolutions.com
  Subject: Re: Hardware performance
  In-Reply-To: <3F162B7D.1020106@joeconway.com>
  References: <004b01c34c0f$256bfb80$0600a8c0@home01> <3F162B7D.1020106@joeconway.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