Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
To: pgsql-hackers@lists.postgresql.org,
 Alvaro Herrera <alvherre@2ndquadrant.com>
References: <20190222215423.GA16912@alvherre.pgsql>
From: Rahila <rahila.syed@2ndquadrant.com>
Message-ID: <17b0f8fd-b51b-ee83-0ec8-9e363ba1d9c6@2ndquadrant.com>
Date: Mon, 4 Mar 2019 17:59:48 +0530
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:60.0)
 Gecko/20100101 Thunderbird/60.5.2
MIME-Version: 1.0
In-Reply-To: <20190222215423.GA16912@alvherre.pgsql>
Content-Type: multipart/alternative;
 boundary="------------8EECDA29313618460DACE991"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------8EECDA29313618460DACE991
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

Hi Alvaro,

Resending the email as earlier one didn't get sent on pgsql-hackers.

On 2/23/19 3:24 AM, Alvaro Herrera wrote:
> On 2019-Feb-13, Amit Langote wrote:
>
>> Doesn't the name amphasename sound a bit too generic, given that it can
>> only describe the phases of ambuild?  Maybe ambuildphase?
> Hmm, yeah, maybe it does.  I renamed it "ambuildphasename", since it's
> not about reporting the phase itself -- it's about translating the phase
> number to the string that's reported to the user.
>
> The attached patch does it that way.  Also, when an index build uses an
> AM that doesn't support progress reporting, it no longer reports a NULL
> phase name while building.  I also changed it to report the progress of
> phase 7 (heap scan validation) using block numbers rather than tuple
> counts.  I also tweaked the strings reported in the view.  They're
> clearer now IMO.
>
> One slight annoyance is that when parallel workers are used, the last
> block number reported in phase 3/subphase 2 (IndexBuildHeapScan stuff)
> is not necessarily accurate, since the tail of the table could well be
> scanned by a worker that's not the leader, and we only report in the
> leader when it gets a new block.
>
> When the AM does not support progress reporting, everything stays as
> zeros during the index build phase.  It's easy to notice how slow hash
> indexes are to build compared to btrees this way!  Maybe it'd be
> better fallback on reporting block numbers in IndexBuildHeapScan when
> this happens.  Thoughts?
>
> I added docs to the monitoring section -- that's the bulkiest part of
> the patch.

1. Thank you for incorporating review comments.
Can you please rebase the latest 0001-Report-progress-of-
CREATE-INDEX-operations.patch on master? Currently it does not apply on 
754b90f657bd54b482524b73726dae4a9165031c
>   15:56:44.694 | building index (3 of 8): initializing (1/5)            |       442478 |      442399 |            0 |           0 |                0 |               0
>   15:56:44.705 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               0
>   15:56:44.716 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               0
>   15:56:44.727 | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |       79057 |                0 |               0
>   15:56:44.738 | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |      217018 |                0 |               0
>   15:56:44.75  | building index (3 of 8): final btree sort & load (5/5) |       442478 |      442399 |    100000000 |      353804 |                0 |               0
>   
2. In the above report, even though we are reporting progress in terms 
of tuples_done for final btree sort & load phase we have not cleared
the blocks_done entry from previous phases. I think this can be 
confusing as the blocks_done does not correspond to the tuples_done in 
the final btree sort & load phase.

Thank you,
Rahila Syed


--------------8EECDA29313618460DACE991
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p>Hi Alvaro,<br>
      <br>
      Resending the email as earlier one didn't get sent on
      pgsql-hackers.<br>
    </p>
    <div class="moz-cite-prefix">On 2/23/19 3:24 AM, Alvaro Herrera
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:20190222215423.GA16912@alvherre.pgsql">
      <pre class="moz-quote-pre" wrap="">On 2019-Feb-13, Amit Langote wrote:

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Doesn't the name amphasename sound a bit too generic, given that it can
only describe the phases of ambuild?  Maybe ambuildphase?
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Hmm, yeah, maybe it does.  I renamed it "ambuildphasename", since it's
not about reporting the phase itself -- it's about translating the phase
number to the string that's reported to the user.

The attached patch does it that way.  Also, when an index build uses an
AM that doesn't support progress reporting, it no longer reports a NULL
phase name while building.  I also changed it to report the progress of
phase 7 (heap scan validation) using block numbers rather than tuple
counts.  I also tweaked the strings reported in the view.  They're
clearer now IMO.

One slight annoyance is that when parallel workers are used, the last
block number reported in phase 3/subphase 2 (IndexBuildHeapScan stuff)
is not necessarily accurate, since the tail of the table could well be
scanned by a worker that's not the leader, and we only report in the
leader when it gets a new block.

When the AM does not support progress reporting, everything stays as
zeros during the index build phase.  It's easy to notice how slow hash
indexes are to build compared to btrees this way!  Maybe it'd be
better fallback on reporting block numbers in IndexBuildHeapScan when
this happens.  Thoughts?

I added docs to the monitoring section -- that's the bulkiest part of
the patch.</pre>
    </blockquote>
    <br>
    <div>1. Thank you for incorporating review comments.</div>
    Can you please rebase the latest 0001-Report-progress-of-
    <div><wbr>CREATE-INDEX-operations.patch on master? Currently it does
      not apply on 754b90f657bd54b482524b73726dae<wbr>4a9165031c<br>
    </div>
    <blockquote type="cite"
      cite="mid:20190222215423.GA16912@alvherre.pgsql">
      <pre class="moz-quote-pre" wrap="">
 15:56:44.694 | building index (3 of 8): initializing (1/5)            |       442478 |      442399 |            0 |           0 |                0 |               0
 15:56:44.705 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               0
 15:56:44.716 | building index (3 of 8): sorting tuples, spool 1 (3/5) |       442478 |      442399 |    100000000 |           0 |                0 |               0
 15:56:44.727 | building index (3 of 8): final btree sort &amp; load (5/5) |       442478 |      442399 |    100000000 |       79057 |                0 |               0
 15:56:44.738 | building index (3 of 8): final btree sort &amp; load (5/5) |       442478 |      442399 |    100000000 |      217018 |                0 |               0
 15:56:44.75  | building index (3 of 8): final btree sort &amp; load (5/5) |       442478 |      442399 |    100000000 |      353804 |                0 |               0
 
</pre>
    </blockquote>
    <div>2. In the above report, even though we are reporting progress
      in terms of tuples_done for final btree sort &amp; load phase we
      have not cleared <br>
      the blocks_done entry from previous phases. I think this can be
      confusing as the blocks_done does not correspond to the
      tuples_done in the final btree sort &amp; load phase. <br>
      <br>
      Thank you,</div>
    <div>Rahila Syed<br>
      <div><span style="color: rgb(34, 34, 34); font-family: Arial,
          Helvetica, sans-serif; font-size: small; font-style: normal;
          font-variant-ligatures: normal; font-variant-caps: normal;
          font-weight: 400; letter-spacing: normal; orphans: 2;
          text-align: start; text-indent: 0px; text-transform: none;
          white-space: normal; widows: 2; word-spacing: 0px;
          -webkit-text-stroke-width: 0px; background-color: rgb(255,
          255, 255); text-decoration-style: initial;
          text-decoration-color: initial; display: inline !important;
          float: none;"></span>
        <div class="yj6qo ajU" style="cursor: pointer; outline: none;
          padding: 10px 0px; width: 22px; margin: 2px 0px 0px; color:
          rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif;
          font-size: small; font-style: normal; font-variant-ligatures:
          normal; font-variant-caps: normal; font-weight: 400;
          letter-spacing: normal; orphans: 2; text-align: start;
          text-indent: 0px; text-transform: none; white-space: normal;
          widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
          background-color: rgb(255, 255, 255); text-decoration-style:
          initial; text-decoration-color: initial;"><br
            class="Apple-interchange-newline">
        </div>
      </div>
    </div>
    <br>
    <div> </div>
  </body>
</html>

--------------8EECDA29313618460DACE991--