Re: Final background writer cleanup for 8.3

>>> On Sun, Aug 26, 2007 at 4:16 PM, in message
<Pine(dot)GSO(dot)4(dot)64(dot)0708261637030(dot)3811(at)westnet(dot)com>, Greg Smith
<gsmith(at)gregsmith(dot)com> 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