autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor

Henry Francisco Garcia Cortez
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




Libre de virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor

Laurenz Albe
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



Reply | Threaded
Open this post in threaded view
|

Re: autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor

Henry Francisco Garcia Cortez
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 (<[hidden email]>) 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




Libre de virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: autovacuum_analyze_scale_factor and autovacuum_analyze_scale_factor

Laurenz Albe
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 (<[hidden email]>) 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