Re: Overhauling GUCS

On Wed, 4 Jun 2008, Aidan Van Dyk wrote:

> * Are backends always writing out dirty buffers because there are no free
> ones? This might mean tweaking settings affecting bgwriter.

What you mean on the first one is "are backends always writing out dirty
buffers becuase there are no *clean* ones"; the server operates with no
*free* buffers as standard operations. Figuring that out is now easy in
8.3 with the pg_stat_bgwriter view.

> * Are the evicted buffers ones with really high usage counts? This
> might mean an increase shared buffers would help?

Evicted buffers must have a 0 usage count. The correct question to ask is
"are buffers never getting high usage counts because they keep getting
evicted too fast?". You can look at that in 8.3 using pg_buffercache,
I've got suggested queries as part of my buffer cache presentation at
http://www.westnet.com/~gsmith/content/postgresql/

> * Are we always spilling small amounts of data to disk for sorting? A
> a small work_mem increase might help...

I was just talking to someone today about building a monitoring tool for
this. Not having a clear way to recommend people monitor use of work_mem
and its brother spilled to disk sorts is an issue right now, I'll whack
that one myself if someone doesn't beat me to it before I get time.

> * 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?

This is hard to do with low overhead in an OS-independant way. The best
solution available now would use dtrace to try and nail it down. There's
movement in this area (systemtap for Linux, recent discussion at the PGCon
Developer Meeting of possibly needing more platform-specific code) but
it's not quite there yet.

So everything you mentioned is either recently added/documented or being
actively worked on somewhere, and the first two were things I worked on
myself after noticing they were missing. Believe me, I feel the items
that still aren't there, but they're moving along at their own pace.
There's already more tuning knowledge available than tools to help apply
that knowledge to other people's systems, which is why I think a diversion
to focus just on that part is so necessary.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD