On 5/18/21 8:08 PM, Andres Freund wrote:
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.
Yeah, emitting WAL is not exactly cheap, although it's just a little bit
more (0.44%). I haven't looked into the details, but I wonder why it has
such disproportionate impact (although, the 32 vs. 40 sec may be off).
I dimly remember that we explicitly discussed that we do *not* want to
emit WAL records here?
Ummm, in which thread?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
