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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
