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
Date: Thu, 17 Sep 2015 15:12:51 -0500
Message-ID: <20150917201251.GA30163@aart.rice.edu> (raw)
In-Reply-To: <CAK17JmnxS6mTNXELZiQGLAhS5vmFcv02cy0+7Qgum5JHeVKODw@mail.gmail.com>
References: <CAK17JmnxS6mTNXELZiQGLAhS5vmFcv02cy0+7Qgum5JHeVKODw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktm@rice.edu, dstibrany@gmail.com
Subject: Re: Occasional Really Slow Running Updates/Inserts
In-Reply-To: <20150917201251.GA30163@aart.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox