Hello, At Wed, 2 Mar 2016 17:48:06 -0600, Jim Nasby <jim.na...@bluetreble.com> wrote in <56d77bb6.6080...@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_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. ===== 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? regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers