Re: Publish autovacuum informations

On 19/03/2016 01:11, Jim Nasby wrote:
> On 3/3/16 3:54 AM, Kyotaro HORIGUCHI wrote:
>> 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.
>> =====
>
> I kinda like where you're going here, but I certainly don't think the
> stats system is the way to do it. Stats bloat is already a problem on
> bigger systems. More important, I don't think having just the last
> result is very useful. If you've got a vacuum problem, you want to see
> history, especially history of the vacuum runs themselves.
>
> The good news is that vacuum is a very low-frequency operation, so it
> has none of the concerns that the generic stats system does. I think it
> would be reasonable to provide event triggers that fire on every
> launcher loop, after a worker has built it's "TODO list", and after
> every (auto)vacuum.

The main issue I see with an event trigger based solution is that you'll
always have to create them and the needed objects on every database.

Another issue is that both of these approach are not intended to give a
global overview but per-database statistics. I'd prefer a global overview.

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