Re: HOT pgbench results - Mailing list pgsql-hackers

Heikki Linnakangas <heikki@enterprisedb.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