Planning to change autovacuum_vacuum_scale_factor value to zero. Please suggest me if any negative impact.

Raghavendra Rao J S V wrote:

We are using postgres 9.2 version on Centos operating system. We have around 1300+ tables.
We have following auto vacuum settings are enables. Still few of the tables which are always busy are not vacuumed. Due to that tables are bloating and observed few areas has performance degradation.

autovacuum_max_workers = 6
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 25
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

To avoid the above problem, I am planning to make ' autovacuum_vacuum_scale_factor' value to zero and autovacuum_vacuum_threshold value to 150. Please suggest me does it have any negative impact.

That's an excellent way to keep your database from functioning well.

Rather, raise autovacuum_vacuum_cost_limit, or, more aggressively,
set autovacuum_vacuum_cost_delay to 0.

It is better to change the settings on individual busy tables than
changing them globally.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Thank you very much for your prompt response.

I requested in my previous mail as , planning to make '
*autovacuum_vacuum_scale_factor*' value to *zero *and
*autovacuum_vacuum_threshold
*value to *150 * in postgreconf file.

Are you suggesting me to keep "autovacuum_vacuum_cost_limit" to zero or "
autovacuum_vacuum_scale_factor" to zero or both? Please clarify me.

Regards,
Raghavendra Rao

On Wed, Apr 11, 2018 at 12:59 PM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Raghavendra Rao J S V wrote:

We are using postgres 9.2 version on Centos operating system. We have

around 1300+ tables.

We have following auto vacuum settings are enables. Still few of the

tables which are always busy are not vacuumed. Due to that tables are
bloating and observed few areas has performance degradation.

autovacuum_max_workers = 6
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 25
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

To avoid the above problem, I am planning to make '

autovacuum_vacuum_scale_factor' value to zero and
autovacuum_vacuum_threshold value to 150. Please suggest me does it have
any negative impact.

That's an excellent way to keep your database from functioning well.

Rather, raise autovacuum_vacuum_cost_limit, or, more aggressively,
set autovacuum_vacuum_cost_delay to 0.

It is better to change the settings on individual busy tables than
changing them globally.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Raghavendra Rao J S V wrote:

Are you suggesting me to keep "autovacuum_vacuum_cost_limit" to zero or
" autovacuum_vacuum_scale_factor" to zero or both? Please clarify me.

autovacuum_vacuum_cost_limit is effectively 100 by default.
You could raise it to 2000 or more.

If that does not take care of your problems, set autovacuum_vacuum_cost_delay to 0.

That will make autovacuum even more aggressive.

Do *not* set autovacuum_vacuum_scale_factor to 0. That will just cause autovacuum
to run all the time without getting anywhere. Sorry for trying to use irony in
e-mail; I am aware I shouldn't do that.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com