Date: Thu, 17 Sep 2015 15:12:51 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: Dave Stibrany <dstibrany@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Occasional Really Slow Running Updates/Inserts
Message-ID: <20150917201251.GA30163@aart.rice.edu>
References: 
 <CAK17JmnxS6mTNXELZiQGLAhS5vmFcv02cy0+7Qgum5JHeVKODw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAK17JmnxS6mTNXELZiQGLAhS5vmFcv02cy0+7Qgum5JHeVKODw@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-12-10)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance