Detailed progress reporting for "vacuuming indexes" stage

On Mon, Jan 23, 2017 at 8:32 PM, Alexander Shchapov
<alexanderad@gmail.com> wrote:

There are 7 stages for standard VACUUM process which is reportable via
this view. For time consuming stages like "scanning heap" or
"vacuuming heap" you are able to get stage progress idea by doing:

(heap_blks_scanned | heap_blks_vacuumed) / heap_blks_total * 100%

However stage "vacuuming indexes" does not give any idea of how long
this stage might take. We have some rather big (500M+ records) with
5-7 indexes. Sometimes it takes us 20 hours+ to get table vacuumed and
all progress reporting we have for stage 3 is that it is stage 3.

Yes, things could be improved here.

According to code
(https://github.com/postgres/postgres/blob/1d25779284fe1ba08ecd57e647292a9deb241376/src/backend/commands/vacuumlazy.c#L708-L712)
looks like there might be a way to report number of indexes left to be
vacuumed.

Indexes may vary in size. For example if a table has a bunch of BRIN
indexes and one huge btree most of the time would be spent on the
btree part. So the number would not make much sense.

The question is: is it a right thing to expect a patch which adds more
detailed stage 3 reporting? Say index number being vacuumed.

What would be more interesting would be like the heap to get
information on the index block being cleaned up with reports being
done via index_bulk_delete(). That's more work, and that would be more
helpful than just a number.

So patches welcome.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jan 24, 2017 at 8:57 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:

What would be more interesting would be like the heap to get
information on the index block being cleaned up with reports being
done via index_bulk_delete(). That's more work, and that would be more
helpful than just a number.

So patches welcome.

Thanks for the reply and a tip, Michael.
Do I get it correctly, that you suggesting to calculate something like
index_blks_total and increment index_blks_vacuumed?

I'll try to look into this.

--
Alexander Shchapov

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 24 January 2017 at 06:57, Michael Paquier <michael.paquier@gmail.com> wrote:

On Mon, Jan 23, 2017 at 8:32 PM, Alexander Shchapov
<alexanderad@gmail.com> wrote:

There are 7 stages for standard VACUUM process which is reportable via
this view. For time consuming stages like "scanning heap" or
"vacuuming heap" you are able to get stage progress idea by doing:

(heap_blks_scanned | heap_blks_vacuumed) / heap_blks_total * 100%

However stage "vacuuming indexes" does not give any idea of how long
this stage might take. We have some rather big (500M+ records) with
5-7 indexes. Sometimes it takes us 20 hours+ to get table vacuumed and
all progress reporting we have for stage 3 is that it is stage 3.

Yes, things could be improved here.

Yes, it seems that VACUUM progress reporting feature is only about 40% complete.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jan 24, 2017 at 5:37 PM, Alexander Shchapov
<alexanderad@gmail.com> wrote:

On Tue, Jan 24, 2017 at 8:57 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:

What would be more interesting would be like the heap to get
information on the index block being cleaned up with reports being
done via index_bulk_delete(). That's more work, and that would be more
helpful than just a number.

So patches welcome.

Thanks for the reply and a tip, Michael.
Do I get it correctly, that you suggesting to calculate something like
index_blks_total and increment index_blks_vacuumed?

Something like that may be helpful as vacuum is basically triggering a
bulk deletion on the indexes vacuumed. What makes the thing tricky is
how to integrate that correctly with index AMs. So while it can be
done, it deserves careful thoughts.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general