Re: Performance degradation of REFRESH MATERIALIZED VIEW

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