>>> On Tue, Aug 21, 2007 at 1:12 PM, in message <[EMAIL PROTECTED]>, Greg Smith <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
