Re: Optimising compactify_tuples() - Mailing list pgsql-hackers

David Rowley wrote:

> I've attached patches in git format-patch format. I'm proposing to commit these in about 48 hours time unless there's
somesort of objection before then. 

Hi David, no objections at all, I've just got reaffirming results here, as per [1] (SLRU thread but combined results
withqsort testing) I've repeated crash-recovery tests here again: 

TEST0a: check-world passes
TEST0b: brief check: DB after recovery returns correct data which was present only into the WAL stream - SELECT sum(c)
fromsometable 

TEST1: workload profile test as per standard TPC-B [2], with majority of records in WAL stream being Heap/HOT_UPDATE on
samesystem with NVMe as described there. 

results of master (62e221e1c01e3985d2b8e4b68c364f8486c327ab) @ 15/09/2020 as baseline:
15.487, 1.013
15.789, 1.033
15.942, 1.118

profile looks most of the similar:
    17.14%  postgres  libc-2.17.so        [.] __memmove_ssse3_back
            ---__memmove_ssse3_back
               compactify_tuples
               PageRepairFragmentation
               heap2_redo
               StartupXLOG
     8.16%  postgres  postgres            [.] hash_search_with_hash_value
            ---hash_search_with_hash_value
               |--4.49%--BufTableLookup
[..]
                --3.67%--smgropen

master with 2 patches by David (v8-0001-Optimize-compactify_tuples-function.patch +
v8-0002-Report-resource-usage-at-the-end-of-recovery.patch): 
14.236, 1.02
14.431, 1.083
14.256, 1.02

so 9-10% faster in this simple verification check. If I had pgbench running the result would be probably better.
Profileis similar: 

    13.88%  postgres  libc-2.17.so        [.] __memmove_ssse3_back
            ---__memmove_ssse3_back
                --13.47%--compactify_tuples

    10.61%  postgres  postgres            [.] hash_search_with_hash_value
            ---hash_search_with_hash_value
               |--5.31%--smgropen
[..]
                --5.31%--BufTableLookup


TEST2: update-only test, just as you performed in [3] to trigger the hotspot, with table fillfactor=85 and update.sql
(100%updates, ~40% Heap/HOT_UPDATE [N], ~40-50% [record sizes]) with slightly different amount of data. 

results of master as baseline:
233.377, 0.727
233.233, 0.72
234.085, 0.729

with profile:
    24.49%  postgres  postgres          [.] pg_qsort
    17.01%  postgres  postgres          [.] PageRepairFragmentation
    12.93%  postgres  postgres          [.] itemoffcompare
(sometimes I saw also a ~13% swapfunc)

results of master with above 2 patches, 2.3x speedup:
101.6, 0.709
101.837, 0.71
102.243, 0.712

with profile (so yup the qsort is gone, hurray!):

    32.65%  postgres  postgres          [.] PageRepairFragmentation
            ---PageRepairFragmentation
               heap2_redo
               StartupXLOG
    10.88%  postgres  postgres          [.] compactify_tuples
            ---compactify_tuples
     8.84%  postgres  postgres          [.] hash_search_with_hash_value

BTW: this message "redo done at 0/9749FF70 system usage: CPU: user: 13.46 s, system: 0.78 s, elapsed: 14.25 s" is
pricelessaddition :)  

-J.

[1] -
https://www.postgresql.org/message-id/flat/VI1PR0701MB696023DA7815207237196DC8F6570%40VI1PR0701MB6960.eurprd07.prod.outlook.com#188ad4e772615999ec427486d1066948
[2] - pgbench -i -s 100, pgbench -c8 -j8 -T 240, ~1.6GB DB with 2.3GB after crash in pg_wal to be replayed
[3] - https://www.postgresql.org/message-id/CAApHDvoKwqAzhiuxEt8jSquPJKDpH8DNUZDFUSX9P7DXrJdc3Q%40mail.gmail.com , in
mycase: pgbench -c 16 -j 16 -T 240 -f update.sql , ~1GB DB with 4.3GB after crash in pg_wal to be replayed