Re: Server vacuuming the same table again and again

Hi Ilya!

> Actually, thise two things are tightly bound and there is no chance to avoid vacuum, you can only postpone it, this kind of work eventually supposed to be done.

I understand that autovacuum has to be done, but not right after previous autovacuum? And then again and again.

And after cancelling that first autovacuum I started another one by hand; from there no autovacuum was cancelled.

> ionice autovacuum instead of mission critical ckeckpointer or bgwriter

Yeah, that was desperate. I restarted server when I had a chance - to drop my ionice settings back to defaults.

> Which exact values have you in the following settings:

autovacuum_analyze_scale_factor = 0.1

autovacuum_analyze_threshold = 50

autovacuum_freeze_max_age = 200000000

autovacuum_max_workers = 3

autovacuum_naptime = 60

autovacuum_vacuum_cost_delay = 20

autovacuum_vacuum_cost_limit = -1

autovacuum_vacuum_scale_factor = 0.2

autovacuum_vacuum_threshold = 50

log_autovacuum_min_duration = 0

All defaults except last one I believe.

Minwhile I noticed in the night logs:

checkpoints are occurring too frequently (138 seconds apart)

Consider increasing the configuration parameter "checkpoint_segments".

Increased checkpoint_segments to 256 and reloaded config.

Best regards,
Dmitriy Shalashov

2014-04-25 12:12 GMT+04:00 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>:

Hi Dmitry,

On Fri, Apr 25, 2014 at 9:47 AM, Дмитрий Шалашов <skaurus@gmail.com> wrote:
> cancelled autovacuum and it seems to help.

> In the morning autovacuum was back. And then it finished and I gone to work.

Actually, thise two things are tightly bound and there is no chance to
avoid vacuum, you can only postpone it, this kind of work eventually
supposed to be done.

What you really need to do as a first thing - configure your
autovacuum aggressively enough and then mayde ionice autovacuum
instead of mission critical ckeckpointer or bgwriter.

Which exact values have you in the following settings:

 autovacuum_analyze_scale_factor
 autovacuum_analyze_threshold
 autovacuum_freeze_max_age
 autovacuum_max_workers
 autovacuum_naptime
 autovacuum_vacuum_cost_delay
 autovacuum_vacuum_cost_limit
 autovacuum_vacuum_scale_factor
 autovacuum_vacuum_threshold
 log_autovacuum_min_duration

?

Best regards, Ilya
>
> Best regards,
> Dmitriy Shalashov



--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com