Re: Performance degradation of REFRESH MATERIALIZED VIEW - Mailing list pgsql-hackers
| From | Andres Freund |
|---|---|
| Subject | Re: Performance degradation of REFRESH MATERIALIZED VIEW |
| Date | |
| Msg-id | 20210518180816.e6erln6pj4x5mora@alap3.anarazel.de Whole thread Raw |
| In response to | Re: Performance degradation of REFRESH MATERIALIZED VIEW (Masahiko Sawada <sawada.mshk@gmail.com>) |
| Responses |
Re: Performance degradation of REFRESH MATERIALIZED VIEW
Re: Performance degradation of REFRESH MATERIALIZED VIEW |
| List | pgsql-hackers |
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
pgsql-hackers by date: