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

From Jeff Janes
Subject Re: how to investigate GIN fast updates and cleanup cycles?
Date
Msg-id CAMkU=1yQkqOk6oV_neFJZFTGRBp7as4AQ9ej-XEQ0ajJasCnog@mail.gmail.com
Whole thread Raw
In response to Re: how to investigate GIN fast updates and cleanup cycles?  (Steve Kehlet <steve.kehlet@gmail.com>)
Responses Re: how to investigate GIN fast updates and cleanup cycles?
List pgsql-general
On Fri, Aug 28, 2015 at 11:06 AM, Steve Kehlet <steve.kehlet@gmail.com> wrote:
On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes <jeff.janes@gmail.com> wrote:
  
Vacuum is overkill (and can be extremely slow to run a large gin index), you just need to get it to autoanalyze by changing the per-table setting of "autovacuum_vacuum_scale_factor" to zero and instead using

Did you mean autovacuum_analyze_scale_factor or does it not matter?

I'm trying to force an autovacuum/autoanalyze this way but unfortunately for me I have autovacuum_max_workers at the default of 3 and there are apparently many tables in line for autovacuuming in front of the table I want :-(. I'm playing whack-a-mole killing them and hoping the table I want will come up.

I overlooked the significance of this paragraph the first time I responded.  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:

alter table some_table_with_gin_index set (autovacuum_analyze_scale_factor =0);
alter table some_table_with_gin_index set (autovacuum_analyze_threshold =100);
 
Killing existing vacuums won't help (at least not until change your system wide setting back and so the above instead) because it will likely just restart on the same table it was on when you killed it.

Cheers,

Jeff

pgsql-general by date:

Previous
From: Dominik Czarnota
Date:
Subject: Re: Does PLPythonU support COPY table FROM string?
Next
From: Steve Kehlet
Date:
Subject: Re: how to investigate GIN fast updates and cleanup cycles?