Re: Publish autovacuum informations

Hello,

At Wed, 2 Mar 2016 17:48:06 -0600, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote in <56D77BB6(dot)6080606(at)BlueTreble(dot)com>
> On 3/2/16 10:48 AM, Julien Rouhaud wrote:
> > Good point, I don't see a lot of information available with this hooks
> > that a native system statistics couldn't offer. To have the same
> > amount
> > of information, I think we'd need a pg_stat_autovacuum view that shows
> > a
> > realtime insight of the workers, and also add some aggregated counters
> > to PgStat_StatTabEntry. I wonder if adding counters to
> > PgStat_StatTabEntry would be accepted though.
>
> I would also really like to see a means of logging (auto)vacuum
> activity in the database itself. We figured out how to do that with
> pg_stat_statements, which was a lot harder... it seems kinda silly not
> to offer that for vacuum. Hooks plus shared memory data should allow
> for that (the only tricky bit is the hook would need to start and then
> commit a transaction, but that doesn't seem onerous).
>
> I think the shared memory structures should be done as well. Having
> that real-time info is also valuable.
>
> I don't see too much point in adding stuff to the stats system for
> this.

I wonder why there haven't been discussions so far on what kind
of information we want by this feature. For example I'd be happy
to see the time of last autovacuum trial and the cause if it has
been skipped for every table. Such information would (maybe)
naturally be shown in pg_stat_*_tables.

=====
=# select relid, last_completed_autovacuum, last_completed_autovacv_status, last_autovacuum_trial, last_autovacuum_result from pg_stat_user_tables;
-[ RECORD 1 ]-----------------+------
relid | 16390
last_completed_autovacuum | 2016-03-01 01:25:00.349074+09
last_completed_autovac_status | Completed in 4 seconds. Scanned 434 pages, skipped 23 pages
last_autovacuum_trial | 2016-03-03 17:33:04.004322+09
last_autovac_traial_status | Canceled by PID 2355. Processed 144/553 pages.
-[ RECORD 2 ]----------+------
...
last_autovacuum_trial | 2016-03-03 07:25:00.349074+09
last_autovac_traial_status | Completed in 4 seconds. Scanned 434 pages, skipped 23 pages
-[ RECORD 3 ]----------+------
...
last_autovacuum_trial | 2016-03-03 17:59:12.324454+09
last_autovac_trial_status | Processing by PID 42334, 564 / 32526 pages done.
-[ RECORD 4 ]----------+------
...
last_autovacuum_trial | 2016-03-03 17:59:12.324454+09
last_autovac_trial_status | Skipped by dead-tuple threashold.
=====

Apart from the appropriateness of the concrete shape, it would be
done by extending the current stats system and needs modification
of some other parts but the hooks and WorkerInfoData is not
enough. This might be a business of Rahila's "VACUUM Progress
Checker" and it convers some real-time info.

https://commitfest.postgresql.org/9/545/

On the other hand, it would be in another place and needs another
method if we want a history like the current autovacuum
completion logs (at debug3..) of 100 latest invocation of
autovacuum worker. Anyway the WorkerInfoData is not enough.

What kind of information we (will) want to have?

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center