>>> On Tue, Aug 21, 2007 at 1:12 PM, in message
<Pine(dot)GSO(dot)4(dot)64(dot)0708211316210(dot)16511(at)westnet(dot)com>, Greg Smith
<gsmith(at)gregsmith(dot)com> wrote:
> My suggestion for a starting 8.3 configuration for you would be adjusting
> these settings as follows:
>
> shared_buffers=1GB
> checkpoint_segments = 50
>
> And then try replaying your data with checkpoint_completion_target at 0.5
> (default), 0.7, and 0.9 and see how each of those works out for you (0.7
> is the least useful of those if you only did two). Hopefully the data
> replay you have can be setup to invoke the same tightly spaced checkpoint
> behavior you commented about.
Under 8.2.4, 8.2.5, 8.3beta4 and a recent compile of the CVS tip,
the test machine (4 2 GHz Xeons with 6 GB RAM) was unable to create
the clusters of timeouts which we saw on the production box (8 3GHz
Xeons with 12 GB RAM), in spite of concerted efforts to recreate the
conditions. It's possible that updates to PostgreSQL or the OS have
made the difference, although I think it's more likely that the
larger OS cache and/or the higher CPU power of the production box
are needed to create the problem on this type of disk array. We're
working on getting a couple new boxes on line, which will allow us
to test using the current production box, but that's weeks away.
Performance under 8.3 is better in general, and there is a very
noticeable smoothing of the output spikes at the OS level. I didn't
notice much difference in vmstat output with the different
checkpoint_completion_target settings.
Our testing methoodology involved playing transaction streams from
both our replication engine and our web site, at the same time,
against the test server, through the normal middle tier software we
use in production. We have timestamps on all of it, which allowed
us to play the streams at various percentages of the production
load. Based on the timeouts from the web renderers (which cancel
a request if the middle tier hasn't responded within 20 seconds),
the middle tier metrics of actual run time once the request is
pulled from the request queue, and the vmstat output, 8.3 shows
significant performance improvement as long as the fix for the
OUTER JOIN optimizer regression is used (that is, with a recent
checkout from the CVS tip, or beta/RC with Tom's patch applied).
-Kevin