Thanks Ken, I'll give that a try. On Thu, Sep 17, 2015 at 4:12 PM, k...@rice.edu <k...@rice.edu> wrote:
> On Thu, Sep 17, 2015 at 03:14:43PM -0400, Dave Stibrany wrote: > > Hi all. I am occasionally seeing really slow update/inserts into a fairly > > large table. By really slow I mean around 10-40 seconds, > > while the majority of queries take milliseconds. I cannot reproduce this > > problem myself, but it is occurring multiple times a day > > (maybe 30 times). > > > > System Info > > --------------- > > Model: Dell PowerEdge R420 > > CPU: 12 core Intel(R) Xeon(R) @ 2.20GHz > > Memory: 16GB > > Disk: PERC H310 Mini Raid Controller using Raid 1 > > OS: Ubuntu 14.04.3 LTS > > > > DB Settings > > ---------------- > > ... a lot of information deleted... > > Hi Dave, > > This search index is almost certainly the cause of your slowdowns: > > > Indexes: > > "document_search_ix" gin (contents_search) > > We observed similar odd slowdowns with a GIN text search index. We > had to disable the 'fastupdate' option for the index to stop the large > pauses by the index entry clean-up processing. There have been some > recent patches to address the penalty problem caused by the fastupdate > processing. > > > What I haven't tried > > -------------------- > > - more aggressive auto-vacuum > > - trying gist table for full text search index instead of gin > > - removing full text search altogether (are users don't use it very much) > > Nope, keep using GIN. GIST is too slow for this usage. Just disable the > 'fastupdate' on the index: > > ALTER INDEX document_search_ix SET (fastupdate = off); > > Regards, > Ken >
