* Tom Lane <[EMAIL PROTECTED]> [080604 20:46]: > If those aren't enough questions, what else must we ask? Or maybe they > aren't the right questions at all --- maybe we should ask "is this a > dedicated machine or not" and try to extrapolate everything else from > what we (hopefully) can find out about the hardware.
For these three things: 1) max connections/workmem 2) shared buffers/effective cache 3) bgwriter/checkpoint parameters What are the stats or measures that PostgreSQL produces (or that you want it too currently doesn't, but you would like it to produce) that the "masters" (i.e. people who tune PostgreSQL effectively, like you, Greg, Simon, Robert, Peter, Josh, Jim, etc - sorry if I missed others) actually use to decide whether to increase or decrease a value? I tune my postgresql.conf mainly on folklore, and "assimilated understanding" from reading the lists and blogs... But I haven't come across (or rather, haven't come across and remembered/bookmarked) anything that helps someone sample/read any stats or counts to find bottleneck points which to start tuning. Stuff like (remembering that I've never had to really work at tuning because PG has always been "fast enough" for my needs, so take this with a grain of salt) * Are backends always writing out dirty buffers because there are no free ones? This might mean tweaking settings affecting bgwriter. * Are the evicted buffers ones with really high usage counts? This might mean an increase shared buffers would help? * Are we always spilling small amounts of data to disk for sorting? A a small work_mem increase might help... * Are all our reads from disk really quick? This probably means OS pagecache has our whole DB, and means random_page_cost could be tweaked? If we could get a definitive list of things like this, or maybe just comprehensive, or even at least agreed-to-not-be-wrong things to look at, that would go a long way to documentation *how* to tune PG effectively, and could lead to any projects that want to tackle examining a running cluster and suggesting some config changes... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave.
signature.asc
Description: Digital signature