Hi,
On 2021-05-18 11:20:07 +0900, Masahiko Sawada wrote:
> Yes. It depends on how much the matview refresh gets slower but I
> think the problem here is that users always are forced to pay the cost
> for freezing tuple during refreshing the matview. There is no way to
> disable it unlike FREEZE option of COPY command.
>
> I’ve done benchmarks for matview refresh on my machine (FreeBSD 12.1,
> AMD Ryzen 5 PRO 3400GE, 24GB RAM) with four codes: HEAD, HEAD +
> Andres’s patch, one before 39b66a91b, and HEAD without
> TABLE_INSERT_FROZEN.
>
> The workload is to refresh the matview that simply selects 50M tuples
> (about 1.7 GB). Here are the average execution times of three trials
> for each code:
>
> 1) head: 42.263 sec
> 2) head w/ Andres’s patch: 40.194 sec
> 3) before 39b66a91b commit: 38.143 sec
> 4) head w/o freezing tuples: 32.413 sec
I don't see such a big difference between andres-freeze/non-freeze. Is
there any chance there's some noise in there? I found that I need to
disable autovacuum and ensure that there's a checkpoint just before the
REFRESH to get halfway meaningful numbers, as well as a min/max_wal_size
ensuring that only recycled WAL is used.
> I also observed 5% degradation by comparing 1 and 2 but am not sure
> where the overhead came from. I agree with Andres’s proposal. It’s a
> straightforward approach.
What degradation are you referencing here?
I compared your case 2 with 4 - as far as I can see the remaining
performance difference is from the the difference in WAL records
emitted:
freeze-andres:
Type N (%) Record size
(%) FPI size (%) Combined size (%)
---- - --- -----------
--- -------- --- ------------- ---
XLOG/CHECKPOINT_ONLINE 1 ( 0.00) 114
( 0.00) 0 ( 0.00) 114 ( 0.00)
Transaction/COMMIT 1 ( 0.00) 949
( 0.00) 0 ( 0.00) 949 ( 0.00)
Storage/CREATE 1 ( 0.00) 42
( 0.00) 0 ( 0.00) 42 ( 0.00)
Standby/LOCK 3 ( 0.00) 138
( 0.00) 0 ( 0.00) 138 ( 0.00)
Standby/RUNNING_XACTS 2 ( 0.00) 104
( 0.00) 0 ( 0.00) 104 ( 0.00)
Heap2/VISIBLE 44248 ( 0.44) 2610642
( 0.44) 16384 ( 14.44) 2627026 ( 0.44)
Heap2/MULTI_INSERT 5 ( 0.00) 1125
( 0.00) 6696 ( 5.90) 7821 ( 0.00)
Heap/INSERT 9955755 ( 99.12) 587389836
( 99.12) 5128 ( 4.52) 587394964 ( 99.10)
Heap/DELETE 13 ( 0.00) 702
( 0.00) 0 ( 0.00) 702 ( 0.00)
Heap/UPDATE 2 ( 0.00) 202
( 0.00) 0 ( 0.00) 202 ( 0.00)
Heap/HOT_UPDATE 1 ( 0.00) 65
( 0.00) 4372 ( 3.85) 4437 ( 0.00)
Heap/INSERT+INIT 44248 ( 0.44) 2610632
( 0.44) 0 ( 0.00) 2610632 ( 0.44)
Btree/INSERT_LEAF 33 ( 0.00) 2030
( 0.00) 80864 ( 71.28) 82894 ( 0.01)
-------- --------
-------- --------
Total 10044313 592616581
[99.98%] 113444 [0.02%] 592730025 [100%]
nofreeze:
Type N (%) Record size
(%) FPI size (%) Combined size (%)
---- - --- -----------
--- -------- --- ------------- ---
XLOG/NEXTOID 1 ( 0.00) 30
( 0.00) 0 ( 0.00) 30 ( 0.00)
Transaction/COMMIT 1 ( 0.00) 949
( 0.00) 0 ( 0.00) 949 ( 0.00)
Storage/CREATE 1 ( 0.00) 42
( 0.00) 0 ( 0.00) 42 ( 0.00)
Standby/LOCK 3 ( 0.00) 138
( 0.00) 0 ( 0.00) 138 ( 0.00)
Standby/RUNNING_XACTS 1 ( 0.00) 54
( 0.00) 0 ( 0.00) 54 ( 0.00)
Heap2/MULTI_INSERT 5 ( 0.00) 1125
( 0.00) 7968 ( 7.32) 9093 ( 0.00)
Heap/INSERT 9955755 ( 99.56) 587389836
( 99.56) 5504 ( 5.06) 587395340 ( 99.54)
Heap/DELETE 13 ( 0.00) 702
( 0.00) 0 ( 0.00) 702 ( 0.00)
Heap/UPDATE 2 ( 0.00) 202
( 0.00) 0 ( 0.00) 202 ( 0.00)
Heap/HOT_UPDATE 1 ( 0.00) 65
( 0.00) 5076 ( 4.67) 5141 ( 0.00)
Heap/INSERT+INIT 44248 ( 0.44) 2610632
( 0.44) 0 ( 0.00) 2610632 ( 0.44)
Btree/INSERT_LEAF 32 ( 0.00) 1985
( 0.00) 73476 ( 67.54) 75461 ( 0.01)
Btree/INSERT_UPPER 1 ( 0.00) 61
( 0.00) 1172 ( 1.08) 1233 ( 0.00)
Btree/SPLIT_L 1 ( 0.00) 1549
( 0.00) 7480 ( 6.88) 9029 ( 0.00)
Btree/DELETE 1 ( 0.00) 59
( 0.00) 8108 ( 7.45) 8167 ( 0.00)
Btree/REUSE_PAGE 1 ( 0.00) 50
( 0.00) 0 ( 0.00) 50 ( 0.00)
-------- --------
-------- --------
Total 10000067 590007479
[99.98%] 108784 [0.02%] 590116263 [100%]
I.e. the additional Heap2/VISIBLE records show up.
It's not particularly surprising that emitting an additional WAL record
for every page isn't free. It's particularly grating / unnecessary
because this is the REGBUF_WILL_INIT path - it's completely unnecessary
to emit a separate record.
I dimly remember that we explicitly discussed that we do *not* want to
emit WAL records here?
Greetings,
Andres Freund
