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:

Previous
From: Tomas Vondra
Date:
Subject: Re: Performance degradation of REFRESH MATERIALIZED VIEW
Next
From: Andres Freund
Date:
Subject: Re: Performance degradation of REFRESH MATERIALIZED VIEW