Re: [PROPOSAL] VACUUM Progress Checker. - Mailing list pgsql-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.

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.