>>> On Sun, Aug 26, 2007 at 4:16 PM, in message <[EMAIL PROTECTED]>, Greg Smith <[EMAIL PROTECTED]> wrote: > On Sun, 26 Aug 2007, Kevin Grittner wrote: > >> usagecount | count | isdirty >> ------------+-------+--------- >> 0 | 9093 | f >> 1 | 6702 | f >> 2 | 2267 | f >> 3 | 602 | f >> 4 | 428 | f >> 5 | 1388 | f > > Notice that you have a much larger number of buffers where the usage count > is 4 or 5. The all-scan part of the 8.2 background writer will waste a > lot of writes when you have a profile that's more like this. If there > have been 4+ client backends touching the buffer recently, you'd be crazy > to write it out right now if you could instead be focusing on banging out > the ones where the usage count is 0. Seems to me I'd be crazy to be writing out anything. Nothing's dirty. In fact, I ran a simple query to count dirty pages once per second for a minute and had three sample show any pages dirty. The highest count was 5. Again, this was Sunday afternoon, which is not traditionally a busy time for the courts. I'll try to get some more meaningful numbers tomorrow.
> One of the systems I was tormented by had the > usagecount of 5 for >20% of the buffers in the cache under heavy load, and > had a physical write been executed every time one of those was touched > that would have been crippling (even if the OS was smart to cache and > therefore make redundant some of the writes, which is behavior I would > prefer not to rely on). Why is that? > The tests that I > run (which I haven't been publishing yet but will be with the final patch > soon) also report worst-case and 90-th percentile latency numbers as well > as TPS. A "regression" that improved TPS at the expense of those two > would not be considered an improvement by anyone involved here. Have you been able to create a test case which exposes the write-spike problem under 8.2.4? By the way, the 90th percentile metric isn't one I'll care a lot about. In our environment any single instance of a "fast" query running slow is considered a problem, and my job is to keep those users happy. -Kevin ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster