Re: Performance degradation of REFRESH MATERIALIZED VIEW

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