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

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

> On Fri, Aug 28, 2015 at 12:10 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> Did you change the system-wide autovacuum_analyze_scale_factor? If so,
>> don't do that. You can use a table's storage parameters to set a custom
>> autovacuum_analyze_scale_factor just for individual tables. So just the
>> table with the troublesome gin index:
>>
>
> No I did it just to the problematic table:
>
> ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor=0);
> ALTER TABLE my_table SET (autovacuum_analyze_scale_factor=0);
>

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.

>
> My system-wide autovacuum_analyze_threshold is 50 so I think that's ok.
>

autovacuum_analyze_scale_factor is the more important one. Only when that
is close to zero does autovacuum_analyze_threshold matter very much.

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

Cheers,

Jeff