On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes <jeff.ja...@gmail.com> wrote:
> Or what I usually do in a case like this is clone the database to a >>> test/QA server then run pg_upgrade to get that running on 9.5, then hope >>> what I learn transfers back to production. >> >> I'll save this great idea. > But the symptoms you describe are exactly what I expect from these clean > up problems, so I would just assume that that is the problem. > > The easiest solution is to turn of fastupdate for that index. Each update > will then be individually slower, but you won't have the periodic lock up > you currently do. > That would be fine and we will try this. > 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. Note that a manual ANALYZE will *not* clear the pending list, it has to be > an autoanalyze. > This is a brain bender, I didn't know there were differences, and this eats away a little bit at my confidence in understand things, but I'll just accept it for now. > (Manual VACUUM will clear the pending list, but you might have trouble > getting manual VACUUM to complete fast enough) > You are exactly right the manual VACUUM is taking forever. >
