Re: Publish autovacuum informations

On 01/03/2016 13:47, Fabrízio de Royes Mello wrote:
> On Tue, Mar 1, 2016 at 8:44 AM, Julien Rouhaud
> <julien(dot)rouhaud(at)dalibo(dot)com <mailto:julien(dot)rouhaud(at)dalibo(dot)com>> wrote:
>>
>> On 01/03/2016 07:50, Michael Paquier wrote:
>> > On Tue, Mar 1, 2016 at 4:38 AM, Julien Rouhaud
>> > <julien(dot)rouhaud(at)dalibo(dot)com <mailto:julien(dot)rouhaud(at)dalibo(dot)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(dot)rouhaud(at)dalibo(dot)com <mailto:julien(dot)rouhaud(at)dalibo(dot)com>
> <mailto:julien(dot)rouhaud(at)dalibo(dot)com <mailto:julien(dot)rouhaud(at)dalibo(dot)com>>>
> wrote:
>> >>>>
>> >>>> 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.
>>
>
> We understood (IMHO is an interesting idea) but as Michael said hooks is
> for a general purpose. So can you demonstrate other use cases for this
> new hooks?
>

I can think of several usage. First, since the hook will always be
called, an extension will see all the activity a worker is doing when
exposing private structure will always be some kind of sampling. Then,
you can have other information that wouldn't be available just by
exposing private structure. For instance knowing a VACUUM isn't
performed by the worker (either because another worker is already
working on it or because it isn't needed anymore). IIRC there was a
discussion about concurrency issue in this case. We can also know if the
maintenance was cancelled due to lock not obtained fast enough.
Finally, as long as the hooks aren't use, they don't have any overhead.
I agree that all this is for monitoring purpose.

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?

> 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

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org