Re: maintenance_work_mem and autovacuum

Why do we have separate parameters for autovacuum and vacuum, except for
maintenance_work_mem?

Should we also have autovacuum_work_mem?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Thu, Mar 26, 2009 at 7:34 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Why do we have separate parameters for autovacuum and vacuum, except for
> maintenance_work_mem?
>
> Should we also have autovacuum_work_mem?

We already discussed it here:
http://archives.postgresql.org/message-id/49353A69.20001@hagander.net

It resulted in a doc patch - not sure it's sufficient but it's
interesting to read this thread before discussing further.

--
Guillaume

On Thu, 2009-03-26 at 19:46 +0100, Guillaume Smet wrote:
> On Thu, Mar 26, 2009 at 7:34 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > Why do we have separate parameters for autovacuum and vacuum, except for
> > maintenance_work_mem?
> >
> > Should we also have autovacuum_work_mem?
>
> We already discussed it here:
> http://archives.postgresql.org/message-id/49353A69.20001@hagander.net
>
> It resulted in a doc patch - not sure it's sufficient but it's
> interesting to read this thread before discussing further.

Hmmm, OK, read that, thanks. Must have missed that thread earlier.

Tom was asking for evidence of a need for them to be different. I don't
see it as a case that requires performance results.

I agree with Magnus' original reasoning: we can have more than one
autovacuum process, so we may have autovacuum_max_workers active and so
the work mem they use must be smaller. For maintenance_work_mem we would
typically only have one session using it at any time, so we either have
to start hardcoding the value in scripts or accept the fact it has been
set lower.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> I agree with Magnus' original reasoning: we can have more than one
> autovacuum process, so we may have autovacuum_max_workers active and so
> the work mem they use must be smaller. For maintenance_work_mem we would
> typically only have one session using it at any time, so we either have
> to start hardcoding the value in scripts or accept the fact it has been
> set lower.

I actually have a client who does both automated and manual vacuums.
Having two settings would definitely be convenient for them.

That said, it would be unnecessary if I could use ROLES to set
parameters more reliably ....

;-)

--Josh

On Thu, 2009-03-26 at 13:43 -0700, Josh Berkus wrote:
> > I agree with Magnus' original reasoning: we can have more than one
> > autovacuum process, so we may have autovacuum_max_workers active and so
> > the work mem they use must be smaller. For maintenance_work_mem we would
> > typically only have one session using it at any time, so we either have
> > to start hardcoding the value in scripts or accept the fact it has been
> > set lower.
>
> I actually have a client who does both automated and manual vacuums.
> Having two settings would definitely be convenient for them.
>
> That said, it would be unnecessary if I could use ROLES to set
> parameters more reliably ....

Hmmm, perhaps the right way to do this is to have a user called
"autovacuum" that is used to perform autovacuums.

That way we can actually get rid of a few autovacuum_* parameters
without losing function, and yet add the capability to change
maintenance_work_mem just for autovacuum. Avoid some special case code
also, like setting of zero_damaged_pages.

Seems like a nice small change for 8.4?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon,

> Hmmm, perhaps the right way to do this is to have a user called
> "autovacuum" that is used to perform autovacuums.

This makes sense, depending on which autovac params actually get picked
up from the session.

> Seems like a nice small change for 8.4?

Hmmm. Maybe not small enough.

--Josh

--On Donnerstag, März 26, 2009 13:43:45 -0700 Josh Berkus
<josh(at)agliodbs(dot)com> wrote:

> I actually have a client who does both automated and manual vacuums.
> Having two settings would definitely be convenient for them.

I often found people doing this running within a) their own superuser with
special GUCs set or b) using their own scripts which are setting
maintenance_work_mem accordingly.

Often people are very confused about the number of *_vaccum_* settings
already present: they sometimes have a really hard time to learn the
difference between manual and auto vaccum and the interaction of
default/inherited settings.

That said, having a separate setting would be consequent, but it seems to
me it's enough there to satisfy all needs, isn't it?

--
Thanks

Bernd

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Thu, 2009-03-26 at 13:43 -0700, Josh Berkus wrote:
>> That said, it would be unnecessary if I could use ROLES to set
>> parameters more reliably ....

> Hmmm, perhaps the right way to do this is to have a user called
> "autovacuum" that is used to perform autovacuums.

Interesting idea, but maybe we should wait on it until we see what
(if any) changes we are going to put into the relationship between
roles and parameters. Josh seems not to be happy with that ...

regards, tom lane

On 3/26/09 4:10 PM, Tom Lane wrote:
> Simon Riggs<simon(at)2ndQuadrant(dot)com> writes:
>> On Thu, 2009-03-26 at 13:43 -0700, Josh Berkus wrote:
>>> That said, it would be unnecessary if I could use ROLES to set
>>> parameters more reliably ....
>
>> Hmmm, perhaps the right way to do this is to have a user called
>> "autovacuum" that is used to perform autovacuums.
>
> Interesting idea, but maybe we should wait on it until we see what
> (if any) changes we are going to put into the relationship between
> roles and parameters. Josh seems not to be happy with that ...

Well, it would work at present for this, because it's a login role,
presumably.

--Josh