On Fri, Mar 30, 2012 at 9:27 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> The more interesting waits, in my view anyway, are the ones that come
> from LWLockAcquire, which account for nearly all of the semaphore
> sleeps. As you can see, XLogInsert accounts for over half of those,
> and ProcArrayEndTransaction and SimpleLruReadPage_ReadOnly account for
> most of the rest. Just out of curiosity, I expanded a couple of those
> another level, and you can see what's going on there, too. I was
> slightly surprised by the number of calls to log_heap_clean, since I
> wouldn't have expected vacuuming to generate that much work; it turns
> out that 99.9% of those are HOT pruning events happening during query
> execution.
I've generally seen a one to one correspondence between log_heap_clean
and updates to pgbench_accounts (not by sampling waits, but by
wal_debug=1).
I figured this was because the pgbench_accounts blocks are packed to
the gills by default, so the only way to make room for the next HOT
update is the prune the dead tuple left over from the previous HOT
update in that same block.
It might be interesting to see if those go away if run with pgbench -i
-F90 or some other value less than 100.
Which I've now done, and it actually goes the other way. with -F50,
not only do almost all pgbench_accounts updates still lead to a clean,
but a good chunk of the updates to pgbench_tellers lead to a clean as
well. If cleans are going to happen so often, would it make sense to
write a combined record for clean+hot_update?
Cheers,
Jeff