Re: Optimising compactify_tuples()

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 some sort 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 with qsort 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) from sometable

TEST1: workload profile test as per standard TPC-B [2], with majority of records in WAL stream being Heap/HOT_UPDATE on same system 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. Profile is 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 priceless addition :)

-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 my case: pgbench -c 16 -j 16 -T 240 -f update.sql , ~1GB DB with 4.3GB after crash in pg_wal to be replayed