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:29:02 |
Message-ID: | CAKPeCUGoq9LybB=KDaE8uHP0FcbCbR2L4yCSS+YwtqjFKNpUXQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
And right now we have a new kind of problem.
Previously during load disk was 100% busy; now we have around 100 active
state queries, 100% loaded proc, but disk is virtually idle... Normally we
have under 10 active queries.
Any hints on that?
Best regards,
Dmitriy Shalashov
2014-04-25 12:22 GMT+04:00 Дмитрий Шалашов <skaurus(at)gmail(dot)com>:
> 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 | Mehdi Ravanbakhsh | 2014-04-25 08:48:05 | pl/pgsql performance |
Previous Message | Дмитрий Шалашов | 2014-04-25 08:22:45 | Re: Server vacuuming the same table again and again |