Re: Initial 9.2 pgbench write results

On Sat, Feb 18, 2012 at 3:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Sat, Feb 18, 2012 at 7:35 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Feb 14, 2012 at 3:25 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
>>> On 02/14/2012 01:45 PM, Greg Smith wrote:
>>>>
>>>> scale=1000, db is 94% of RAM; clients=4
>>>> Version TPS
>>>> 9.0  535
>>>> 9.1  491 (-8.4% relative to 9.0)
>>>> 9.2  338 (-31.2% relative to 9.1)
>>>
>>> A second pass through this data noted that the maximum number of buffers
>>> cleaned by the background writer is <=2785 in 9.0/9.1, while it goes as high
>>> as 17345 times in 9.2.  The background writer is so busy now it hits the
>>> max_clean limit around 147 times in the slower[1] of the 9.2 runs.  That's
>>> an average of once every 4 seconds, quite frequent.  Whereas max_clean
>>> rarely happens in the comparable 9.0/9.1 results.  This is starting to point
>>> my finger more toward this being an unintended consequence of the background
>>> writer/checkpointer split.
>>
>> I guess the question that occurs to me is: why is it busier?
>>
>> It may be that the changes we've made to reduce lock contention are
>> allowing foreground processes to get work done faster.  When they get
>> work done faster, they dirty more buffers, and therefore the
>> background writer gets busier.  Also, if the background writer is more
>> reliably cleaning pages even during checkpoints, that could have the
>> same effect.  Backends write fewer of their own pages, therefore they
>> get more real work done, which of course means dirtying more pages.
>
> The checkpointer/bgwriter split allows the bgwriter to do more work,
> which is the desired outcome, not an unintended consequence.
>
> The general increase in performance means there is more work to do. So
> both things mean there is more bgwriter activity.

I think you're saying pretty much the same thing I was saying, so I agree.

Here's what's bugging me. Greg seemed to be assuming that the
business of the background writer might be the cause of the
performance drop-off he measured on certain test cases. But you and I
both seem to feel that the business of the background writer is
intentional and desirable. Supposing we're right, where's the
drop-off coming from? *scratches head*

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company