Re: Performance degradation of REFRESH MATERIALIZED VIEW - Mailing list pgsql-hackers
| From | Tomas Vondra |
|---|---|
| Subject | Re: Performance degradation of REFRESH MATERIALIZED VIEW |
| Date | |
| Msg-id | 54155b7c-3862-76fc-8acc-437e2b19a921@enterprisedb.com Whole thread Raw |
| In response to | Re: Performance degradation of REFRESH MATERIALIZED VIEW (Andres Freund <andres@anarazel.de>) |
| Responses |
Re: Performance degradation of REFRESH MATERIALIZED VIEW
|
| List | pgsql-hackers |
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
pgsql-hackers by date: