Re: HOT pgbench results

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:

> I ran some CPU intensive pgbench tests on HOT. Results are not
> surprising, HOT makes practically no difference on the total transaction
> rate, but reduces the need to vacuum:
>
> unpatched HOT
> tps 3680 3790
> WAL written(MB) 5386 4804
> checkpoints 10 9
> autovacuums 116 43
> autoanalyzes 139 60

I also ran pgbench with/without HOT using a bit different configurations
(pgbench -s10 -c10 -t500000). There were 10% performance win on HOT,
although the test was CPU intensive and with FILLFACTOR=100%.

unpatched HOT
tps 3366 3634
WAL written(MB) 4969 4374
checkpoints 9 8
autovacuums 126 42
autoanalyzes 146 59

I gathered oprofile logs. There were 4 HOT-related functions, that didn't
appear in the unpatched test. But it is probably not so serious.
- heap_page_prune 1.84%
- PageRepairFragmentation 0.94%
- pg_qsort 0.44% (called from PageRepairFragmentation)

On the other hand, the number of _bt_compare and _bt_checkkeys were
reduced by HOT, because we avoid the most part of index insertions.
It looks like LWLockAcquire/Release were also reduced, but I cannot
assure it is a benefits of HOT or a measurement deviation.

unpatched HOT % symbol name
4.0867 4.2314 AllocSetAlloc
2.7839 2.8022 base_yyparse
1.8392 heap_page_prune
1.8459 1.6659 SearchCatCache
1.7405 1.6087 MemoryContextAllocZeroAligned
1.6936 1.5743 hash_search_with_hash_value
1.0672 1.1822 base_yylex
1.2430 1.1570 XLogInsert
0.9356 PageRepairFragmentation
1.3549 0.8911 LWLockAcquire
1.0977 0.8663 LWLockRelease
0.8018 0.7284 nocachegetattr
0.7568 0.7124 FunctionCall2
0.5264 0.6795 ScanKeywordLookup
0.7115 0.6462 hash_any
0.7399 0.5963 AllocSetFree
0.6650 0.5925 GetSnapshotData
0.5536 0.5789 MemoryContextAlloc
0.5643 0.5547 hash_seq_search
0.4660 0.5005 expression_tree_walker
0.5293 0.4777 ExecInitExpr
0.4381 pg_qsort
0.4376 0.4321 hash_uint32
0.4160 0.4268 expression_tree_mutator
0.4322 0.4183 LockAcquire
0.6933 0.3911 _bt_compare
0.5270 0.3828 PinBuffer
0.4025 0.3798 fmgr_info_cxt_security
0.4458 0.3758 MemoryContextAllocZero
0.5101 _bt_checkkeys

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center