Thread: pg_upgrade vs vacuum_cost_delay

pg_upgrade vs vacuum_cost_delay

From
Magnus Hagander
Date:
Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new cluster? Not talking about the post-analyze script, but when it runs vacuumdb to analyze and freeze before loading the new schema, in prepare_new_cluster()? Those run during downtime, so it seems like you'd want those to run as fast as possible.

--

Re: pg_upgrade vs vacuum_cost_delay

From
Euler Taveira
Date:
On 16-06-2016 09:05, Magnus Hagander wrote:
> Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new
> cluster? Not talking about the post-analyze script, but when it runs
> vacuumdb to analyze and freeze before loading the new schema, in
> prepare_new_cluster()? Those run during downtime, so it seems like you'd
> want those to run as fast as possible.
> 
Doesn't --new-options do the job?


--   Euler Taveira                   Timbira - http://www.timbira.com.br/  PostgreSQL: Consultoria, Desenvolvimento,
Suporte24x7 e Treinamento
 



Re: pg_upgrade vs vacuum_cost_delay

From
Magnus Hagander
Date:
On Thu, Jun 16, 2016 at 4:35 PM, Euler Taveira <euler@timbira.com.br> wrote:
On 16-06-2016 09:05, Magnus Hagander wrote:
> Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new
> cluster? Not talking about the post-analyze script, but when it runs
> vacuumdb to analyze and freeze before loading the new schema, in
> prepare_new_cluster()? Those run during downtime, so it seems like you'd
> want those to run as fast as possible.
>
Doesn't --new-options do the job?

You could, but it seems like it should do it by default. 


--

Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Bruce Momjian
Date:
On Thu, Jun 16, 2016 at 04:45:14PM +0200, Magnus Hagander wrote:
> On Thu, Jun 16, 2016 at 4:35 PM, Euler Taveira <euler@timbira.com.br> wrote:
> 
>     On 16-06-2016 09:05, Magnus Hagander wrote:
>     > Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new
>     > cluster? Not talking about the post-analyze script, but when it runs
>     > vacuumdb to analyze and freeze before loading the new schema, in
>     > prepare_new_cluster()? Those run during downtime, so it seems like you'd
>     > want those to run as fast as possible.
>     >
>     Doesn't --new-options do the job?
> 
> 
> You could, but it seems like it should do it by default. 

Based on this seven year old post, I realized there are minimal
directions in pg_upgrade docs about how to generate statistics quickly,
so I created this patch to help.

We do have docs on updating planner statistics:

    https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-STATISTICS

but that doesn't seem to cover cases where you are doing an upgrade or
pg_dump restore.  Should I move this information into there instead?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

Attachment

Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Magnus Hagander
Date:
On Thu, Nov 23, 2023 at 5:23 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Thu, Jun 16, 2016 at 04:45:14PM +0200, Magnus Hagander wrote:
> > On Thu, Jun 16, 2016 at 4:35 PM, Euler Taveira <euler@timbira.com.br> wrote:
> >
> >     On 16-06-2016 09:05, Magnus Hagander wrote:
> >     > Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new
> >     > cluster? Not talking about the post-analyze script, but when it runs
> >     > vacuumdb to analyze and freeze before loading the new schema, in
> >     > prepare_new_cluster()? Those run during downtime, so it seems like you'd
> >     > want those to run as fast as possible.
> >     >
> >     Doesn't --new-options do the job?
> >
> >
> > You could, but it seems like it should do it by default.
>
> Based on this seven year old post, I realized there are minimal
> directions in pg_upgrade docs about how to generate statistics quickly,
> so I created this patch to help.
>
> We do have docs on updating planner statistics:
>
>         https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-STATISTICS
>
> but that doesn't seem to cover cases where you are doing an upgrade or
> pg_dump restore.  Should I move this information into there instead?

Wow, that's... A while :)

I don't think that final sentence really helps much - for anybody who
doesn't know that functionality well already, it will just be
confusing. At the very least it should be a link that sends you to the
documentation of how that functionality works?

But beyond that, perhaps what we'd really want (now that vacuumdb has
gained more functionality, and is used instead of the custom script
all the way) to add is a parameter --no-cost-delay that would issue a
SET to turn it off for the run?

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/



Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Michael Banck
Date:
Hi,

On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> +     Non-zero values of
> +     <varname>vacuum_cost_delay</varname> will delay statistics generation.

Now I wonder wheter vacuumdb maybe should have an option to explicitly
force vacuum_cost_delay to 0 (I don't think it has?)?


Michael



Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Magnus Hagander
Date:
On Fri, Nov 24, 2023 at 11:21 AM Michael Banck <mbanck@gmx.net> wrote:
>
> Hi,
>
> On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> > +     Non-zero values of
> > +     <varname>vacuum_cost_delay</varname> will delay statistics generation.
>
> Now I wonder wheter vacuumdb maybe should have an option to explicitly
> force vacuum_cost_delay to 0 (I don't think it has?)?

That's exactly what I proposed, isn't it? :)


--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/



Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Michael Banck
Date:
Hi,

On Fri, Nov 24, 2023 at 12:17:56PM +0100, Magnus Hagander wrote:
> On Fri, Nov 24, 2023 at 11:21 AM Michael Banck <mbanck@gmx.net> wrote:
> > On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> > > +     Non-zero values of
> > > +     <varname>vacuum_cost_delay</varname> will delay statistics generation.
> >
> > Now I wonder wheter vacuumdb maybe should have an option to explicitly
> > force vacuum_cost_delay to 0 (I don't think it has?)?
> 
> That's exactly what I proposed, isn't it? :)

You're right, I somehow only saw your mail after I had already sent
mine.

To make up for this, I created a patch that implements our propoals, see
attached.


Michael

Attachment

Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Bruce Momjian
Date:
On Fri, Nov 24, 2023 at 01:10:01PM +0100, Michael Banck wrote:
> Hi,
> 
> On Fri, Nov 24, 2023 at 12:17:56PM +0100, Magnus Hagander wrote:
> > On Fri, Nov 24, 2023 at 11:21 AM Michael Banck <mbanck@gmx.net> wrote:
> > > On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> > > > +     Non-zero values of
> > > > +     <varname>vacuum_cost_delay</varname> will delay statistics generation.
> > >
> > > Now I wonder wheter vacuumdb maybe should have an option to explicitly
> > > force vacuum_cost_delay to 0 (I don't think it has?)?
> > 
> > That's exactly what I proposed, isn't it? :)
> 
> You're right, I somehow only saw your mail after I had already sent
> mine.
> 
> To make up for this, I created a patch that implements our propoals, see
> attached.

This is already posssible with PGOPTIONS, so I don't see the need for
a separate option:

    PGOPTIONS='-c vacuum_cost_delay=99' psql -c 'SHOW vacuum_cost_delay;'
    test
     vacuum_cost_delay
    -------------------
     99ms
    (1 row)

Here is a patch which shows its usage.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

Attachment

Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Magnus Hagander
Date:
On Fri, Nov 24, 2023 at 5:34 PM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Fri, Nov 24, 2023 at 01:10:01PM +0100, Michael Banck wrote:
> > Hi,
> >
> > On Fri, Nov 24, 2023 at 12:17:56PM +0100, Magnus Hagander wrote:
> > > On Fri, Nov 24, 2023 at 11:21 AM Michael Banck <mbanck@gmx.net> wrote:
> > > > On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> > > > > +     Non-zero values of
> > > > > +     <varname>vacuum_cost_delay</varname> will delay statistics generation.
> > > >
> > > > Now I wonder wheter vacuumdb maybe should have an option to explicitly
> > > > force vacuum_cost_delay to 0 (I don't think it has?)?
> > >
> > > That's exactly what I proposed, isn't it? :)
> >
> > You're right, I somehow only saw your mail after I had already sent
> > mine.
> >
> > To make up for this, I created a patch that implements our propoals, see
> > attached.
>
> This is already posssible with PGOPTIONS, so I don't see the need for
> a separate option:
>
>         PGOPTIONS='-c vacuum_cost_delay=99' psql -c 'SHOW vacuum_cost_delay;'
>         test
>          vacuum_cost_delay
>         -------------------
>          99ms
>         (1 row)
>
> Here is a patch which shows its usage.

Given how common this would be I think that's a pretty use-unfriendly
way to do it. I'd vote for still adding it.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/



Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Bruce Momjian
Date:
On Fri, Nov 24, 2023 at 06:20:28PM +0100, Magnus Hagander wrote:
> On Fri, Nov 24, 2023 at 5:34 PM Bruce Momjian <bruce@momjian.us> wrote:
> > On Fri, Nov 24, 2023 at 01:10:01PM +0100, Michael Banck wrote:
> > > You're right, I somehow only saw your mail after I had already sent
> > > mine.
> > >
> > > To make up for this, I created a patch that implements our propoals, see
> > > attached.
> >
> > This is already posssible with PGOPTIONS, so I don't see the need for
> > a separate option:
> >
> >         PGOPTIONS='-c vacuum_cost_delay=99' psql -c 'SHOW vacuum_cost_delay;'
> >         test
> >          vacuum_cost_delay
> >         -------------------
> >          99ms
> >         (1 row)
> >
> > Here is a patch which shows its usage.
> 
> Given how common this would be I think that's a pretty use-unfriendly
> way to do it. I'd vote for still adding it.

Well, the big question is how many people have a non-default
vacuum_cost_delay, since it defaults to zero.  If someone has changed
the default (a small percentage), how many of those will be confused by
PGOPTIONS?  At that point, it seems unnecessary.  Also consider that a
new option will only be useful for those who have non-default
vacuum_cost_delay values, which can also be confusing.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.



Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

From
Bruce Momjian
Date:
On Fri, Nov 24, 2023 at 06:20:28PM +0100, Magnus Hagander wrote:
> On Fri, Nov 24, 2023 at 5:34 PM Bruce Momjian <bruce@momjian.us> wrote:
> >
> > On Fri, Nov 24, 2023 at 01:10:01PM +0100, Michael Banck wrote:
> > > Hi,
> > >
> > > On Fri, Nov 24, 2023 at 12:17:56PM +0100, Magnus Hagander wrote:
> > > > On Fri, Nov 24, 2023 at 11:21 AM Michael Banck <mbanck@gmx.net> wrote:
> > > > > On Wed, Nov 22, 2023 at 11:23:34PM -0500, Bruce Momjian wrote:
> > > > > > +     Non-zero values of
> > > > > > +     <varname>vacuum_cost_delay</varname> will delay statistics generation.
> > > > >
> > > > > Now I wonder wheter vacuumdb maybe should have an option to explicitly
> > > > > force vacuum_cost_delay to 0 (I don't think it has?)?
> > > >
> > > > That's exactly what I proposed, isn't it? :)
> > >
> > > You're right, I somehow only saw your mail after I had already sent
> > > mine.
> > >
> > > To make up for this, I created a patch that implements our propoals, see
> > > attached.
> >
> > This is already posssible with PGOPTIONS, so I don't see the need for
> > a separate option:
> >
> >         PGOPTIONS='-c vacuum_cost_delay=99' psql -c 'SHOW vacuum_cost_delay;'
> >         test
> >          vacuum_cost_delay
> >         -------------------
> >          99ms
> >         (1 row)
> >
> > Here is a patch which shows its usage.
> 
> Given how common this would be I think that's a pretty use-unfriendly
> way to do it. I'd vote for still adding it.

Patch applied to master.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.