From: | Дмитрий Шалашов <skaurus(at)gmail(dot)com> |
---|---|
To: | ik(at)postgresql-consulting(dot)com |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Server vacuuming the same table again and again |
Date: | 2014-04-25 08:22:45 |
Message-ID: | CAKPeCUECiXxsNnJzejH4xqzR5NF2_Pa1L3t1tWVR=ROw0feYSg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)kosmodemiansky(at)postgresql-consulting(dot)com>:
> Hi Dmitry,
>
> On Fri, Apr 25, 2014 at 9:47 AM, Дмитрий Шалашов <skaurus(at)gmail(dot)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(at)postgresql-consulting(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Дмитрий Шалашов | 2014-04-25 08:29:02 | Re: Server vacuuming the same table again and again |
Previous Message | Ilya Kosmodemiansky | 2014-04-25 08:12:29 | Re: Server vacuuming the same table again and again |