Re: how to investigate GIN fast updates and cleanup cycles? - Mailing list pgsql-general

From Steve Kehlet
Subject Re: how to investigate GIN fast updates and cleanup cycles?
Date
Msg-id CA+bfosHR=D2+SyNF-gxHz-WjieUd9T_kBqQ+TvgE+_13NLqZZA@mail.gmail.com
Whole thread Raw
In response to Re: how to investigate GIN fast updates and cleanup cycles?  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: how to investigate GIN fast updates and cleanup cycles?
List pgsql-general
On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes <jeff.janes@gmail.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. 

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: wal files stay in the pg_xlog dir
Next
From: kingl
Date:
Subject: Re: wal files stay in the pg_xlog dir