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
