Re: Hardware performance - Mailing list pgsql-performance

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