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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to