Re: Occasional Really Slow Running Updates/Inserts

Thanks Ken, I'll give that a try.

On Thu, Sep 17, 2015 at 4:12 PM, ktm(at)rice(dot)edu <ktm(at)rice(dot)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
>