Tuning for PostGreSQL Database

Paul Pathiakis paul at pathiakis.com
Sat Jul 19 15:13:15 PDT 2003


	I'm trying to setup a dedicated PostGreSQL Database server.  First off, don't 
get me going on the inadequacies of the hardware setup,  I am a sysadmin with 
a partial clue.  :-) (The hardware was bought without any input from me for 
demo purposes.)  It's an Intel board with twin 2.8 GHZ processors and Promise 
ATA Fastrack RAID built-in, 1 GB of RAM.   Since this is just a 
throughput/response test against SuSe Linux Enterprise Server, Windows XP and 
FreeBSD, I'm going to break the mirror and try to do some intelligence about 
at least trying to log to a different drive.  Anyhow, there are 2-3 drives in 
this box, no scsi, nothing.  I want it to perform like no tomorrow on 
FreeBSD, not being a DB admin, I'm going to try to cover all the bases on 
this, so please let me know if I've missed something:

	First, this is a demo, I'm not worried about redundancy at this point.

	FreeBSD 4.8 or 5.1?

	OS configuration, any suggestions on SWAP layout, how much swap, both drives?

	Are there optimal parameters for sizing on the disk throughput?  That is, 
newfs parameters, i-node configuration, tunefs parameters, async on the 
drives, etc?  (consider nothing trivial, please feel free to just lay into 
this like I'm a newbie - considering my not-so-extensive experience with 

	Should I keep softupdates on or off?  This is a db and I am constrained by 
data integrity issues, however, I'm looking for as much speed as possible 
given the limited setup.

	what is the best layout of OS, PostgreSQL, and PostgreSQL logging on a 2 disk 
configuration?  3 disk configuration?

	Is there anything that I should put in the following files for tuning:

	systrcl.conf  (semaphore and shared memory configurations, etc?)

	loader.conf (hw.ata.atapi_dma on or off?  how do I make the hardware scream 
as fast as possible at tuning level and then with considerations to running 
PostgreSQL?  Give as much too me as you can.  I'm the sysadmin and I love how 
easily supportable FreeBSD is with regards to software, package, OS, 
maintenance.  People are vying for XP and Suse.  However, I'll end up 
supporting this and I know how easy FreeBSD is to support.

	If there's additional reading, let me know.  I'm more than happy to follow 
links, read more about PostgreSQL, etc.

	Thank you all!

	Paul Pathiakis


More information about the freebsd-performance mailing list