On Thu, Mar 31, 2016 at 6:09 AM, Julien Rouhaud <julien.rouh...@dalibo.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers