From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Rahila Syed <rahila.syed@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Cc: David Fetter <david@fetter.org>
Cc: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
Date: Mon, 1 Apr 2019 18:33:03 -0300
Message-ID: <20190401213303.GA26854@alvherre.pgsql> (raw)
In-Reply-To: <CAOajBXRSqA4hDDJRov+gmYxJNocULphv-PO74JRwQEWGrYcjfA@mail.gmail.com>
On 2019-Apr-02, Rahila Syed wrote:
> 1. FWIW, below results for CIC show that blocks_done does not become equal
> to blocks_total at the end of the phase or it processes last 800 blocks so
> fast that
> the update is not visible in less than 1 secs interval.
Yeah, I noticed this too and decided it's not fixable, nor it's
desirable to spend a lot of effort in getting it perfectly accurate -- I
mean, we could introduce locking or sleeping to get the results we want,
but do we really want to make the index building process slower just to
report those block numbers.
Anyway, I think this effect is caused by parallel btree building: those
final blocks are scanned by a worker process, and the leader didn't get
the latest block number scanned. If you set
max_parallel_maintenance_workers to 0, the effect disappears.
(I used \watch 0.01 to see even faster progress updates; even in that
case the final batch of block numbers is not seen in the updates. The
btree build code is stupidly fast.)
> 2. However in case of partitioned tables, the following difference in
> blocks_done versus blocks_total at the end of phase is notably high for the
> first partition . Subsequent partitions show negligible difference.
> Partition 1:
> Mon Mar 25 14:27:57 IST 2019
> pid | datid | datname | relid | phase |
> lockers_total | lockers_done | current_locker_pid | blocks_total |
> blocks_done | tuples_total | tuples_done | partitions_total |
> partitions_done
> -------+-------+----------+-------+----------------------------+---------------+--------------+--------------------+--------------+-------------+--------------+-------------+------------------+-----------------
> 10630 | 13533 | postgres | 16394 | building index: table scan
> | 0 | 0 | 0 | 381342 |
> 221233 | 0 | 0 | 3 | 0
> (1 row)
Hmm, in my tests with partitioned tables, I never noticed such a large
discrepancy. I'm going to have another look. 800 blocks scanned by
workers I can believe, but 160000 sounds a bit too much.
> 3. Sorry for nitpicking, I think following phase name can be made more
> consistent with the others.
> The non-am specific phase for scanning a table is named as scan heap while
> am-specific one is named as table scan.
> Can we use heap for am-specific one as well since heap is used elsewhere in
> progress reporting too?
Hmm, I'd rather go the other way and use "table" everywhere rather than
heap, since we've been getting a lot of stuff done for table AMs.
> 4. - scan = table_beginscan_parallel(btspool->heap,
> ParallelTableScanFromBTShared(btshared));
> + scan = table_beginscan_parallel(btspool->heap,
> +
> ParallelTableScanFromBTShared(btshared));
>
> Is this change required?
Yes, for my OCD.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, rahila.syed@2ndquadrant.com, andres@anarazel.de, robertmhaas@gmail.com, simon@2ndquadrant.com, pgsql-hackers@lists.postgresql.org, david@fetter.org, pavan.deolasee@gmail.com, Langote_Amit_f8@lab.ntt.co.jp
Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
In-Reply-To: <20190401213303.GA26854@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox