Re: Publish autovacuum informations

On Thu, Mar 31, 2016 at 6:09 AM, Julien Rouhaud
<julien(dot)rouhaud(at)dalibo(dot)com> wrote:
> 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.

Which has surely a performance impact as those are row-based. I have
seen complains regarding the fact that those objects can be easily
forgotten...

> 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.

That's important, autovacuum GUC parameters, like the number of
workers, are system-wide.
--
Michael