Re: [PROPOSAL] VACUUM Progress Checker.

On 11 September 2015 at 15:43, Syed, Rahila <Rahila(dot)Syed(at)nttdata(dot)com> wrote:

>
> Hello,
>
> Please find attached updated VACUUM progress checker patch.
> Following have been accomplished in the patch
>
> 1. Accounts for index pages count while calculating total progress of
> VACUUM.
> 2. Common location for storing progress parameters for any command. Idea
> is every command which needs to report progress can populate and interpret
> the shared variables in its own way.
> Each can display progress by implementing separate views.
> 3. Separate VACUUM progress view to display various progress parameters
> has been implemented . Progress of various phases like heap scan, index
> scan, total pages scanned along with
> completion percentage is reported.
> 4.This view can display progress for all active backends running VACUUM.
>
> Basic testing has been performed. Thorough testing is yet to be done.
> Marking it as Needs Review in Sept-Commitfest.
>
> ToDo:
> Display count of heap pages actually vacuumed(marking line pointers unused)
> Display percentage of work_mem being used to store dead tuples.
>

Okay, I've just tested this with a newly-loaded table (1,252,973 of jsonb
data), and it works fine during a vacuum. I can see the scanned_pages,
scanned_heap_pages and percent_complete increasing, but after it's
finished, I end up with this:

json=# select * from pg_stat_vacuum_progress;
-[ RECORD 1 ]-------+-------
pid | 5569
total_pages | 217941
scanned_pages | 175243
total_heap_pages | 175243
scanned_heap_pages | 175243
total_index_pages | 42698
scanned_index_pages |
percent_complete | 80

This was running with a VACUUM ANALYZE. This output seems to suggest that
it didn't complete.

After, I ran VACUUM FULL. pg_stat_vacuum_progress didn't change from
before, so that doesn't appear to show up in the view.

I then deleted 40,000 rows from my table, and ran VACUUM ANALYZE again.
This time it progressed and percent_complete reached 100.

--
Thom