Re: Publish autovacuum informations

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

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.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com