From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Ants Aasma <ants(dot)aasma(at)eesti(dot)ee>, Greg Smith <greg(at)2ndquadrant(dot)com>, simon(at)2ndquadrant(dot)com, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Initial 9.2 pgbench write results |
Date: | 2012-03-06 21:35:13 |
Message-ID: | CAMkU=1z4RMLq3=RpZgqX5dfMoscBHDMwqX7jQF1Xt2h5iMQg3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Feb 28, 2012 at 9:49 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Feb 28, 2012 at 11:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> This is an interesting hypothesis which I think we can test. I'm
>> thinking of writing a quick patch (just for testing, not for commit)
>> to set a new buffer flag BM_BGWRITER_CLEANED to every buffer the
>> background writer cleans. Then we can keep a count of how often such
>> buffers are dirtied before they're evicted, vs. how often they're
>> evicted before they're dirtied. If any significant percentage of them
>> are redirtied before they're evicted, that would confirm this
>> hypothesis. At any rate I think the numbers would be interesting to
>> see.
>
> Patch attached.
>
...
> That doesn't look bad at all. Then I reset the stats, tried it again,
> and got this:
>
> LOG: bgwriter_clean: 3863 evict-before-dirty, 198 dirty-before-evict
> LOG: bgwriter_clean: 3861 evict-before-dirty, 199 dirty-before-evict
> LOG: bgwriter_clean: 3978 evict-before-dirty, 218 dirty-before-evict
> LOG: bgwriter_clean: 3928 evict-before-dirty, 204 dirty-before-evict
> LOG: bgwriter_clean: 3956 evict-before-dirty, 207 dirty-before-evict
> LOG: bgwriter_clean: 3906 evict-before-dirty, 222 dirty-before-evict
> LOG: bgwriter_clean: 3912 evict-before-dirty, 197 dirty-before-evict
> LOG: bgwriter_clean: 3853 evict-before-dirty, 200 dirty-before-evict
>
> OK, that's not so good, but I don't know why it's different.
I don't think reseting the stats has anything to do with it, it is
just that the shared_buffers warmed up over time.
On my testing, this dirty-before-evict is because the bgwriter is
riding too far ahead of the clock sweep, because of
scan_whole_pool_milliseconds. Because it is far ahead, that leaves a
lot of run between the two pointers for re-dirtying cache hits to
land.
Not only is 2 minutes likely to be too small of a value for large
shared_buffers, but min_scan_buffers doesn't live up to its name. It
is not the minimum buffers to scan, it is the minimum to find/make
reusable. If lots of buffers have a nonzero usagecount (and if your
data doesn't fix in shared_buffers, it is hard to see how more than
half of the buffers can have zero usagecount) or are pinned, you are
scanning a lot more than min_scan_buffers.
If I disable that, then the bgwriter remains "just in time", just
slightly ahead of the clock-sweep, and the dirty-before-evict drops a
lot.
If scan_whole_pool_milliseconds is to be used at all, it seems like it
should not be less than checkpoint_timeout. If I don't want
checkpoints trashing my IO, why would I want someone else to do it
instead?
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2012-03-06 21:40:51 | Re: foreign key locks, 2nd attempt |
Previous Message | Simon Riggs | 2012-03-06 21:33:13 | Re: foreign key locks, 2nd attempt |