Tuning for PostGreSQL Database

[Sean Chittenden]

> 	FreeBSD 4.8 or 5.1?

4.8 is currently faster than 5.1.

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

If this is a demo and you're looking for speed, SWAP isn't going to
buy you anything.  Have some swap that way idle bits can be paged out.
As for your drives, use two drives for data (striped) and one drive
for WAL logs.  Put swap wherever, it won't matter.

> 	Are there optimal parameters for sizing on the disk
> throughput?

Increase your file system cache.

> 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 PostgreSQL)

Read the /usr/local/pgsql/post-install-notes, for starters.

> 	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.

Softupdates, on.

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

See above.

> 	Is there anything that I should put in the following files for
> 	tuning:
> 
> 	systrcl.conf (semaphore and shared memory configurations,
> 	etc?)

See the archives, there are bunches of recommendations for this.  You
might also want to check the performance at PostgreSQL.org list's
archives too.

> 	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.

Use the most recent version of PostgreSQL, 7.4-devel (most recent CVS
snapshot if you can, it's in BETA and very stable, IMHO).  Once you
get your demo installed, read through:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
http://developer.postgresql.org/docs/postgres/performance-tips.html

A fair number of the PgSQL experts recommend using FreeBSD's,
actually:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#freebsd

Make liberal use of EXPLAIN ANALYZE [query], VACUUM ANALYZE, and
possibly CLUSTER.  If you have any probs once you have something up
and running, come back with details and we'll get your demo to fly if
it isn't already (should be after reading the above links though).

-sc

-- 
Sean Chittenden