Re: Initial 9.2 pgbench write results

On 02/23/2012 07:36 AM, Simon Riggs wrote:
> Since scan_whole_pool_milliseconds is set to 2 minutes we scan the
> whole bufferpool every 2 minutes, no matter how big the bufferpool,
> even when nothing else is happening. Not cool.

It's not quite that bad.  Once the BGW has circled around the whole 
buffer pool, such that it's swept so far ahead it's reached the clock 
sweep strategy point, it stops.  So when the system is idle, it creeps 
forward until it's scanned the pool once.  Then, it still wakes up 
regularly, but the computation of the bufs_to_lap lap number will reach 
0.  That aborts running the main buffer scanning loop, so it only burns 
a bit of CPU time and a lock on BufFreelistLock each time it wakes--both 
of which are surely to spare if the system is idle.  I can agree with 
your power management argument, I don't see much of a performance win 
from eliminating this bit.

The goals was to end up with a fully cleaned pool ready to absorb going 
from idle to a traffic spike.  The logic behind where the "magic 
constants" controlling it came from was all laid out at 
http://archives.postgresql.org/pgsql-hackers/2007-09/msg00214.php 
There's a bunch of code around that whole computation that only executes 
if you enable BGW_DEBUG.  I left that in there in case somebody wanted 
to fiddle with this specific tuning work again, since it took so long to 
get right.  That was the last feature change made to the 8.3 background 
writer tuning work.

I was content at that time to cut the minimal activity level in half 
relative to what it was in 8.2, and that measured well enough.  It's 
hard to find compelling benchmark workloads where the background writer 
really works well though.  I hope to look at this set of interesting 
cases I found here more, now that I seem to have both positive and 
negative results for background writer involvement.

As for free list contention, I wouldn't expect that to be a major issue 
in the cases I was testing.  The background writer is just one of many 
backends all contending for that.  When there's dozens of backends all 
grabbing, I'd think that its individual impact would be a small slice of 
the total activity.  I will of course reserve arguing that point until 
I've benchmarked to support it though.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com