Re: Final background writer cleanup for 8.3

>>> On Sun, Aug 26, 2007 at  4:16 PM, in message
<Pine.GSO.4.64.0708261637030.3811@westnet.com>, Greg Smith
<gsmith@gregsmith.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