From: | dinesh kumar <dineshkumar02(at)gmail(dot)com> |
---|---|
To: | Rahila Syed <rahilasyed90(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PROPOSAL] VACUUM Progress Checker. |
Date: | 2015-06-30 08:09:09 |
Message-ID: | CALnrH7oEEGZo8vPNFjdEU5-oqfFpXjJCHOuz=N=X5zb3AwCFAw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Jun 30, 2015 at 1:07 PM, Rahila Syed <rahilasyed90(at)gmail(dot)com> wrote:
> Hello Hackers,
>
> Following is a proposal for feature to calculate VACUUM progress.
>
> Use Case : Measuring progress of long running VACUUMs to help DBAs make
> informed decision
> whether to continue running VACUUM or abort it.
>
> +1
I am excited to know how the progress works in when any of the statement
got blocked during locks. Rather displaying the stats in the LOG, shall we
have this in a pg_stat_vacuum_activity[ New catalog for all auto-vacuum
stats].
Best Regards,
Dinesh
manojadinesh.blogspot.com
Design:
>
> A shared preload library to store progress information from different
> backends running VACUUM, calculate remaining time for each and display
> progress in the
> in the form a view.
>
>
> VACUUM needs to be instrumented with a hook to collect progress
> information (pages vacuumed/scanned) periodically.
>
> The patch attached implements a new hook to store vacuumed_pages and
> scanned_pages count at the end of each page scanned by VACUUM.
>
> This information is stored in a shared memory structure.
>
> In addition to measuring progress this function using hook also calculates
> remaining time for VACUUM.
>
>
>
> The frequency of collecting progress information can be reduced by
> appending delays in between hook function calls.
>
> Also, a GUC parameter
>
> log_vacuum_min_duration can be used.
>
> This will cause VACUUM progress to be calculated only if VACUUM runs more
> than specified milliseconds.
>
> A value of zero calculates VACUUM progress for each page processed. -1
> disables logging.
>
>
> Progress calculation :
>
>
> percent_complete = scanned_pages * 100 / total_pages_to_be_scanned;
>
> remaining_time = elapsed_time * (total_pages_to_be_scanned -
> scanned_pages) / scanned_pages;
>
>
> Shared memory struct:
>
> typedef struct PgStat_VacuumStats
>
> {
>
> Oid databaseoid;
>
> Oid tableoid;
>
> Int32 vacuumed_pages;
>
> Int32 total_pages;
>
> Int32 scanned_pages;
>
> double elapsed_time;
>
> double remaining_time;
>
> } PgStat_VacuumStats[max_connections];
>
>
>
> Reporting :
>
> A view named 'pg_maintenance_progress' can be created using the values in
> the struct above.
>
> pg_stat_maintenance can be called from any other backend and will display
> progress of
>
> each running VACUUM.
>
>
> Other uses of hook in VACUUM:
>
>
> Cost of VACUUM in terms of pages hit , missed and dirtied same as
> autovacuum can be collected using this hook.
>
> Autovacuum does it at the end of VACUUM for each table. It can be done
> while VACUUM on a table is in progress.
> This can be helpful to track manual VACUUMs also not just autovacuum.
>
> Read/Write(I/O) rates can be computed on the lines of autovacuum.
> Read rate patterns can be used to help tuning future vacuum on the
> table(like shared buffers tuning)
> Other resource usages can also be collected using progress checker hook.
>
>
> Attached patch is POC patch of progress calculation for a single backend.
>
> Also attached is a brief snapshot of the output log.
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2015-06-30 08:31:42 | Re: Reducing ClogControlLock contention |
Previous Message | Thomas Munro | 2015-06-30 07:59:07 | Re: [PROPOSAL] VACUUM Progress Checker. |