Re: Publish autovacuum informations - Mailing list pgsql-hackers

On Tue, Mar 1, 2016 at 8:44 AM, Julien Rouhaud <julien.rouhaud@dalibo.com> wrote:
>
> On 01/03/2016 07:50, Michael Paquier wrote:
> > On Tue, Mar 1, 2016 at 4:38 AM, Julien Rouhaud
> > <julien.rouhaud@dalibo.com> wrote:
> >> On 29/02/2016 20:20, Fabrízio de Royes Mello wrote:
> >>>
> >>> On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud
> >>> <julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:
> >>>>
> >>>> On 04/06/2015 22:10, Guillaume Lelarge wrote:
> >>>>> 2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
> >>> <mailto:guillaume@lelarge.info>
> >>>>> <mailto:guillaume@lelarge.info <mailto:guillaume@lelarge.info>>>:
> >>>>>
> >>>>>     2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
> >>> <mailto:robertmhaas@gmail.com>
> >>>>>     <mailto:robertmhaas@gmail.com <mailto:robertmhaas@gmail.com>>>:
> >>>>>
> >>>>>         On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane
> >>> <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
> >>>>>         <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
> >>>>>         > I'd be all right with putting the data structure
> >>> declarations in a file
> >>>>>         > named something like autovacuum_private.h, especially if
> >>> it carried an
> >>>>>         > annotation that "if you depend on this, don't be surprised
> >>> if we break
> >>>>>         > your code in future".
> >>>>>
> >>>>>         Works for me.  I am not in general surprised when we do
> >>> things that
> >>>>>         break my code, or anyway, the code that I'm responsible for
> >>>>>         maintaining.  But I think it makes sense to segregate this
> >>> into a
> >>>>>         separate header file so that we are clear that it is only
> >>>>>         exposed for
> >>>>>         the benefit of extension authors, not so that other things in
> >>>>>         the core
> >>>>>         system can touch it.
> >>>>>
> >>>>>
> >>>>>     I'm fine with that too. I'll try to find some time to work on that.
> >>>>>
> >>>>>
> >>>>> So I took a look at this this week. I discovered, with the help of a
> >>>>> coworker, that I can already use the AutoVacuumShmem pointer and read
> >>>>> the struct. Unfortunately, it doesn't give me as much details as I would
> >>>>> have liked. The list of databases and tables aren't in shared memory.
> >>>>> They are local to the process that uses them. Putting them in shared
> >>>>> memory (if at all possible) would imply a much bigger patch than I was
> >>>>> willing to write right now.
> >>>>>
> >>>>> Thanks anyway for the help.
> >>>>>
> >>>>>
> >>>>
> >>>> Sorry to revive such an old thread.
> >>>>
> >>>> I think some hooks in the autovacuum could be enough to have good
> >>>> insight without exposing private structure.
> >
> > Instead of introducing 4 new hooks, which do not represent a general
> > use actually, why don't you expose a portion of this information in
> > shared memory as mentioned upthread? This sounds like a good approach
> > to me. Your extension could then scan them as needed and put that on
> > view or a function. This information is now private in the autovacuum
> > processes, exposing them would allow plugin authors to do a bunch of
> > fancy things I think, in a more flexible way than those hooks. And
> > there is no need to add more hooks should the structure of the
> > autovacuum code change for a reason or another in the future.
> >
>
> I thought exposing private structures could be a blocking issue.  I
> tried to see what could be done using hooks, and one thing I like is
> that we can compute the process time of each relation, or even aggregate
> some statistics.  Having the vacuum time is something that we can
> actually only obtain by setting log_autovacuum_min_duration and parsing
> the logs, and I don't think it would be possible to do this by just
> exposing current private structure.
>

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello