pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor
4+ messages / 2 participants
[nested] [flat]
* autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor
@ 2021-01-06 17:05 Henry Francisco Garcia Cortez <garcortez@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Henry Francisco Garcia Cortez @ 2021-01-06 17:05 UTC (permalink / raw)
To: pgsql-admin
Hi community I have a question why settings default postgresql has
autovacuum_vacuum_scale_factor in 0.2 is higher than
autovacuum_analyze_scale_factor 0.1, so If want to tune these parameters, I
always must leave autovacuum_vacuum_scale_factor higher than
autovacuum_analyze_scale_factor, for instance I wanted to leave
autovacuum_vacuum_scale_factor in 0.002 and autovacuum_analyze_scale_factor
in 0.001
--
Ing. Henry G. Cortez
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor
@ 2021-01-07 01:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Henry Francisco Garcia Cortez <garcortez@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2021-01-07 01:53 UTC (permalink / raw)
To: Henry Francisco Garcia Cortez <garcortez@gmail.com>; pgsql-admin
On Wed, 2021-01-06 at 11:05 -0600, Henry Francisco Garcia Cortez wrote:
> I have a question why settings default postgresql has autovacuum_vacuum_scale_factor in 0.2
> is higher than autovacuum_analyze_scale_factor 0.1, so If want to tune these parameters,
> I always must leave autovacuum_vacuum_scale_factor higher than autovacuum_analyze_scale_factor,
> for instance I wanted to leave autovacuum_vacuum_scale_factor in 0.002 and
> autovacuum_analyze_scale_factor in 0.001
The autovacuum scale factor has been twice the autoanalyze scale factor since the
beginning. You could search the archives from around 2004 for a reason, but I'd
say it might have to do with the fact that ANALYZE is much cheaper than VACUUM,
and that the impact of bad statistics is usually more severe than the impact of
some dead tuples.
But there is no need for you to keep it that way when you change the parameters.
For example, if you want to speed up index only scans, you may want to set
"autovacuum_vacuum_scale_factor" to something like 0.01, but leave
"autovacuum_analyze_scale_factor" at the default value.
The values that you suggest seem much too low - that will result in autovacuum
running all the time.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor
@ 2021-01-07 16:56 Henry Francisco Garcia Cortez <garcortez@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Henry Francisco Garcia Cortez @ 2021-01-07 16:56 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin
Thanks for the information Laurenz Albe I have another question what will
be effect if there are autovacuums running all the time, what will be
settings you could suggest me for autovacuum_vacuum_scale_factor and
autovacuum_analyze_scale_factor
El mié, 6 ene 2021 a las 19:53, Laurenz Albe (<laurenz.albe@cybertec.at>)
escribió:
> On Wed, 2021-01-06 at 11:05 -0600, Henry Francisco Garcia Cortez wrote:
> > I have a question why settings default postgresql has
> autovacuum_vacuum_scale_factor in 0.2
> > is higher than autovacuum_analyze_scale_factor 0.1, so If want to tune
> these parameters,
> > I always must leave autovacuum_vacuum_scale_factor higher than
> autovacuum_analyze_scale_factor,
> > for instance I wanted to leave autovacuum_vacuum_scale_factor in 0.002
> and
> > autovacuum_analyze_scale_factor in 0.001
>
> The autovacuum scale factor has been twice the autoanalyze scale factor
> since the
> beginning. You could search the archives from around 2004 for a reason,
> but I'd
> say it might have to do with the fact that ANALYZE is much cheaper than
> VACUUM,
> and that the impact of bad statistics is usually more severe than the
> impact of
> some dead tuples.
>
> But there is no need for you to keep it that way when you change the
> parameters.
> For example, if you want to speed up index only scans, you may want to set
> "autovacuum_vacuum_scale_factor" to something like 0.01, but leave
> "autovacuum_analyze_scale_factor" at the default value.
>
> The values that you suggest seem much too low - that will result in
> autovacuum
> running all the time.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
--
Ing. Henry G. Cortez
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor
@ 2021-01-08 09:04 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Henry Francisco Garcia Cortez <garcortez@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2021-01-08 09:04 UTC (permalink / raw)
To: Henry Francisco Garcia Cortez <garcortez@gmail.com>; +Cc: pgsql-admin
On Thu, 2021-01-07 at 10:56 -0600, Henry Francisco Garcia Cortez wrote:
> El mié, 6 ene 2021 a las 19:53, Laurenz Albe (<laurenz.albe@cybertec.at>) escribió:
>
> > On Wed, 2021-01-06 at 11:05 -0600, Henry Francisco Garcia Cortez wrote:
> > > I have a question why settings default postgresql has autovacuum_vacuum_scale_factor in 0.2
> > > is higher than autovacuum_analyze_scale_factor 0.1, so If want to tune these parameters,
> > > I always must leave autovacuum_vacuum_scale_factor higher than autovacuum_analyze_scale_factor,
> > > for instance I wanted to leave autovacuum_vacuum_scale_factor in 0.002 and
> > > autovacuum_analyze_scale_factor in 0.001
> >
> > The autovacuum scale factor has been twice the autoanalyze scale factor since the
> > beginning. You could search the archives from around 2004 for a reason, but I'd
> > say it might have to do with the fact that ANALYZE is much cheaper than VACUUM,
> > and that the impact of bad statistics is usually more severe than the impact of
> > some dead tuples.
> >
> > But there is no need for you to keep it that way when you change the parameters.
> > For example, if you want to speed up index only scans, you may want to set
> > "autovacuum_vacuum_scale_factor" to something like 0.01, but leave
> > "autovacuum_analyze_scale_factor" at the default value.
> >
> > The values that you suggest seem much too low - that will result in autovacuum
> > running all the time.
>
> Thanks for the information Laurenz Albe I have another question what will be effect
> if there are autovacuums running all the time, what will be settings you could suggest me for
> autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor
Unless there are indications to choose different values, stick with the default
values of 0.1 and 0.2.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-01-08 09:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-06 17:05 autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor Henry Francisco Garcia Cortez <garcortez@gmail.com>
2021-01-07 01:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-07 16:56 ` Henry Francisco Garcia Cortez <garcortez@gmail.com>
2021-01-08 09:04 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox