Re: Occasional Really Slow Running Updates/Inserts

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