Re: Server vacuuming the same table again and again - Mailing list pgsql-performance

Dmitry,

How is you filesystem under database exactly mount? (mount -l) And
just in case, while increasing checkpoint_segments, better to increase
checkpoint_timeout, otherwise all checkpoints will be still frequent
because segment threshold will be never reached. You could monitor
your pg_stat_bgwriter to understand which type of checkpoint happens
more frequent.

On Fri, Apr 25, 2014 at 10:22 AM, Дмитрий Шалашов <skaurus@gmail.com> wrote:
> I understand that autovacuum has to be done, but not right after previous
> autovacuum? And then again and again.

That is exactly what happen: your autovacuum is not aggresive enough
and that is why it runs constantly instead of doing it s job by small
portions.

you should try something like this:

 autovacuum                          | on
 autovacuum_analyze_scale_factor     | 0.05
 autovacuum_analyze_threshold        | 5
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 10 # set 10 for example and
then you could see - if they all working constantly, maybe you need
more. or less if not.
 autovacuum_multixact_freeze_max_age | 400000000
 autovacuum_naptime                  | 1
 autovacuum_vacuum_cost_delay        | 10
 autovacuum_vacuum_cost_limit        | -1
 autovacuum_vacuum_scale_factor      | 0.01
 autovacuum_vacuum_threshold         | 10
 log_autovacuum_min_duration         | -1


Best regards, Ilya

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



--
Ilya Kosmodemiansky,

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