Re: Publish autovacuum informations
От | Julien Rouhaud |
---|---|
Тема | Re: Publish autovacuum informations |
Дата | |
Msg-id | 56DA0383.2090108@dalibo.com обсуждение исходный текст |
Ответ на | Re: Publish autovacuum informations (Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>) |
Ответы |
Re: Publish autovacuum informations
(Michael Paquier <michael.paquier@gmail.com>)
|
Список | pgsql-hackers |
On 03/03/2016 10:54, Kyotaro HORIGUCHI wrote: > Hello, > > At Wed, 2 Mar 2016 17:48:06 -0600, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote in <56D77BB6.6080606@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_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. > ===== > > 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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Alvaro HerreraДата:
Сообщение: Re: Performance improvement for joins where outer side is unique