Re: Publish autovacuum informations

On 03/03/2016 10:54, Kyotaro HORIGUCHI wrote:
> Hello,
> 
> At Wed, 2 Mar 2016 17:48:06 -0600, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote in <56D77BB6.6080606@BlueTreble.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_resultfrom 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?
> 

Very good suggestion.

I think the most productive way to work on this is to start a wiki page
to summarize what's the available information, what we should store and
how to represent it.

I'll update this thread as soon as I'll have a first draft finished.

> 
> regards,
> 


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org