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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com