Re: Displaying accumulated autovacuum cost

Hi Greg,

(2011/08/28 18:54), Greg Smith wrote:
> Updated patch cleans up two diff mistakes made when backing out the
> progress report feature. The tip-off I screwed up should have been the
> absurdly high write rate shown. The usleep was accidentally deleted, so
> it was running without cost limits even applying. Here's a good one
> instead:
>
> LOG: automatic vacuum of table "pgbench.public.pgbench_accounts": index
> scans: 1
> pages: 0 removed, 163935 remain
> tuples: 2000000 removed, 2928356 remain
> buffer usage: 117393 hits, 123351 misses, 102684 dirtied, 2.168 MiB/s
> write rate
> system usage: CPU 2.54s/6.27u sec elapsed 369.99 sec

I took a look at your patch, and it seems fine about fundamental
functionality, though the patch needed to be rebased against current
HEAD. Please see attached patch which I used for review.

IIUC, this patch provides:
* Three counters, which are used to keep # of buffers which were (1)
Hits: found in shared buffers, (2) Missed: not found in shared buffers,
and (3) Dirtied: marked as dirty, in an autovacuum of a relation.
These counters are used only when cost-based autovacuum is enabled by
setting vacuum_cost_delay to non-zero.
* Capability to report # of buffers above, and buffer write rate
(MiB/sec) in the existing autovacuum logging message, only when actual
duration > autovacuum_min_duration, and cost-based autovacuum is enabled.

I think one concern is the way showing statistics. If showing summary
of statistics (at the end of an autovacuum) in server log is enough,
current implementation is fine. Also showing progress report in server
log would be easy to achieve. In contrast, reporting progress via
another backend would require shared memory or statistics collector,
rather than per-process global variables. Anyway, this patch can be the
base of such enhancement.

There are some trivial comments:
* Local variables added by the patch (secs, usecs, write_rate and
endtime) can be moved into narrower scope.
* Initializing starttime to zero seems unnecessary.

In addition, documents about how to use the statistics would be
necessary, maybe in "23.1.5. The Autovacuum Daemon".

I'll set the status of this patch to waiting-on-author. Would you rebase
the patch and post it again?

Regards,
--
Shigeru Hanada