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.kehlet@xxxxxxxxx> wrote:
On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes <jeff.janes@xxxxxxxxx> 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.
