Re: how to investigate GIN fast updates and cleanup cycles?

I wanted to follow up and report we altered the index in question and
turned off fastupdate and we have not seen the issue I originally reported
since. Just for the record I did:

ALTER INDEX my_index SET (fastupdate=off);
SET vacuum_cost_delay=0;
VACUUM VERBOSE my_table_with_that_index;

We looked into the performance impact on the inserts/updates and decided it
was better for us to take the tiny hit on them and thus avoid these
occasional blockages caused by the pending list cleanup cycle.

Thank you Jeff and Tom for the help! I have a number of todos still from
this too, including adjusting our autovacuum settings and looking into
improving that blocked/blocking query.

On Fri, Aug 28, 2015 at 1:44 PM Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com> wrote:

> On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> You should RESET the autovacuum_vacuum_scale_factor for the table. You
>> don't want it to be vacuumed aggressively, just autoanalyzed aggressively.
>> Sorry if my copy-paste error led you astray on that.
>>
>
> No problem, done, thank you.
>
> There is a bulk load going on right now so a lot of tables are needing
>>> vacuuming. I really need to increase my autovacuum_max_workers.
>>>
>>
>> But those workers all share the same IO throttling amongst themselves.
>> Increasing it mostly just gives you more workers all working more slowly.
>>
>
> Ah, you're right, that won't help.
>
>
>> Assuming your IO subsystem can handle it, you are better off lowering
>> autovacuum_vacuum_cost_delay, which can be done without a server restart
>> (although the change won't take full effect until the existing workers go
>> away and restart). I also set vacuum_cost_page_hit and
>> vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty
>> to do the throttling.
>>
>
> Thank you for these great suggestions, I will play with them.
>
>>