This might be interesting: pgtune. It is supposed to help tune postgresql for the machine it is running on. I haven't tried it since I'm running Windows which uses EnterpriseDB Tuning-Wizard (which I've just installed and haven't actually tried yet).
I started looking up PostgreSQL tuning documentation. Here's what I have so far:
- Tuning Your PostgreSQL Server:
- shared_buffers - memory for PostgreSQL itself, depending on version the units are different.
- effective_cache_size - available system memory that the PostgreSQL query planner can use
- checkpoint_segments - data written to the database are done in WAL (write-ahead log) segements. Each WAL is 16MB and the default is 3 segments before writing. Writing is expensive, but storing more can lead to problems as well. Better to increase 30 to avoid the following log message: checkpoints are occurring too frequently but could not avoid if there are more insert/update than the configuration.
- checkpoint_completion_target - how much of the previous checkpoint data should be written before the next checkpoint is reached
- default_statistics_target - default is 10, recommend 100; the more stats, the better the optimizer
- work_mem - used for each sorting operation; losts of sorts = losts of memory so be careful
- wal_sync_method - advanced users only, operating system dependent (somewhat): a value of open_sync is the most common (default is no good)
- wal_buffers - a value of 1MB is good for large write-heavy systems
- constraint_exclusion - turn on if using table partitions, off otherwise
- random_page_cost - the cost of accessing a random page from the disk; default is 4, fast systems use 2-3, uber-fast (like Dave's) use 1
- autovacuum - automate the execution of VACUUM and ANALYZE commands.
- Tuning PostgreSQL for Performance - supplement to above:
- WAL files: by turning off fsync, you run the risk of loosing all "commited" data since none of it will actually be written to the database (stored in the WAL); however, the write speeds will go through the roof. If the amount of written data is large and you want to leave on the immediate sync, then move the WAL file over to another disk (RAID might do this automatically)
- Performance Turning PostgreSQL
- commit_delay and commit_siblings - tweak to enhance simultaneous commits
- Checkpoints and the Background Writer
Other interesting sites:
- PostgreSQL wiki
- GUCs: A Three Hour Tour - Grand Unified Configuration settings: 187 or 194 (conflicting numbers on the same site) parameters
There's a lot of interesting stuff out there; most of which is over my head. I'll add more stuff as I find it.