From: Greg Smith <gsmith@gregsmith.com>
To: Aidan Van Dyk <aidan@highrise.ca>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Overhauling GUCS
Date: Wed, 4 Jun 2008 22:04:54 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0806042134090.3874@westnet.com> (raw)
In-Reply-To: <20080605011455.GF14498@yugib.highrise.ca>
References: <48409D1E.3070208@agliodbs.com>
<200806010032.53337.peter_e@gmx.net>
<Pine.GSO.4.64.0806020452220.26912@westnet.com>
<17932.1212415926@sss.pgh.pa.us>
<20080604131810.GH65238@decibel.org>
<4846A483.8000001@pse-consulting.de>
<Pine.GSO.4.64.0806041045520.2258@westnet.com>
<29525.1212613052@sss.pgh.pa.us>
<48472E47.7030701@dunslane.net>
<7473.1212625397@sss.pgh.pa.us>
<20080605011455.GF14498@yugib.highrise.ca>
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@gregsmith.com http://www.gregsmith.com Baltimore, MD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: gsmith@gregsmith.com, aidan@highrise.ca
Subject: Re: Overhauling GUCS
In-Reply-To: <Pine.GSO.4.64.0806042134090.3874@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox