Re: HOT is applied

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)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