Re: HOT is applied

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Tom Lane wrote:
>> I'd still like to think about whether we
>> can be smarter about when to invoke pruning, but that's a small enough
>> issue that the patch can go in without it.

> Yeah. I'm doing some micro-benchmarking, and the attached test case is
> much slower with HOT. It's spending a lot of time trying to prune, only
> to find out that it can't.

Not sure if that's an appropriate description or not.  oprofile
(on a dual Xeon running Fedora 6) shows me this:

CPU: P4 / Xeon with 2 hyper-threads, speed 2792.99 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped)
with a unit mask of 0x01 (mandatory) count 100000
samples  %        symbol name
1070003  29.8708  LWLockAcquire
1015097  28.3380  LWLockRelease
283514    7.9147  heap_page_prune
252270    7.0425  AllocSetAlloc
148981    4.1590  HeapTupleSatisfiesMVCC
146442    4.0882  TransactionIdIsInProgress
92673     2.5871  AllocSetFree
84966     2.3720  HeapTupleSatisfiesVacuum
83097     2.3198  TransactionIdGetStatus
80737     2.2539  SimpleLruReadPage_ReadOnly
52803     1.4741  TransactionLogFetch
43406     1.2117  heapgetpage
42536     1.1875  HeapTupleHeaderGetCmin
34842     0.9727  TransactionIdIsCurrentTransactionId
27761     0.7750  TransactionIdDidAbort
24833     0.6933  MemoryContextAlloc
16446     0.4591  TransactionIdPrecedes
16089     0.4491  HeapTupleHeaderGetCmax
12919     0.3607  hash_search_with_hash_value
11857     0.3310  pfree
4964      0.1386  heap_page_prune_opt
3683      0.1028  hash_any
3215      0.0898  LWLockConditionalAcquire
3086      0.0862  PinBuffer
2573      0.0718  UnpinBuffer
2009      0.0561  ConditionalLockBufferForCleanup
1854      0.0518  ReadBuffer_common
934       0.0261  XLogInsert
784       0.0219  heapgettup_pagemode

so basically it's all about the locking.  Maybe the problem is that with
HOT we lock the buffer too often?  heap_page_prune_opt is designed to
not take the buffer lock unless there's a good probability of needing
to prune, but maybe that's not working as intended.

[ pokes at it a bit more... ]  Actually the disturbing part is that
pruning doesn't seem to be working at all: after the test finishes,
I see

regression=# vacuum verbose testtable;
INFO:  vacuuming "public.testtable"
INFO:  "testtable": removed 10000 row versions in 44 pages
INFO:  "testtable": found 10000 removable, 1 nonremovable row versions in 45 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 9955 unused item pointers.
45 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Shouldn't we be able to prune rows that have been inserted and deleted
by the same transaction?  I'd have hoped to see this example use only
one heap page ...
        regards, tom lane