Re: Publish autovacuum informations
От | Julien Rouhaud |
---|---|
Тема | Re: Publish autovacuum informations |
Дата | |
Msg-id | 56D7196D.1060504@dalibo.com обсуждение исходный текст |
Ответ на | Re: Publish autovacuum informations (Michael Paquier <michael.paquier@gmail.com>) |
Ответы |
Re: Publish autovacuum informations
|
Список | pgsql-hackers |
On 02/03/2016 07:30, Michael Paquier wrote: > On Tue, Mar 1, 2016 at 11:37 PM, Julien Rouhaud wrote: >> I'm not sure what are the fancy things that Michael had in mind with >> exposing the private structure. Michael, was it something like having >> the ability to change some of these data through an extension? > > I was referring to you here :) > I have witnessed already many fancy things coming out of brain, and I > have no doubt you could make something out of just a sampling of data. :D > Jokes apart, what I mean with fancy things here is putting the > sampling data in a shape that a user suits to him. It would be easy to > exploit that for example in a background worker that scans > periodically the shared memory, or have an extension that represents > this shared memory data into something that can be queried at SQL > level. Now, the main use case that I have with the data available in > shared memory is more or less: > - represent the current shared memory as SQL > - Scan this data, reshape it and report it elsewhere, say a background > worker printing out a file. > > Now, take your set of hooks... There are 4 hooks here: > - autovacuum_list_tables_hook, to do something with the list of tables > a worker has collected, at the moment they have been collected. > - autovacuum_end_table_hook, to do something when knowing that a table > is skipped or cancelled > - autovacuum_begin_table_hook, to trigger something when a relation is > beginning to be processed. > - autovacuum_database_finished_hook, to trigger something once a > database is done with its processing. > > The only use cases that I have in mind here for those hooks, which > would help in decision-making to tune autovacuum-related parameters > would be the following: > - Log entries regarding those operations, then why not introducing a > GUC parameter that is an on/off switch, like log_autovacuum (this is > not a per-relation parameter), defaulting to off. Those could be used > by pgbadger. This would be nice, but the point if this proposal is to be able to have this available at SQL level. (but big +1 on the feature) > - Have system statistics with a new system relation like > pg_stat_autovacuum, and make this information available to user. > Are there other things that you think could make use those hooks? Your > extension just does pg_stat_autovacuum and emulates the existing > pg_stat_* facility when gathering information about the global > autovacuum statistics. So it seems to me that those hooks are not that > necessary, and that this may help in tuning a system, particularly the > number of relations skipped would be interesting to have. > > The stats could have a delay, the point being to have hints on how > autovacuum workers are sorting things out. In short, I am doubtful > about the need of hooks in those code paths, the thing that we should > try to do instead is to improve native solutions to give user more > information regarding how autovacuum works, which help in tuning it. > 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. -- Julien Rouhaud http://dalibo.com - http://dalibo.org
В списке pgsql-hackers по дате отправления: