Re: Initial 9.2 pgbench write results

On 02/18/2012 02:35 PM, Robert Haas wrote:
> I see CheckpointWriteDelay calling BgBufferSync
> in 9.1. Background writing would stop during the sync phase and
> perhaps slow down a bit during checkpoint writing, but I don't think
> it was stopped completely.

The sync phase can be pretty long here--that's where the worst-case
latency figures lasting many seconds are coming from. When checkpoints
are happening every 60 seconds as in some of these cases, that can
represent a decent percentage of time. Similarly, when the OS cache
fills, the write phase might block for a larger period of time than
normally expected. But, yes, you're right that my "BGW is active twice
as much in 9.2" comments are overstating the reality here.

I'm collecting one last bit of data before posting another full set of
results, but I'm getting more comfortable the issue here is simply
changes in the BGW behavior. The performance regression tracks the
background writer maximum intensity. I can match the original 9.1
performance just by dropping bgwriter_lru_maxpages, in cases where TPS
drops significantly between 9.2 and 9.1. At the same time, some cases
that improve between 9.1 and 9.2 perform worse if I do that. If whether
9.2 gains or loses compared to 9.1 is adjustable with a tunable
parameter, with some winning and other losing at the defaults, that path
forward is reasonable to deal with. The fact that pgbench is an unusual
write workload is well understood, and I can write something documenting
this possibility before 9.2 is officially released. I'm a lot less
stressed that there's really a problem here now.

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