Re: Overhauling GUCS

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [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,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.