Thread: pg_upgrade vs vacuum_cost_delay
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.
--
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
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.
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
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/
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
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/
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
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
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/
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.
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.