On 03/03/2016 10:54, Kyotaro HORIGUCHI wrote: > 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? >
Very good suggestion. I think the most productive way to work on this is to start a wiki page to summarize what's the available information, what we should store and how to represent it. I'll update this thread as soon as I'll have a first draft finished. > > regards, > -- Julien Rouhaud http://dalibo.com - http://dalibo.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers